WinForm项目开发中NPOI用法实例解析
本文实例展示了WinForm项目开发中NPOI用法，对于C#初学者有一定的借鉴价值。具体实例如下：
privatevoidExportMergeExcel()
{
if(File.Exists(templateXlsPath))
{
inti=4,_recordNo=1;
using(FileStreamfile=newFileStream(templateXlsPath,FileMode.Open,FileAccess.Read))
{
HSSFWorkbook_excel=newHSSFWorkbook(file);
ICellStyle_cellStyle=CreateCellStly(_excel);
ISheet_sheetBasic=_excel.GetSheet(ExcelReadHelper.sheet_BasicInfo.Replace("$",""));
ISheet_sheetStreatLamp=_excel.GetSheet(ExcelReadHelper.sheet_LampMoreLess.Replace("$",""));
ISheet_sheetBasicEx=_excel.GetSheet(ExcelReadHelper.sheet_BasicExInfo.Replace("$",""));
ISheet_sheetStreatLampEx=_excel.GetSheet(ExcelReadHelper.sheet_LampMoreLessExInfo.Replace("$",""));
ISheet_sheetBasicTeamEx=_excel.GetSheet(ExcelReadHelper.sheet_BasicTeamStatistics.Replace("$",""));
ISheet_sheetBasicLampTypeEx=_excel.GetSheet(ExcelReadHelper.sheet_BasicTypeStatistics.Replace("$",""));
ISheet_sheetStreetLampMLEx=_excel.GetSheet(ExcelReadHelper.sheet_LampMoreLessTeamStatistics.Replace("$",""));
ISheet_sheetStreetLampTeamML=_excel.GetSheet(ExcelReadHelper.sheet_LampMoreLessTypeStatistics.Replace("$",""));
file.Close();
FillBasicSheetDb(_sheetBasic,i,_recordNo);
_recordNo=1;i=4;
FillStreetLampDb(_sheetStreatLamp,i,_recordNo);
_recordNo=1;i=4;
FillBasicExSheetDb(_sheetBasicEx,i,_recordNo);
_recordNo=1;i=4;
FillStreetLampExDb(_sheetStreatLampEx,i,_recordNo);
i=1;IRow_rowSum=null;int_lampTotalLampCnt=0,_colLampCnt=0,_ncolLampCnt=0;double_lampTotalLampPw=0,_colLampPw=0,_ncolLampPw=0;
FillBasicTeamExSheetDb(_excel,_rowSum,_sheetBasicTeamEx,_cellStyle,i,_lampTotalLampCnt,_colLampCnt,_ncolLampCnt,_lampTotalLampPw,_colLampPw,_ncolLampPw);
i=1;_lampTotalLampCnt=0;_colLampCnt=0;_ncolLampCnt=0;_lampTotalLampPw=0;_colLampPw=0;_ncolLampPw=0;
FillbasicLampTypeExSheetDb(_excel,_rowSum,_sheetBasicLampTypeEx,_cellStyle,i,_lampTotalLampCnt,_colLampCnt,_ncolLampCnt,_lampTotalLampPw,_colLampPw,_ncolLampPw);
_lampTotalLampCnt=0;_lampTotalLampPw=0;i=1;
FillsheetStreetLampMLSheetDb(_excel,_rowSum,_sheetStreetLampMLEx,_cellStyle,i,_lampTotalLampCnt,_lampTotalLampPw);
_lampTotalLampCnt=0;_lampTotalLampPw=0;i=1;
FillStreetLampTeamMLSheetDb(_excel,_rowSum,_sheetStreetLampTeamML,_cellStyle,i,_lampTotalLampCnt,_lampTotalLampPw);
OutPutMergeExcel(_excel);
}
}
}
privatevoidFillBasicTeamExSheetDb(HSSFWorkbook_excel,IRow_rowSum,ISheet_sheetBasicTeamEx,ICellStyle_cellStyle,inti,int_lampTotalLampCnt,int_colLampCnt,int_ncolLampCnt,double_lampTotalLampPw,double_colLampPw,double_ncolLampPw)
{
foreach(ExcelStatisticsexcelBasicExinbasicTeamExList)
{
IRow_row=_sheetBasicTeamEx.CreateRow(i);
ExcelWriteHelper.CreateStatisticsExcelRow(_row,excelBasicEx,"BasicTeam");
#region总灯数
int_lTotalLampCnt=0;
int.TryParse(excelBasicEx.LampCount,out_lTotalLampCnt);
_lampTotalLampCnt+=_lTotalLampCnt;
#endregion
#region总计算功率(KW)
double_lTotalLampPw=0;
double.TryParse(excelBasicEx.LampPower,out_lTotalLampPw);
_lampTotalLampPw+=_lTotalLampPw;
#endregion
#region汇总灯数
int_cLampCount=0;
int.TryParse(excelBasicEx.CollectCount,out_cLampCount);
_colLampCnt+=_cLampCount;
#endregion
#region汇总功率(KW)
double_cLampPw=0;
double.TryParse(excelBasicEx.CollectPower,out_cLampPw);
_colLampPw+=_cLampPw;
#endregion
#region非汇总灯数
int_ncLampCount=0;
int.TryParse(excelBasicEx.NotCollectCount,out_ncLampCount);
_ncolLampCnt+=_ncLampCount;
#endregion
#region非汇总功率(KW)
double_ncLampPw=0;
double.TryParse(excelBasicEx.NotCollectPower,out_ncLampPw);
_ncolLampPw+=_ncLampPw;
#endregion
i++;
}
_rowSum=_sheetBasicTeamEx.CreateRow(i);
_rowSum.HeightInPoints=20;
_rowSum.CreateCell(0).SetCellValue("合计：");
_rowSum.CreateCell(1).SetCellValue(_lampTotalLampCnt);
_rowSum.CreateCell(2).SetCellValue(_lampTotalLampPw);
_rowSum.CreateCell(3).SetCellValue(_colLampCnt);
_rowSum.CreateCell(4).SetCellValue(_colLampPw);
_rowSum.CreateCell(5).SetCellValue(_ncolLampCnt);
_rowSum.CreateCell(6).SetCellValue(_ncolLampPw);
SetRowStyle(_rowSum,_cellStyle);
}
定义样式：
///<summary>
///样式创建
///eg:
///privateICellStyleCreateCellStly(HSSFWorkbook_excel)
///{
///IFont_font=_excel.CreateFont();
///_font.FontHeightInPoints=11;
///_font.FontName="宋体";
///_font.Boldweight=(short)FontBoldWeight.Bold;
///ICellStyle_cellStyle=_excel.CreateCellStyle();
/////_cellStyle.FillForegroundColor=NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
/////_cellStyle.FillPattern=NPOI.SS.UserModel.FillPattern.SolidForeground;
///_cellStyle.SetFont(_font);
///return_cellStyle;
///}
///为行设置样式
///</summary>
///<paramname="row">IRow</param>
///<paramname="cellStyle">ICellStyle</param>
publicstaticvoidSetRowStyle(thisIRowrow,ICellStylecellStyle)
{
if(row!=null&&cellStyle!=null)
{
for(intu=row.FirstCellNum;u<row.LastCellNum;u++)
{
ICell_cell=row.GetCell(u);
if(_cell!=null)
_cell.CellStyle=cellStyle;
}
}
}