导出excel是再常见不过的功能，其中NPOI是我一直使用的导出excel的框架。你可以猛击这里了解NPOI，最近有这样一个需求，当我想一次性导出多个excel文件时。我想在内存里面压缩后输出到浏览器进行下载。由此接触到了另外一个框架SharpZipLib，猛击这里了解。demo的代码贴在这里，以备后面查阅。
using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using ICSharpCode.SharpZipLib.Zip;
namespace NpoiAndZip
{
public partial class ExportDownloadExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnOK_Click(object sender, EventArgs e)
{
string filename = "test.zip";
Response.ContentType = "application/zip";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
InitializeWorkbook();
GenerateData();
MemoryStream ms1 = new MemoryStream();
MemoryStream ms2=new MemoryStream();
MemoryStream ms3=new MemoryStream();
ms1 = WriteToStream(hssfworkbook);
ms2 = WriteToStream(hssfworkbook2);
ms3 = WriteToStream(hssfworkbook3);
MemoryStream st = new MemoryStream();
using (ZipFile zip = ZipFile.Create(st))
{
zip.BeginUpdate();
StreamDataSource d1=new StreamDataSource(ms1);
StreamDataSource d2 = new StreamDataSource(ms2);
StreamDataSource d3 = new StreamDataSource(ms3);
//添加文件
zip.Add(d1, "test1.xls");
zip.Add(d2, "test2.xls");
zip.Add(d3, "test3.xls");
zip.CommitUpdate();
}
Response.BinaryWrite(st.GetBuffer());
Response.End();
}
HSSFWorkbook hssfworkbook;
HSSFWorkbook hssfworkbook2;
HSSFWorkbook hssfworkbook3;
MemoryStream WriteToStream(HSSFWorkbook workbook)
{
//Write the stream data of workbook to the root directory
MemoryStream file = new MemoryStream();
workbook.Write(file);
return file;
}
void GenerateData()
{
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
ISheet sheet2 = hssfworkbook2.CreateSheet("Sheet1");
ISheet sheet3 = hssfworkbook3.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
sheet3.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
for (int i = 1; i <= 15; i++)
{
IRow row = sheet2.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
for (int i = 1; i <= 15; i++)
{
IRow row = sheet3.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
}
void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
hssfworkbook2 = new HSSFWorkbook();
hssfworkbook3 = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi2 = PropertySetFactory.CreateDocumentSummaryInformation();
dsi2.Company = "NPOI Team";
hssfworkbook2.DocumentSummaryInformation = dsi2;
////create a entry of SummaryInformation
SummaryInformation si2 = PropertySetFactory.CreateSummaryInformation();
si2.Subject = "NPOI SDK Example";
hssfworkbook2.SummaryInformation = si2;
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi3 = PropertySetFactory.CreateDocumentSummaryInformation();
dsi3.Company = "NPOI Team";
hssfworkbook3.DocumentSummaryInformation = dsi3;
////create a entry of SummaryInformation
SummaryInformation si3 = PropertySetFactory.CreateSummaryInformation();
si3.Subject = "NPOI SDK Example";
hssfworkbook3.SummaryInformation = si3;
}
}
//只有实现IStaticDataSource接口才能实现流操作
class StreamDataSource : IStaticDataSource
{
public byte[] bytes { get; set; }
public StreamDataSource(MemoryStream ms)
{
bytes = ms.GetBuffer();
}
public Stream GetSource()
{
Stream s = new MemoryStream(bytes);
return s;
}
}
}
