npoi实现数据导出Excel
- 摘要:npoi.NET第三方的Office功能组件。链接地址http://npoi.codeplex.com/引用命名空间usingNPOI.HSSF.UserModel;usingNPOI.HPSF;usingNPOI.POIFS.FileSystem;usingNPOI.SS.UserModel;功能代码///<summary>///操作EXCEL导出数据报表的类///</summary>publicclassDataToExcel{///<summary>
- 标签:excel 实现 导出excel 数据
.NET第三方的Office功能组件。
- 链接地址
- http://npoi.codeplex.com/
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
/// <summary>
/// 操作EXCEL导出数据报表的类
/// </summary>
public class DataToExcel
{
/// <summary>
/// 以HTTP的形式创建Excel文件
/// </summary>
/// <param name="ds"></param>
public static void CreateExcel(DataSet ds)
{
HSSFWorkbook hssfworkbook;
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
HttpContext.Current.Response.Clear();
InitializeWorkbook(out hssfworkbook);
GenerateData(ref hssfworkbook, ds.Tables[0]);
HttpContext.Current.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());
HttpContext.Current.Response.End();
}
/// <summary>
/// 写入字符流
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
{
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return file;
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="hssfworkbook"></param>
private static void GenerateData(ref HSSFWorkbook hssfworkbook, DataTable dt)
{
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row0 = sheet1.CreateRow(0);
for (int z = 0; z < dt.Columns.Count; z++)
{
row0.CreateCell(z).SetCellValue(dt.Columns[z].ColumnName.ToString());
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
/// <summary>
/// 初始化工作薄
/// </summary>
/// <param name="hssfworkbook"></param>
private static void InitializeWorkbook(out HSSFWorkbook hssfworkbook)
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "";
hssfworkbook.SummaryInformation = si;
}
}