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

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

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

代码如下：

if(File.Exists(templateXlsPath))

{inti = 4, j = 4;using(FileStream file =newFileStream(templateXlsPath, FileMode.Open, Fileaccess.Read)){

HSSFWorkbook _excel =newHSSFWorkbook(file);ISheet _sheetBasic = _excel.GetSheet(sheet_basicInfo.Replace("$",""));

ISheet _sheetStreatLamp = _excel.GetSheet(sheet_LampMoreLess.Replace("$",""));file.Close();foreach(ExcelBasicInfo excelBasicinbasicInfoList)

{

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 streatLampinstreatLamps)

{

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 =newSaveFileDialog();

_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 =newFileStream(_savefileDialog.FileName.ToString().Trim(), FileMode.Create, FileAccess.ReadWrite))

{

_excel.Write(f);

}MessageToolV2.ShowInfo("合并EXCEL成功!");

}

}

}

.csharpcode, .csharpcode pre

{

font-size: small;

color: black;

font-family: consolas, "Courier New", courier, monospace;

background-color: #ffffff;

/*white-space: pre;*/

}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp{ background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt

{

background-color: #f4f4f4;

width: 100%;

margin: 0em;

}

.csharpcode .lnum { color: #606060; }代码效果