Mentioned date format (dd/MM/yyyy) is not working for current date in excel using Apache Poi

Question

I need to generate excel file with date values using HSSFWorkbook(.xlsx). I am passing date value generated by Date class and also I am adding the format to the excel cell as dd/MM/yyyy using createHelper.createDataFormat().getFormat("dd/MM/yyyy") but in the downloaded excel file it is showing in different format.

passing date as Wed Apr 19 15:00:17 IST 2023, I am expecting the format as 19/04/2023 however I am getting as shown in the image.

Attached the code snippets,

Workbook wb = new HSSFWorkbook();  
CreationHelper createHelper = wb.getCreationHelper();  
Sheet sheet = wb.createSheet("New Sheet");  
Row row     = sheet.createRow(0);  
Cell cell   = row.createCell(0);  
cell.setCellValue("test");
CellStyle cellStyle = wb.createCellStyle();  
cellStyle.setDataFormat(  
            createHelper.createDataFormat().getFormat("dd/MM/yyyy"));  
cell = row.createCell(1); 
Date date = new Date();
System.out.println("Date :"+date);
cell.setCellValue(date);    
cell.setCellStyle(cellStyle);

Thanks

Answer 1

The Excel Number format DD/MM/YYYY means a date format DMY having the delimiter which is defined in Systems region settings. Seems for your system that delimiter is hyphen (minus). In other words, the / in that format code does not mean the slash but the default date delimiter.

Have a look at Control Panel - Region what is the delimiter in Date (short). This is the default date delimiter.

If you want set a user defined format which always uses the slash as the delimiter, then you need mark the slash as meaningless character. That would be DD\/MM\/YYYY - backslash preceded or DD"/"MM"/"YYYY - slash in double quotes.

Complete Example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

class CreateExcelDate {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new HSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./Excel.xls") ) {

   CellStyle cStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();         
   //cStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
   //cStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd\\/MM\\/yyyy"));
   cStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd\"/\"MM\"/\"yyyy"));

   Sheet excelSheet = workbook.createSheet();
  
   for (int r = 1; r < 10; r++) {
    Row dataRow = excelSheet.createRow(r);;
    Cell dataCell = dataRow.createCell(1);;
    dataCell.setCellValue(new java.util.GregorianCalendar(2023, 3, r*2, r, r, r));
    dataCell.setCellStyle(cStyle);
   }

   excelSheet.setColumnWidth(1, 15 * 256);

   workbook.write(fileout);
  }

 }
}

---

When it comes to the question about documentation for this, there is:

Number format codes

and for regionalization:

Number formatting

But since we are on file storage level using Apache POI and not on Excel GUI level, following needs also to be known:

Microsoft Office always stores en_US settings in files. The regionalization is done in GUI only.

So for number formats that means:

A stored number format #,##0.00 in a file may be #.##0,00 in a GUI which uses regional settings where comma is decimal delimiter and dot is thousands separator.

A stored date format DD/MM/YYYY in a file may be DD.MM.YYYY in a GUI which uses regional settings where dot is the date separator. It also may be TT.MM.JJJJ when "Day" spells "Tag" and "Year" spells "Jahr" in that regions language.

If one put DD/MM/YYYY in a GUI which region settings have not slash as the date separator, then in file storage DD\/MM\/YYYY will be stored to mark that slash as a meaningless character. But as Apache POI directly writes in file storage, there DD\/MM\/YYYY needs to be used directly. Else slash means the default date separator.