.Net查询结果下载成Excel（HSSFWorkbook）

首先添加引用

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

 方法内容，不需要在代码中配置模板。

        //获取参数
        string wo = Request.QueryString["wo"];

        GLCustomizeBO _bo = BLLFactory.GetBal<GLCustomizeBO>(userInfo);
        //得到查询的dataset
        DataSet ds = _bo.CKDPackingReport(cartonid, model, timefrom, timeto);
        Int32 count = ds.Tables[0].Rows.Count;
        if (count == 0)
        {
            Response.Write("no data");
            return;
        }
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        IDataFormat format = hssfWorkbook.CreateDataFormat();
        //设置excle样式
        ICellStyle[] cellStyles = new ICellStyle[] {
                    hssfWorkbook.CreateCellStyle(),//表头
                    hssfWorkbook.CreateCellStyle()//內容
                };
        cellStyles[0].BorderTop = cellStyles[1].BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
        cellStyles[0].BorderBottom = cellStyles[1].BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
        cellStyles[0].BorderLeft = cellStyles[1].BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
        cellStyles[0].BorderRight = cellStyles[1].BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        cellStyles[0].Alignment = cellStyles[1].Alignment = HorizontalAlignment.CENTER;//水平居中
        cellStyles[0].VerticalAlignment = cellStyles[1].VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
        cellStyles[0].FillPattern = FillPatternType.SOLID_FOREGROUND;//前景色
        cellStyles[0].WrapText = cellStyles[1].WrapText = true;
        cellStyles[0].DataFormat = cellStyles[1].DataFormat = format.GetFormat("#,##0");
        cellStyles[0].FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index;

        IRow row = null;
        //ICell cell = null;
        ISheet hssfSheet = hssfWorkbook.CreateSheet("CKDPacking Report");
        row = hssfSheet.CreateRow(0);
        row.Height = 500;
        hssfSheet.DefaultRowHeight = 30 * 20;
        //填充头
        row.CreateCell(0).SetCellValue("箱號");
        row.CreateCell(1).SetCellValue("數量");
        for (int q = 0; q < 2; q++)
        {
            hssfSheet.SetColumnWidth(q, 6000);
            row.GetCell(q).CellStyle = cellStyles[0];
        }
        //填充主体
        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
        {
            row = hssfSheet.CreateRow(j + 1);
            for (int i = 0; i < 2; i++)
            {
                row.CreateCell(i).SetCellValue(ds.Tables[0].Rows[j][i].ToString());
                row.GetCell(i).CellStyle = cellStyles[1];
            }
        }
        MemoryStream file = new MemoryStream();
        hssfWorkbook.Write(file);
        String fileName = "CKDPacking Report" + DateTime.Now.ToString("yyyyMMddHHmmss");
        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
        Response.AddHeader("Content-Length", file.Length.ToString());
        Response.AddHeader("Content-Transfer-Encoding", "binary");
        Response.ContentType = "application/octet-stream";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.BinaryWrite(file.GetBuffer());
        Response.Flush();
        Response.End();

有模板的方式

 Int32 count = Convert.ToInt32(((DataTable)objs[1]).Columns.Count);
        if (((DataTable)objs[1]).Rows.Count==0)
        {
            Response.Write("no data");
            return;
        }
        FileStream sFile = new FileStream(WebHelper.GetAttachmentTempPath() + "\\FirstFailPSN.xls", FileMode.Open, FileAccess.Read);

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(sFile);
        sFile.Close();
        ISheet hssfSheet = hssfWorkbook.GetSheetAt(0);

        IRow row = null;
    
        //填充主体
        for (int i = 0; i < ((DataTable)objs[1]).Rows.Count; i++)
        {
            row = hssfSheet.CreateRow(i + 1);
            for (int j = 0; j <count; j++)
            {
                row.CreateCell(j).SetCellValue(((DataTable)objs[1]).Rows[i][j].ToString());
            }
        }
         MemoryStream file = new MemoryStream();

        hssfWorkbook.Write(file);
        byte[] datas = file.GetBuffer();
        String fileName = sign+flag + DateTime.Now.ToString("yyyyMMddHHmmss");
        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
        Response.AddHeader("Content-Length", datas.Length.ToString());
        Response.AddHeader("Content-Transfer-Encoding", "binary");
        Response.ContentType = "application/octet-stream";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.BinaryWrite(datas);
        Response.Flush();
        Response.End();