Invalid Column Index error

[KallindSoni]

spire.xls.common.SpireException: Invalid column index.: at sprd1m.spra(Int32) + 0x4a at sprfip.sprm(Int32) + 0x16 at sprezm.sprf(Int32, Int32) + 0x638 at Spire.Xls.AOT.NLWorksheet.Worksheet_Move(IntPtr, IntPtr, IntPtr, IntPtr) + 0x10b

Traceback:
File "C:\Users\Kallind.Soni\OneDrive - insyncanalytics\Code Repo\CROWN_KRAFT_Main\segment.py", line 708, in reorder_columns
sheet.Move(source_range,target_range)
File "C:\Users\Kallind.Soni\AppData\Local\anaconda3\envs\consensus\lib\site-packages\plum\function.py", line 642, in __call__
return self.f(self.instance, *args, **kw_args)
File "C:\Users\Kallind.Soni\AppData\Local\anaconda3\envs\consensus\lib\site-packages\plum\function.py", line 584, in __call__
return method(*args, **kw_args)
File "C:\Users\Kallind.Soni\AppData\Local\anaconda3\envs\consensus\lib\site-packages\spire\xls\Worksheet.py", line 545, in Move
CallCFunction(GetDllLibXls().Worksheet_Move, self.Ptr, intPtrsourceRange,intPtrdestRange)
File "C:\Users\Kallind.Soni\AppData\Local\anaconda3\envs\consensus\lib\site-packages\spire\xls\common\__init__.py", line 113, in CallCFunction
raise SpireException(info)

can u let me know what this error stands for? i am working on some automation in excel and my code which uses your Move function is working except for one file where it gives this error ? if u can explain me the reason for this i can fix my code


Code:
source_range= sheet.Range[5,position+count,sheet.LastDataRow+1,position+count]
target_range = sheet.Range[5,index+start,sheet.LastDataRow+1,index+start]
sheet.Move(source_range,target_range)

[William.Zhang]

Hello,

Thansk for your inquiry.
Sorry, we are unable to determine the specific cause of the issue from the information currently provided. To help us investigate your issue more accurately, could you please provide us with your full test code and test files (if any)? You can upload them as attachments or send them to this email [email protected]. Thanks in advance.

Sincerely,
William
E-iceblue support team

[KallindSoni]

Here is the function i am using

from spire.xls import Workbook, Stream, FileFormat

def reorder_columns(sheet):
list_of_headers = []
for col in range(7, sheet.LastDataColumn + 1):
header1 = sheet.get_Item(5, col).Value

list_of_headers.append(header1.strip())

sorted_list_of_years= sort_fiscal_periods_reorder(list_of_headers)
count=0
for index,element in enumerate(sorted_list_of_years):

if sheet.get_Item(5,index+7).Value==element:
continue
else:

sheet.InsertColumn(index+7)
count=count+1
position = list_of_headers.index(element) + 7

source_range= sheet.Range[5,position+count,sheet.LastDataRow+1,position+count]
target_range = sheet.Range[5,index+7,sheet.LastDataRow+1,index+7]
sheet.Move(source_range,target_range)



wb = Workbook()
wb.LoadFromFile(r"spire_file.xlsx")
sheet=wb.Worksheets['Model']

reorder_columns(sheet)
wb.SaveToFile('output_Spire.xlsx')


This function works on my other similar files, i tested it on 10 files

[William.Zhang]

Hello，

Thanks for the more information. But you didn't provide the sort_fiscal_periods_reorder function, so I still can't investigate further. Please provide us with this function, or you can directly provide us with the values ​​corresponding to source_range and target_range. Thanks in advance.

Sincerely,
William
E-iceblue support team

[KallindSoni]

def sort_fiscal_periods_reorder(periods):
print(periods)
#if q in period return true
q= any('Q' in period for period in periods)
#if h in period return true
h= any('H' in period for period in periods)

def parse_period(period):
#print("Sorting:",period)

if period == '':
return (float('inf'), float('inf'), True, float('inf'))

is_r_prefix = period.startswith('R-') or period.startswith('R1-')

if is_r_prefix:
stripped_period = period.split('-')[1]
else:
stripped_period = period

suffix_order = {'E': 1, 'A': 2, 'Var': 3, 'Guide': 4, 'LowGuide': 5, 'HighGuide': 6, 'DeltaE': 7, '': 8}
suffix = ''
for key in suffix_order:
if stripped_period.endswith(key) and key != '':
suffix = key
main_part = stripped_period[:-len(key)]
break
else:
main_part = stripped_period
if q and h :
if 'Q' in main_part:
quarter, year = main_part.split('Q')
if quarter == '3' or quarter =='4':
return int(year[-2:]), int(quarter)+1,is_r_prefix, suffix_order.get(suffix,
else:
return int(year[-2:]), int(quarter),is_r_prefix, suffix_order.get(suffix,
elif 'H' in main_part:
half, year = main_part.split('H')
if half == '1':
return int(year[-2:]), 3, is_r_prefix, suffix_order.get(suffix,
else:
return int(year[-2:]), 6, is_r_prefix, suffix_order.get(suffix,
else:
return int(main_part[-2:]), 7, is_r_prefix, suffix_order.get(suffix,
elif q and not h:
if 'Q' in main_part:
quarter, year = main_part.split('Q')
return int(year[-2:]), int(quarter),is_r_prefix, suffix_order.get(suffix,
else:
return int(main_part[-2:]), 5, is_r_prefix, suffix_order.get(suffix,
elif h and not q:
if 'H' in main_part:
half, year = main_part.split('H')
return int(year[-2:]), int(half), is_r_prefix, suffix_order.get(suffix,
else:
return int(main_part[-2:]), 3, is_r_prefix, suffix_order.get(suffix,


sorted_periods = sorted(periods, key=parse_period)
return sorted_periods


Sharing image of the function as well since its getting special characters in it

[William.Zhang]

Hello,

Thanks for the more details. Now I can run the code you provided directly, but I don't encounter the issue you mentioned. I can generate the result document correctly (it has been uploaded to the attachment for your reference). Are you currently using the latest Spire.XLS for Python Version: 15.5.0? If not, please upgrade to this version for testing.

Sincerely,
William
E-iceblue support team

[KallindSoni]

I am using the latest version, i think since i cleared all the data it worked for you i had not tested it, i have added dummy data and i tried running it again on the latest version still getting the issue i am sharing a zip with you which contains the file and python code file please try it again you will encounter the same issue

[William.Zhang]

Hello,

Thanks for your reply.
Yes, I have reproduced the issue you mentioned in the latest document you provided and logged it to our tracking system with ticket SPIREXLS-5867. Our development team will investigate further and fix it. We will notify you as soon as the issue is resolved. Sorry for the inconvenience.

Sincerely,
William
E-iceblue support team

[KallindSoni]

Are there any updates related to ticket SPIREXLS-5867?

[William.Zhang]

Hello,

Thanks for your following up.
We have already fixed issue SPIREXLS-5867 internally, and it is currently in the testing phase. Once the official version is available, we will notify you as soon as possible.

Sincerely,
William
E-iceblue support team

[William.Zhang]

Hello,

Thank you for your patience.
Our latest release of Spire.XLS for Python Version:15.7.1 fixes the issue SPIREXLS-5867, please update the test.
WebSite Link:https://www.e-iceblue.com/Download/Spire-XLS-Python.html

Sincerely,
William
E-iceblue support team