Copying values instead of formulas

[mkcgn]

Hi e-iceblue-team,

Having a bit of trouble with Spire.XLS at the moment.

I am starting out with a large Excel sheet with various formulas. I now want to copy only the calculated values into a new sheet (in another workbook). This new worksheet should ONLY contain the values, no formulas.

So far, the only way seems to be calculating cell for cell and copying the values manually. However, this is very slow and I additionally get "unknown formula IFERROR" exceptions.

Is there a better way to do this?

Alternatively, is there a way to replace all formulas in a certain range with their calculated values?

Best regards,
Markus

[Jane.Bai]

Hello,

Thanks for your inquiry.
Considering your case, sorry there seems no better way to do that, you had to calculate first and then copy the values, However, we have a method " workbook.CalculateAllValue()" which supports calculating all the formulas in the document together, there's no need to calculate cell by cell. As for the exception thrown, could you please send the document to us(support@e-iceblue.com) for a better investigation?

Sincerely,
Jane,
E-icebllue support team

[mkcgn]

Hello Jane,

thank you for your quick response.

What is the recommended way of reading the value from a cell after using CalculateAllValue()?
The Value Property returns the formula again.

Unfortunately I cannot provide you with the document in question since it contains sensitive client information.

Best regards,
Markus

[Jane.Bai]

Hi Markus,

Thank you for your reply.
Please use the "FormulaValue" to get the result value after calculation rather than "Value". eg.workSheet.Range["B12"].FormulaValue;
In addition, I'm afraid we are not able to detect the cause of the exception without the document since every document has its unique data structure. We will keep it confidential and never use it for other purposes, see if you could send it to us. Besides, you could also remove the confidential part as long as we could reproduce the scenario.

Sincerely,
Jane
E-iceblue support team

[mkcgn]

Hi Jane,

thank you for your update. We finally managed to get our copy function working.

Apparently the exception no longer appears in this version, so it may have been due to a mistake in the code.

Thank you for your support.

Best regards,
Markus

[Jane.Bai]

Hi Markus,

Glad to hear that.
Please feel free to contact us if you need any assistance.

Sincerely,
Jane
E-iceblue support team