Apache POI setting date format not always properly formatting in Excel

Question

I've been working with Apache POI lately to export to XLS. I'm trying to export dates with proper formatting. For this, I use the following code:

CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("dd-mm-yyyy")
);

cell.setCellValue(exportDate.getDate());

cell.setCellStyle(cellStyle);

// To check that this row is properly formatted
log.info("Format of row " + rowIndex + " and cell " + cellIndex + ": " + DateUtil.isCellDateFormatted(cell));

The problem is that in the beginning, Apache POI is exporting this date correctly, but after approximately 20 times exporting a date, it will not be formatted anymore. I've checked at the end of the creation of the workbook if everything is still a date format with DateUtil.isCellDateFormatted(cell) and every call returned true.

Does anyone know what's happening? There is a related question from 5 years ago but unanswered.

Could this be some weird behavior from Excel?

Answer 1

I had the same problem when i was trying to modify cell's style, this problem appears when you exceed a certain number of instances of the class CellStyle. I solved this problem by using the same object for all of the cells witch require the same style. Here is an example of a class that i have created to solve the problem:

package com.h2s.service.utils;

import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;


public class ExcelCellStyle {

    private HSSFCellStyle defaultCellStyle = null;
    private Map<Integer, HSSFCellStyle> mapColoredStyles = new HashMap<>();
    private HSSFWorkbook workBook = null;
    private HSSFCellStyle defaultCellStyleForTitle = null;
    private HSSFCellStyle defaultCellStyleForBigTitle = null;
    private HSSFFont bigFont;

    private ExcelCellStyle(){
    }

    public static ExcelCellStyle getInstance(){
        return new ExcelCellStyle();
    }

    public HSSFCellStyle getDefaultCellStyle() {
        if(defaultCellStyle == null){
            generateDefaultStyle();
        }
        return defaultCellStyle;
    }

    private void generateDefaultStyle() {
        defaultCellStyle = workBook.createCellStyle();
        defaultCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        defaultCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        defaultCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        defaultCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    }

    public HSSFCellStyle getColoredCellStyle(short index) {
        if(!mapColoredStyles.containsKey(index)){
            HSSFCellStyle newCellStyle = workBook.createCellStyle();
            newCellStyle.setFillForegroundColor(index);
            newCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            mapColoredStyles.put((int) index, newCellStyle);
        }
        return mapColoredStyles.get((int)index);
    }

    public void setWorkBook(HSSFWorkbook wb) {
        this.workBook  = wb;
    }

    public HSSFCellStyle getDefaultCellStyleForTitle() {
        if(defaultCellStyleForTitle == null){
            defaultCellStyleForTitle = workBook.createCellStyle();
            defaultCellStyleForTitle.setBorderRight(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForTitle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForTitle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForTitle.setBorderTop(HSSFCellStyle.BORDER_THICK);
        }
        return defaultCellStyleForTitle;
    }

    public HSSFCellStyle getDefaultCellStyleForBigTitle() {
        if(defaultCellStyleForBigTitle == null){
            defaultCellStyleForBigTitle = workBook.createCellStyle();
            defaultCellStyleForBigTitle.setBorderRight(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForBigTitle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForBigTitle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForBigTitle.setBorderTop(HSSFCellStyle.BORDER_THICK);
            defaultCellStyleForBigTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            defaultCellStyleForBigTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            defaultCellStyleForBigTitle.setFont(getBigFont());
            defaultCellStyleForBigTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            defaultCellStyleForBigTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        }
        return defaultCellStyleForBigTitle;
    }

    private HSSFFont getBigFont() {
        if(bigFont == null){
            bigFont = workBook.createFont();
            bigFont.setFontHeightInPoints((short) 28);
            bigFont.setFontName("Calibri");
        }
        return bigFont;
    }
}

Have a nice day.