using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
namespace NPOI_Test1
{
public partial class Page1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnExport_Click(object sender, EventArgs e)
{
DataSet ds = null;//这里ds是执行自己的方法,处理数据库获取DataSet对象
DataTable dt = ds.Tables[0];
ExportExcel(dt);
}
protected void ExportExcel(DataTable dt)
{
HttpContext curContext = HttpContext.Current;
//设置编码及附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
string fullName = HttpUtility.UrlEncode("FileName.xls", Encoding.UTF8);
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8)); //attachment后面是分号
byte[] data = TableToExcel(dt, fullName).GetBuffer();
curContext.Response.BinaryWrite(TableToExcel(dt, fullName).GetBuffer());
curContext.Response.End();
}
public MemoryStream TableToExcel(DataTable dt, string file)
{
//创建workbook
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx")
workbook = new XSSFWorkbook();
else if (fileExt == ".xls")
workbook = new HSSFWorkbook();
else
workbook = null;
//创建sheet
ISheet sheet = workbook.CreateSheet("Sheet1");
//表头
IRow headrow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell headcell = headrow.CreateCell(i);
headcell.SetCellValue(dt.Columns[i].ColumnName);
}
//表内数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转化为字节数组
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
NET 导出excel
最新推荐文章于 2022-12-29 12:56:57 发布