Optimize calculation chain generation

[lzeitz]

Do you want to request a feature or report a bug?

• Bug
• Feature

Version of ClosedXML

e.g. 0.87.1

What is the current behavior?
Calling the workbook save takes 2 minutes and 50 seconds for a 3.5 MB file. I have only modified 2 cells on one worksheet. Saving within Excel 2016 takes under 5 seconds on the file.
If the current behavior is a bug, please provide the steps to reproduce and
if possible a minimal demo of the problem with a sample spreadsheet.
I have a large excel file with many tabs, some of which contain images. I have a hidden tab that contains metadata that I modify during file upload by setting the values for 2 fields on the sheet. When I am done, I call workbook.Save(). This takes an extremely long time.
What is the expected behavior or new feature?
Saving a workbook should be on the order of a file save within Excel.
Did this work in previous versions of our tool? Which versions?

• I attached a sample spreadsheet. (You can drag files on to this issue)

Code to reproduce problem:

public void Main()
{
    // Where possible, post full code to reproduce your issue that adheres to:
    // - Fully runnable. I should be able to copy and paste this code into a 
    //   console application and run it without having to edit it much.
    // - Declare all your variables (this follows from the previous point)
    // - The code should be a minimal code sample to illustrate issue. The code 
    //   samples on the wiki are good examples of the terseness that I want. Don't
    //   post your full application.
}

[igitur]

Too vague. If you can identify a certain piece of code that can be optimised, let me know.

[lzeitz]

I am using version 0.87.0 of ClosedXml.

I found that the piece of code that is taking a long time is GenerateCalculationChainPartContent.

Would it be possible to skip regenerating this step as all I want to do is save? Our workbooks are downloaded and filled in by our users. All calculations of theirs have already been made when they closed and saved the workbooks and we do not need to recalculate formulas for this reason. The workbooks have formulas that span tabs and can traverse up to 15 tabs in some cases.

[igitur]

Attach a sample file for me to inspect.

[lzeitz]

Test File with Calculations.xlsx

[lzeitz]

Attached a file
Note - my files are xlsm but I have saved as an xlsx to remove the macros

[igitur]

Try the build at https://ci.appveyor.com/project/Pyropace/closedxml/build/0.8.447/artifacts - by default it should be better.

If you really want to skip the generation of the calculation chain, you can do:

workbook.SaveAs(file, new SaveOptions { GenerateCalculationChain = false });

[lzeitz]

I attempted to use the build you specified with a .Save(false, false) and with the suggestion provided above, but all formulas on the Source Cash Flow Tab were garbled when saving the workbook. Almost every column in that tab had its formula changed. For example, the formula in T13 should be
=SUMIF($C$62:$C$1024,$C13,T$62:T$1024)

Instead, it is
=SUMIF($C$62:$C$1024,$C13,E$62:E$1024)

I can see that the template I tried to scrub lost formulas before it was uploaded. I am providing a clean empty template since I am experiencing the slowness on write.
Clean Template.xlsx

The time to save dropped to approximately 20 seconds (down from 1 minute 20 seconds) on my computer and 40 seconds (down from 2 minutes and 50 seconds) on our development server (not sure why the difference is there).

[igitur]

Yeah, I introduced a bug in shared formula parsing. Fixed now. Try the artifacts at https://ci.appveyor.com/project/Pyropace/closedxml/build/0.8.449/artifacts

[igitur]

https://www.nuget.org/packages/ClosedXML/0.90.0-beta2 is released and should include the fix for this issue.

[csarcor15]

how macros are generated?

[igitur]

@csarcor15 Macros are not supported yet.