问题描述
最近在用NPOI处理Excel03,主要是根据判断要求遍历修改单元格的背景色。第一次运行时候没问题，当第二次运行同一个表格的时候报错，提示为：Themaximumnumberofcellstyleswasexceeded.Youcandefineupto4000stylesina.xlsworkbook。当表格有修改后又不会报错。好像每次运行上一次CreatCell的都没释放掉一样，可是程序每次运行后我都把程序进程退出了啊。有人说把ICellStylestyle=myworkbook.CreateCellStyle();//样式//边框style.BorderBottom=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderLeft=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderRight=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderTop=NPOI.SS.UserModel.BorderStyle.Thin;这段放在循环外面，可是这样处理后结果不对。纠结好久了，NPOI处理Excel的速度还是蛮快的，不想换其它方法了。还希望大神赐教。附上我的源码和要处理的Excel表格，在线等候！PS：程序处理原则参见Excel里面说明的Sheet页。
关键代码：privatevoidbutton1_Click(objectsender,EventArgse){intn=0,sheetNum=0;stringtmpID="",tmpValue="",sheetName="";HSSFSheetmysheet;HSSFWorkbookmyworkbook;//IWorkbookmyworkbook;//ISheetmysheet;OpenFileDialogopenFileDialog=newOpenFileDialog();openFileDialog.Filter="Excel2003文件|*.xls|Excel2007文件|*.xlsx";//openFileDialog.Filter="Excel文件(*.xls,*.xlsx)|*.xls;*.xlsx";if(openFileDialog.ShowDialog()==DialogResult.OK){toolStripStatusLabel1.Text="正在处理...";button1.Enabled=false;Application.DoEvents();stringextension=System.IO.Path.GetExtension(openFileDialog.FileName);//if(extension.Equals(".xls"))//myworkbook=newHSSFWorkbook(FileToStream(openFileDialog.FileName));//elsemyworkbook=newXSSFWorkbook(FileToStream(openFileDialog.FileName));myworkbook=newHSSFWorkbook(FileToStream(openFileDialog.FileName));sheetName=myworkbook.GetSheetName(1);mysheet=(HSSFSheet)myworkbook.GetSheet(sheetName);sheetNum=mysheet.LastRowNum;for(inti=0;i<m_RQInfos.Length;i++){DataTabledt;dt=ExcelRender.RenderFromExcel(mysheet,1,sheetNum,i*2,i*2+2);//读取foreach(DataRowdrindt.Rows){tmpID=dr["台区编码"].ToString().Trim();tmpValue=dr["线损值"].ToString().Trim();n=m_RQInfos[i].TQInfos.Length;System.Array.Resize(refm_RQInfos[i].TQInfos,n+1);m_RQInfos[i].TQInfos[n].ID=tmpID;m_RQInfos[i].TQInfos[n].Value=tmpValue;}}sheetName=myworkbook.GetSheetName(0);mysheet=(HSSFSheet)myworkbook.GetSheet(sheetName);sheetNum=mysheet.LastRowNum;DataTabletable=newDataTable();IRowheaderRow=mysheet.GetRow(0);intcellCount=headerRow.LastCellNum;//ICellStylestyle=myworkbook.CreateCellStyle();//样式////边框//style.BorderBottom=NPOI.SS.UserModel.BorderStyle.Thin;//style.BorderLeft=NPOI.SS.UserModel.BorderStyle.Thin;//style.BorderRight=NPOI.SS.UserModel.BorderStyle.Thin;//style.BorderTop=NPOI.SS.UserModel.BorderStyle.Thin;for(inti=1;i<=mysheet.LastRowNum;i++){stringtmpTQID="",tmpRQ="";IRowrow=mysheet.GetRow(i);tmpTQID=ExcelRender.GetCellValue(row.GetCell(3));for(intj=0;j<m_RQInfos.Length;j++){for(intk=0;k<m_RQInfos[j].TQInfos.Length;k++){if(tmpTQID==m_RQInfos[j].TQInfos[k].ID){tmpRQ=m_RQInfos[j].RQTime;for(intm=6;m<=36;m++)//日期填值{stringtmpqq=mysheet.GetRow(0).GetCell(m).ToString();if(tmpRQ==tmpqq){stringtmpVales=m_RQInfos[j].TQInfos[k].Value;if(IsNumeric(tmpVales)){if(Convert.ToDecimal(tmpVales)>=-1&&Convert.ToDecimal(tmpVales)<=5){ICellStylestyle=myworkbook.CreateCellStyle();//样式//第二次运行此处报错引用//边框style.BorderBottom=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderLeft=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderRight=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderTop=NPOI.SS.UserModel.BorderStyle.Thin;//设置背景色style.FillForegroundColor=NPOI.HSSF.Util.HSSFColor.Green.Index;style.FillPattern=FillPattern.SolidForeground;style.FillBackgroundColor=NPOI.HSSF.Util.HSSFColor.Green.Index;row.Cells[m].CellStyle=style;}else{ICellStylestyle=myworkbook.CreateCellStyle();//样式//边框style.BorderBottom=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderLeft=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderRight=NPOI.SS.UserModel.BorderStyle.Thin;style.BorderTop=NPOI.SS.UserModel.BorderStyle.Thin;//设置背景色style.FillForegroundColor=NPOI.HSSF.Util.HSSFColor.Red.Index;style.FillPattern=FillPattern.SolidForeground;style.FillBackgroundColor=NPOI.HSSF.Util.HSSFColor.Red.Index;row.Cells[m].CellStyle=style;}//row.Cells[m].CellStyle=style;}}}}}}}FileStreamfs1=File.OpenWrite(openFileDialog.FileName);myworkbook.Write(fs1);//向打开的这个xls文件中写入表并保存。fs1.Close();toolStripStatusLabel1.Text="";button1.Enabled=true;MessageBox.Show("完成");}foreach(FormtmpExistFrminApplication.OpenForms){if(tmpExistFrm.Name=="Form1"){tmpExistFrm.Close();break;}}}
解决方案
解决方案二：
样式创建超出了限制了没必要每一个单元格都去createstyle可以在循环外面创建几种需要的样式出来用的时候直接cellstyle=你创建好的样式