using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace YourNameSpace
{
public class NPOIExcelExportHelper
{
/// <summary>
/// 导出DataTable到Excel文件的方法
/// </summary>
/// <param name="response"></param>
/// <param name="data"></param>
/// <param name="fileExtension"></param>
/// <param name="fileName"></param>
public static void ExportExcel(HttpResponse response,DataTable data,string fileExtension="xlsx", string fileName = null)
{
if (string.IsNullOrWhiteSpace(fileName))
{
fileName = Guid.NewGuid().ToString();
}
IWorkbook exportData = TableToWorkbook(data, fileExtension);
using (MemoryStream stream = new MemoryStream())
{
exportData.Write(stream);
response.Clear();
response.Buffer = true;
response.Charset = Encoding.UTF8.BodyName;
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (fileExtension == "xlsx") //xlsx file format
{
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
else if (fileExtension == "xls") //xls file format
{
response.ContentType = "application/vnd.ms-excel";
}
else
{
throw new Exception("文件格式错误");
}
response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName + "." + fileExtension));
response.BinaryWrite(stream.ToArray());
response.End();
}
}
public static IWorkbook TableToWorkbook(DataTable dt,string extension="xlsx")
{
if (dt == null)
{
dt = new DataTable();
}
IWorkbook workbook;
if (extension == "xlsx")
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
//make a header row
IRow row1 = sheet1.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellValue(columnName);
}
//loops through data
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellValue(dt.Rows[i][columnName].ToString());
}
}
return workbook;
}
}
}
记录一个使用NPOI导出DataTable到Excel中方法~~~从Stack OverFlow找来的~~