Write formulas to XLSX files. #425
Conversation
New function, try to identify a formula string
Supporting *writing* formulas in XLSX files is truly trivial.
|
Verified that @stevesweets has signed the CLA. Thanks for the pull request! |
|
This works for this, too: |
| if (CellHelper::isNonEmptyString($cellValue)) { | ||
|
|
||
| if (CellHelper::isFormulaString($cellValue)) { | ||
| $cellXML .= '><f>'.substr($cellValue,1).'</f><v>0</v></c>'; // seriously, that's it. |
There was a problem hiding this comment.
I suspect that the <v>0</v> is not going to work across all softwares. Which software did you use to test your changes?
There was a problem hiding this comment.
Also, by setting the value to 0, reading back a spreadsheet with a formula won't work as Spout would return "0" for the cell value. Not ideal...
There was a problem hiding this comment.
Tested on OSX, with excel 15 and numbers.
0 works because excel formulas are recalculated when an excel file is loaded.
You are correct that reading a formula will result in unexpected results, I had hoped the title of the PR was specific enough for this to be clear.
Thanks for coming back to me, glad to have been of any use at all.
There was a problem hiding this comment.
I tried it on Win10 and LibreOffice and it doesn't seem to work. It displays 0, like adrilo predicted. Using empty "<v></v>" seems to work for me.
There was a problem hiding this comment.
@stevesweets can you try the empty <v></v>?
There may always be some software that won't work but if the main ones work, we can consider merging this change.
There was a problem hiding this comment.
Hi there, realise i left this a long time - i will try this.
S
There was a problem hiding this comment.
I try do the change on code for .ods
ODS/Internal/Worksheet.php
if (CellHelper::isFormulaString($cellValue)) {
$data .= ' table:formula="'.$cellValue.'" office:value-type="float" office:value="' . 0 . '" calcext:value-type="float">';
$data .= '<text:p>' . 0 . '</text:p>';
$data .= '</table:table-cell>';
}else if (CellHelper::isNonEmptyString($cellValue)) {
$data .= ' office:value-type="string" calcext:value-type="string">';
$cellValueLines = explode("\n", $cellValue);
foreach ($cellValueLines as $cellValueLine) {
$data .= '<text:p>' . $this->stringsEscaper->escape($cellValueLine) . '</text:p>';
}
$data .= '</table:table-cell>';
} else if (CellHelper::isBoolean($cellValue)) {...}too add the same change the CellHelper.php like .xlsx
public static function isFormulaString($value)
{
return (strpos($value,'=') === 0);
}but now have this problem
not reconize the form in ods like excel. i neet to open document "make change at formule" and save to func
this is the code .fods
<table:table-row table:style-name="ro1">
<table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
<text:p>1</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="9" calcext:value-type="float">
<text:p>9</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="3" calcext:value-type="float">
<text:p>3</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="7" calcext:value-type="float">
<text:p>7</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=[.A1]+[.B1]+[.C1]+[.D1]" office:value-type="float" office:value="20" calcext:value-type="float">
<text:p>20</text:p>
</table:table-cell>
</table:table-row>if any can help me i give thanks
There was a problem hiding this comment.
Well, you can see that formulas don't work the same:
<table:table-cell table:formula="of:=[.A1]+[.B1]+[.C1]+[.D1]" office:value-type="float" office:value="20" calcext:value-type="float">
<text:p>20</text:p>
This would indicate a formula: table:formula="of:=[.A1]+[.B1]+[.C1]+[.D1]", so your isFormulaString implementation is incorrect. Setting the value to 0 may not work in ODS. This is a trick in XLSX because softwares do recompute the value when you open the file, but for ODS maybe softwares use the given value directly. In this case, I don't have a good solution for you.
|
Thanks @stevesweets ! |
|
|
|
Will this PR ever get merged? |
|
Merged a variation in openspout/openspout#5 |
Ref this issue: #424
I utterly understand that reading formulas is a pain. It should be noted that writing formulas to ODS is rather a pain, too, but the nature of xlsx actually makes it rather easy to write them.
This way, we can use the super-performant spout when we want to generate an xlsx file that needs to contain some excel formulas, and I don't think this lands outside of the goals of Spout.
I await your judgement!