How to open SpreadsheetDocument without calcchain.xml

[Distancehe]

The Excel file itself do not contain calcchain.xml, but calcchain.xml is referenced from workbook.xml.rels as below snapshot. When trying to load the excel with SpreadsheetDocument.Open(), it will throw exception since the calcchain.xml is missing. The question is, how to open the excel file with calcchain.xml missing? Is there some way to ignore this and continue open the file ? Thanks !

[tomjebo]

@Distancehe Can you provide the sample document that is failing to open in Excel and also explain how this file was produced? Our implementer notes don't say anything about the requirement for the calcChain root or it's containing part calcchain.xml so I would have to research this a little to find out if it's supposed to be valid or not. But in the meantime, it would help to know how the workbook was created.

[Distancehe]

@tomjebo thanks for the response ! This is the sample file and it generated from service with POI https://poi.apache.org/

[Distancehe]

When I create an empty calcChain.xml and add to the excel file, the document can be opened successfully. But I think this more like a workaround, so still want for official solution.

Empty calcChain.xml content is just like below,

[Distancehe]

From https://learn.microsoft.com/en-us/office/open-xml/working-with-the-calculation-chain, "While calculation chain information can be loaded by a spreadsheet application, it is not required. A calculation chain can be constructed in memory at load-time based on the formulas and their interdependence, if the spreadsheet application finds this information useful."
Can we bypass the checking for calcChain.xml while SpreadsheetDocument.Open() the spreadsheet ?

[tomjebo]

@Distancehe so Excel will also add the calcChain.xml part back into the package on save, but with some kind of "correct" content. After looking at what Excel adds for the content, I'm not sure having SpreadsheetDocument.Open() add the missing part is the right way to go. Ignoring it might be possible but that would leave a missing part relationship and potentially important information that should have been added by the producer of the package. Excel is essentially saying that it can recreate the missing information but the SDK wouldn't have the logic that Excel has. (That's why it's Excel). Having said that, I will talk to our Excel team to find out what the ramifications of adding an empty calcChain.xml part (because the SDK doesn't want to process the whole workbook looking for formulas and adding cells) would be. For example, after opening with a blank calcChain.xml inserted but before saving the workbook with Excel, could it lead to incorrect formula computations?

[Distancehe]

To confirm, "Their recommendation was actually to remove the relationship vs adding a part to complete it.", is it mean to remove below relationship from workbook.xml.rels when Open() ? If so, by open the file with Excel app, will it add the calcChain.xml back if there's formula in the sheet ?

Relationship Id="rIdx" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/calcChain" Target="calcChain.xml"

[tomjebo]

Yes, the recommendation was to remove the relationship to correct the package, not necessarily in the Open() but that's where we might be able to do it. And they did say that Excel would add calcChain parts if they are needed.

[Distancehe]

I see. So even without the relationship definition from rel file, Excel can add the calcChain.xml back and make the formula work as expected ?

[tomjebo]

If the part was missing to begin with then there's not much choice anyway. But yes, the Excel team says that the recommendation to 3rd party authors has been not to write a calcChain part or relationship and let Excel take care of this. Excel is able to understand all dependencies as well as optimizations and concurrency/parallel options of subchains. The chain will actually change as partial and full calculations are done by Excel until it reaches a steady state which is optimal. You can see some description of this in ISO 29500-1:2016 L.2.5 Calculation Chain. From the overview of that section: "In other words, the Calculation Chain part does not indicate the
dependencies that formulas have on other cell values; it only indicates the order in which the cells were
last calculated" Excel does this processing.

[Distancehe]

Got it. Thanks for the explanation and help all along !

[tomjebo]

@Distancehe I've submitted a fix in PR #1288, please test if you have time. This does leave the relationship intact vs removing it as I had mentioned before. I can rework if you think it should remove the relationship entry.

[Distancehe]

Thanks @tomjebo !