[C#]利用NPOI以模板形式生成EXCEL

2019-11-14 16:45:50

字体：大中小

来源：转载

供稿：网友

在开发中，有时候需要生成的Excel样子比较复杂，如果用代码形式去控制EXCEL比较繁琐费时，所以事先做好需要生成EXCEL的模板，再生成的时候往里面填充数据这样子比较简便。

这里利用NPOI（链接：https://npoi.codeplex.com/）第三方东西来实现；

代码如下：

                if (File.Exists(templateXlsPath))                {                    int i = 4, j = 4;                    using (FileStream file = new FileStream(templateXlsPath, FileMode.Open, Fileaccess.Read))                    {                        HSSFWorkbook _excel = new HSSFWorkbook(file);                        ISheet _sheetBasic = _excel.GetSheet(sheet_basicInfo.Replace("$", ""));                        ISheet _sheetStreatLamp = _excel.GetSheet(sheet_LampMoreLess.Replace("$", ""));                        file.Close();                        foreach (ExcelBasicInfo excelBasic in basicInfoList)                        {                            IRow _row = _sheetBasic.CreateRow(i);                            _row.CreateCell(0).SetCellValue(excelBasic.Number);                            _row.CreateCell(1).SetCellValue(excelBasic.TeamName);                            _row.CreateCell(2).SetCellValue(excelBasic.CtrlCabNumber);                            _row.CreateCell(3).SetCellValue(excelBasic.CurrentStatus);                            _row.CreateCell(4).SetCellValue(excelBasic.DevicesUseTime);                            _row.CreateCell(5).SetCellValue(excelBasic.ConfirmLampCount);                            _row.CreateCell(6).SetCellValue(excelBasic.CalculateKW);                            _row.CreateCell(7).SetCellValue(excelBasic.OldDeviceNumber);                            _row.CreateCell(8).SetCellValue(excelBasic.CtrlCabType);                            _row.CreateCell(9).SetCellValue(excelBasic.InstallationSites);                            _row.CreateCell(10).SetCellValue(excelBasic.Section);                            _row.CreateCell(11).SetCellValue(excelBasic.SuperiorPower);                            _row.CreateCell(12).SetCellValue(excelBasic.TableName);                            _row.CreateCell(13).SetCellValue(excelBasic.LampCount);                            _row.CreateCell(14).SetCellValue(excelBasic.LampTypeA);                            _row.CreateCell(15).SetCellValue(excelBasic.LamppowerA);                            _row.CreateCell(16).SetCellValue(excelBasic.LampCountA);                            _row.CreateCell(17).SetCellValue(excelBasic.RunModeA);                            _row.CreateCell(18).SetCellValue(excelBasic.LampTypeB);                            _row.CreateCell(19).SetCellValue(excelBasic.LamppowerB);                            _row.CreateCell(20).SetCellValue(excelBasic.LampCountB);                            _row.CreateCell(21).SetCellValue(excelBasic.RunModeB);                            _row.CreateCell(22).SetCellValue(excelBasic.LampTypeC);                            _row.CreateCell(23).SetCellValue(excelBasic.LamppowerC);                            _row.CreateCell(24).SetCellValue(excelBasic.LampCountC);                            _row.CreateCell(25).SetCellValue(excelBasic.RunModeC);                            _row.CreateCell(26).SetCellValue(excelBasic.LampTypeD);                            _row.CreateCell(27).SetCellValue(excelBasic.LamppowerD);                            _row.CreateCell(28).SetCellValue(excelBasic.LampCountD);                            _row.CreateCell(29).SetCellValue(excelBasic.RunModeD);                            _row.CreateCell(30).SetCellValue(excelBasic.Remark);                            i++;                        }                        foreach (ExcelStreetLamp streatLamp in streatLamps)                        {                            IRow _row = _sheetStreatLamp.CreateRow(j);                            _row.CreateCell(0).SetCellValue(streatLamp.Number);                            _row.CreateCell(1).SetCellValue(streatLamp.TeamName);                            _row.CreateCell(2).SetCellValue(streatLamp.PRojectName);                            _row.CreateCell(3).SetCellValue(streatLamp.CtrlCabNumber);                            _row.CreateCell(4).SetCellValue(streatLamp.ChangeDate);                            _row.CreateCell(5).SetCellValue(streatLamp.MoreLess);                            _row.CreateCell(6).SetCellValue(streatLamp.MoreLessTotalCount);                            _row.CreateCell(7).SetCellValue(streatLamp.LampTypeA);                            _row.CreateCell(8).SetCellValue(streatLamp.LamppowerA);                            _row.CreateCell(9).SetCellValue(streatLamp.LampCountA);                            _row.CreateCell(10).SetCellValue(streatLamp.RunModeA);                            _row.CreateCell(11).SetCellValue(streatLamp.LampTypeB);                            _row.CreateCell(12).SetCellValue(streatLamp.LamppowerB);                            _row.CreateCell(13).SetCellValue(streatLamp.LampCountB);                            _row.CreateCell(14).SetCellValue(streatLamp.RunModeB);                            _row.CreateCell(15).SetCellValue(streatLamp.LampTypeC);                            _row.CreateCell(16).SetCellValue(streatLamp.LamppowerC);                            _row.CreateCell(17).SetCellValue(streatLamp.LampCountC);                            _row.CreateCell(18).SetCellValue(streatLamp.RunModeC);                            _row.CreateCell(19).SetCellValue(streatLamp.LampTypeD);                            _row.CreateCell(20).SetCellValue(streatLamp.LamppowerD);                            _row.CreateCell(21).SetCellValue(streatLamp.LampCountD);                            _row.CreateCell(22).SetCellValue(streatLamp.RunModeD);                            _row.CreateCell(23).SetCellValue(streatLamp.Remark);                            j++;                        }                        SaveFileDialog _savefileDialog = new SaveFileDialog();                        _savefileDialog.Filter = " excel files(*.xls)|*.xls|All files(*.*)|*.*";                        _savefileDialog.RestoreDirectory = true;                        _savefileDialog.FileName = string.Format("上海路灯每月增减表_{0}", DateTime.Now.ToString("yyyyMMddHHmm"));                        if (_savefileDialog.ShowDialog() == DialogResult.OK)                        {                            using (FileStream f = new FileStream(_savefileDialog.FileName.ToString().Trim(), FileMode.Create, FileAccess.ReadWrite))                            {                                _excel.Write(f);                            }                            MessageToolV2.ShowInfo("合并EXCEL成功!");                        }                    }                }