Excel or POI changes my date format to something that doesn't work-CodePudding

I've got an Excel file I want to recreate through POI. The existing Excel file uses as DataFormat _(* #.##0_);_(* (#.##0);_(* "-"??_);_(@_). But when I assign that data format through POI, the resulting Excel file will instead use _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_). Note the tiny difference: a dot changed to a comma. Because of this, the entire format doesn't work anymore. It's not like it's now showing a comma where it used to have a dot; it's formatting the entire value in a completely different way.

Why does this happen? And how do I fix it?

The correct format string _(* #.##0_);_(* (#.##0);_(* "-"??_);_(@_) results in the number being displayed as 13.534.000.

The incorrect format string that Excel or POI changes it to, _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_) formats the value as 13534000,0.

It's a complete mystery to me why it would do that. I suppose it has something to do with the US and Europe using different formats to display big numbers, but I would imagine that that's exactly what this data format is supposed to address. Instead, it turns it into nonsense.

CodePudding user response：

Apache POI creates Microsoft Office files. Those files never are localized. They always store data in en_US locale. The locale dependent adjustments are done in locale Office applications then. So a Microsoft Office file can be sent around the world without the need to change the stored data to a foreign locale.

So if you set the data format using ...

...
  Workbook workbook = new XSSFWorkbook();

  DataFormat dataformat = workbook.createDataFormat();
  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setDataFormat(dataformat.getFormat("_(* #,##0_);_(* (#,##0);_(* \"-\"??_);_(@_)"));
...

... the format pattern always needs to be en_US. That means dot is decimal separator, comma is thousands delimiter. A locale Excel application might adjust that to _(* #.##0_);_(* (#.##0);_(* "-"??_);_(@_) then.

Let's have a complete example:

import java.io.FileOutputStream;

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

public class CreateExcelNumberFormat {

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();

  DataFormat dataformat = workbook.createDataFormat();
  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setDataFormat(dataformat.getFormat("_(* #,##0_);_(* (#,##0);_(* \"-\"??_);_(@_)"));
  
  Sheet sheet = workbook.createSheet();
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue(1234567.89);
  cell.setCellStyle(cellStyle);
  
  row = sheet.createRow(1);
  cell = row.createCell(0);
  cell.setCellValue(-1234567.89);
  cell.setCellStyle(cellStyle);

  sheet.setColumnWidth(0, 15*256);

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

The result Excel file looks in my German Excel like so: