Re: Scotts HSSFR4 and "too many different cell formats" error

Your problem is that you are creating a new cell style for each cell. I
believe the logical limit is 255 for Excel 2003 and below.

Each time you invoke the HSSFWorkbook_createCellStyle(HSSFWorkbook);
procedure you are creating a new cell style. What you want to do is create 1
cell style per column, generally speaking that is.

HTH,
--
James R. Perkins
http://twitter.com/the_jamezp


On Thu, Aug 27, 2009 at 07:13, tim <tim2006@xxxxxxxxxxx> wrote:

I am using Scotts HSSFR4 to create an excel sheet via RPG. When I open the
created sheet in excel 2007, it looks fine. When I open it in Excel 2003, I
get the following errors:

"Too many different formats"

"Excel encountered an error and had to remove some formatting to avoid
corrupting the workbook. Please re-check your formatting carefully"

Any ideas on how to fix this?

Thanks.

Sample code:
col# = 0;
HSSFCellStyle = HSSFWorkbook_createCellStyle(HSSFWorkbook);
HSSFDataFormat = HSSFWorkbook_createDataFormat(HSSFWorkbook);
jString = new_String('######');
NumFmt = HSSFDataFormat_getFormat(HSSFDataFormat : jString );
HSSFCellStyle_setAlignment(HSSFCellStyle :ALIGN_Center);
HSSFCellStyle_setBorderBottom(HSSFCellStyle :border_thin);
HSSFCellStyle_setBordertop(HSSFCellStyle :border_thin);
HSSFCellStyle_setBorderleft(HSSFCellStyle :border_thin);
HSSFCellStyle_setBorderright(HSSFCellStyle :border_thin);
HSSFCell = HSSFRow_createCell(hssfRow:col#);
HSSFCell_setCellType(hssfcell: CELL_TYPE_NUMERIC);
HSSFCell_setCellStyle(hssfcell: HSSFCellStyle);
HSSFCell_setCellValueD(hssfcell: p5cl);

col# = 1;
HSSFCellStyle = HSSFWorkbook_createCellStyle(HSSFWorkbook);
HSSFDataFormat = HSSFWorkbook_createDataFormat(HSSFWorkbook);
jString = new_String('m/d/yyyy');
NumFmt = HSSFDataFormat_getFormat(HSSFDataFormat : jString );
HSSFCellStyle_setDataFormat(HSSFCellStyle :NumFmt);
HSSFCellStyle_setAlignment(HSSFCellStyle :ALIGN_center);
HSSFCellStyle_setBorderBottom(HSSFCellStyle :border_thin);
HSSFCellStyle_setBordertop(HSSFCellStyle :border_thin);
HSSFCellStyle_setBorderleft(HSSFCellStyle :border_thin);
HSSFCellStyle_setBorderright(HSSFCellStyle :border_thin);
HSSFCell = HSSFRow_createCell(hssfRow:col#);
HSSFCell_setCellType(hssfcell: CELL_TYPE_NUMERIC);
HSSFCell_setCellStyle(hssfcell: HSSFCellStyle);
julian = %DIFF ( cl_t : d'01/01/1900' : *DAYS) + 2;
HSSFCell_setCellValueD(hssfcell: julian);

col# = 2; // Appr
HSSFCellStyle = HSSFWorkbook_createCellStyle(HSSFWorkbook);
HSSFDataFormat = HSSFWorkbook_createDataFormat(HSSFWorkbook);
HSSFCellStyle_setAlignment(HSSFCellStyle :ALIGN_center);
HSSFCellStyle_setBorderBottom(HSSFCellStyle :border_thin);
HSSFCellStyle_setBordertop(HSSFCellStyle :border_thin);
HSSFCellStyle_setBorderleft(HSSFCellStyle :border_thin);
HSSFCellStyle_setBorderright(HSSFCellStyle :border_thin);
HSSFCell = HSSFRow_createCell(hssfRow:col#);
HSSFCell_setCellType(hssfcell: CELL_TYPE_STRING);
HSSFCell_setCellStyle(hssfcell: HSSFCellStyle);
if c_c = 'Y';
jString = new_String(%trim( 'O' ));
else;
jString = new_String(%trim( '3' ));
endif;
HSSFCell_setCellValueStr(hssfcell: jString);



Etc for several columns....

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.