随笔 - 230, 文章 - 2, 评论 - 47
  博客园  :: 联系 :: 管理

使用NPOI导出excel

Posted on 2014-09-24 15:59  独孤雁  阅读(126)  评论(0编辑  收藏

NPOI下载地址http://npoi.codeplex.com/releases

从项目中引用NPOI.bll和NPOI.OOXML.bll

引用命名控件

using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

using System.IO;

 

在页面(*.aspx)中使用

protected void Button1_Click(object sender, EventArgs e)
        {
            enumCj_State cjState = enumCj_State.待审核;

            tbCj_Sale_BaseInfo[] objList = cjManager.search_cj_sale_tongji(null, null, (int)cjState, -1, string.Empty);

            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("销售当日台账");
            IRow headrow = sheet.CreateRow(0);//编写标题列
            headrow.CreateCell(0, CellType.String).SetCellValue("签约店");
            headrow.CreateCell(1, CellType.String).SetCellValue("业务编号");

            int intRolNum = 0;
            foreach (tbCj_Sale_BaseInfo objcj in objList)
            {
                IRow row = sheet.CreateRow(intRolNum + 1);
                //row.CreateCell(0, CellType.String).SetCellValue(((DateTime)objcj.CJ_DATE).ToShortDateString());
                row.CreateCell(1, CellType.String).SetCellValue(objcj.QY_DEPTNAME);
                row.CreateCell(2, CellType.String).SetCellValue(objcj.FK_FYCode);
                
                    intRolNum ++;
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);

            // 設定強制下載標頭
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls"));
            // 輸出檔案
            Response.BinaryWrite(ms.ToArray());
            ms.Close();
            ms.Dispose();
            Response.End();
        }

在一般程序(*.ashx)中使用

case "excel":
                    {
                        context.Response.Clear();
                        context.Response.ClearContent();
                        context.Response.ClearHeaders();
                       

                        context.Response.ContentType = "application/x-excel";
                        string fileName = HttpUtility.UrlEncode("动态数据库.xls");
                        context.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
                        HSSFWorkbook workbook = new HSSFWorkbook();
                        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
                        HSSFRow row = (HSSFRow)sheet.CreateRow(0);

                      

                        row.CreateCell(1, CellType.String).SetCellValue("Hello excel");

                  

                        MemoryStream ms = new MemoryStream();
                        workbook.Write(ms);

                        // 設定強制下載標頭

                        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls"));
                        // 輸出檔案
                        context.Response.BinaryWrite(ms.ToArray());
                        ms.Close();
                        ms.Dispose();
                        context.Response.End();

                        break;
                    }