how calculate value for merged cells

[sxi123123123]

I am using the method of the workbook object described below.
The version used is Spire.XLS 15.1.3.0.
⇒ Workbook.CalculateAllValue();

I understand that all formulas in the target Excel file are recalculated using the above method, but an issue has occurred where formulas set in merged cells are not being recalculated.
I would like to recalculate the formulas for merged cells as well; is there any workaround for this?

I have attached an example of a file where recalculation is not occurring.

■ XXX.xlsm
⇒ In the "ccc" sheet, the formulas in the merged cells from H123 to Q123 are not activated, resulting in a #VALUE error.
■ YYY.xlsm
⇒ In the "ddd" sheet, the formulas in the merged cells from K8 to Y8, X23 to AG23, and X39 to AG39 are not activated, leading to the displayed date format being different from what is expected.

[sxi123123123]

I have an additional question.

I found that when you press Enter while in Excel's edit mode (after selecting the cell and pressing F2), it displays correctly for the original values referenced by the formula.

Is it possible to implement such an operation using Spire's methods?

I would greatly appreciate your response on this matter.

Thank you.

[William.Zhang]

Hello,

Thanks for your inquiry.
Please be informed that our Workbook.CalculateAllValue() method corresponds to the Calculate Sheet function in Excel. I have tried to open your document directly in Excel and click the Calculate Sheet button, but the corresponding formulas are not recalculated. Sometimes a Complie error is prompted. Therefore, we believe that this problem is related to your document itself. We currently do not have the appropriate code to help you calculate these formulas. Thanks for your understanding.

Sincerely,
William
E-iceblue support team

[sxi123123123]

Thank you. I will check that.
How about the second question?

I look forward to your response.

[William.Zhang]

Hello，

Thanks for your reply.
Regarding the second question, could you please provide us with more details, such as some screenshots and test documents to help us investigate more accurately? Thanks in advance.

Sincerely,
William
E-iceblue support team

[sxi123123123]

I have pasted the operation image capture in operation.xlsx.
test.xlsx is a sample file.

[William.Zhang]

Hello,

Thanks for the additional information.
For your file, the operation performed in Excel actually converts the text in this cell into a number. In Spire.Xls, you can use the sheet.Range["B2"].ConvertToNumber(); method to achieve the same effect.

Sincerely,
William
E-iceblue support team

[sxi123123123]

Thank you.

In the previous case, I provided a sample file for numeric values, but what I want to address now is not limited to numbers.

The same issue occurs in the "XXX.xlsm" file that I provided at the beginning of this thread.

I will share a capture of the specific manual operation image. (I have provided two resolutions on separate sheets.)

Pressing F2 resolves the issue, but if there are other methods to eliminate #VALUE!, I would like to know about those as well.

If you need any additional information, please let me know.

[William.Zhang]

Hello,

Thanks for your reply.
As I replied, the previous case should be related to your document itself, and this issue is different from the issue in the ''test.xlsx'' file you provided, so the above solution is not suitable. I will share your document to our development team for further analysis, and they will assist in investigating and confirming whether there is a solution. If there is any good news, I will inform you as soon as possible.

Sincerely,
William
E-iceblue support team

[William.Zhang]

Hello,

Thanks for your following up.
Regarding the formula issue mentioned above, our development team has adjusted the code according to your needs. Now after loading and saving the document through the code, the corresponding formula result can be calculated. Please use Spire.XLS Pack (Hotfix) Version: 15.7.8 for testing.
WebSite Link:https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget:https:www.nuget.org/packages/Spire.XLS/15.7.8

Sincerely,
William
E-iceblue support team