Asp.net操作Excel（终极方法NPOI）(转)

2021-08-27 19:13:05

    先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。

    另：http://files.cnblogs.com/zhongxinWang/NPOI.rar

一：将数据导出到excel

    List<>作为数据源

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("降雨量日报表");
//获取list数据
List<ST_RainInfo_Day> listRainInfo = ST_RainInfo_Day_Helper.ObjectList(dtBeginDate, dtEndDate);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("站名");
row1.CreateCell(1).SetCellValue("河名");
row1.CreateCell(2).SetCellValue("地址");
row1.CreateCell(3).SetCellValue("日雨量");
row1.CreateCell(4).SetCellValue("时间");
row1.CreateCell(5).SetCellValue("天气");
//将数据逐步写入sheet1各个行
   for (int i = 0; i < listRainInfo.Count; i++)
   {
       NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
       rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].STNM.ToString());
       rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].RVNM.ToString());
       rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].STLC.ToString());
       rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].DYP.ToString());
       rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].TM.ToString());
       //用GetWether方法进行数据转换
       rowtemp.CreateCell(5).SetCellValue(GetWether(listRainInfo[i].WTH.ToString()));
    }
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();

　　DataTable作为数据源

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DataTable dt = GetData();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("特征值报表");//雨情
 
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("站号");
row1.CreateCell(1).SetCellValue("站名");
row1.CreateCell(2).SetCellValue("平均降雨(mm)");
row1.CreateCell(3).SetCellValue("总降雨量(mm)");
row1.CreateCell(4).SetCellValue("最大测站降雨(mm)");
row1.CreateCell(5).SetCellValue("最小测站降雨(mm)");
  for (int i = 0; i < dt.Rows.Count; i++)
  {
     NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
     rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["STCD"].ToString());
     rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["STNM"].ToString());
     rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["AvgDYP"].ToString());
     rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["SumDYP"].ToString());
     rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["MaxDYP"].ToString());
     rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["MinDYP"].ToString());
  }
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();

　　

 

二：从Excel中导入数据到

    可先将excel文件上传到服务器的临时位置，获取filePath，然后再行读取、导入。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
HSSFWorkbook hssfworkbook; 
#region 
public DataTable ImportExcelFile(string filePath) 
{ 
    #region//初始化信息 
    try 
    { 
        using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 
        { 
            hssfworkbook = new HSSFWorkbook(file); 
        } 
    } 
    catch (Exception e) 
    { 
        throw e; 
    } 
    #endregion 
   
    NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0); 
    System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 
    DataTable dt = new DataTable();       //给DdataTable添加表头
    for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) 
    { 
        dt.Columns.Add(Convert.ToChar(((int)‘A‘) + j).ToString()); 
    }     //读取数据 
    while (rows.MoveNext()) 
    { 
        HSSFRow row = (HSSFRow)rows.Current; 
        DataRow dr = dt.NewRow(); 
        for (int i = 0; i < row.LastCellNum; i++) 
        { 
            NPOI.SS.UserModel.Cell cell = row.GetCell(i); 
            if (cell == null) 
            { 
                dr[i] = null; 
            } 
            else 
            { 
                dr[i] = cell.ToString(); 
            } 
        } 
        dt.Rows.Add(dr); 
    } 
    return dt; 
} 
#endregion

　　

三：大于65535条记录

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
//创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("sheet1");
 
            //sheet列表,防止记录条数大于65535
            List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>();
            sheetList.Add(sheet1);
//给sheet1添加数据
            SheetFirst(sheet1, book, datalist);
            //给其他sheet添加数据   从1开始：去掉第一个sheet   +2是有表头和标题的那2行
            int rows = datalist.Count + 2;
            int p = rows % 65535 == 0 ? rows / 65535 : (rows / 65535) + 1;
            for (int i = 1; i < p; i++)
            {
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet" + (i + 1).ToString());
                //为sheet添加数据
                SheetElse(sheet, (i - 1) * 65535 + 65533, datalist);
            }
 
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
//第一个Sheet
        protected void SheetFirst(NPOI.SS.UserModel.ISheet sheet1, NPOI.HSSF.UserModel.HSSFWorkbook book, List<ST_WaterLevel_GradualMonth> datalist)
        {
            //标题
            NPOI.SS.UserModel.ICell cellTitle = sheet1.CreateRow(0).CreateCell(0);
            cellTitle.SetCellValue("水位月报表--" + drpCategory.SelectedItem.Text);
            //设置标题行样式
            NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            NPOI.SS.UserModel.IFont font = book.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cellTitle.CellStyle = style;
            //合并标题行
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
 
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(1);
            row1.CreateCell(0).SetCellValue("序号");
            row1.CreateCell(1).SetCellValue("测站编码");
            row1.CreateCell(2).SetCellValue("测站名称");
            row1.CreateCell(3).SetCellValue("河流名称");
            row1.CreateCell(4).SetCellValue("日期");
            row1.CreateCell(5).SetCellValue("平均水位");
            row1.CreateCell(6).SetCellValue("八点水位");
            row1.CreateCell(7).SetCellValue("零点水位");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < datalist.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 2);
                rowtemp.CreateCell(0).SetCellValue(datalist[i].Row.ToString());
                rowtemp.CreateCell(1).SetCellValue(datalist[i].STCD.ToString());
                rowtemp.CreateCell(2).SetCellValue(datalist[i].STNM.ToString());
                rowtemp.CreateCell(3).SetCellValue(datalist[i].RVNM.ToString());
                rowtemp.CreateCell(4).SetCellValue(datalist[i].TM.ToString());
                rowtemp.CreateCell(5).SetCellValue(datalist[i].AvgLevel.ToString());
                rowtemp.CreateCell(6).SetCellValue(datalist[i].EightLevel.ToString());
                rowtemp.CreateCell(7).SetCellValue(datalist[i].ZeroLevel.ToString());
            }
        }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//其他sheet 
        protected void SheetElse(NPOI.SS.UserModel.ISheet sheet, int j, List<ST_WaterLevel_GradualMonth> datalist)
        {
            //将数据逐步写入sheet1各个行
            for (int i = 0; j + i < datalist.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i);
                rowtemp.CreateCell(0).SetCellValue(datalist[j + i].Row.ToString());
                rowtemp.CreateCell(1).SetCellValue(datalist[j + i].STCD.ToString());
                rowtemp.CreateCell(2).SetCellValue(datalist[j + i].STNM.ToString());
                rowtemp.CreateCell(3).SetCellValue(datalist[j + i].RVNM.ToString());
                rowtemp.CreateCell(4).SetCellValue(datalist[j + i].TM.ToString());
                rowtemp.CreateCell(5).SetCellValue(datalist[j + i].AvgLevel.ToString());
                rowtemp.CreateCell(6).SetCellValue(datalist[j + i].ZeroLevel.ToString());
            }
        }

　　转自http://www.cnblogs.com/zhongxinWang/archive/2012/08/07/2627476.html

Asp.net操作Excel（终极方法NPOI）(转),布布扣,bubuko.com

Asp.net操作Excel（终极方法NPOI）(转)

原文：http://www.cnblogs.com/luwenlong/p/3613287.html

上一篇：JSON和JSONP有哪些区别，PhoneGap跨域请求如何实现


下一篇：如何用HTML5+PhoneGap写个Path项目