I am using ExportDataTableAsString to a DataTable, and I am not getting the values of a cell’s formula. Am I missing something? Is there a way to Export the value information into a DataTable when a formula is supplying the value?
-Chris
Hi Chris,
Currently Aspose.Excel doesn’t support formula calculation at run time. So if you set a formula to a cell at run time, the value cannot be retrieved. We are working on the invisible calculation engine. It will be availabe at the end of July.
If your formulas are set in your designer file, it can be exported. Could you email me your designer file and the code to export DataTable? Thanks.
What do you mean by designer file? I am in the beginning stages of testing. I have a normal xls file that has inserted data, values or formulas. Can the export that you mentioned handle the formulas?
We call the template as designer spreadsheet because you can design everything in the xls file.
Yes. Aspose.Excel can handle the values of formulas in your xls file. If you have any problems, please email the file and your export code to me.
Here is what I have tried. I created a simple spreadsheet with input data. For example, I put in values of integers, text, and simple formulas (=sum(A1:B4).
I then want to retrieve the data and put into a DataTable. I want all data off the sheet as it is and stored into a string. I used the ExportDataTableAsString to retrieve the data and put into a DataTable. The formula cells are given out as null values instead of the numerical value. Am I doing this incorrectly?
I am using spreadsheets that are given from other parties. I do not want to have any user interaction of manually changing or opening the spreadsheets. The process I am working on will be completely automated.
- Chris
Hi Chris,
Now Aspose.Excel supports get/set formulas in cells but doesn’t support to calculate the result of formula. So if you set a formula "=SUM(A1:B4), the result of formula cannot be retrieved. We just put the formula into the Excel file. When MS Excel open the file, it will automatically calculate the formulas.
We are working on the invisible calculation engine. When we complete it, Aspose.Excel will meet your need for this issue. If you have already bought a license, you can free upgrade to new version.
Was this fix ever completed. I have a formula and I was to get the value of the formula. I kee getting zero even though the result of the foirmula is a number.
Thanks
Please call Excel.CalculateFormula method before retrieve result of formulas.