Unable to save with setFormatCode in ODS, and getFormatCode from existing documents

[MinxAa]

This is:

[X] a bug report

What is the expected behavior?

Formatted numbers.

What is the current behavior?

No formatting.

What are the steps to reproduce?

<?php

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Ods;

$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();

$activeWorksheet->setCellValue('A1', 123456.789);
$activeWorksheet->getStyle('A1')->getNumberFormat()->setFormatCode('#,##0.00');

$writer = new Ods($spreadsheet);
$writer->save('test-fmt-01.ods');

Have also tried all kind of variants like

$activeWorksheet->setCellValue([1, 1], 123456.789);
$activeWorksheet->setCellValueExplicit($loc, $val, DataType::TYPE_NUMERIC);

// ...

$activeWorksheet->getStyle('A1')->applyFromArray([
	'numberFormat' => [
		'formatCode' => '0,00E+00'
	]
]);

$activeWorksheet->getStyle('A1')->getNumberFormat()->setFormatCode(
	PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR
);

// ...

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Ods");
$writer->save("out/test01.ods");

• dozens of more tweaks.

Have also tried all sorts of variants with numbers; as string, as int,as float, ...

If I do:

		var_dump( ... getFormatCode());
		 ... setFormatCode($fmt);
		var_dump(... getFormatCode());

The correct format is dumped after applying. But never applied to the written file.

---

Have also tried to save a file with formatting using LibreOffice then reading it with PhpSpreadsheet - but the var_dump(... getFormatCode()); only say string(7) "General". Have also tried to scan 100x100 cells but no cell has any format.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Which versions of PhpSpreadsheet and PHP are affected?

• PhpSpreadsheet 2.0.0
• PHP 8.3.3

[oleibman]

There is very limited support for reading most styles (getFormatCode) in Ods Reader, and it might take a lot of effort to add more. Although I don't see any support for Number Formats in Ods Writer (setFormatCode), it might be possible to add some. I'll be away a while, but I'll take a look when I get back.

[MinxAa]

Then I am missing something here. The Features cross reference#writers say ODS has support for writing all sorts of Cell Formatting including Number Format Mask.

The #reader table states ODS supports reading Number Format Mask.

I have no problems setting font-name, font-size, font-weight, cell-alignment etc. Both for entire sheet and individual cells. The same Feature table say cell column-width / row-height is not supported for ODS, but those work fine as well. I am also using cell-merging without problem.

What I use and that works fine with ODS:

• Sheet font
• Column-width
• Row-height
• Merging cell-ranges
• Cell font- size, weight, alignment
• Border bottom for cell-ranges
• Set cell formula =Foo(...)

The one thing not working (and the thing I of course implemented last hah) was cell-formatting. And this does not apply.

Checking further; the last things that I had thought adding does not work either:

• Printing area
• Header / footer

---

In conclusion I guess I use XLS(X) instead of ODS and convert the product to ODS locally, i.e:

libreoffice --convert-to ods --outdir ./conv *.xlsx

---

As a note I believe it should state more clearly that ODS has limited support. As an anecdote my process was this:

Have been using a hack where I create document templates as ODS in LibreOffice. Save them and unpack them. Then I have a PHP script that use this as base to create new documents. As this is both cumbersome and a product of an ad-hock solution I went looking for some library.

Found this one where it says at top "PhpSpreadsheet is a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc." - OK, good, I thought. Went on to install it and start poking around. First test was the "Hello World!" from https://phpspreadsheet.readthedocs.io/en/latest/ . All good. Then changed to ODS. Still all good. Fired up the test-server and had a look around. All OK. Then started to write a simple wrapper class and filling in data poking around in the PhpSpreadsheet Documentation. All good. Then came to formatting and full stop. Searching the WWW. No fix. Trying various ways to set it and also set the data (as described in OP) no fix. Looking at the API docs. Nothing further. And so on ... at last ending up posting a ticket here.

---

IMO perhaps some clear statement in both README and the front-page of the Documentation informing about the big feature differences between formats. I got the impression it was opaque to containers.

I mean looking at this page: https://phpspreadsheet.readthedocs.io/en/latest/

I get the impression (First line of File formats supported) is ODS. Nothing about limitations. Further down in the Getting started section - still no notice about limitations etc.

As the spreadsheet API is opaque to file-format and there is no errors or warnings when using calls that does not apply it is, was at least for me, easy to have blinders on that prevent one from understanding it is a container issue.

And - yes, the Features cross reference says a lot of this, - but to be honest I jumped right into topics and did not register that table until after I posted OP. Not that it would have helped as from how I read it - it say it supports number formatting. As it say no on row/column dimensions I'd skip it though ...

---

It is of course a likelihood it is all about me being a moron, but still.

[oleibman]

I am sorry that the experience has been so frustrating for you. As far as the documentation is concerned, I will look into the errors. But ... we do accept PRs, and, since you are obviously further ahead than I will be in what is wrong or not emphasized sufficiently, perhaps you would like to take a shot at correcting some of the doc problems with a PR.

[MinxAa]

I do not mean to sound negative. It's an awesome project, was merely meant as some hints.

As for PR I'll see what I find out when back after Easter. Would have to look closer at various aspects before deciding on a suggestion.

[j-applese3d]

TL;DR: the "currency" format reader for Ods is broken, but I'm not sure there's an easy way to fix it because the number of decimals is not necessarily consistent.

Ps. I thought this was relevant to this issue. If it isn't, I can delete it :)

---

The Reader\Ods Reader does not handle the "currency" type correctly. The formatting is specifically set to FORMAT_CURRENCY_USD_INTEGER. That is: "$#,##0_-"

Since it already checks if the value has decimals, could

PhpSpreadsheet/src/PhpSpreadsheet/Reader/Ods.php

Lines 513 to 516 in 1b68270

	if (floor($dataValue) == $dataValue) {
	$dataValue = (int) $dataValue;
	}
	$formatting = NumberFormat::FORMAT_CURRENCY_USD_INTEGER;

be changed to:

// does this make sense? might be better to do:
// if (str_contains($textValue, '.')) => it's a float so use decimals...
if (floor($dataValue) == $dataValue) {
    $dataValue = (int) $dataValue;
    $formatting = NumberFormat::FORMAT_CURRENCY_USD_INTEGER;
} else {
    // it's a float, use 2 decimals. Note that the value is possibly truncated!
    $formatting = NumberFormat::FORMAT_CURRENCY_USD;
}

Looking into the .ods content.xml file, I see the following for my test value:

<table:table-cell office:value-type="currency" 
                  office:currency="CAD" 
                  office:value="12.33" 
                  calcext:value-type="currency">
    <text:p>$12.330</text:p>
</table:table-cell>

I can see why there's a challenge getting the format. And it might make most sense to leave it as a text value as opposed to trying to store it as numeric (float) + a format. By not converting, the number of decimal places won't be lost (like my suggested code above does).

That said, if you don't formatData, it won't truncate the data. (ex: rangeToArray(formatData: false))
This will then let you handle the values yourself... maybe that's the simplest 🤷‍♂️

[oleibman]

@MinxAa Documentation update is included as part of PR #4087. Please review if possible.