NPOI 处理greatCell 超过4000行报错

zyr987503101 2014-12-25 05:09:46

最近在用NPOI 处理Excel03,主要是根据判断要求遍历修改单元格的背景色。第一次运行时候没问题，当第二次运行同一个表格的时候报错，提示为：The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook。当表格有修改后又不会报错。好像每次运行上一次CreatCell的都没释放掉一样，可是程序每次运行后我都把程序进程退出了啊。有人说把 ICellStyle style = 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页。

关键代码： private void button1_Click(object sender, EventArgs e)
{
int n = 0, sheetNum=0;
string tmpID = "", tmpValue = "", sheetName="";
HSSFSheet mysheet;
HSSFWorkbook myworkbook;
//IWorkbook myworkbook;
//ISheet mysheet;

OpenFileDialog openFileDialog = new OpenFileDialog();
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();
string extension = System.IO.Path.GetExtension(openFileDialog.FileName);
//if (extension.Equals(".xls"))
// myworkbook = new HSSFWorkbook(FileToStream(openFileDialog.FileName));
//else myworkbook = new XSSFWorkbook(FileToStream(openFileDialog.FileName));

myworkbook = new HSSFWorkbook(FileToStream(openFileDialog.FileName));
sheetName = myworkbook.GetSheetName(1);
mysheet = (HSSFSheet)myworkbook.GetSheet(sheetName);
sheetNum = mysheet.LastRowNum;

for (int i = 0; i < m_RQInfos.Length; i++)
{
DataTable dt;
dt = ExcelRender.RenderFromExcel(mysheet, 1, sheetNum, i*2, i*2+2);//读取

foreach (DataRow dr in dt.Rows)
{
tmpID = dr["台区编码"].ToString().Trim();
tmpValue = dr["线损值"].ToString().Trim();

n = m_RQInfos[i].TQInfos.Length;
System.Array.Resize(ref m_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;

DataTable table = new DataTable();
IRow headerRow = mysheet.GetRow(0);
int cellCount = headerRow.LastCellNum;

//ICellStyle style = 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 (int i = 1; i <= mysheet.LastRowNum; i++)
{
string tmpTQID="",tmpRQ="";
IRow row = mysheet.GetRow(i);

tmpTQID =ExcelRender.GetCellValue(row.GetCell(3));

for (int j = 0; j < m_RQInfos.Length; j++)
{
for (int k= 0; k< m_RQInfos[j].TQInfos.Length; k++)
{
if (tmpTQID == m_RQInfos[j].TQInfos[k].ID)
{
tmpRQ=m_RQInfos[j].RQTime;
for (int m =6; m <=36; m++)//日期填值
{
string tmpqq = mysheet.GetRow(0).GetCell(m).ToString();
if (tmpRQ == tmpqq)
{
string tmpVales= m_RQInfos[j].TQInfos[k].Value;
if (IsNumeric(tmpVales))
{
if (Convert.ToDecimal(tmpVales) >= -1 && Convert.ToDecimal(tmpVales) <= 5)
{

ICellStyle style = 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
{
ICellStyle style = 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;

}
}
}

}
}

}
}

FileStream fs1 = File.OpenWrite(openFileDialog.FileName);
myworkbook.Write(fs1); //向打开的这个xls文件中写入表并保存。
fs1.Close();
toolStripStatusLabel1.Text = "";
button1.Enabled = true;
MessageBox.Show("完成");

}
foreach (Form tmpExistFrm in Application.OpenForms)
{
if (tmpExistFrm.Name == "Form1")
{
tmpExistFrm.Close();
break;
}
}
}

...全文