Cell style is lost or not displayed in Excel 97-2003 (.xls)

Question

I am using the Apache POI library to export data to Excel. I have tried all the latest versions (3.17, 4.1.2, and 5.2.1).

I have a problem with Excel 97 (.xls) format in relation to cell styles. The cell style somehow is lost (or not displayed) after a certain number of columns.

Here is my sample code:

private void exportXls() {
  try (
      OutputStream os = new FileOutputStream("test.xls");
      Workbook wb = new HSSFWorkbook();) {
    Sheet sh = wb.createSheet("test");
    Row r = sh.createRow(0);
    for (int i = 0; i < 50; i++) {
      Cell c = r.createCell(i);
      c.setCellValue(i + 1);
      
      CellStyle cs = wb.createCellStyle();
      cs.setFillBackgroundColor(IndexedColors.WHITE.index);
      cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      cs.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
      c.setCellStyle(cs);
    }
    wb.write(os);
    os.flush();
  } catch (Exception e) {
    e.printStackTrace();
  }
}

And the result as viewed by MS Excel 2019 Viewed by MS Excel

As you can see, the style/format is lost after cell 43rd.

But, when I open the same file by other applications like XLS Viewer Free (from Microsoft Store) or Google Sheets (online), the style/format still exists and is displayed well. Viewed by XLS Viewer Free Viewed by Google Sheets

Could anyone please tell me what is going on here?

Did I miss something in my code?

Is there any hidden setting in MS Excel that causes this problem?

Thank you.

You're probably running out of cell styles - Excel has a limit. Create the styles outside the loop? — Gagravarr, Mar 09 '22 at 16:59
https://stackoverflow.com/search?q=%5Bapache-poi%5D+cell+style+limit+workbook+level — Axel Richter, Mar 09 '22 at 18:24
@Gagravarr How it could be? If you look at my sample code, I created only 50 cells corresponding to 50 cell styles. Whereas POI documents that HSSF can have up to 4000 unique styles. Here is my file https://drive.google.com/file/d/1ooU5hQBEiDyVac2WjGL7PO8PQNB7uEOA/view?usp=sharing — Hoang Ngo, Mar 10 '22 at 02:48

Axel Richter · Accepted Answer · 2022-03-11T06:49:56.600

Creating cell styles for each single cell is not a good idea using apache poi. Cell styles are stored on workbook level in Excel. The sheets and cells share the cell styles if possible.

And there are limits for maximum count of different cell styles in all Excel versions. The limit for the binary *.xls is less than the one for the OOXML *.xlsx.

The limit alone cannot be the only reason for the result you have. But it seems as if Excel is not very happy with the 50 exactly same cell styles in workbook. Those are memory waste as only one shared style would be necessary as all the 50 cells share the same style.

Solutions are:

Do creating the cell styles on workbook level outside cell creating loops and only set the styles to the cells in the loop.

Example:

 private static void exportXlsCorrect() {
  try (
   OutputStream os = new FileOutputStream("testCorrect.xls");
   Workbook wb = new HSSFWorkbook();) {
       
   CellStyle cs = wb.createCellStyle();
   cs.setFillBackgroundColor(IndexedColors.WHITE.index);
   cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   cs.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
    
   Sheet sh = wb.createSheet("test");
   Row r = sh.createRow(0);
   for (int i = 0; i < 50; i++) {
    Cell c = r.createCell(i);
    c.setCellValue(i + 1);
      
    c.setCellStyle(cs);
   }
   wb.write(os);
   os.flush();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

Sometimes it is not really possible to know all possible needed cell styles before creating the cells. Then CellUtil can be used. This has a method CellUtil.setCellStyleProperties which is able to set specific style properties to cells. Doing that new cell styles are created on workbook level only if needed. If already present, the present cell styles are used.

Example:

 private static void exportXlsUsingCellUtil() {
  try (
   OutputStream os = new FileOutputStream("testUsingCellUtil.xls");
   Workbook wb = new HSSFWorkbook();) {
            
   Sheet sh = wb.createSheet("test");
   Row r = sh.createRow(0);
   for (int i = 0; i < 50; i++) {
    Cell c = r.createCell(i);
    c.setCellValue(i + 1);
    
    java.util.Map<java.lang.String,java.lang.Object> properties = new java.util.HashMap<java.lang.String,java.lang.Object>();
    properties.put(org.apache.poi.ss.util.CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.WHITE.index);
    properties.put(org.apache.poi.ss.util.CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.LIGHT_BLUE.getIndex());
    properties.put(org.apache.poi.ss.util.CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
    org.apache.poi.ss.util.CellUtil.setCellStyleProperties(c, properties);

   }
   wb.write(os);
   os.flush();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

You are right: "But it seems as if Excel is not very happy with the 50 exactly same cell styles in workbook. Those are memory waste as only one shared style would be necessary as all the 50 cells share the same style.". Thank you so much — Hoang Ngo, Mar 11 '22 at 07:41

Cell style is lost or not displayed in Excel 97-2003 (.xls)

1 Answers1