Basic operation of openpyxl (office automation)

Time:2020-10-31

Introduction to openpyxl

Openpyxl is a third-party library, which can process Excel files in xlsx format, that is, Excel files in Excel 2003 or above.

(the 2003 version can use xlrd and xlwt libraries to read, but I personally suggest that the XLS file should be handled with pandas. )

The first step is to install openpyxl

Enter PIP install openpyxl – I in CMDhttps://pypi.doubanio.com/simple

(here, the author uses Douban source. The download speed is much faster than that without changing the domestic source. If the domestic source has been configured, you can directly input the previous part of – I)

-Creating files

from openpyxl import Workbook


#Method 1: the default table name is sheet
wb = Workbook()
ws = wb.active  #Create a new sheet

#Method 2: this method can customize the table name and insertion position. If you enter the location, the default value is 0
ws = wb.create_sheet('Shee1', 0)

-Open the file

from openpyxl import load_workbook

wb = load_workbook('xxx.xlsx')

-Get table name

#Get the names of all the sheets and get a list of all the sheet names
sheet_names = wb.sheetnames

-Selection table

#Method one reads the selected sheet in Excel directly
ws = wb.active

#Method 2 enter the name of the sheet by yourself
ws = wb['Sheet']

-Storing data

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

#The first is row and column
ws.cell(row=1, column=2).value = 1
#It can be directly simplified as
ws.cell(1, 2).value = 1

#The second method
ws['B1'].value = 1

#Add one row of data at a time
ws.append([1, 2, 3, '4'])
#Note that any number added here should be enclosed in brackets

#Finally, remember to save the data
wb.save('file name. Xlsx')

-Read data

from openpyxl import load_workbook

#Here test.xlsx  You can create a file in the same directory of Py file and fill in the data
wb = load_workbook('test.xlsx')
ws = wb.active

#Read a cell method one
print(ws['A1'].value)

#Read a cell method two
print(ws.cell(1, 1).value)

#Read the value of the cell and the value of the storage cell is similar, one is to read out the value, the other is to assign value

-Gets the maximum number of rows and columns of the sheet table

from openpyxl import load_workbook

wb = load_workbook('test.xlsx')
ws = wb.active

#Get the maximum number of rows
max_r = ws.max_row
#Gets the maximum number of columns
max_c = ws.max_column

-Column letters and coordinate numbers are converted to each other

from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl import Workbook


wb = Workbook()
ws = wb.active

#Returns letters based on the number of columns
print(get_column_letter(3))  # C
#Returns the number of columns based on letters
print(column_index_from_string('D'))  # 4

-Traversing cells

from openpyxl import load_workbook

wb = load_workbook('test.xlsx')
ws = wb.active

#Attention
#Openpyxl reads the index of Excel from 1
#Because the range function is left closed and right open, and the index starts from 1, the maximum value must be + 1
for i in range(1, ws.max_row+1):
    for j in range(1, ws.max_column+1):
        print(ws.cell(i, j).value)

The next article will write about some advanced operations of openpyxl, such as formatting cells, colors, and so on

Like the article can pay attention to me, if you think the article is useful to you, please point a praise and collection

Basic operation of openpyxl (office automation)

This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint