Obtaining Formula values

Aspose.Cells Product Family
1

I am trying to display a chart using the values obtained from cells that have formulas. The values calculated by the formula appear on the worksheet correctly. I would like to copy just the numeric value of these cells to another cell on the worksheet.

Everytime I try to obtain the cell value it outputs a null value or a 0. Is it possible to obtain the numeric value?

2

Please post your file and sample code here.

To get formula result, please call Workbook.CalculateFormula method first.

3

Please see attached spreadsheet, below is a small sample of code illustrating the problem.

// sample code, formula for Remaining Budget column = Cn-Hn

workbook.Worksheets[0].Cells[6, 9].Formula = "=C7-H7";

workbook.CalculateFormula();

// sample cell to check value obtained by formula is output correctly

workbook.Worksheets[0].Cells["K2"].PutValue(worksheet.Cells[6, 9].Value);

4

any update please ?

5

I don’t find any problem. Maybe it’s a problem in old version. Please try this attached fix.

6

No change i'm afraid.

I am using the latest version of Aspose Cells, 4.0.1.0. Just in case there is some kind of misunderstanding, I am trying to copy the value obtained by the formula (in this case at cell J7) to another cell in the spreadsheet. In Excel I usually do this through copying cell J7, then paste special (values and number format) to the destination cell.

Thanks for your time on this.

7

The attached version is v4.0.1.8. Please try it.

I used the following test code:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\sample output.xls");
workbook.Worksheets[0].Cells[6, 9].Formula = "=C7-H7";

workbook.CalculateFormula();

// sample cell to check value obtained by formula is output correctly

workbook.Worksheets[0].Cells["K2"].PutValue(workbook.Worksheets[0].Cells[6, 9].Value);
workbook.Save("d:\\test\\abc.xls");

Attached is output file.

8

Thanks Laurence it is now working. Apologies, it was a fault on my part. The formulas were added at the time the borders and styles were applied but before the cells were filled with values. Hence the formula values appeared correctly as they update automatically when cells in the formula range are modified.

However, the test code to copy the values in the formula cells was in the same location as the code for formulas hence the value 'at that time' would have been empty/0.

9

So now the problem is solved, right?

10

Yes, thank you