NPOI生成excel

摘要：文本导出excel使用的是NPOI，需求是从模板页中取生成文件

1.从固定模板文件中取excel，复制文件

    var savePath = @"D:\WebSite\ITTS-MAILROOM-WEB\file";
                var filePath = System.AppDomain.CurrentDomain.BaseDirectory + "ExcelTemple\\bulkmail.xls";
                FileStream fs = File.OpenRead(filePath);
                IWorkbook workbook = null;
                string extension = Path.GetExtension(filePath);
                if (extension.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = new XSSFWorkbook(fs);
                }
                fs.Close();

                ISheet sheetMain = workbook.GetSheetAt(0);

设置文件样式

   #region 设置样式

                    ISheet sheet = sheetMain.CopySheet(postKind.POSTKIND_NAME);

                    ICellStyle style1 = workbook.CreateCellStyle();//样式
                    style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
                    style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
                                                                                          //设置边框
                    style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    style1.WrapText = true;//自动换行

                    #endregion 设置样式

3.将模板中占位符给替换掉

      #region 替换里面占位符数据

                    //第一行
                    IRow RowData1 = sheet.GetRow(0);
                    RowData1.GetCell(0).SetCellValue(RowData1.GetCell(0).StringCellValue.Replace("{0}", postKind.POSTKIND_NAME));

                    //第二行
                    IRow RowData2 = sheet.GetRow(1);
                    RowData2.GetCell(0).SetCellValue(RowData2.GetCell(0).StringCellValue.Replace("{1}", getAC.SENDER).Replace("{2}", getAC.POSTOFFICE_AC_NUMBER));
                    RowData2.GetCell(7).SetCellValue(RowData2.GetCell(7).StringCellValue.Replace("{0}",  DateTime.Now.ToString()));

                    IRow RowData27 = sheet.GetRow(27);
                    RowData27.GetCell(2).SetCellValue(RowData27.GetCell(2).StringCellValue.Replace("{0}", "一千五百万"));
                    RowData27.GetCell(11).SetCellValue(RowData27.GetCell(11).StringCellValue.Replace("{0}", "2018一千五百万-02-02"));

                    IRow RowData28 = sheet.GetRow(28);
                   // RowData28.GetCell(3).SetCellValue(RowData28.GetCell(3).StringCellValue.Replace("{0}", par.PostOfficeName));
                    RowData28.GetCell(3).SetCellValue( par.PostOfficeName);

                    IRow RowData20 = sheet.GetRow(20);
                    RowData20.GetCell(6).SetCellValue(RowData20.GetCell(6).StringCellValue.Replace("{0}", exportData.Sum(c=>c.Count).ToString()));
                    RowData20.GetCell(10).SetCellValue(RowData20.GetCell(10).StringCellValue.Replace("{0}", exportData.Sum(c => c.TotalMonery).ToString()));

                    #endregion 替换里面占位符数据

4.往每行中取填充数据

 #endregion 替换里面占位符数据

                    //填写数据
                    int iRowIndex = 4;//第五行

                    foreach (var item1 in exportData)
                    {
                        IRow RowData = sheet.GetRow(iRowIndex);
                        RowData.GetCell(1).SetCellValue(item1.PostKindName);
                        RowData.GetCell(6).SetCellValue(item1.Count.ToString());
                        RowData.GetCell(7).SetCellValue(item1.WeightRegion);
                        RowData.GetCell(8).SetCellValue(item1.EachMonery.ToString());
                        RowData.GetCell(10).SetCellValue(item1.TotalMonery.ToString());
                        iRowIndex++;
                    }

                    #endregion

5.保存文件

    var fileName = Guid.NewGuid() + ".xls";
                //创建一个文件流，将内容写入到硬盘中
                FileStream fs1 = new FileStream(savePath + "\\" + fileName, FileMode.OpenOrCreate);
                workbook.Write(fs1);
                fs1.Flush();
                fs1.Close();