I’m trying to render the formulas in a worksheet to an image (PNG). To be clear,
I don’t want the formulas results, but rather the formulas themselves to appear
in the image. Am I doing something wrong?
xl_example_02.zip (627 Bytes)
In that case, you do not need to apply formula using SetFormula() rather use PutValue() to specify the formula string for the cell.
@patjep,
You may follow the suggestion provided by @Amjad_Sahi above if you only want to display formula as string in the image. If you want to display formulas of a workbook then you may call following function after setting the values and formulas, as without calling it the effect of ws->SetShowFormulas(true); will not be shown.
wb->CalculateFormula()
@Amjad_Sahi
Well, yes, but the problem is that our customers creates the xlsx documents
via Microsoft Excel before importing them into our application. This will cause
Aspose.Cells to automatically register all formula cells as such, if I’m not mistaken.
Yes, the devised solution suggested by Ahsan Iqbal should display all the formulas in the worksheet. Using my workaround, you may choose your desired formula cells and re-insert formula string via PutValue().
Hope, this helps a bit.
@Amjad_Sahi @ahsaniqbalsidiqui
I’m sorry but I can’t get your solution to work. Calling wb->CalculateFormula()
simply renders the result of my formula in the PNG, not the formula itself.
It might be that I need to render an new PNG quite often, so I would rather not
have to replace all fomulas with PutValue() as it seems rather inefficient. But
I’ll try this solution if there’s no other way.
@patjep,
We have observed the issue in Aspose.Cells for CPP version whereas the same code works fine in .NET version of the product. We have logged this issue in our database for more analysis and fix. We will write back here once ready to share any update in this regard.
This issue has been logged as
CELLSCPP-237 – Formula not displayed in the rendered image
If you do not need to show the results of the formulas, you should use “PutValue” to set the formula strings that would also simplify your code and it is better for you. Moreover, if you want to go for other approach (shared by Ahsan Iqbal) which is currently not working, please notice the formula calculations also will take time so it will not be beneficial to you.
Thank you for the information, I will consider your option. I’m sure the calculations will
take time but I feel it’s a cleaner and overall better solution, albeit not working right now.
What I’m trying to do is essentially render a complete Excel sheet in our own designer
software, including some light editing. It’s also important not to edit our the sheets outside
of the select few sheets the customer want to edit. That said, how do you propose that
your idea should be implmented? Will re-setting all formulas with PutValue affect the
sheet in a negative way?
You need to scan through the formula cells and re-insert formula strings via PutValue() for your desired sheets only.
It won’t create negative effect or performance issue apart from the case where you are browsing huge list of cells (millions of cells).