Formula incorrectly recalculates for large (>64K row) sheets

Aspose.Cells Product Family
1

Using Aspose.Cells, V4.8.2.0. I have an Excel 2007 / XLSX book with large (150K or so row) sheet with a SUM formula, totaling all cells above in the applicable row (e.g. sum(M1:M150015)). When I load the sheet, I can see the proper value as a string value, however, if I invoke the CalculateFormula() method, the value is incorrectly calculated (see code and comments below). Looks like this may have to do with with the sheet having more than 64K rows, I have had no problems with smaller sheets before. See code below, should be easy to reproduce.


private static bool GetCostsFromFile(string strFilePath,
out decimal totalSales,
out decimal totalRebates)
{
double totalSales = 0;
double totalSales_NO_FORMULA = 0;

try
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“Aspose.Cells.lic”);

Aspose.Cells.Workbook wkb = new Workbook ();
wkb.Open(strFilePath);

Worksheet sheet = wkb.Worksheets[wkb.Worksheets.Count - 1];
int intRow = wkb.Worksheets[wkb.Worksheets.Count - 1].Cells.Rows.Count;
// ***** IF I TAKE NEXT STATEMENT OUT I GET THE RIGHT VALUE,
// BUT THEN I AM AT THE MERCY OF THE USER RECALCULATING
// FORMULAS BEFORE SAVING THE WORKSHEET
wkb.CalculateFormula(); // just in case
//
totalSales = (decimal) sheet.Cells[intRow -1, 12].DoubleValue;
totalSales_NO_FORMULA = (decimal) sheet.Cells[intRow-1, 13].DoubleValue;

return true;
}
catch (Exception ex)
{
Log.WriteLogEntry("Failed to get costs cost from : " + strFilePath);
return false;
}


2

Hi,

Please try the attached latest fix v4.9.1.13. If you still find the issue, kindly do post the template file with all the details on which cells the issue is occuring. We will check your issue soon.

Thank you.

3

This version works fine. Thanks!