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