使用npoi.dll导出数据到excel

      .net数据导出excel数据有多种方法，最常用的就是使用office组件，但随之而来的问题也很棘手，又要调权限又要确定是否安装office很是麻烦，最近一个项目中也有数据导出功能，随使用excel模板完美完成功能，调试完成发布服务器，又是一通调试，最终可以导出。但是项目中不只一处要数据导出，有四个同事来做这就带来很多麻烦，大家每人都创建了很多模板（当然很多功能属同一模块的都放在一个模板中创建了若干sheet），以后维护很麻烦。于是网上搜索到了npoi,研究了一会尝试使用npoi导出数据。

首先添加npoidll引用（ NPOI.dll和Ionic.Zip.dll  注: npoi版本是NPOI_1.2.5_binary）

        #region NPOI导出
        public MemoryStream DataMemory(DataTable dt, string headerText)
        {
            NOPIHelper nopi = new NOPIHelper();

            MemoryStream ms = new MemoryStream();
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(headerText);

            #region 文件右键属性
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "Golden3C";
                workbook.DocumentSummaryInformation = dsi;
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = "hbj"; //填加xls文件作者信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            #region 导出excel的名称
            IRow row = sheet.CreateRow(0);
            row.HeightInPoints = 30;//行高           
            row.CreateCell(0).SetCellValue(headerText);
            sheet.GetRow(0).GetCell(0).CellStyle = nopi.SetCellStyle(workbook, 20);//设置excel单元格样式
            nopi.MergedRegion(sheet, 0, 0, 0, 20);
            #endregion

            #region 排列表头
            {
                string[] arrRow1 = new string[] {
                    "序号",
                    "数据类型",
                    "年/半年/季度/月",
                    "区县",
                    "企业名称",
                    "企业类型(现有、新建)",
                    "设计生产能力(万吨/年)",
                    "治污设施主体处理工艺",
                    "新建减排措施投运时间(XXXX年XX月)",
                    "产品类型（浆、机制纸及纸板、纸制品）",
                    "产品产量（吨）",
                    "废水排放量（吨）",
                    "排放去向",
                    "",
                    "平均出水COD浓度（mg/L）",
                    "",
                    "平均出水氨氮浓度（mg/L）",
                    "",
                    "是否有治污设施中控系统",
                    "是否安装在线监测仪器、数据与地方环保部门联网并通过有效性审核",
                    "备注"
                };

                IRow row1 = sheet.CreateRow(1);//第二行
                row1.HeightInPoints = 20;
                
                for (int i = 0; i < arrRow1.Length; i++)
                {
                    row1.CreateCell(i).SetCellValue(arrRow1[i]);
                    row1.GetCell(i).CellStyle = nopi.SetCellStyle(workbook, 10);
                }

                string[] arrRow2 = new string[] { 
                    "","","","","","","","","","","","",
                    "是否纳管", 
                    "纳管后排入集中污水处理设施名称",
                    "减排措施实施前平均出水COD浓度",
                    "减排措施实施后平均出水COD浓度",
                    "减排措施实施前平均出水氨氮浓度",
                    "减排措施实施后平均出水氨氮浓度","","",""
                };

                IRow row2 = sheet.CreateRow(2);//第三行
                for (int j = 0; j < arrRow2.Length; j++)
                {
                    row2.CreateCell(j).SetCellValue(arrRow2[j]);
                    row2.GetCell(j).CellStyle = nopi.SetCellStyle(workbook, 10);
                }
                //开始合并单元格--跨行合并
                nopi.MergedRegion(sheet, 1, 2, 0, 0);
                nopi.MergedRegion(sheet, 1, 2, 1, 1);
                nopi.MergedRegion(sheet, 1, 2, 2, 2);
                nopi.MergedRegion(sheet, 1, 2, 3, 3);
                nopi.MergedRegion(sheet, 1, 2, 4, 4);
                nopi.MergedRegion(sheet, 1, 2, 5, 5);
                nopi.MergedRegion(sheet, 1, 2, 6, 6);
                nopi.MergedRegion(sheet, 1, 2, 7, 7);
                nopi.MergedRegion(sheet, 1, 2, 8, 8);
                nopi.MergedRegion(sheet, 1, 2, 9, 9);
                nopi.MergedRegion(sheet, 1, 2, 10, 10);
                nopi.MergedRegion(sheet, 1, 2, 11, 11);
                nopi.MergedRegion(sheet, 1, 2, 18, 18);
                nopi.MergedRegion(sheet, 1, 2, 19, 19);
                nopi.MergedRegion(sheet, 1, 2, 20, 20);
                //跨列合并
                nopi.MergedRegion(sheet, 1, 1, 12, 13);
                nopi.MergedRegion(sheet, 1, 1, 14, 15);
                nopi.MergedRegion(sheet, 1, 1, 16, 17);
            }
            #endregion 排列表头

            #region  处理列值
            {
                string[] arrColumn = new string[] { 
                    "TA032_Type",
                    "TA032_dateTime",
                    "EC001_SSQXMC",                
                    "EC101_WRYMC",
                    "TA032_WSCLX",
                    "TA032_SJSCNL",
                    "TA032_ZWSSGY",
                    "TA032_RunDate",
                    "TA032_ProductType",
                    "TA032_ProductNum",
                    "TA032_FSPFL",
                    "TA032_SFNG",
                    "TA032_NGHCSMC",
                    "TA032_CODJPSSQND",
                    "TA032_CODJPSSHND",
                    "TA032_NH3JPSSQND",
                    "TA032_NH3JPSSHND",
                    "TA032_SFYZK",
                    "TA032_SFYZX",
                    "TA032_remark"
                };
                for (int j = 0; j < dt.Rows.Count; j++)//循环行
                {
                    IRow rowColumn = sheet.CreateRow(j + 3);

                    rowColumn.CreateCell(0).SetCellValue(j + 1);//序号
                    rowColumn.GetCell(0).CellStyle = nopi.SetCellStyle(workbook, 10);

                    for (int i = 1; i < arrColumn.Length - 1; i++)
                    {
                        //处理前两列(除去序号列)
                        if (i == 1)
                        {
                            string time = dt.Rows[j]["TA032_dateTime"].ToString();
                            //存储的数据类型：0=年数据；1=月数据；2=季数据,3=半年数据
                            if (dt.Rows[j]["TA032_Type"].ToString() == "0")
                            {
                                rowColumn.CreateCell(1).SetCellValue("年数据");
                                rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年");
                            }
                            else if (dt.Rows[j]["TA032_Type"].ToString() == "1")
                            {
                                rowColumn.CreateCell(1).SetCellValue("月数据");
                                rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年" + time.Substring(4, 2) + "月");
                            }
                            else if (dt.Rows[j]["TA032_Type"].ToString() == "2")
                            {
                                string season = "";
                                rowColumn.CreateCell(1).SetCellValue("季度数据");
                                if (time.Substring(4, 2) == "21")
                                    season = "一季度";
                                else if (time.Substring(4, 2) == "22")
                                    season = "二季度";
                                else if (time.Substring(4, 2) == "23")
                                    season = "三季度";
                                else
                                    season = "四季度";
                                rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年" + season);
                            }
                            else if (dt.Rows[j]["TA032_Type"].ToString() == "3")
                            {
                                string halfYear = "下半年";
                                rowColumn.CreateCell(1).SetCellValue("半年数据");
                                if (time.Substring(4, 3) == "306")
                                    halfYear = "上半年";
                                rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年" + halfYear);
                            }
                            rowColumn.GetCell(1).CellStyle = nopi.SetCellStyle(workbook, 10);
                            rowColumn.GetCell(2).CellStyle = nopi.SetCellStyle(workbook, 10);

                        }
                        //从第三列往后 0 1 2 3 4
                        switch (dt.Columns[arrColumn[i + 1]].DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                rowColumn.CreateCell(i + 2).SetCellValue(dt.Rows[j][arrColumn[i + 1]].ToString());
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dtime = DateTime.Parse(dt.Rows[j][arrColumn[i + 1]].ToString());
                                rowColumn.CreateCell(i + 2).SetCellValue(dtime.ToString("yyyy年MM月"));
                                break;
                            case "System.Boolean"://布尔型
                                bool boolValue = false;
                                bool.TryParse(dt.Rows[j][arrColumn[i + 1]].ToString(), out boolValue);
                                rowColumn.CreateCell(i + 2).SetCellValue(boolValue);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intValue = 0;
                                int.TryParse(dt.Rows[j][arrColumn[i + 1]].ToString(), out intValue);
                                rowColumn.CreateCell(i + 2).SetCellValue(intValue);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubValue = 0;
                                double.TryParse(dt.Rows[j][arrColumn[i + 1]].ToString(), out doubValue);
                                rowColumn.CreateCell(i + 2).SetCellValue(doubValue);
                                break;
                            case "System.DBNull"://空值处理
                                rowColumn.CreateCell(i + 2).SetCellValue("");
                                break;
                            default:
                                rowColumn.CreateCell(i + 2).SetCellValue("");
                                break;
                        }

                        rowColumn.GetCell(i + 2).CellStyle = nopi.SetCellStyle(workbook, 10);
                    }
                }
            }
            #endregion

            //自动设置列宽
            AutoSizeColumns(sheet);
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return ms;
        }
        #endregion NPOI导出

//***************************************************************************************************************************

//注释：排列表头中每个数组代表excel中的每一行，数组中的""作用是合并单元格，否则合并单元格后表头会很乱。

              处理列值 即根据数据源dataset  和要导出的excel表头排列column，这要就不用循环去定位excel列对应dataset里的那个列

//***************************************************************************************************************************

  #region  通用方法（单行or复杂表头）
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public void MergedRegion(HSSFSheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
            sheet.SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, HSSFColor.BLACK.index);//需要设置边框颜色，否则左侧没有 added by sean 2014-07-28
        }

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="fontSize">字体大小</param>
        /// <returns>样式</returns>
        public ICellStyle SetCellStyle(HSSFWorkbook workbook, short fontSize)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//居中
            style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
            style.WrapText = true;//自动换行

            //设置字体格式
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";//字体
            font.FontHeightInPoints = fontSize;//字号
            //font1.Color = HSSFColor.RED.index;//颜色
            font.Boldweight = 700;//粗体
            //font.IsItalic = true;//斜体
            //font.Underline = (byte)FontUnderlineType.DOUBLE;//添加双下划线
            style.SetFont(font);
            //单元格边框
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            return style;
        }
       
      
        /// <summary>
        /// excel文件右键属性
        /// </summary>
        /// <param name="workbook"></param>
        public void SetFileAttribute(HSSFWorkbook workbook)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Golden3C";
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "hbj"; //填加xls文件作者信息
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;
        }
        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        public void AutoSizeColumns(HSSFSheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(sheet.PhysicalNumberOfRows - 1);//获取最后一行，因为列是根据最后一行来排
                for (int j = 0; j < headerRow.Cells.Count; j++)
                {
                    sheet.AutoSizeColumn(j);
                }

            }
        }
        #endregion