Formula not calculating after save.

[efuture]

Hello,

currently i have the follwing problem with your library:
I manipulate a excel-sheet and save it. When i open the modified file with Excel the formulas are not evaluated.
Even if i click on "Recaluclate Sheet" in Excel its showing zero. But when i click on the cell formula and press enter the forumla gets updated. See screenshot in attachment - it should show Edeka-EH not zero. When i reenter the Formula ("='Promo-Set2'!C$5") it calculates.


I also noticed that when i try to save the file with

Code: Select all

workbook.Save();

there is no error.
But when i use

Code: Select all

workbook.SaveToFile("test.xlsx", ExcelVersion.Version2016);

it throws:

Code: Select all

System.NotSupportedException: The formula is not supported in this Version
   bei Spire.Xls.Core.Spreadsheet.FormulaUtil.3(Ptg[] A_0)
   bei spr1987.0(XmlWriter A_0, spr2354 A_1, XlsCellRecordCollection A_2)
   bei spr1987.0(XmlWriter A_0, BiffRecordRaw A_1, RowStorageEnumerator A_2, XlsCellRecordCollection A_3, Dictionary`2 A_4, String A_5)
   bei spr1987.0(XmlWriter A_0, spr2403 A_1, XlsCellRecordCollection A_2, Dictionary`2 A_3, String A_4)
   bei spr1987.0(XmlWriter A_0, spr2403 A_1, XlsCellRecordCollection A_2, Int32 A_3, Dictionary`2 A_4, String A_5, Boolean A_6)
   bei spr1987.0(XmlWriter A_0, XlsCellRecordCollection A_1, Dictionary`2 A_2, String A_3, Dictionary`2 A_4, Boolean A_5)
   bei spr1987.0(XmlWriter A_0, XlsWorksheet A_1, Stream A_2, Stream A_3, Dictionary`2 A_4)
   bei spr645.0(XlsWorksheet A_0, Dictionary`2 A_1)
   bei spr645.0(XlsWorksheet A_0, Dictionary`2 A_1, Dictionary`2 A_2)
   bei spr339.0(XlsWorksheet A_0, String A_1, Dictionary`2 A_2, Dictionary`2 A_3)
   bei spr339.0(XlsWorksheetBase A_0, String A_1, RelationsCollection A_2, String A_3, Dictionary`2 A_4, Dictionary`2 A_5)
   bei spr339.0(RelationsCollection A_0, String A_1, Dictionary`2 A_2, Dictionary`2 A_3)
   bei spr339.0(Dictionary`2 A_0, Dictionary`2 A_1)
   bei spr339.1(ExcelSaveType A_0)
   bei spr339.2(ExcelSaveType A_0)
   bei spr339.0(Stream A_0, ExcelSaveType A_1)
   bei spr339.0(String A_0, ExcelSaveType A_1)
   bei Spire.Xls.Core.Spreadsheet.XlsWorkbook.SaveAs(String fileName, ExcelSaveType saveType, ExcelVersion version)
...


unfortunately i can not provide code or the exel sheet. It contains sensitive data from a customer.
Is there a way to investigate which formula is not supported? Iam using Excel 2016.
Maybe thats the problem why the save() method does not evaluate the formulas? Because it has a problem with a formula?
I also noticed if i use

Code: Select all

workbook.CalculateAllValue()

it throws a StacKoverflow Error. The Excel file is large and complex maybe thats the problem? I also tried to repair the file.

Best regards
Oliver Haase

[Simon.yang]

Hello Oliver,

Thanks for your post. If you are using an older version, please first update to the latest one(Spire.XLS Pack(Hotfix) Version:8.3.5) and try again. In case the issue still exists, please share us with your full code. In addition, if you could provide us the document, it will help us a lot to resolve it. We promise to keep it confidential and won't use it for any other purpose. You could also remove the sensitive parts in it and send to our email(support@e-iceblue.com).

Best regards,
Simon
E-iceblue support team

[Simon.yang]

Hello Oliver,

How is the issue going?
We will appreciate it if you could give us some feedback.

Best regards,
Simon
E-iceblue support team

[efuture]

Hello,

i prepared a sample file for you. It is the output file after processing it with your library.
The problem here is, that the Cell A123 in "Listungsveränderung" has a direct reference to ='Promo-Set2'!C$5 but the cell is not updating on loading the workbook in excel. Even if i click "Recaluclate" in Excel nothing happens.

Please investigate that file - there should be a bug in maybe in the inner structure.

[efuture]

Hello Oliver,

Thanks for your post. If you are using an older version, please first update to the latest one(Spire.XLS Pack(Hotfix) Version:8.3.5) and try again. In case the issue still exists, please share us with your full code. In addition, if you could provide us the document, it will help us a lot to resolve it. We promise to keep it confidential and won't use it for any other purpose. You could also remove the sensitive parts in it and send to our email(support@e-iceblue.com).

Best regards,
Simon
E-iceblue support team

I am using always the latest version from stable channel from nuget.

[Simon.yang]

Hello Oliver,

Thanks for your feedback. In order to help us reproduce the issue, would you please share us with your input Excel file and full code?

Best regards,
Simon
E-iceblue support team

[tosers]

I have the same problem


If I manipulate a excel-sheet and save it. When i open the modified file with Excel the formulas are not evaluated.

I have to make a macro with the following statment Application.CalculateFullRebuild,

[rachel.lei]

Hi Tomas,

Thanks for your post.
You mean that the formula value of the modified file is not updated correctly, right? Please invoke the CalculateAllValue method to caculate all formula for the workbook before saving the file.

Code: Select all

    Workbook workbook = new Workbook();
    //......
    workbook.CalculateAllValue();

If the issue still happens after trying, to help us investigate your issue more accurately and quickly, please provide your input file, your full test code along with your output file. You could send them to us(support@e-iceblue.com) via email. Thanks in advance for your assistance.

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello Tomas,

Greetings from E-iceblue!
Has your issue been resolved?
Looking forward to your feedback!

Sincerely,
Rachel
E-iceblue support team