Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
To: poi-user@jakarta.apache.org
Subject: trouble with "Too many different cell formats" issue
MIME-Version: 1.0
Message-ID: 
 <OFD6C989E3.6F478633-ONC12571B7.0044AB39-C12571B7.004985BE@HelvetiaPrevision.com>
From: EAstiz@helvetiaprevision.com
Date: Wed, 26 Jul 2006 15:21:42 +0200
Content-Type: multipart/alternative;
 boundary="=_alternative 004985BBC12571B7_="

--=_alternative 004985BBC12571B7_=
Content-Type: text/plain; charset="US-ASCII"

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 

--=_alternative 004985BBC12571B7_=--