1. Cause analysis

Xlrd and xlwt are functions used to process XLS files in Python. The maximum number of rows in a single sheet is 65535. Therefore, this error occurs when the amount of read and write data exceeds: valueerror: row index was 65536, not allowed by xls format

2.Solution

1. Method 1

The xlwt library is still used for processing, but it limits the maximum number of 5W rows of data in each sheet

# Note: What needs to be written is the two-dimensional list target_data
# Write data into excel table
workbook = xlwt.Workbook()
sheet1 = workbook.add_sheet("Sheet1")
sheet2 = workbook.add_sheet("Sheet2")
sheet3 = workbook.add_sheet("Sheet3")
al = xlwt.Alignment()
al.horz = 0x02 # Set horizontal center
# Create a style object, initialize the style
style = xlwt.XFStyle()
style.alignment = al # set horizontal center

print(len(target_data)) # Look at the length of target_data data, by checking that there are actually 14W rows, so we split into 3 sheets

for i in range(0,50000):
    for j in range(0,len(target_data[i])):
     sheet1.write(i, j, target_data[i][j], style)

for i in range(50000,100000):
    for j in range(0,len(target_data[i])):
     sheet2.write(i-50000, j, target_data[i][j], style)

for i in range(100000,len(target_data)):
    for j in range(0,len(target_data[i])):
     sheet3.write(i-100000, j, target_data[i][j], style)

# Set the width of each column
sheet1.col(0).width=256*15
sheet1.col(1).width=256*15
sheet1.col(2).width=256*15
sheet1.col(3).width=256*15
sheet2.col(0).width=256*15
sheet2.col(1).width=256*15
sheet2.col(2).width=256*15
sheet2.col(3).width=256*15
sheet3.col(0).width=256*15
sheet3.col(1).width=256*15
sheet3.col(2).width=256*15
sheet3.col(3).width=256*15

workbook.save("target_data1220.xls") # 保存到target_data.xls

2. Method 2

Using openpyxl library, the maximum number of rows that can be processed reaches 1048576

# Description: need to write is a two-dimensional list target_data
# Write data to excel table
workbook = openpyxl.Workbook() 
sheet0 = workbook.create_sheet(index=0) # create sheet0
sheet0.column_dimensions['A'].width=15 # set the width of column A
sheet0.column_dimensions['B'].width=22 # Set the width of column B
sheet0.column_dimensions['C'].width=15 # Set the width of column C
sheet0.column_dimensions['D'].width=15 # Set the width of column D
# Write data in a loop, centered and aligned
for i in range(len(target_data)):
    for j in range(len(target_data[i])):
        sheet0.cell(i+1,j+1).value = target_data[i][j] # write data
        sheet0.cell(i+1,j+1).alignment = Alignment(horizontal='center', vertical='center') # center alignment
workbook.save('test.xlsx') # Save the file