你的位置:首页 > ASP.net教程

[ASP.net教程]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();    }