Good afternoon,
At one of our projects, the Advanced Excel component got updated to its latest version. Everything is working nice, expect for one thing: the refresh of data in on of the worksheets.
I cannot share the sheet as it is at the moment due to client data, but image there is a table with 10 rows and 5 columns, and at the end of each row there is a SUM (of that row) and below each column there is a SUM (of that column). Below there is also a graph visualising the table.
I used both "Workbook_Calculate" and "Worksheet_Calculate" at the end of the logic, just before calling "Workbook_GetBinaryData" and "Workbook_Close", however, no luck so far in calculating the SUMS.
Any thoughts? Is there something that I'm missing here
Cheers,Richard
Hi, Richard.I did some tests and I think I found the issue. Here is the revised oml so you can check my changes. I think the issue was, in the "CellType" property of the action Cell_Write, you were using "Entities.CellType.Integer" and it should be just "integer". This was not being translated as an integer into Excel, hence the error when calculating the SUM function.
I did disable some actions since they were not necessary or I didn't have the necessary forge components installed. Let me know if this solution works for you.
Hi Richard
Do the SUMS calculate when you open the file in Excel or do you see an error value in the cells?
How are you writing the SUM formulas to the cells?
Hanno
Goodmorning Hanno,
Attached a screenshot of the sheet in Excel and the PDF.
Thank you in advance for your help!
Hi Richard.
There is an action called SetFormulaByName that might help you set the sum calculations.
I also tried "Cell_CalculateByIndex", unfortunately without success..
I also noticed that if there is already a value defined in the template, the action will not overwrite the value in the cell. Maybe that helps in solving this matter (since a formula is already defined in the template)..
Hi all,
I have attached an example. Hopefully this gives a better indication of the error.
When using function "Cell_ReadFormulaByIndex" on row 14 column 2, it gives "SUM(B2:B13)". However, the calculate function "Cell_CalculateByIndex" doesn't do anything, as the cell still shows 0 on the exported pdf.
Also "Worksheet_Calculate" and "Workbook_Calculate" don't have the expected effect...
Thank you in advance!
Hi, Richard.Have you tried the action SetFormulaByName? You can then write "SUM(B2:B13)" and it should give you the desired result.
Unfortunately that's not working.. Could you give it a try in the provided oml?
Hi Richard van Osnabrugge,
I had created a simple demo regarding worksheet_ calculate methods in Advanced Excel Demo component. You can access the application via link. Below I have attached the screenshot of calculating average of a column runtime.
Please let me know if you have further query.
Thank you.
Nice! Could you give me access to the module?
You can download the component and access it.
Thanks, will give it a try!
I got the SUM() working based on your example provided. I will post an update soon what the cause was why it was not working before..
That's great it is working now.
Thanks all for your help! I have marked the final solution.