Bug 14879 - Cannot open Excel file - XL: Error Message: Too Many Different Cell Formats
Summary: Cannot open Excel file - XL: Error Message: Too Many Different Cell Formats
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 1.5.1
Hardware: PC All
: P3 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2002-11-27 03:54 UTC by Yuri Abelev
Modified: 2005-03-20 17:06 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Yuri Abelev 2002-11-27 03:54:16 UTC
I've got a problem opening Excel file after it was populated by using POI 1.5.1

This is the code I'm using:

        POIFSFileSystem fs      =
            new POIFSFileSystem(new FileInputStream("Book1.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        // If number of rows 402 or smaller - Excel file opens OK	
        for (iRow=0; iRow <403; iRow++){  
          HSSFRow row = sheet.createRow((short)iRow);
          row = sheet.createRow((short)iRow);
          for (sCell=0; sCell<10; sCell++){
              HSSFCell cell = row.createCell((short)(sCell));
              cell = row.createCell((short)sCell);
              HSSFCellStyle style = wb.createCellStyle();
              style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
              style.setBottomBorderColor(HSSFCellStyle.BLACK);
              style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
              style.setLeftBorderColor(HSSFCellStyle.BLACK);
              style.setBorderRight(HSSFCellStyle.BORDER_THIN);
              style.setRightBorderColor(HSSFCellStyle.BLACK);
              style.setBorderTop(HSSFCellStyle.BORDER_THIN);
              style.setTopBorderColor(HSSFCellStyle.BLACK);
              style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
              cell.setCellStyle(style);
              cell.setCellValue("my cell");
          }
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("Book2.xls");
        wb.write(fileOut);
        fileOut.close();

When I open Book2.xls, it gives an error message "Too Many Different Cell 
Formats".
When I decrease number of rows from 403 to 402 or smaller, Book2.xls opens with 
no problem.

I found this article on the Microsoft Knowledge Base website : 
http://support.microsoft.com/default.aspx?scid=KB;en-us;213904& 
It says: " This problem occurs when the workbook contains more than 
approximately 4,000 different combinations of cell formats."
But my code has only 1 formatting combination not 4000! However, the number of 
cells on my worksheet is about 4000 (403 X 10).
The problem goes away if I comment all code with 'style" (HSSFCellStyle).

But how can I format my cells then?
Comment 1 Jason Height 2002-11-27 04:04:31 UTC
Just write your code differently:

 ...
 HSSFCellStyle style = wb.createCellStyle();
 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
 style.setBottomBorderColor(HSSFCellStyle.BLACK);
 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
 style.setLeftBorderColor(HSSFCellStyle.BLACK);
 style.setBorderRight(HSSFCellStyle.BORDER_THIN);
 style.setRightBorderColor(HSSFCellStyle.BLACK);
 style.setBorderTop(HSSFCellStyle.BORDER_THIN);
 style.setTopBorderColor(HSSFCellStyle.BLACK);
 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

 for (iRow=0; iRow <403; iRow++){  
   HSSFRow row = sheet.createRow((short)iRow);
   row = sheet.createRow((short)iRow);
   for (sCell=0; sCell<10; sCell++){
     HSSFCell cell = row.createCell((short)(sCell));
     cell = row.createCell((short)sCell);
     cell.setCellStyle(style);
     cell.setCellValue("my cell");
   }
 }


Jason