load and save a ods or xlsx file with a cell containing formular including an external filepah on windows-system leads to a replacement [1] instead of the correct filepath

[comfine]

=WENNFEHLER(SVERWEIS(D2;'file:///C:/Program Files (x86)/xampp/htdocs/cao-adresses.xlsx'#$Worksheet.A2:C99999;3;0); "")

will be converted to

=WENNFEHLER(SVERWEIS(D2;[1]worksheet!a2:C99999;3;0); "")

when loading the file with phpspreadsheet and save it again as a new file.

i attached a sample-file, that can be used for testing. cell containing the Formular is E2.

broken.ods

[oleibman]

What release of PhpSpreadsheet and Php are you using? I get multiple errors trying to load and save it with the newest release.

As for the [1], I don't think that is anything that PhpSpreadsheet would introduce on its own. However, if you load your ods spreadsheet in Excel, and save it to xlsx, then, yes, Excel will replace the file name in the formula with [1], and resolve that in file externalLinks_rels\externalLink1.xml.rels. We do not yet support the externalLinks files. So, if you loaded ods in Excel, saved it as xlsx, then used PhpSpreadsheet to load the xlsx file, you would see the replacement. Is it possible that this is how you encountered this problem?

[comfine]

i am running PHP Version 8.2.12, and i think phpspreadsheet is 4.1.0 - i downloaded a fix that you provided in another issue, that i reported. but dont know how to retrieve the exact version info for that.

i dont load ods-files in excel. as in the other issue, i convert the ODS file using libreoffice command line to xlsx, than i load and save the xlsx with phpspreadsheet. i doublechecked if the the externallink gets lost in the conversion of libreoffice, but it doesnt. its stil there after the converson. only after load/save with phpspreadsheet it turns into [1].

but did i understand you correct, that it is just not supported yet?

[oleibman]

I use the following command, which should essentially be the same as yours:

"c:\Program Files\LibreOffice\program\soffice.com" --convert-to xlsx --outdir \temp c:\git\issue.4435.ods

When I now inspect \temp\issue.4435.xlsx, I see the following in sheet1.xml:

<c r="E2" s="20" t="str">
<f aca="false">
IFERROR(VLOOKUP(D2,[1]Worksheet!A2:C99999,3,0), "")
</f>
<v/>
</c>

And I see the externalLinks directory and files. PhpSpreadsheet is not involved in this scenario.

Not that it matters a lot. As you correctly understood, this isn't supported yet.

[comfine]

yes, i use the same convert-command, but with the --headless parameter. you're right, in the sheet1.xml is just the[1], but libreoffice shows still the right path in the formular of the cell. the external link seems to be stored separatly (with a relative path) in xl\externalLinks_rels\externalLink1.xml.rels if you maybe want to implement this feature...here is the hint to the right file, that needs to be created :-)