首页 > 代码库 > c# NPOI 导出EXCEL

c# NPOI 导出EXCEL

需要引入dll文件  

 

 技术分享

 

 

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace CSR_Web.Common{    public   class NPOIExport    {        public static NPOI.HSSF.UserModel.HSSFWorkbook DoExport(System.Data.DataTable dt, string notile)        {            //创建工作簿            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();            //创建表            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(notile);            //自适应列宽          //  sheet.AutoSizeColumn(1, true);            //标题行合并单元格            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count-1));                      NPOI.SS.UserModel.IRow firstrow = sheet.CreateRow(0);            NPOI.SS.UserModel.ICell firstcell = firstrow.CreateCell(0);            //表名样式            NPOI.SS.UserModel.ICellStyle styleHeader = book.CreateCellStyle();            NPOI.SS.UserModel.IFont fontHeader = book.CreateFont();            styleHeader.Alignment =NPOI.SS.UserModel.HorizontalAlignment.Center;            styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;            fontHeader.FontHeightInPoints =20;            styleHeader.SetFont(fontHeader);            firstcell.CellStyle = styleHeader;            firstcell.SetCellValue(notile);                        try            {                //列名样式                NPOI.SS.UserModel.ICellStyle styleColName = book.CreateCellStyle();                NPOI.SS.UserModel.IFont fontColName = book.CreateFont();                styleColName.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                styleColName.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;                fontColName.FontHeightInPoints = 14;                styleColName.SetFont(fontColName);                //数据的样式、字体大小                NPOI.SS.UserModel.ICellStyle styleBody = book.CreateCellStyle();                NPOI.SS.UserModel.IFont fontBody = book.CreateFont();                styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;                fontBody.FontHeightInPoints = 12;                styleBody.SetFont(fontBody);                               //创建具体单元格数据                int rowCount = dt.Rows.Count;                int colCount = dt.Columns.Count;                NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow(1);                for (int x = 0; x < colCount; x++) { //将列名写入单元格                    NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(x);                    colNameCell.SetCellValue(dt.Columns[x].ColumnName);                    colNameCell.CellStyle = styleColName;                }                    for (int i = 0; i < rowCount; i++)                    {                        NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 2);//数据从第三上开始 第一行表名 第二行列名                        for (int j = 0; j < colCount; j++)                        {                            //填充数据                            NPOI.SS.UserModel.ICell cell = row.CreateCell(j);                            if (dt.Rows[i][j] != null)                            {                                cell.SetCellValue(dt.Rows[i][j].ToString());                            }                            else                            {                                cell.SetCellValue("");                            }                            cell.CellStyle = styleBody;                        }                    }                //自适应列宽                for (int x = 0; x < colCount; x++)                {                    sheet.AutoSizeColumn(x, true);                }                //此处代码是将 xls文件发到页面通过浏览器直接下载到本地  可以放到 界面调用的地方                //System.IO.MemoryStream ms = new System.IO.MemoryStream();                //book.Write(ms);                //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=绩效统计.xls"));                //Response.BinaryWrite(ms.ToArray());                //book = null;                //ms.Close();                //ms.Dispose();                  return book;            }            catch {             throw new Exception();            }finally{                book=null;            }        }                               }}
  /// <summary>        ///导出        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void btnExport_Click(object sender, EventArgs e)      {                    DataTable dt = cmbll.getdt();               NPOI.HSSF.UserModel.HSSFWorkbook book = NPOIExport.DoExport(dt, "xxx报表");            //写入客户端            try            {                WriteClient(book);            }            catch            {            }            finally            {                book = null;            }                 }        public void WriteClient(NPOI.HSSF.UserModel.HSSFWorkbook book)        {            System.IO.MemoryStream ms = new System.IO.MemoryStream();            book.Write(ms);            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=客户资料"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xls"));            Response.BinaryWrite(ms.ToArray());            book = null;            ms.Close();            ms.Dispose();        }

 

c# NPOI 导出EXCEL