Bug 14879

Summary: Cannot open Excel file - XL: Error Message: Too Many Different Cell Formats
Product: POI Reporter: Yuri Abelev <Yuri.X.Abelev>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: critical    
Priority: P3    
Version: 1.5.1   
Target Milestone: ---   
Hardware: PC   
OS: All   

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