Applying calculateformula on Worksheet

Hi,

I would like to calculateformula on worksheet, but it takes a parameter , what that should be?


Hi Soyeesh,


Thanks for your inquiry. I’m moving your request to the related forum; Aspose.Cells. There one of my colleagues will guide you properly.

Best Regards,

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We will look into your issue regarding how to use the Worksheet.CalculateFormula(string formula) method and get back to you asap.

This issue has been logged as CELLSNET-41145.

Hi ,

I am also facing issue while fetching the values of the cell which is calculated by a formula, even after calling the calculateformula method on workbook.

Sample: testWorkbook.calculateFormula, testWorkbook.calculateFormula(true)

I am finding few columns are filled with results of formulas where few columns are not filled even formula execution happened.

Could you explain why columns based on formula is not getting filled even after calling calculateformula (workbook).

Sample formulas used : =DATE(YEAR(Sheet1!C14),12,31) - This formula is getting executed

=SUMIF(Sheet2!K:K,Sheet3!A2,Sheet2!E:E) - This formula is not getting executed and hense no results.

What I can do to make sure I have results for all formulas in page/sheet.

Regards

Soyeesh

Hi,

I would like to add few info regarding this.

I am using ASPOS cells for Java, version aspos-cells - 7.1.0.4.jar

Regards

Soyeesh M

Hi,

Thanks for your posting and using Aspose.Cells for Java.

It seems, your issue is occurring because of the ignoreError parameter.

i.e

workbook.calculateFormula(bool ignoreError);

Since, you have set it true, so you are ignoring the errors that’s why calculateFormula() does not fail but in final output file you get some cells with no or wrong results. These cells mostly depend on other cells for the calculation of formulas.

Please change your code into

workbook.calculateFormula(false);


So that all formulas could be calculated and if there are some errors, exception or message could be displayed.

Hi,

I tried your suggestion but didnt work even after that.

Also tried like - worksheet.calculateformula(cell.getformula);

Even then its not working.

Regards

Soyeesh M

Hi,

Thanks for your input.

It seems, there is some issue with your source file or it might be a bug with Aspose.Cells for Java which is unable to handle your file.

Please provide us your source file so that we could investigate this issue further and help or advise you.

Hi ,

Please find the attached template which we are processing.

While processing Sheet "Sheet8" we need the results from cells

B2 to B19 , C2 to C19 , D2 to D19 etc as results

I am getting the values of A2:A19 as expected, but B,C,D etc columns values are NOT being fetched.

Version used aspose-7.3.2.1.

I am calculating formula, on workbook [with true/false as params] as well as on worksheets.

Still I am not getting results on sheet8 columns [B, C etc].

I tried calculation on sheets also as sheet.calculatFormula(true, true, null);

If you could share a ICustomFunction implementation It might be helpfull.

Reuquest your support ASAP.

Regards

Soyeesh M

Hi ,

Please find the attached template which we are processing.

While processing Sheet "Sheet8" [result sheet] we need the results from cells

B2 to B19 , C2 to C19 , D2 to D19 etc as results

I am getting the values of A2:A19 as expected, but B,C,D etc columns values are NOT being fetched.

Version used aspose-7.3.2.1.

I am calculating formula, on workbook [with true/false as params] as well as on worksheets.

Still I am not getting results on sheet8 columns [B, C etc].

I tried calculation on sheets also as sheet.calculatFormula(true, true, null);

If you could share a ICustomFunction implementation It might be helpfull.

If save the workbook, I could see the values in output sheets, but while processing [executing , in memmory] , I am not able to get the formula results with ASPOS .

[Means not an issue with file opening in excel, but unable to fetch results through aspos while application execution]

Code sample on output sheets.

tempCells.get("B2").getStringValue() - returns empty values only.

Request your support ASAP.

Regards

Soyeesh M

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Please download and try the latest version:
Aspose.Cells
for Java v7.3.2.4

and see if it makes any difference.

We have tested your file
with the following code and we were unable to find any problem.

  1. Worksheet.CalculateFormula(string formula) is used to directly calculate this formula and not calculate the formulas in the worksheet.

  2. We tried Worksheet.CalculateFormula(true, true,null); it also worked fine with the latest version:
    Aspose.Cells
    for Java v7.3.2.4

Java

Workbook workbook = new Workbook(“d:\FileTEmp\test_sheet.xlsx”);

Worksheet sheet = workbook.getWorksheets().get(“Sheet8”);

sheet.calculateFormula(true, true,null);

System.out.println(sheet.getCells().get(“B2”).getStringValue());//" - "


Hi,

I will explain the scnario here.

The output sheet[Sheet8] is depending on other sheets where data will be filled by application during execution.

Once the data is fed to the sheet, I am expecting my output sheet to be ready by executing the formulas.

Before fetching the output [from sheet 8], I am ensuring that

wrkbook.calculateFormula(false); , outputSheet.calculateFormula(true, true, null); these are called.

I tried with version 7.3.2.4.jar - .

What I am unable to do is a [kind of] refresh on workbook / sheet, so that calculated results of the sheet reflects while fetching. [MS excel might be handling this while opening the file or we could refresh the file manualy]. Achiving the same through ASPOSE while processing is what I am unable to do.

I am attaching asheet where I filled the data Sheets [3,4,5] , I could see the results in sheet 8. During the application execution I am not able to fetch these results.

Sheet 8 , column A I am able to fetch the value [green].

Sheet 8 B, C, D.....[marked red] unable to fetch the values.

Note :- There is no file reading from disc, the workbook will inititaed from DB contents and then its all in memmory processing [No saving to disck and fetching back].

Regards

Soyeesh M

Hi,

Thanks for your feedback.

It’s difficult to look into your issue because of lack of sample test code. However, we will look into it and share our findings with you as soon as possible.

Hi,

Found the issue.

Columns which were having issue in formula calculation was reffering to a date field, which was formated and the result was a string. Since the input fed was string instead of Date what goes to input sheet while applying the formula this might have caused the issue.

If the API could handle the Date and Date formatted in String this could be resolved.

Regards

Soyeesh M

Hi,

It’s good to know you were able to find the root of this problem.

Let us know if you find any other issue, we will be glad to help you asap.

FYI:
There is Cell.putValue() overload that can convert the string input to date input. e.g

cell.putValue(“20-Sep-2009”, true);

will convert the string into date while inserting it.

Below is a complete sample code for testing this method overload.

Java


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“A1”);


cell.putValue(“20-Sep-2009”, true);


Style st = cell.getStyle();

st.setNumber(14);

cell.setStyle(st);


worksheet.autoFitColumns();


workbook.save(“outputs.xlsx”, SaveFormat.XLSX);