使用NPOI導出excel

時間 2019-11-11 標籤使用 npoi 導出 excel

NPOI下載地址http://npoi.codeplex.com/releases數據庫

從項目中引用NPOI.bll和NPOI.OOXML.bllapp

引用命名控件spa

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

using System.IO;

在頁面(*.aspx)中使用excel

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)中使用code

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