Apache POI设置日期格式并不总是在Excel中正确格式化

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:

我最近一直在使用Apache POI导出到XLS。我正在尝试使用正确的格式导出日期。为此,我使用以下代码:

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.

问题是,在开始时,Apache POI正在正确导出此日期,但在导出日期约20次后,它将不再格式化。我已经在工作簿创建结束时检查了如果所有内容仍然是DateUtil.isCellDateFormatted(cell)的日期格式,并且每次调用都返回true。

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

有谁知道发生了什么? 5年前有一个相关的问题,但没有答案。

Could this be some weird behavior from Excel?

这可能是Excel的一些奇怪行为吗?

4 个解决方案

#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:

当我试图修改单元格的样式时遇到了同样的问题,当你超过CellStyle类的一定数量的实例时会出现这个问题。我通过对所有需要相同样式的单元使用相同的对象来解决这个问题。以下是我为解决问题而创建的类的示例:

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.

祝你今天愉快。

#2

@Mauro Palsgraaf I used to set the date stye for both XSSF and HSSF and I never faced a problem. I use similar code as explained by XtremeBaumer

@Mauro Palsgraaf我曾经为XSSF和HSSF设置日期,我从来没有遇到过问题。我使用XtremeBaumer解释的类似代码

workbook.createDataFormat().getFormat("dd-mm-yyyy");
style = workbook.createCellStyle()
style.setDataFormat(dateFormat);
cell.setCellStyle(style);

The only difference I can notice in your code is that you use creationHelper to createDataFormat but I use the workbook.I am not sure whether this is really an issue but may be you can try using the Workbook

我在你的代码中可以注意到的唯一区别是你使用creationHelper来创建createDataFormat,但我使用的是workbook。我不确定这是否真的是一个问题,但可能你可以尝试使用工作簿

createHelper.createDataFormat().getFormat("dd-mm-yyyy")

#3

The input from everybody so far helped to get the solution. I'm not sure how this could be the problem, but what helped was by creating the CellStyle once and then use the same style for different cells. Before I dynamically created the rows and cells which created a new CellStyle every time. I've noticed no difference between using workbook.getCreationHelper() and workbook.createDataFormat().

到目前为止,每个人的意见都有助于获得解决方案。我不确定这可能是什么问题,但是通过创建一次CellStyle然后对不同的单元格使用相同的样式有所帮助。在我动态创建每次创建新CellStyle的行和单元格之前。我注意到使用workbook.getCreationHelper()和workbook.createDataFormat()之间没有区别。

#4

This code is for XSSFWorkbook so I am not sure if it will fit you 100%.

此代码适用于XSSFWorkbook,因此我不确定它是否适合您100%。

short dateStyle = workBook.createDataFormat().getFormat("dd-mm-yyyy;@");
XSSFCellStyle dateCellFormat = workBook.createCellStyle();
dateCellFormat.setDataFormat(dateStyle);
cell.setCellStyle(dateCellFormat);

For XSSF it works perfectly fine. Maybe it has to be reworked for HSSF

对于XSSF,它工作得非常好。也许它必须为HSSF重做