1

this is my first post in the site so sorry if I miss something, I´ll try to be as clear as I can

I´m generating a XLSX file via Apache POI, with one the columns containing dates (in date format)

The main problem is that the column which contains the dates is not well converted to date type when it is opened by Microsoft Excel (it is well viewed in LibreOffice for linux tho)

In this image you can see how it is viewed from the LibreOffice

In this image you can see how it is in MS Office (any version)

Here is the code for the cell format and value:

private void setCellValue(Cell cell, Object value) {
    if (value instanceof String) {
        cell.setCellValue((String) value);
    } else if (value instanceof Integer) {
        cell.setCellValue((Integer) value);
    } else if (value instanceof Date) {
        cell.setCellValue((Date) value);

        CellStyle style = cell.getCellStyle();

        if (style == null) {
            style = getWorkbook().createCellStyle();
        }
        
        style.setDataFormat(getDateDataFormat());

        cell.setCellStyle(style);
    }
}

private short getDateDataFormat() {
    return getWorkbook()
            .getCreationHelper()
            .createDataFormat()
            .getFormat("m/d/yy");
}

I´m using XSSF kind of workbook in the process, here is the code for generating the workbook

public Reporte getReport(String titulo) throws IOException {
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    getWorkbook().write(outputStream);
    getWorkbook().close();

    return new Reporte(titulo, new String(new Base64().encode(outputStream.toByteArray())));
}

(with class Reporte having only properties Name and Content)

Curious thing: when I open the file with a text editor, it seems as it is a binary file and not a xml as it should be

Curious thing 2: when I convert the file to a XML and then open it with MS Office, it shows the date right

Thank you in advance

  • Check this posts: https://stackoverflow.com/questions/46982950/setting-date-format-to-excel-cell-with-apache-poi-is-not-working#autocomment117531499 – leonardo souto Mar 04 '21 at 20:55
  • 1
    A `*.xlsx` file is a `ZIP` archive **containing** **multiple** `XML` files. So it is a binary file. So your assumptions are wrong and it is not clear what exactly leads to your issue since your code is not complete. – Axel Richter Mar 05 '21 at 06:01

0 Answers0