CalculateAllValue() Not Able to Handle Some Formulas

[BFeldman]

Hi,

See my sample project attached. There is an Excel file in the project where I have one tab that has daily stock volumes. In the other tab I calculate the prior 4 weeks total volume. In the sample app I add a new date to the Volumes tab of the Excel with some sample data and then run workbook.CalculateAllValue() and then save the excel workbook. However, when I open up the saved Excel file, in the Weekly Average tab the cells C3:E7 all say "#VALUE!". If I click into the cell and then click out of it the cells calculate correctly. Can you please help me identify the problem so that the correct calculated values show in those cells without having to click into them? I need to convert the file to PDF so I need the correct values to calculate without manual intervention.


Thanks so much,
Ben

[Triste.Dai]

Hi,

Thanks for your inquiry.
I tested your project and did reproduce the issue you mentioned, I have logged this issue into our bug tracking system with the ticket number SPIREXLS-4375, our development team will investigate and fix it, sorry for the inconvenience caused. Once there are any updates available, I will inform you asap.

Sincerely,
Triste
E-iceblue support team

[BFeldman]

Thanks. I also found an issue where workbook.CalculateAllValue() does not properly calculate this formula: =WORKDAY("2022-12-27",-1,'Federal Holidays'!$C$2:$C$101) where 12/26/2022 is a date in the Federal Holidays tab in column C. Let me know if I should open a separate ticket for that or if it is a similar enough issue to be on this thread.

[Triste.Dai]

Hi,

Thanks for your feedback.
I have done some tests in Excel with the WORKDAY() function, if I input some dates in it manually, for example, “=WORKDAY(2022-12-27,-1,2022-12-26)”, this formula will not calculate the correct value in Excel, since the dates are not resolved correctly, you are supposed to use the date() function, for example,” =WORKDAY(DATE(2022,12,27),-1,DATE(2022,12,26))”, this formula will get the right value. In addition, if you define the dates in cells, you can directly refer their positions, for example, “=WORKDAY(A12,B12,E16)”, this formula will also get the right value. You can follow my code and have a test.

Code: Select all

    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];
    sheet.Range["A2"].DateTimeValue = new DateTime(2022, 12, 27);
    sheet.Range["B2"].NumberValue= -1;
    sheet.Range["C2"].DateTimeValue = new DateTime(2022, 12, 26);
    sheet.Range["D2"].Formula = "WORKDAY(A2,B2,C2)";
    sheet.Range["D2"].Style.NumberFormat = "yyyy/MM/dd";

    sheet.Range[12, 3].Formula = "=WORKDAY(Date(2022,12,27),-1,Date(2022,12,26))";
    sheet.Range[12, 3].Style.NumberFormat = "yyyy/MM/dd";

    workbook.CalculateAllValue();   
    workbook.SaveToFile("output.xlsx");
    workbook.Dispose();

The issue SPIREXLS-4375 still exists in the output file, sorry for the inconvenience caused again. We will fix it ASAP.
If you have further questions, just feel free to contact us.

Sincerely,
Triste
E-iceblue support team.

[BFeldman]

The =WORKDAY function still does not work for me. The line below returns 12/26:

workbook.CaculateFormulaValue("=WORKDAY(Date(2022,12,27),-1,Date(2022,12,26))");

When I execute that line in the immediate window in Visual Studios I get:
workbook.CaculateFormulaValue("=WORKDAY(Date(2022,12,27),-1,Date(2022,12,26))");
Exception thrown: 'sprḯ' in Spire.XLS.dll
{12/26/2022 12:00:00 AM}

workbook.CalculateAllValue() also does not fix it.

[Triste.Dai]

Hi,

Thanks for your feedback.
I did some tests with the code you provided, I reproduced your issue, the calculated date was 12/26, not the correct date 12/23. In addition, in my project, there is no exception thrown. Could you please provide us with the following information to help us investigate further? You can send them to us via email (support@e-iceblue.com) or attach them here.
1) the code which throws exception during the period of execution.
2) your region setting, such as (China, Chinese).
3) your device’s date and time formats, you can see the picture in the attachment for reference.

Sincerely,
Triste
E-iceblue support team

[BFeldman]

Thanks.

1) I only got the error when trying to use the immediate window in Visual Studios. When running the code normally, I did not receive any error. I just got the wrong date.

2) United States, English

3) See attached

[Triste.Dai]

Hi,

Thanks for your feedback.
I have logged this issue into our bug tracking system with the ticket number SPIREXLS-4381, our development team will investigate and fix it. Sorry for the inconvenience caused. Once the issue is fixed, I will inform you ASAP.

Sincerely,
Triste
E-iceblue support team

[egmansoori]

Hi,

Can function "CaculateFormulaValue" calculate formulas contain item, index, ...

Thanks,

[Abel.He]

Hello,

Thanks for your inquiry.
Our Spire.Xls follows Microsoft's documentation specification, so long as the formula that can be figured out by MS Excel, which can be figured out by Spire.Xls.

Sincerely
Abel
E-iceblue

[egmansoori]

Very nice !
Thanks a lot.

[Abel.He]

You are welcome!

Sincerely
Abel
E-iceblue support team

[Triste.Dai]

Hi,

Thanks for your patience!
Glad to inform you that we just released Spire.Office 8.1.4 which fixes the issues SPIREXLS-4375 and SPIREXLS-4381.
Please download the new version from the following links to test.
Website download link: https://www.e-iceblue.com/Download/download-office-for-net-now.html
Nuget download link: https://www.nuget.org/packages/Spire.Office/8.1.4

Sincerely,
Triste
E-iceblue support team