How to calculate pasted formulas

Aspose.Cells Product Family
1

Hi,

I have some cell that contains formula pasted from another cell. And in MS Excel I was able to calculate the value by clicking on each item.

calculate.gif (203.8 KB)

I could also create macro to automate this calculation if needed.

However, in Aspose, when I ran

workbook.CalculateFormula();

those cells were not calculated and when I checked those cells, they were not considered as formula (IsFormula = false)

Could you let me know how to get those formulas calculated? I’ve attached the test files for your reference.

test.zip (82.4 KB)

Thanks,

2

@ServerSide527,
Thank you for your query. If we evaluate the formulas in the sample excel file using MS Excel, it shows the following:

the cell currently being evaluated contains a constant

If we google this statement, it shows that formula’s are copied in the cells which are formatted as text. In this case the formulas are not calculated by MS Excel as well. You can verify this by calculating your sample Excel file in MS Excel and observe that it does not calculate the sheet.

However if you format the complete column as General and then paste the formulas again, these will be calculated properly by MS Excel and Aspose.Cells. Please give it a try and share the feedback.

3

Hi @ahsaniqbalsidiqui

Thanks for your reply.

Yes, I understood Excel does not evaluate those formulas automatically, however, as soon as you click on those items (without making any changes), the cell gets calculated. You can check my gif attached in my previous comment.

Also, to calculate those pasted formulas all at once, there’s a simple workaround available in Excel (which can be written in a macro): just do a ctrl + H (pop up the replace dialog), then replace character = with = (the same character), click on ‘Replace All’, all cells get calculated automatically.
image.png (3.6 KB)

I’m wondering if Aspose has some similar ability to calculate those cells.

Thanks,

4

@ServerSide527,
We have understood your requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix(if applicable). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSNET-47230 – How to calculate pasted formulas

5

@ServerSide527,

Please note, when you make a cell in edit mode and press enter in MS Excel, you fire the operation of resetting cell’s value or formula. To do the same operation by Aspose.Cells, you may gather all cells which should be taken as formula and reset them as formula. See the sample code for your reference:
e.g
Sample code:

......
object v = cell.Value;
if (v is string) {
string s = (string)v;
if (s.Length > 1 && s[0] == '=') {
cell.Formula = s;
}
}

Hope, this helps a bit.

6

Hi @Amjad_Sahi thanks for your advice.

I think it’s fair that we need to check cells one by one to calculat these formulas. However, I did not find a way to distinguish non formula cells (’=A1) and formula cells (=A1) because both of the cells values are =A1 as string.

However, in Excel, even if you press enter or do a = to = replace on cells like (’=A1), it won’t calculate because any cells beginning with single quote is a string.

When I checked all members of the cells from Aspose class, I didn’t find a way to distinguish them, even if when I opened the same workbook, I could see the cells are different in Excel.
image.png (86.3 KB)

Could you provide some assistance on how to use Aspose apis to find which cells should be a formula and which cells shouldn’t?

7

@ServerSide527,

Thanks for your feedback and further details.

We will evaluate your requirements and get back to you soon.

8

@ServerSide527,
You may check the property:

Style.QuotePrefix

for those cells.

Let us know your feedback.

9

This works beautifully :slight_smile:

Thanks for your help!
.

10

@ServerSide527,

You are welcome. Good to know that the suggested attribute works for your needs.