Asp.net操作Excel(终极方法NPOI)
先去官网: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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。