Cell not calculating

[dstomps96]

I have an excel workbook that i input values in the first worksheet (titled rate and something) and then the second worksheet uses those values to calculate some fields in it (the sheet's title is quote with fees) after that i save the second sheet as excel. The problem is that its not calculating the fields, particularly the APR as its very important.
NOTE: when i manually run "Ctrl + Alt + F9" it calculates the fields correctly

WHAT I TRIED:
I tried using the CalculateAllValue() func but it didnt work

code
# The first Sheet that the values are to be inputed [Rate & Details]
workbook = Workbook()
workbook.LoadFromFile("DS-API-C2_QUOTE-600-619.xlsx")
sheet = workbook.Worksheets.get_Item("Rate & Details")

# Input the value in the sheet
for value in value_mappings:
cell = sheet.Range[value["cell"]]
cell.Value = value["value"]

sheet.SaveToPdf("quote.xlsx")
workbook.Dispose()

version
Spire.Xls==13.12.6 (Python) (the latest version doesnt work inside docker container because of some font issue)

env
Windows 11 and Docker (the issue happens on both environments)

application: FastAPI

[Doris.Liu]

Hi,

Thanks for your inquiry and sorry to reply late for weekend.
I simulated a document to test using the following code and the cell formulas automatically calculate the values. It is recommended that you test with the latest version of Spire.XLS 14.4.4 as it contains more fixes. If you still have problems, please send your test document to 'support@e-iceblue.com' for us to test and investigate. Thank you for your assistance!

Code: Select all

Workbook = Workbook()
Workbook.LoadFromFile("testCal.xlsx")
sheet = Workbook.Worksheets[1]
cell = sheet.Range["A2"]
val1 = Workbook.Worksheets[0].Range["A1"].Value
val2 = Workbook.Worksheets[0].Range["B1"].Value
cell.Formula = "=" + val1 + "+" + val2
Workbook.SaveToFile("testVal.xlsx")

Sincerely,
Doris
E-iceblue support team

[dstomps96]

Whenever i try that i get an error
`
Error in Cell: C14-Arg_IndexOutOfRangeException"=IF(C10="","12",_xlfn.IFERROR(MAX(C38,C39),""))".: at spr暧.spr똁(spr崩, String, Byte, Boolean) + 0x2fd
at spr崩.spr뫳(String) + 0x4f
at Spire.Xls.Core.Spreadsheet.XlsRange.set_Value(String) + 0x1c1
at Spire.Xls.AOT.NLXlsRange.XlsRange_set_Value(IntPtr, IntPtr, IntPtr) + 0x66
Traceback (most recent call last):
File "C:\Users\Bello Ango\Desktop\gohighlevel_automation_backend\app\automation\go_high_level\entry.py", line 49, in start_automation
excel_automation(
File "C:\Users\Bello Ango\Desktop\gohighlevel_automation_backend\app\automation\go_high_level\entry.py", line 230, in excel_automation
edited_workbook = fill_excel(
^^^^^^^^^^^
File "C:\Users\Bello Ango\Desktop\gohighlevel_automation_backend\app\automation\go_high_level\excel.py", line 48, in fill_excel
workbook = calculate_apr(workbook=workbook)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\Bello Ango\Desktop\gohighlevel_automation_backend\app\automation\go_high_level\excel.py", line 81, in calculate_apr
cell.Value = "=IF(C10=" "," ",IFERROR(MAX(C38,C39)," "))"
^^^^^^^^^^
File "c:\Users\Bello Ango\Desktop\gohighlevel_automation_backend\.venv\Lib\site-packages\spire\xls\XlsRange.py", line 890, in Value
CallCFunction(GetDllLibXls().XlsRange_set_Value, self.Ptr, value)
File "c:\Users\Bello Ango\Desktop\gohighlevel_automation_backend\.venv\Lib\site-packages\spire\xls\common\__init__.py", line 109, in CallCFunction
raise SpireException(info)
spire.xls.common.SpireException: Error in Cell: C14-Arg_IndexOutOfRangeException"=IF(C10="","12",_xlfn.IFERROR(MAX(C38,C39),""))".: at spr暧.spr똁(spr崩, String, Byte, Boolean) + 0x2fd
at spr崩.spr뫳(String) + 0x4f
at Spire.Xls.Core.Spreadsheet.XlsRange.set_Value(String) + 0x1c1
at Spire.Xls.AOT.NLXlsRange.XlsRange_set_Value(IntPtr, IntPtr, IntPtr) + 0x66
Error occured while running Automation: 3
Error in Cell: C14-Arg_IndexOutOfRangeException"=IF(C10="","12",_xlfn.IFERROR(MAX(C38,C39),""))".: at spr暧.spr똁(spr崩, String, Byte, Boolean) + 0x2fd
at spr崩.spr뫳(String) + 0x4f
at Spire.Xls.Core.Spreadsheet.XlsRange.set_Value(String) + 0x1c1
at Spire.Xls.AOT.NLXlsRange.XlsRange_set_Value(IntPtr, IntPtr, IntPtr) + 0x66
`


here is my code
cell = quote_sheet.Range[cell]
cell.Value = "=IF(C10=" "," ",IFERROR(MAX(C38,C39)," "))"

[dstomps96]

I also did some checks, its like spire corrupts the file. the moment i open the excel with spire i cant open it in openpyxl until i open the file using my local excel, save it before i can use it.

i.e. saving with SaveToFile corrupts it

[Doris.Liu]

Hello,

Thank you for your response.
I have replicated the issue you mentioned regarding the formula "=IF(C10=" "," ",IFERROR(MAX(C38,C39)," "))" causing an error, and I have documented this issue in our bug tracking system under the reference SPIREXLS-5268. Our development team will investigate and fix this issue, and we will promptly notify you once it is resolved.

Additionally, concerning the error encountered when loading files exported from our product using openpyxl, I speculate that it might be due to some data incompatibility issues. I have also recorded this issue in our tracking system under SPIREXLS-5267. Rest assured, we will keep you timely informed of any progress made by the development team in their investigation.

We apologize for any inconvenience this has caused and appreciate your patience while we work towards resolving these matters.

Sincerely,
Doris
E-iceblue support team

[Doris.Liu]

Hi,

Thank you for your patience.
Regarding SPIREXLS-5268 (formula issue), after investigation by our development team, it was found that the issue was caused by nested double quotes in Python. Please use the following method to add the formula instead. I have tested it and it does not produce any errors. If you have any further questions, please feel free to reply.

Code: Select all

cell.Value = '=IF(C10=" "," ",IFERROR(MAX(C38,C39)," "))'

Sincerely,
Doris
E-iceblue support team