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?
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