trouble with "Too many different cell formats" issue

[EA...@helvetiaprevision.com]

Hello,

While generating a spreadsheet with some sheets (about 10) and many data 
inserted into it, I got the following error when the created workbook was 
opened with MS Excel 2002: "Too many different cell formats".

This is a well-known issue reported by microsoft saying that workbooks 
with more than 4000 different cell formats (or combinations as Microsoft 
calles them) can not be handled and therefore, formatting is disabled for 
the remaining cells. Everything ok up to the this point.

I generate lots of data that are included in the workbook, more than 4000 
data cells. However, I do not apply more than 20 different styles. 
According to Microsoft, two cells with the same format do not account as 
two different formats. Nevertheless, excel complains. Trying to find out a 
solution, first I set the same style to all the cells. When more than 4000 
cells were created I came accross to the same error.  Style stands for all 
font formatting (for example: typeface, font size, italic, bold, and 
underline), borders (for example: location, weight, and color), cell 
patterns, number formatting, alignment, and cell protection.

First try did not work, so afterwards I tried the following: 
I generated styles with the instruction: 

HSSFWorkbook wb = new HSSFWorkbook();
.....
HSSFCellStyle style = wb.createCellStyle();

but, I did not set the style as it should be done: 

# cell.setCellStyle(style);    // note that it is commented out. cell is 
an instance of HSSFCell class

Excell still complained..... the same error.

Finally, I commented out the line where style is created from the workbook 
reference. In this case, Excel did not complain and all the cells were 
unformatted.

I guess that HSSF library creates two different styles when the following 
code is executed:

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("0");
HSSFRow row = sheet.createRow(0);
HSSFCell cell1 = row.createCell(0);
// Style type 1 is created
HSSFCellStyle style1 = wb.createCellStyle();
cell1.setCellStyle(style1);
cell1.setCellValue("first cell data");

HSSFCell cell2 = row.createCell(1);
// Style type 2 is created
HSSFCellStyle style2 = wb.createCellStyle();
cell2.setCellStyle(style2);
cell2.setCellValue("second cell data");

The right behavior should be that only one style is created eventhough it 
is set in two different cells. I think that HSSF creates a style every 
time createCellStyle() method is called from the workbook reference.

Is there any way to fix it? Maybe I am not rigth in my thinking.

Thanks in advance

Eguzki Astiz Lezaun
Helvetia Prevision
Software engineer 

Re: trouble with "Too many different cell formats" issue

[av...@itellix.com]

Eguzki,

See this:

http://jakarta.apache.org/poi/faq.html#faq-N100C3

Regards
-
Avik


Quoting EAstiz@helvetiaprevision.com:

> Hello,
>
> While generating a spreadsheet with some sheets (about 10) and many data
> inserted into it, I got the following error when the created workbook was
> opened with MS Excel 2002: "Too many different cell formats".
>
> This is a well-known issue reported by microsoft saying that workbooks
> with more than 4000 different cell formats (or combinations as Microsoft
> calles them) can not be handled and therefore, formatting is disabled for
> the remaining cells. Everything ok up to the this point.
>
> I generate lots of data that are included in the workbook, more than 4000
> data cells. However, I do not apply more than 20 different styles.
> According to Microsoft, two cells with the same format do not account as
> two different formats. Nevertheless, excel complains. Trying to find out a
> solution, first I set the same style to all the cells. When more than 4000
> cells were created I came accross to the same error.  Style stands for all
> font formatting (for example: typeface, font size, italic, bold, and
> underline), borders (for example: location, weight, and color), cell
> patterns, number formatting, alignment, and cell protection.
>
> First try did not work, so afterwards I tried the following:
> I generated styles with the instruction:
>
> HSSFWorkbook wb = new HSSFWorkbook();
> .....
> HSSFCellStyle style = wb.createCellStyle();
>
> but, I did not set the style as it should be done:
>
> # cell.setCellStyle(style);    // note that it is commented out. cell is
> an instance of HSSFCell class
>
> Excell still complained..... the same error.
>
> Finally, I commented out the line where style is created from the workbook
> reference. In this case, Excel did not complain and all the cells were
> unformatted.
>
> I guess that HSSF library creates two different styles when the following
> code is executed:
>
> HSSFWorkbook wb = new HSSFWorkbook();
> HSSFSheet sheet = wb.createSheet("0");
> HSSFRow row = sheet.createRow(0);
> HSSFCell cell1 = row.createCell(0);
> // Style type 1 is created
> HSSFCellStyle style1 = wb.createCellStyle();
> cell1.setCellStyle(style1);
> cell1.setCellValue("first cell data");
>
> HSSFCell cell2 = row.createCell(1);
> // Style type 2 is created
> HSSFCellStyle style2 = wb.createCellStyle();
> cell2.setCellStyle(style2);
> cell2.setCellValue("second cell data");
>
> The right behavior should be that only one style is created eventhough it
> is set in two different cells. I think that HSSF creates a style every
> time createCellStyle() method is called from the workbook reference.
>
> Is there any way to fix it? Maybe I am not rigth in my thinking.
>
> Thanks in advance
>
> Eguzki Astiz Lezaun
> Helvetia Prevision
> Software engineer
>



---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/