首页 > 代码库 > 使用NPOI导入导出标准Excel
使用NPOI导入导出标准Excel
使用NPOI导出
从DataTable读取内容来创建Workbook对象:
?
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 | public static MemoryStream RenderToExcel(DataTable table) { MemoryStream ms = new MemoryStream(); using (table) { using (IWorkbook workbook = new HSSFWorkbook()) { using (ISheet sheet = workbook.CreateSheet()) { IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in table.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //If Caption not set, returns the ColumnName value // handling value. int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; } } } return ms; } |
如果看不惯DataTable,那么DataReader也行:
?
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 | public static MemoryStream RenderToExcel(IDataReader reader) { MemoryStream ms = new MemoryStream(); using (reader) { using (IWorkbook workbook = new HSSFWorkbook()) { using (ISheet sheet = workbook.CreateSheet()) { IRow headerRow = sheet.CreateRow(0); int cellCount = reader.FieldCount; // handling header. for ( int i = 0; i < cellCount; i++) { headerRow.CreateCell(i).SetCellValue(reader.GetName(i)); } // handling value. int rowIndex = 1; while (reader.Read()) { IRow dataRow = sheet.CreateRow(rowIndex); for ( int i = 0; i < cellCount; i++) { dataRow.CreateCell(i).SetCellValue(reader[i].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; } } } return ms; } |
以上代码把创建的Workbook对象保存到流中,可以通过以下方法输出到浏览器,或是保存到硬盘中:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | static void SaveToFile(MemoryStream ms, string fileName) { using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { byte [] data = http://www.mamicode.com/ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); data = http://www.mamicode.com/ null ; } } static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName) { if (context.Request.Browser.Browser == "IE" ) fileName = HttpUtility.UrlEncode(fileName); context.Response.AddHeader( "Content-Disposition" , "attachment;fileName=" + fileName); context.Response.BinaryWrite(ms.ToArray()); } |
使用NPOI导入
需要注意的是,sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1,这里可能存在BUG:当没有数据或只有一行数据时sheet.LastRowNum为0,PhysicalNumberOfRows 表现正常。
这里读取流中的Excel来创建Workbook对象,并转换成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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | static DataTable RenderFromExcel(Stream excelFileStream) { using (excelFileStream) { using (IWorkbook workbook = new HSSFWorkbook(excelFileStream)) { using (ISheet sheet = workbook.GetSheetAt(0)) //取第一个表 { DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0); //第一行为标题行 int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1 //handling header. for ( int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for ( int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null ) { for ( int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null ) dataRow[j] = GetCellValue(row.GetCell(j)); } } table.Rows.Add(dataRow); } return table; } } } } |
或者是直接生成SQL语句来插入到数据库:
?
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 | public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction) { int rowAffected = 0; using (excelFileStream) { using (IWorkbook workbook = new HSSFWorkbook(excelFileStream)) { using (ISheet sheet = workbook.GetSheetAt(0)) //取第一个工作表 { StringBuilder builder = new StringBuilder(); IRow headerRow = sheet.GetRow(0); //第一行为标题行 int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1 for ( int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row != null ) { builder.Append(insertSql); builder.Append( " values (" ); for ( int j = row.FirstCellNum; j < cellCount; j++) { builder.AppendFormat( "‘{0}‘," , GetCellValue(row.GetCell(j)).Replace( "‘" , "‘‘" )); } builder.Length = builder.Length - 1; builder.Append( ");" ); } if ((i % 50 == 0 || i == rowCount) && builder.Length > 0) { //每50条记录一次批量插入到数据库 rowAffected += dbAction(builder.ToString()); builder.Length = 0; } } } } } return rowAffected; } |
这里的Excel可能没有数据,所以可以加一个方法来检测:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | public static bool HasData(Stream excelFileStream) { using (excelFileStream) { using (IWorkbook workbook = new HSSFWorkbook(excelFileStream)) { if (workbook.NumberOfSheets > 0) { using (ISheet sheet = workbook.GetSheetAt(0)) { return sheet.PhysicalNumberOfRows > 0; } } } } return false ; } |
转自http://www.cnblogs.com/lwme/archive/2011/11/18/npoi_excel_import_export.html
使用NPOI导入导出标准Excel
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。