Apache POI custom data format is modified

Question

I'm using Apache POI to alter an Excel file and put some data in it. Most of what I try goes well. However my currency formatting is not done 100% correctly...

What I want is my currencies to be format like € 1.000,43. So the Euro sign on the left, the amount on the right with dot as thousands separator, comma as the decimal separator and two decimals.

If I do that in Excel and apply this custom formatting rule it works: "_ [$€-nl-BE] * #.##0,00_ ;_ [$€-nl-BE] * -#.##0,00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "

So I just copied that rule and apply it through Apache POI:

XSSFDataFormat dataFormat = (XSSFDataFormat) workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("_ [$€-nl-BE] * #.##0,00_ ;_ [$€-nl-BE] * -#.##0,00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "));

However that results in something slightly different: € 1.000,4300. You can see that it goes well except for the decimals. There are 4 instead of 2... When I have a look in Excel at the custom formatting it is also slightly different (notice the 4 zeroes): _ [$€-nl-BE] * #.##0,00000_ ;_ [$€-nl-BE] * -#.##0,00000_ ;_ [$€-nl-BE] * "-"?????_ ;_ @_

So I wonder what could be wrong that somewhere someone (either Apache POI or Excel) do put 4 decimals instead of the 2 that I defined. Notice that if I change it in Excel back to 2 decimals that it showing correct again. Even after saven and closing and reopening the document.

Does it work with XSSFDataFormat dataFormat = workbook.getCreationHelper().createDataFormat(); instead of XSSFDataFormat dataFormat = (XSSFDataFormat) workbook.createDataFormat();? — deHaar
@deHaar No that does not make any difference... So I changed it to this: XSSFDataFormat dataFormat1 = (XSSFDataFormat) workbook.getCreationHelper().createDataFormat(); cellStyle.setDataFormat(dataFormat1.getFormat("_ [$€-nl-BE] * #.##0,00_ ;_ [$€-nl-BE] * -#.##0,00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ ")); — dirkvranckaert

Axel Richter Axel Richter · Accepted Answer · 2020-04-01T09:34:31

Apache poi creates the *.xlsx file as Excel would store it. And in the storage the number formats never are localized but always are in en_US format. Only the Excel GUI then localizes the number format.

So for the storage your number format would must be

"_ [$€-nl-BE] * #,##0.00_ ;_ [$€-nl-BE] * -#,##0.00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "

Note decimal separator is . and thousands separator is , because of en_US locale.

If opened in localized Excel GUI this changes then to the locale specific decimal separator and thousands separator.

But Excel itself also is not storing language tags like nl-BE in number formats. Instead it uses the Windows Language Code Identifier (LCID). This is 813 for nl-BE. So using [$€-813], which is the same as [$€-nl-BE], will be better.

Following works for me also in my German Excel:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

class CreateExcelCustomNumberFormat {

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

  Workbook workbook = new XSSFWorkbook();
  DataFormat dataFormat = workbook.createDataFormat();
  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setDataFormat(dataFormat.getFormat("_ [$€-813] * #,##0.00_ ;_ [$€-813] * -#,##0.00_ ;_ [$€-813] * \"-\"??_ ;_ @_ "));

  Cell cell = workbook.createSheet().createRow(0).createCell(0);
  cell.setCellValue(1000.43);
  cell.setCellStyle(cellStyle);

  FileOutputStream out = new FileOutputStream("Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

This code is tested using current apache poi 4.1.2 and Excel 2016.

Apache POI custom data format is modified

1 Answers