[C#]利用NPOI以模板形式生成EXCEL_.NET_编程开发_程序员俱乐部

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

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

代码如下：

class="csharpcode">                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成功!");
                        }
                    }

                }

代码效果