Hi, this is related to my other thread but different enough to warrant it's own.
We've noticed that there is different behavior when dealing with XLSX files that have calculated formulas than we were used to seeing with XLS files that have formulas. The old files seemed to cache their last calculated values and Cells seemed to read them correctly. With XLSX files we have to call CalculateFormula() on the workbook to get the cells to contain the value of the formula. We'd prefer to use the cached values from Excel for several reasons, the main one is that those are the values the user last saw so they would expect those values to be the exact same. Is this just a limitation of the XLSX file format or will there be development on Cells in the future to use the cached values?
Here are tests to demonstrate the difference. I cannot upload the sample excel file, but you can create it by opening up Excel 2007 and putting "=1+1" in the first cell and then saving it as an XLSX file and XLS file.
[Test]
public void Excel2007GetValueForEquation()
{
string filename = "C:\\test.xlsx";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx);
//wb.CalculateFormula();
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
Aspose.Cells.Cell cell = ws.Cells[0, 0];
String s = cell.StringValue;
Assert.AreEqual("2", s); // FAIL
int integerValue = cell.IntValue;
Assert.AreEqual(2, integerValue); // FAIL
}
I get s = "0" and integerValue = 0
[Test]
public void Excel2003GetValueForEquation()
{
string filename = "C:\\test.xls";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(filename, Aspose.Cells.FileFormatType.Default);
//wb.CalculateFormula();
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
Aspose.Cells.Cell cell = ws.Cells[0, 0];
String s = cell.StringValue;
Assert.AreEqual("2", s); // PASS
int integerValue = cell.IntValue;
Assert.AreEqual(2, integerValue); // PASS
}
Please let me know if you need any clarification. Thank you.