We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

SUBTOTAL function does not return a value

Hello,

When we are using the SUBTOTAL function on a cell and try to query the value of the cell, it returns '#NAME?' and cannot retrieve the value.

Here is the test that goes along with the attached Sheet.

[Test]
public void Value_SubtotalCell_ReturnsValue()
{
var workbook = new Workbook();
workbook.Open("SubtotalFunction.xls", FileFormatType.Default);

workbook.CalculateFormula();

var worksheet = workbook.Worksheets[0];

var cell = worksheet.Cells[0, 0];

Assert.IsNotNull(cell);
Assert.IsTrue(cell.StringValue.Equals("10"));
}

Thanks,

Michael

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

Thank you for considering Aspose.

Well, SUBTOTAL formula is currently not support by workbook.CalculateFormula(). We will get back to you after providing the feature / formula.

Please see the list of formulas currently supported by workbook.CalculateFormula() method.

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/aspose.cells.workbook.calculateformula_overload_1.html

And, you can try workbook.ReCalcOnOpen=true to recalculate the formulas when the workbook is opened. Please modify your code as follows,

var workbook = new Workbook();

workbook.ReCalcOnOpen=true;

workbook.Open("C:\\Files\\SubtotalFunction.xls", FileFormatType.Default);

// workbook.CalculateFormula();

var worksheet = workbook.Worksheets[0];

var cell = worksheet.Cells[0, 0];

Thank You & Best Regards,

Thanks for the response, it seems to do the trick. (Note: we have purchased Aspose.Cells)

So what is the difference between ReCalcOnOpen and CalculateFormula?

Thanks,

Michael

<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” /><o:p><o:p>

Hi Michael,

Thank you for considering Aspose.

Well, Workbook.ReCalcOnOpen property is used when you want all the workbook formulas to get automatically re-calculated when the workbook will be opened. Whereas, Workbook.CalculateFormula() method can be used whenever you want to calculate the result of the workbook formulas (may be after opening and manipulating the data of the workbook you want to calculate the results.). CalcualteFormula can be used at any time in your code as per your requirement to calculate the formula results but using ReCalcOnOpen will only automatically re-calculate when the workbook is opened.

Thank You & Best Regards,

I understand the usage but why does one allow calculating the SUBTOTAL function while the other does not?

Thanks,

Michael