I'm trying to use EPPlus to get an excel file, make some updates to that file, and then use the updated file. This is a .NET Web API project, and the Read and Write are GET and POST endpoints. The POST updates the excel file based on the input body of the post and then performs a lot of calculations to update data on other worksheets in the workbook. The Read gets info from the original and other worksheets based on calculations made when the first worksheet is updated. Those calculations do not seem to be dependent on saving the file, but happen as soon as the change is made when working with the Excel file directly.
When I use the Save or SaveAs methods to make the changes, I can see the updates are made to the local copy. If I execute a Read on the data I changed I get back the updated data as expected. When I do a Read on the other worksheets I see the old data.
When I look at the local copy of the excel file, I can see the changed data on the worksheet that I changed, and I can see the calculations have been updated on the other worksheet.
The weird thing is if I click save on the local copy and close it, now all the updated data is returned - the stuff I changed directly and the data that was changed as a result of the calculations done by excel.
I originally built the Import, Save, and Read methods to utilize a singleton, hoping to reduce read times by using a static version of the excel file. I changed it to use FileInfo in a using block to get and dispose of a new copy of the file each time but I am still getting the same results.
I can see the fields I updated directly immediately return the new data, but the fields on the other worksheets that require calculation return old data.
When I open the file in Excel, I can see all the changes as I expect to see them after I have executed a Save(). I changed to SaveAs(new FileInfo(path)) to try to save a completely new version of the file, but it is still the same.
I am wondering if there is an issue with the calculated fields, even though if I open the file in Excel the calculated fields appear correct.
Is there some way to force the calculations?
I didn't realize I didn't ask the right question. Part of my save dialog was calling the Calculate() method on the entire workbook.
It wasn't calculating because the excel file was just too much, and was timing out before it finished calculating. Since I had the logic behind a POST endpoint, I wasn't getting the right error back to know.
The working code doesn't calculate anything at save time, it just saves the edited cells. When I need to work with the other worksheets, I call Calculate() on the cell I need, as I need it. Workbook and even worksheet level calls to calculate were too much, and timed out.
Using calculate at such a granular level and at that time in the workflow is not necessarily the best way to do it, but I am working with an excel file that is a huge mess, I have no control over, and have to do what I can.