0

I have a service that reads financial information that belongs to different companies from an excel file (File 1) and produces several separated excel files out of it (Files C1, C2, …. Cn), by copying a template (File T1) and writing into the copies using open xml 2.5 (lets call this one service A). After that I have the service B, which reads each excel produced by service A and stores the data individually.

Service A reads the input file (File 1) and, imagine cell C21 has a value of 10. Service A writes into the new file C1 which template already has a formula on cell C21 (C21 = C20 – C19) which gives a different result if calculated;

If you open the file C1 using MS Excel, it will contain the value of 10 on cell C21 and the formula C20-C19 that would give a different result if calculated. Let us call this Scenario 1.

I did not wanted that, so I forced the formula to be calculated as many answers suggest:

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; 
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

So now, If you open the file using MS Excel it will contain the inner text as the result of the calculation of the formula (Scenario 2), but still the value is 10. The service B still reads the same value for scenario 1 and 2.

Scenario 3 – On the scenario 2, after opening the file using MS Excel, if you save it, the service B will read the file with the result of the calculation of the formula. The cell value is changed.

Is there any way to get the scenario 3 programmatically? In other words, if a Cell contains a value that differs from the result of the formula of the cell, can I force it to assume the formula?

If I open the xml structure of the excel files, this is what you get:

enter image description here

I am reading the excel files with the MSDN approach and I am also writing following MSDN sample code

Turay Melo
  • 114
  • 5
  • Have you considered just dealing with your excel presentation with VBA instead of C#? I find using OpenXml that keeping the C# strictly to text then writing a macro for `Auto_Open` to be a nice strategy. – Philip Brack Nov 02 '17 at 17:46
  • Thanks. No, i haven't considered. The reading service is in production. If there isn't another way, i will consider. Thank you. – Turay Melo Nov 02 '17 at 17:57

0 Answers0