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
Read More:
- How to Solve Python Xlwt ValueError: More than 4094 XFs (styles)
- [Solved] AttributeError WriteOnlyWorksheet object has no attribute cell
- python2.7 ExcelWriter error Exception caught in workbook destructor. Explicit close() may be require
- Python Openyxl Error: AttributeError: ‘int‘ object has no attribute ‘upper‘ [How to Solve]
- Python openpyxl excel open zipfile error resolution: zipfile.BadZipFile: File is not a zip file
- error: (-5:Bad argument) in function ‘seamlessClone‘ and error: (-215:Assertion failed) 0 <= roi.x && 0 [How to Solve]
- Opencv: How to Draw Palette
- Python reads excel and stores it in the list according to the header hash copy.deepcopy
- Panda error in modifying line name index does not support mutable operations
- Httprunner Run Error: while parsing a block mapping
- Error:output with shape [1, 224, 224] doesn‘t match the broadcast shape [3, 224, 224]
- RuntimeError: stack expects each tensor to be equal size [How to Solve]
- [example multitasking] Python multithreading module
- Parallel processing in Python (Pool.map(), Pool.starmap(), Pool.apply ())
- Tensor for argument #2 ‘mat1‘ is on CPU, but expected it to be on GPU (while checking arguments for
- How to Solve RuntimeError: Expected all tensors to be on the same device, but found at least two devices, cpu
- Set the maximum number of Postgres connections Error [How to Solve]
- Python synthesizes multiple pictures into one video
- Python RuntimeError: Expected 4-dimensional input for 4-dimensional weight [32, 1, 5, 5]
- Pyexcel Error: xlrd.biffh.XLRDError: Excel xlsx file; not supported