公司突然用到大数据量的导出问题,之前写的导出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