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
