NPOI导出多个工作簿

前端代码：

  <table cellpadding="6" cellspacing="0" style="text-align: left; width: 100%; border: none; table-layout: fixed;">
                    <colgroup>
                        <col width="90px" />
                        <col />
                    </colgroup>
                    <tr style="border: none;">
                    </tr>

                    <tr style="border: none;">
                        <td align="center" style="text-align: left; border: none; width: 80px; font-size: 13px;">导出文件：
                        </td>
                        <td style="border: none;" colspan="2">
                            <span style="font-size: 12px;">录入归档年度:</span>
                            <Asp:TextBox ID="txt_Year" runat="server"></Asp:TextBox>
                            <asp:Button runat="server" Text="导出表格" ID="upLoad" Style="font-size: 13px; font-family: 宋体; font-weight: bold;" OnClick="upLoad_Click"
                                OnClientClick="return change()" />&nbsp;&nbsp;&nbsp;
                            <asp:Label ID="LabMess" runat="server" Style="color: #FF0000;" Text=""></asp:Label>
                            <asp:HiddenField ID="hidkey" runat="server" Value="0" />
                            &nbsp;
                        </td>
                    </tr>
                </table>

后端代码（运用的是webform点击事件）:

在点击事件中创建导出相关代码例如：
  /// <summary>
    /// 数据导出 使用npoi处理
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void upLoad_Click(object sender, EventArgs e)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建工作簿
        string sql = string.Empty;
        if (!string.IsNullOrEmpty(this.txt_Year.Text))//判断年度是否录入
        {
            sql = @"sql语句可自行配置，不同的年份导出不同数据";//根据年份查询数据信息
            DataTable dtTheYear = dao.GetDataTable(sql);
            if (dtTheYear.Rows.Count > 0)
            {
                for (int i = 0; i < dtTheYear.Rows.Count; i++)
                {
                    ExportNpoi(workbook, dtTheYear.Rows[i]["SaveType"].ToString(), dtTheYear.Rows[i]["TheYear"].ToString());
                }
            }
            else
            {
                this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>alert('没有和该年度相对应的数据信息,无法导出！')</script>");
                return;
            }
        }
        else
        {
            sql = @"归年度为空的时候导出数据库中所有的年份的不同信息";
            DataTable dtGui = dao.GetDataTable(sql);
            if (dtGui.Rows.Count > 0)
            {
                for (int i = 0; i < dtGui.Rows.Count; i++)
                {
                    ExportNpoi(workbook, dtGui.Rows[i]["SaveType"].ToString(), dtGui.Rows[i]["TheYear"].ToString());
                }
            }
            else
            {
                this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>alert('没有该年度的数据信息,无法导出！')</script>");
                return;
            }
        }
        ExprotWrite(workbook);
    }


**重点在此：**
 private void ExportNpoi(HSSFWorkbook workbook, string saveType, string theYear)
    {
        string sqlYong = @"SELECT BuildNumber AS 件号,PersonLiable AS 责任者,DocumentCode AS 文号,
                                    TitleName AS 题名,
                                        CONVERT(VARCHAR(8),REPLACE(REPLACE(REPLACE(REPLACE(DocumentDT,'-',''),'/',''),'~',''),'.',''),112) AS 日期,PageTotal AS 页数,Remark AS 备注,
                                            CONVERT(VARCHAR(12),AcceptDT,112) AS 收文日期
                                                FROM TDocumentManagement WHERE TheYear = '" + theYear + "' and saveType ='" + saveType + "' and Status ='已归档'  ORDER BY CAST(BuildNumber AS INT)";
        DataTable dtYong = dao.GetDataTable(sqlYong); //获取数据中的值
        if (dtYong.Rows.Count > 0)//判断是否有值
        {
            ISheet sheet = workbook.CreateSheet(saveType + theYear);//根据不同的年度创建工作表
            ICellStyle HeadercellStyle = workbook.CreateCellStyle();//设置列样式类型
            HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;//定义边框样式
            HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            //字体
            NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
            headerfont.Boldweight = (short)FontBoldWeight.BOLD;
            HeadercellStyle.SetFont(headerfont);
            //合并单元格
            CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 7);
            sheet.AddMergedRegion(region0);
            CellRangeAddress region1 = new CellRangeAddress(1, 1, 0, 2);
            sheet.AddMergedRegion(region1);
            CellRangeAddress region2 = new CellRangeAddress(1, 1, 4, 7);
            sheet.AddMergedRegion(region2);
            //创建第一行
            ICellStyle HeadercellSt = workbook.CreateCellStyle();
            HeadercellSt.Alignment = HorizontalAlignment.CENTER;
            HeadercellSt.VerticalAlignment = VerticalAlignment.CENTER;
            NPOI.SS.UserModel.IFont headerf = workbook.CreateFont();
            headerf.Boldweight = (short)FontBoldWeight.BOLD;
            headerf.FontHeight = 350;
            HeadercellSt.SetFont(headerf);
            IRow DataRowOne = sheet.CreateRow(0);
            ICell cellOne = DataRowOne.CreateCell(0);
            cellOne.SetCellValue("归档文件目录");
            cellOne.CellStyle = HeadercellSt;
            //创建第二行
            ICellStyle HeadercellS = workbook.CreateCellStyle();
            HeadercellS.BorderBottom = NPOI.SS.UserModel.BorderStyle.NONE;
            HeadercellS.BorderLeft = NPOI.SS.UserModel.BorderStyle.NONE;
            HeadercellS.BorderRight = NPOI.SS.UserModel.BorderStyle.NONE;
            HeadercellS.BorderTop = NPOI.SS.UserModel.BorderStyle.NONE;
            HeadercellS.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
            //字体
            NPOI.SS.UserModel.IFont headers = workbook.CreateFont();
            headers.Boldweight = (short)FontBoldWeight.BOLD;
            headers.FontHeight = 250;
            HeadercellS.SetFont(headers);
            IRow DataRowTwo = sheet.CreateRow(1);
            ICell cellTwo0 = DataRowTwo.CreateCell(0);
            cellTwo0.SetCellValue("归档年度:" + theYear);
            cellTwo0.CellStyle = HeadercellS;
            ICell cellTwo1 = DataRowTwo.CreateCell(4);
            cellTwo1.SetCellValue("保管期限:" + saveType + "");
            //((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, NPOI.SS.UserModel.BorderStyle.NONE, NPOI.HSSF.Util.HSSFColor.RED.index);
            cellTwo1.CellStyle = HeadercellS;
            //用column name 作为列名
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(2);
            foreach (DataColumn item in dtYong.Columns)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(item.ColumnName);
                cell.CellStyle = HeadercellStyle;
                icolIndex++;
            }
            ICellStyle cellStyle = workbook.CreateCellStyle();
            //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.WrapText = true;
            IFont cellfont = workbook.CreateFont();
            cellfont.Boldweight = (short)FontBoldWeight.NORMAL;
            cellStyle.SetFont(cellfont);
            //建立内容行
            int iRowIndex = 3;
            int iCellIndex = 0;
            foreach (DataRow Rowitem in dtYong.Rows)
            {
                IRow DataRow = sheet.CreateRow(iRowIndex);
                foreach (DataColumn Colitem in dtYong.Columns)
                {
                    ICell cell = DataRow.CreateCell(iCellIndex);
                    cell.SetCellValue(Rowitem[Colitem].ToString());
                    cell.CellStyle = cellStyle;
                    iCellIndex++;
                }
                iCellIndex = 0;
                iRowIndex++;
            }
            //自适应列宽度
            for (int i = 0; i < icolIndex + 2; i++)
            {
                if (i == 1 || i == 2 || i == 6)
                {
                    sheet.SetColumnWidth(i, 5500);
                }
                else
                {
                    sheet.SetColumnWidth(i, 2650);
                }
            }
            for (int i = 0; i <= dtYong.Rows.Count + 2; i++)
            {
                IRow currentRow = sheet.GetRow(i);
                ICell currentCell = currentRow.GetCell(20);
                //int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
                currentRow.HeightInPoints = 20 * (60 / 60 + 1);
            }
        }
    }

 /// <summary>
    /// 向客户端输出文件
    /// </summary>
    /// <param name="workbook"></param>
    private void ExprotWrite(HSSFWorkbook workbook)
    {
        HttpContext context = HttpContext.Current;
        context.Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("目录" + this.txt_Year.Text + ".xls")); ;
        context.Response.Clear();
        context.Response.BinaryWrite(WriteToStream(workbook).GetBuffer());
        context.Response.End();
    }

    private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
    {
        //Write the stream data of workbook to the root directory
        MemoryStream file = new MemoryStream();
        hssfworkbook.Write(file);
        return file;
    }

**

表达略显拙略忘见谅！

**