npoi实现数据导出Excel

npoi

    .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;
        }
 
    }