How to write more than 65536 data in xlwt to xlsx

reason

Today, I used python's xlwt module to process data. For the first time, I encountered the following error

 ValueError: row index was 65536, not allowed by .xls format

I learned that xls only supports 65536 lines of content. If you want to process more data, you cannot use the xlwt module

Solution

1. The second sheet will be assigned as soon as 65536 arrives
2. Use xlsx format and openpyxl extension to implement related functions

How to use openpyxl

Openpyxl supports up to 1048576 rows of single table data, which is very suitable for our project

Install openpyxl

 pip install openpyxl

Using openpyxl

 import openpyxl def readExel(): filename = r'D:\test.xlsx' Inwb=openpyxl. load_workbook (filename) # Read file Sheetnames=inwb. get_sheet_names() # Get all the sheets in the read file by name Ws=inwb. get_sheet_by_name (sheetnames [0]) # Get the first sheet content #Get the maximum number of rows and columns of the sheet rows = ws.max_row cols = ws.max_column for r in range(1,rows): for c in range(1,cols): print(ws.cell(r,c).value) if r==10: break def writeExcel(): Outwb=openpyxl. Workbook() # Open a file to be written Outws=outwb. create_sheet (index=0) # Create a sheet in the file to be written for row in range(1,70000): for col in range(1,4): Outws. cell (row, col). value=row * 2 # Write file print(row) saveExcel = "D:\\test2.xlsx" Outwb. save (saveExcel) # Remember to save

Then I know how to use it

reference resources

1. [Resolved] ValueError: row index was 65536, not allowed by .xls format
2. Python xlwt Write to Excel