We are switching from version 18.6 to 19.10 and we noticed that we have to invoke Workbook.CalculateFormula() method in order for the formulas to calculate values once we’ve placed the raw data (the data we look up against) in the new version.
In the previous version, we would have the Display sheet contain formulas, we would then do a data dump in the “raw data” sheet and when the excel would open on client side, it would calculate the formulas and display values.
In the newer version (19.10), we do the same thing, but if we spit out the excel without using calculate formula, it does not show any values. If we then go into the cell (Key: F2) and press enter, the formula gets calculated.
We are aware that we can just call workbook.CalculateFormula() at the end, but this would mean the the formulas are getting calculated on the server side, which takes memory. Wondering if there is a way to get back to old functionality where formula is calculated on the client side on open of workbook.
Currently using Excel 2016 and .Net Environment
Code:
string dataDir = @“C:/Projects/Test/Aspose19.10Demo/”;
var wb = new Workbook(dataDir + “AsposeTestBook.xlsx”);
var RawSheet = wb.Worksheets[“RawData”];
RawSheet.Cells[0, 0].PutValue(1);
RawSheet.Cells[1, 0].PutValue(2);
RawSheet.Cells[2, 0].PutValue(3);
RawSheet.Cells[3, 0].PutValue(4);
RawSheet.Cells[4, 0].PutValue(5);
RawSheet.Cells[0, 1].PutValue("Test Data 1");
RawSheet.Cells[1, 1].PutValue("Test Data 2");
RawSheet.Cells[2, 1].PutValue("Test Data 3");
RawSheet.Cells[3, 1].PutValue("Test Data 4");
RawSheet.Cells[4, 1].PutValue("Test Data 5");
// wb.CalculateFormula();
wb.Save(dataDir + "OutputExcelFromVersion19.10_2.xlsx");
AsposeTestBook.zip (6.6 KB)