Apache POI: How do I set the dataFormat of a cell based on a Java DateTimeFormatter

Question

I need to export date and time data to Excel, using the format specified on the host OS.

The only way I've found to get this format in Java is by using DateTimeFormatter. I then need to set the data format of the Excel cell using Apache POI. How do I do that?

To get a data format for the method setDataFormat in CellStyle I must have a format string (or a short representing a build in type), but I cannot get a string pattern out of the DateTimeFormatter. Is there any way to make this conversion?

final SXSSFWorkbook workbook;
final CellStyle style;
final DataFormat formatFactory;

style = workbook.createCellStyle();
DateTimeFormatter format = DateTimeFormatter.ofLocalizedTime(FormatStyle.SHORT); //Format depends on local settings on client machine

style.setDataFormat(formatFactory.getFormat(format)); //Doesn't work

Axel Richter · Answer 1 · 2019-09-04T04:58:21.987

This requirement can be achieved using java.text.DateFormat like so:

DateFormat format = DateFormat.getDateTimeInstance(
                     DateFormat.SHORT, DateFormat.SHORT,
                     Locale.getDefault());
String pattern = ((SimpleDateFormat)format).toPattern();
System.out.println(pattern);

Or using java.time.format.DateTimeFormatterBuilder like so:

String pattern = DateTimeFormatterBuilder.getLocalizedDateTimePattern(
                  FormatStyle.SHORT, FormatStyle.SHORT, 
                  Chronology.ofLocale(Locale.getDefault()),
                  Locale.getDefault());
System.out.println(pattern);

In both cases the pattern needs to be converted for usage in Excel using DateFormatConverter like so:

pattern = DateFormatConverter.convert(Locale.getDefault(), pattern);
System.out.println(pattern);

Complete example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.DateFormatConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Date;
import java.util.Locale;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.FormatStyle;
import java.time.chrono.Chronology;

class CreateExcelCellDateFormat {

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

  //Locale.setDefault(new Locale("en", "US"));

  System.out.println(Locale.getDefault());

  String pattern = DateTimeFormatterBuilder.getLocalizedDateTimePattern(
   FormatStyle.SHORT, FormatStyle.SHORT, 
   Chronology.ofLocale(Locale.getDefault()), Locale.getDefault());
  System.out.println(pattern);

/*
  DateFormat format = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT, Locale.getDefault());
  String pattern = ((SimpleDateFormat)format).toPattern();
  System.out.println(pattern);
*/

  pattern = DateFormatConverter.convert(Locale.getDefault(), pattern);
  System.out.println(pattern);

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   CellStyle style = workbook.createCellStyle();
   style.setDataFormat(workbook.createDataFormat().getFormat(pattern));

   Sheet sheet = workbook.createSheet();
   Cell cell = sheet.createRow(0).createCell(0);
   cell.setCellStyle(style);
   cell.setCellValue(new Date());

   sheet.setColumnWidth(0, 25 * 256);

   workbook.write(fileout);
  }

 }
}

But if the really requirement is to create a Excel workbook which shows date-time values dependent on the user locale the Excel application runs in, then this all is not necessary. Then do using the BuiltinFormats 0xe, "m/d/yy" for short date or 0x16, "m/d/yy h:mm" for short date-time.

Example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelCellDateFormatUserLocale {

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

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   CellStyle style = workbook.createCellStyle();
   //style.setDataFormat((short)14); //0xe, "m/d/yy"
   style.setDataFormat((short)22); //0x16, "m/d/yy h:mm"

   Sheet sheet = workbook.createSheet();
   Cell cell = sheet.createRow(0).createCell(0);
   cell.setCellStyle(style);
   cell.setCellValue(new java.util.Date());

   sheet.setColumnWidth(0, 25 * 256);

   workbook.write(fileout);
  }

 }
}

That code produces a Excel file which shows the date-time exactly as Excel does dependent on the locale the Excel application runs in. For Germany it shows dd.MM.yyyy hh:mm. For US it shows MM/dd/yy h:m AM/PM. For UK it shows dd/MM/yyyy hh:mm.

Apache POI: How do I set the dataFormat of a cell based on a Java DateTimeFormatter

1 Answers1