NPOI 导出Excel
下载NPOI包 引用dotnet2 或dotnet4
public void NpoiExportExcel()
{
string sql = "select * from DimPlaza";
List<DimPlaza> listData = DataFactory.Database().FindList<DimPlaza>(sql);
string head = "PlazaInfoID,BranchID,PlazaID,PlazaName";
Export("name", head,listData, true);
}
public void Export(string filename, string head, List<DailySummaryController.DimPlaza> listData, bool showNumber)
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename + ".xlsx"));
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//是否显示行号标题
string[] headExcel = head.Split(',');
if (showNumber)
{
row1.CreateCell(0).SetCellValue("序号");
for (int i = 0; i < headExcel.Length; i++)
{
row1.CreateCell(i + 1).SetCellValue(headExcel[i]);
}
}
else
{
for (int i = 0; i < headExcel.Length; i++)
{
row1.CreateCell(i).SetCellValue(headExcel[i]);
}
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < listData.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
//是否显示行号数
if (showNumber)
{
rowtemp.CreateCell(0).SetCellValue(i + 1);
rowtemp.CreateCell(1).SetCellValue(listData[i].PlazaInfoID);
rowtemp.CreateCell(2).SetCellValue(listData[i].BranchID);
rowtemp.CreateCell(3).SetCellValue(listData[i].PlazaID);
rowtemp.CreateCell(4).SetCellValue(listData[i].PlazaName);
}
else
{
rowtemp.CreateCell(0).SetCellValue(listData[i].PlazaInfoID);
rowtemp.CreateCell(1).SetCellValue(listData[i].BranchID);
rowtemp.CreateCell(2).SetCellValue(listData[i].PlazaID);
rowtemp.CreateCell(3).SetCellValue(listData[i].PlazaName);
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
public class DimPlaza
{
public string BranchID { get; set; }
public string PlazaName { get; set; }
public string PlazaInfoID { get; set; }
public string PlazaID { get; set; }
}