Free Support Forum - aspose.com

Difference in formula handling between XLSX and XLS files

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.

Hi,

Well, currently you have to call Workbook.CalculateFormula() for xlsx file. We will figure it out soon for your need.

Thank you.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

This feature is available now in the attached fix, please try it.

Thank you.

our initial testing shows that this fixes the problem. thanks for your unbelievably quick response