Python operates on Excel's xlsx files
- 2020-05-27 06:14:06
Previously, xlrd/xlwt were used for reading and writing excel, but the disadvantage of these two libraries is that they only work well with xls, not xlsx. Since everyone is using the latest version of office and excel is xlsx, it is not appropriate to continue to use xlrd/xlwt. Fortunately, we can also use openpyxl to read and write xlsx files.
I'm not familiar with excel and I don't usually use it, so the processing of excel is very simple, just simple reading and writing. What is demonstrated here is also simple reading and writing operation. For specific advanced functions, you can refer to the link address after the text.
1: write one excel file as follows
from openpyxl import Workbook from openpyxl.utils import get_column_letter # Create in memory 1 a workbook Object, and it will at least be created 1 a worksheet wb = Workbook() # Gets the currently active worksheet, The default is number one 1 a worksheet ws = wb.active # Set the value of the cell, A1 Is equal to the 6( The test shows openpyxl The rows and columns are numbered from 1 Start counting ) . B1 Is equal to the 7 ws.cell(row=1, column=1).value = 6 ws.cell("B1").value = 7 # From the first 2 Line start, write 9 line 10 Column data whose value is the corresponding column ordinal number A , B , C , D... for row in range(2,11): for col in range (1,11): ws.cell(row=row, column=col).value = get_column_letter(col) # You can use append insert 1 Rows of data ws.append([" I "," you "," she "]) # save wb.save(filename="/Users/budong/Desktop/a.xlsx")
2: read the excel just written as follows
from openpyxl import load_workbook # Open the 1 a workbook wb = load_workbook(filename="/Users/budong/Desktop/a.xlsx") # Gets the currently active worksheet, The default is number one 1 a worksheet #ws = wb.active # You can also use the following method # Get all the tables (worksheet) The name of the sheets = wb.get_sheet_names() # The first 1 The name of the table sheet_first = sheets # Get specific worksheet ws = wb.get_sheet_by_name(sheet_first) # Gets all the rows and columns of the table, both of which are iterable rows = ws.rows columns = ws.columns # Iterate over all rows for row in rows: line = [col.value for col in row] print line # Read the value through the coordinates print ws.cell('A1').value # A The column ,1 Said line print ws.cell(row=1, column=1).value