Missing data when saving excel named range to image

Aspose.Cells Product Family
1

I am trying to save an excel named ranged to an image and noticed that certain data points are not showing up in the image. The data behind the scenes are calculated via formulas and conditional formatting.


Are they any issues when saving as an image if there are formulas or conditional formatting associated to it?

Thanks





2

Hi,


Thanks for your posting and using Aspose.Cells.

When there are formulas inside the workbook or worksheet, you must call Workbook.CalculateFormula() method before taking the image of worksheet. Calling this method before taking image of worksheet or range, should fix your issue. Let us know your feedback.

But if your issue still persists, then provide us your source Excel file as well as your sample code, we will look into it and help you out. Thanks for your cooperation in this regard and have a good day.

3

Hi Shakeel,


That worked but it brings up another question. I have similar excel sheets that did not have the same issue. Why is it that for this particular excel file, I had to call Workbook.Calculate.Formula() method before taking the image of worksheet but did not have to do it for other excel files that are similar?


Thanks,

Sean
4

Hi,


Thanks for your posting and using Aspose.Cells.

MS-Excel does not always store all of the values in Excel file. Some of the values (of formulas or other objects) are calculated at runtime when Microsoft Excel opens it.

If you will not call Workbook.CalculateFormula() method, Aspose.Cells will use the stored values inside the Excel file and will not calculate them at runtime. These values might not be accurate so calculate formula method is a necessary method.

You should always call it because you don’t know, whether formulas need to be calculated or only stored values inside your source Excel file will be OK.

5

Shakeel,


Thanks your reply but I have another question. The formulas are the same in both Excel files, so I’m still confused on why I had to call the Workbook.CalculateFormula() method on one but not the other?


6

Hi,


Thanks for your posting and using Aspose.Cells.

Please share your Excel files so that we could look into it further. However, calculating formula is must because you don’t know when stored values in Excel file will yield correct result or invalid result.

Same formulas do not mean that resulting values will also be same. Please consider this.

A1: 10
A2: 20
A3: =Sum(A1:A2)

Here A3 will always have same formula but if you will change A1 or A2, then A3 will give different results.

A1: =Rand()

Here, A1 has always same formula but whenever you will open Excel file, formula values will be changed.

7

Shakeel,


Attached is a zip file with two excels files. One works (works.xlsm) and the other does not(Does not work.xlsm).

The named range that I am trying to save as an image is called PricingDetailsTable.

You will notice that one of the image will not have the correct “Units” table data.

Let me know if you need more info.

Thanks.
8

Hi,


Thanks for your posting and using Aspose.Cells.

When you open the Excel file and close it and if it prompts you (Save or Don’t Save or Cancel) message, then it means, MS-Excel has re-calculated its values and now asking you to save new calculated values. This (Save or Don’t Save) message box means, this Excel file needs Workbook.CalculateFormula() method.

Please see this screenshot for more help. Let us know if you still have any question.

9

It is asking for Save on both files (Works.xlsm and Does not work.xlsm) when closing but why is it that “Works.xlsm” does not require the calculate function whereas “Does not work.xlsm” needs it in order to take an image correctly.



10

Hi,


Thanks for your posting and using Aspose.Cells.

The prompt means, MS-Excel re-calculates values and asks you to store them in Excel file on closing. These values are called cached values.

If you will not call workbook calculation method, then Aspose.Cells will use cached values which might or might not yield correct results.

This is why Aspose.Cells sometimes work correctly even without calling workbook formula function.