C# NPOI+ICSharpCode.SharpZipLib 实现海量数据导出excel

码拜

9年 ago

NPOI 是 POI 项目的 .NET 版本。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。

ICSharpCode.SharpZipLib.dll 主要实现压缩解压ZIP文件。二者结合，效力更强。

private void ExcelZip(DataTable dt ）{

int rows = dt.Rows.Count;

int pagesize = 20000;

int pagecount = rows % pagesize == 0 ? rows / pagesize : rows /pagesize + 1;

int nexrows = 0;

string template = Server.MapPath(“../excel/CheckHouses.xls”);

MemoryStream ms = new MemoryStream();

using (ZipFile zip = new ZipFile(ms))

{

zip.BeginUpdate();

for (int pageindex = 0; pageindex < pagecount; pageindex++)

{

int num = 0;

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = ExcelHelper.ExcelToClient(“Sheet1”, template,ref workbook);

HSSFCellStyle style = workbook.CreateCellStyle();

style.VerticalAlignment = CellVerticalAlignment.CENTER;

//自动换行

style.WrapText = true;

HSSFFont font = workbook.CreateFont();

font.FontHeightInPoints = 10;

//字体大小10

style.SetFont(font);

//边框

style.BorderLeft = style.BorderRight = style.BorderTop = style.BorderBottom = CellBorderType.THIN;

//设置excel的标题

ExcelHelper.SetExcelToCell(sheet, 0, 0, “问题数据”);

nexrows = (pageindex + 1) * pagesize > rows ? rows : (pageindex + 1) * pagesize;

for (int i = pageindex * pagesize; i < nexrows; i++)

{

//在excel表中加入一行

ExcelHelper.SetExcelToCell(sheet, (2 + num), 0, (i + 1).ToString());

sheet.GetRow(2 + num).GetCell(0).CellStyle = style;

//在excel表中加入一行

ExcelHelper.SetExcelToCell(sheet, (2 + num), 1, dt.Rows[i][“buildyear”].ToString());

sheet.GetRow(2 + num).GetCell(1).CellStyle = style;

ExcelHelper.SetExcelToCell(sheet, (2 + num), 2, dt.Rows[i][“builduse”].ToString());

sheet.GetRow(2 + num).GetCell(2).CellStyle = style;

ExcelHelper.SetExcelToCell(sheet, (2 + num), 3, dt.Rows[i][“houseaddress”].ToString());

sheet.GetRow(2 + num).GetCell(3).CellStyle = style;

ExcelHelper.SetExcelToCell(sheet, (2 + num), 4, dt.Rows[i][“checkstr”].ToString());

sheet.GetRow(2 + num).GetCell(4).CellStyle = style;

num = num + 1;

}

MemoryStream mst = new MemoryStream();

workbook.Write(mst);

StreamDataSource sds = new StreamDataSource(mst);

zip.Add(sds, string.Format( “问题数据({0}).xls”, (pageindex + 1)));

mst.Close();

mst.Dispose();

}

zip.CommitUpdate();

string filename = DateTime.Now.ToString(“yyyyMMddHHmmss”) + “.zip”;

filename =HttpUtility.UrlEncode(System.Text.UTF8Encoding.UTF8.GetBytes(filename));

HttpResponse response = System.Web.HttpContext.Current.Response;

response.ContentType = “application/zip”;

response.AddHeader(“Content-Disposition”, string.Format(“attachment; filename=” + filename));

response.BinaryWrite(ms.GetBuffer());

ms.Close();

ms.Dispose();

response.Flush();

response.End();

}

public static void SetExcelToCell(HSSFSheet sheet, int row, int cell, string value)

{

HSSFRow dRow = sheet.GetRow(row);

if (dRow == null)

{

dRow = sheet.CreateRow(row);

}

HSSFCell nCell = dRow.GetCell(cell);

if (nCell == null)

{

nCell = dRow.CreateCell(cell);

}

SetECellValue(nCell, “System.String”, value);

}