NPOI导出excel web输出及输出到文件
公司突然用到大数据量的导出问题,之前写的导出excel的方法有点儿shit,经过查资料,找到了NPOI导出excel的高效方法并分享给大家,希望大家共同进步。
1.//从datereader(也可以使用datatable) 读取数据添加到excel工作簿,然后转换到内存流
//从datereader(也可以使用datatable) 读取数据添加到excel工作簿,然后转换到内存流
- 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;
- }
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; }
//将内存流保存到文件
- static void SaveToFile(MemoryStream ms, string fileName)
- {
- using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
- {
- byte[] data = ms.ToArray();
- fs.Write(data, 0, data.Length);
- fs.Flush();
- data = null;
- }
- }
static void SaveToFile(MemoryStream ms, string fileName) { using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); data = null; } }
- //将内存流输出为下载文件
//将内存流输出为下载文件
- {
- if (context.Request.Browser.Browser == "IE")
- fileName = HttpUtility.UrlEncode(fileName);
- context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
- context.Response.BinaryWrite(ms.ToArray());
- }
转自:http://blog.csdn.net/zhouqinghe24/article/details/8649346