Locale currency and date format

[extraric]

Hi!

Is there a way to set the locale for the html writer?
The source MSExcel file cells:

After loaded the file and put to webpage:

These settings not helped at all:
setlocale(LC_ALL, "hu_HU");
\PhpOffice\PhpSpreadsheet\Settings::setLocale('hu');

Do I have to loop through all the cells and set proper formatting?

[MarkBaker]

The Settings locale is used for the calculation engine language, but there is no explicit locale setting for currency or date formatting in PhpSpreadsheet. None of the date/currency formatting is locale-aware; and has to be set manually; so you would need to set it for all cells that needed it, but you can set styles (including number formatting) for a range of cells (e.g. A2:A120), or a row/column range (e.g. 2:5 or A:B).

[extraric]

Thank you!
Its just have to process unknown files, so content of cells not known by me.
Can I detect the assumed format of the cell to correct after?

[MarkBaker]

There is an isDateTime() method in Shared\Date that will accept a cell as an argument and returns a boolean if that cell is formatted as a date/time. There isn't any equivalent for Currency/Accounting formatting, so that would need to be checked manually.

You can retrieve the formatting mask for a cell using:

$cell->getStyle()->getNumberFormat()->getFormatCode();

which returns the formatting mask as a string (exactly the values used in MS Excel). And you could use that value to identify if the cell was formatted as a Currency/Accounting value.

An alternative might be to use the Cell's getFormattedValue() method, which would return the value as it's displayed in MS Excel (except for locale specific formatting), but that has the overhead of forcing a calculation if the cell contains a formula.

[extraric]

Just one interesting observation: if the cell has Accounting formatting, it shows '$' but if its custom or Currency then it is in our localisation: Ft

The Excel cell format function shows C2, C0, C2 to Accounting, custom, Currency (top to bottom), so Accounting and Currency should be the same?

[MarkBaker]

That depends on the exact mask format being used; as with Dates/Times, some of the built-in Accounting and Currency formats in MS Excel are locale-aware.
This means that the same mask will display differently in MS Excel, depending on Excel's locale setting. Typically, those masks are displayed in Excel's drop-down with a * alongside them.

PhpSpreadsheet is not locale-aware, so cannot directly replicate this behaviour, but it does make a check against the locale-settings of the server that it is running on.

[idovdw]

Hi Mark, you mention that PhpSpreadsheet is not locale-aware. Can we set it up that it does? I'd like to share some code with you. For this I've added issue I've added issue #3538.