Refresh Excel spreadsheet automatically with formulas via Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi,

Is there any method or way to refresh the .xlsx file with formulas using java aspose.cell.

My requirement is on click of a button a predefined aspose spread sheet with formulas (calculations)should populate datas to be specified sheets and using this input data, the sheet named 'OUTPUT 'should run the formulas and calculated result should upload to database. The import data from database is working perfectly fine but when I take the calculated data from 'OUTPUT ' giving the value "#N/A". That means the automatic calculation (refresh) is not happening. I have tried with the method 'workbook.calculateformula()', but it taking large amout of time to finish the calculation since the spreadsheet have lot of calculations.

It would be great if any one can provide me a solution ASAP.

Thanks & Regards

Aneesh

2

Hi,

Well, you need to call calculateFormula() method if you need to get the calculated values at runtime. I think you may try Worksheet.calculateFormula() method if you need to get calculated values from a single worksheet, the description of the method is given as:

public void calculateFormula(boolean recursive,
                             boolean ignoreError)
                      throws FormulaCalcException
Calculates all formulas in the worksheet.

Parameters:
recursive - True means if the worksheet' cells depend on the cells of other worksheets, the dependant cells in other worksheets will be calculated too. False means all the formulas in the worksheet have been calculated and the values are right.
ignoreError - Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.


If you still need some enhancements regarding calculation/recalculation, please provide us the template file here, we will check it soon.


Thank you.
3

I have tried with 'workbook.calculateFormula(true)' and 'workshee.calculateFormula(boolean recursive,boolean ignoreError)'

This is initiating automatic calculation and populating the calculated value for some cell .But in some other cells aspose populated #VALUE (aspose thrown exception saying problem with formula), But if we open the excel spreadsheet which is saved in local drive is giving values.

please help to solve it. note: The OUTPUT sheet formulas are refering other sheets also

eg. =VLOOKUP(E26,'II. Consolidated portfolio'!$A$2:$IV$278,MATCH(OUTPUT!$B$3,'II. Consolidated portfolio'!$A$2:$IV$2,0),FALSE)

is given #VALUE

4

Hi,

Please provide us the sample Excel file to show the issue of Aspose.Cells formula calculation engine, we will check it soon.

thank you.

5

Hi,

I am not able to set the fileformat type for workbook. I mean there is no method showing setFileFormatType(int fileFormatType) for workbook in my env.But as per API ref this method is there for workbook.

I am using the version aspose cell 2.1.1.6

Is there any way to set the file format type for workbook .

regards

Aneesh

6

Hi,

Well, the method is there in the new versions. Kindly download and try Latest version of Aspose.Cells for Java:


Thank you.