Article directory
- Install
- Prerequisite knowledge
- Read Excel
-
- Get the content of a single cell
- Get the contents of multiple cells
- Get all values
- Write to Excel
-
- SaveExcel
- Create new Excel
- Write to cell
- Write one or more rows of data
- Insert one/multiple columns
- Delete multiple rows/columns
- Moving range data
- Style adjustment
-
- Set font style
- Set alignment style
- Set border style
- Set cell fill style
- Set cell row height/column width
- Cell merge/unmerge
Installation
openpyxl is a non-standard library, so you need to install it yourself. The installation process is not difficult. Windows/Mac users can use pip to install it in the command line (CMD)/terminal (Terminal).
pip install openpyxl
Prerequisite knowledge
An Excel workbook is composed of one or more worksheets. A sheet can be regarded as composed of multiple rows or multiple columns, and each row and column is composed of multiple cells. composition!
Read Excel
Note that load_workbook can only open existing Excel and cannot create new workbooks. Workbook is used to create a new workbook
Sample form:
Get the content of a single cell
from openpyxl import load_workbook #Import load_workbook module
workbook = load_workbook(filename='yxy.xlsx') #Open the workbook named 'yxy.xlsx'
print(workbook.sheetnames) #Print all worksheet names
sheet = workbook['Test Project 1'] #Open the worksheet named Test Project 1. If there is only one worksheet, you can also use sheet = workbook.active
print(sheet.dimensions) #Get the range of the worksheet content (upper left cell: lower right cell)
cell_1 = sheet['A1'] #Specify coordinates to obtain cell content
cell_2 = sheet.cell(row=2,column=2) #Specify the number of rows and columns to get the cell content
print('A1=',cell_1) #Output cell information of A1
print('row2column2.value=',cell_2.value) #Output the contents of the cells in the second row and second column
print(cell_2.row, cell_2.column, cell_2.coordinate) #Output the row, column and coordinate of the target cell
The result of executing the above code is as follows
Get the contents of multiple cells
Each column in Excel is identified by a letter, which is a character type; each row is identified by a number, which is an integer type. Of course, the following three methods 1-3 are to obtain a bunch of tables. Now to output the value of each table, you need to traverse:
- Method 1-3: Specify the coordinate range, specify the column, and specify the row
from openpyxl import load_workbook #Import load_workbook module
workbook = load_workbook(filename='yxy.xlsx') #Open the workbook named 'yxy.xlsx'
sheet = workbook['Test Project 1'] #Open the worksheet named Test Project 1. If there is only one worksheet, you can also use sheet = workbook.active)
cells_1 = sheet['A1:B3'] #Cell A1:B3
cells_2 = sheet['A:C'] #Column A-Column C
cells_3 = sheet['4:6'] #row 4-row 6
print('cells1 is arranged line by line')
for row in cells_1: #Read row by row
for cell_1 in row: #Read cells one by one in the row
print(cell_1.value) #Print the value in the cell
print('cells2 is arranged line by line')
for row in cells_2: #Read row by row
for cell_2 in row: #Read cells one by one in the row
print(cell_2.value) #Print the value in the cell
print('cells3 are arranged line by line')
for row in cells_3: #Read row by row
for cell_3 in row: #Read cells one by one in the row
print(cell_3.value) #Print the value in the cell
The result of executing the above code is as follows
Three methods still have their own limitations, if I need a specific range of values and am too lazy to convert to alphanumeric coordinates. For example, I want to get all cells from rows 2 to 5 and columns 1 to 3. Therefore, you must master the fourth method:
- Method 4: Specify a range of values
from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
for row in sheet.iter_rows(
min_row=2,max_row=4,
min_col=2,max_col=3
): #Get the values of rows 2-4 and columns 2-3
for cell in row:
print(cell.value)
Get all values
from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
for row in sheet: #Read row by row
for cell in row: #Read cells one by one in the row
print(cell.value) #Print the value in the cell
Write to Excel
Save Excel
If the paths for reading and writing Excel are the same, the original file will be modified.
If the paths for reading and writing Excel are different, save them as new files.
workbook.save(filename='ChangeName.xlsx')
Create new Excel
from openpyxl import Workbook workbook = Workbook() workbook.save(filename='New.xlsx')
Write to cell
cell = sheet['A1'] cell.value = 'name'
- Write formula
Example:
sheet['K11'] = '=AVERAGE(K1:K10)'
- Write hyperlink
Example:
sheet["A1"].hyperlink = "https://www.example.com" sheet["A1"].style = hyperlink_style
Write one or more rows of data
Will be appended to the existing data.
- Write line by line
from openpyxl import load_workbook workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] data1=['Cavia',30,'Shandong Yantai'] sheet.append(data1) data2=['Kaer',35,'Zhejiang Huzhou'] sheet.append(data2) workbook.save(filename='yxy.xlsx')
- Write multiple lines at once
from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
data=[['Cavia',30,'Shandong Yantai'],
['Kaer',35,'Zhejiang Huzhou']]
for i in data:
sheet.append(i)
workbook.save(filename='yxy.xlsx')
Insert one/multiple columns
The idx column is an index, insert to the left of the idx column
from openpyxl import load_workbook workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] sheet.insert_cols(idx=2,amount=3) #Insert 3 columns before the second column. Where ',amount=3' is omitted to insert a column workbook.save(filename='yxy.xlsx')
Delete multiple rows/columns
idx is the index, delete multiple columns starting from (inclusive) the idx row/column
from openpyxl import load_workbook workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] sheet.delete_cols(idx=2,amount=2) sheet.delete_rows(idx=4,amount=3) workbook.save(filename='yxy.xlsx')
Moving range data
Positive numbers mean down or right, negative numbers mean left or up
from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
sheet.move_range('A7:A8',rows=-4,cols=1)
workbook.save(filename='yxy.xlsx')
Style adjustment
Need to import openpyxl.styles
Set font style
name=Microsoft Yahei’—-Font name
size=12—-font size
bold=True—-Bold
italic=True—-Italic
color=7035BC’——Color
from openpyxl import load_workbook from openpyxl.styles import Font workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] cell =sheet['A1'] font=Font(name='Microsoft Yahei',size=12,bold=True,italic=True,color='7035BC') cell.font = font workbook.save(filename='yxy.xlsx')
The external link image transfer failed. The source site may have an anti-leeching mechanism. It is recommended to save the image and upload it directly.
Set alignment style
horizontal=center’—-Horizontal alignment
vertical=center’—-Vertical alignment
text_rotation=45—-Font tilt
wrap_text=True——Automatic line wrapping
The horizontal alignment options and their meanings are as follows:
distributed: Distributes text evenly across cells to fill the entire width.
justify: Justifies the text on both ends, but does not make additional spacing adjustments.
center: Center align the text.
left: Align text to the left.
fill: Fills the text to the width of the cell, adding extra spaces between the text to fill the empty portions.
centerContinuous: Center aligns text continuously in cells, but if there are merged cells, only applies center alignment to the first cell.
right: Align text to the right.
general: expresses a general format without specific alignment settings.
The vertical alignment options and their meanings are as follows:
bottom: Align the bottom of the text with the bottom of the cell.
distributed: Distributes text evenly across cells to fill the entire height.
justify: Aligns the text top and bottom, but does no additional spacing.
center: Align text vertically in the center.
top: Align the top of the text with the top of the cell.
from openpyxl import load_workbook from openpyxl.styles import Alignment workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] cell =sheet['A1'] alignment=Alignment(horizontal='center',vertical='center',text_rotation=45,wrap_text=True) cell.alignment = alignment workbook.save(filename='yxy.xlsx')
Set border style
style=thin’——Border style
color=89284D’——border color
The edge style options and their meanings are as follows:
double: double-sided line.
mediumDashDotDot: medium dash dot dot.
slantDashDot: slash dotted line.
dashDotDot: dotted line.
dotted: dotted line.
hair: thin edges.
mediumDashed: medium dashed line.
dashed: dashed line.
dashDot: dashed line.
thin: thin line.
mediumDashDot: medium dash dot.
medium: medium line.
thick: thick line.
Border (left, right, upper and lower borders)
from openpyxl import load_workbook from openpyxl.styles import Side,Border workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] cell =sheet['B2'] side = Side(style='thin',color='89284D') border = Border(left=side,right=side,top=side,bottom=side) cell.border =border workbook.save(filename='yxy.xlsx')
Set cell fill style
from openpyxl import load_workbook
from openpyxl.styles import PatternFill,GradientFill
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
#Single color fill
cell1 =sheet['A3']
pattern_fill = PatternFill(fill_type='solid',fgColor='99ccff')
cell1.fill =pattern_fill
#Gradient color fill
cell2 =sheet['B4']
gradient_fill = GradientFill(stop=('FFFFFF','99CCFF','000000'))
cell2.fill =gradient_fill
workbook.save(filename='yxy.xlsx')
Set cell row height/column width
from openpyxl import load_workbook workbook = load_workbook(filename='yxy.xlsx') sheet = workbook['Test Project 1'] sheet.row_dimensions[6].height = 50 sheet.column_dimensions['B'].width = 20 workbook.save(filename='yxy.xlsx')
Cell merge/unmerge
- merge
from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
sheet.merge_cells('A1:B2') #Specify range
sheet.merge_cells(start_row=4, start_column=1,
end_row=6, end_column=2) #Specify the row and column range
workbook.save(filename='yxy.xlsx')
- Unmerge
from openpyxl import load_workbook
workbook = load_workbook(filename='yxy.xlsx')
sheet = workbook['Test Project 1']
sheet.unmerge_cells('A1:B2') #Specify range
sheet.unmerge_cells(start_row=4, start_column=1,
end_row=6, end_column=2) #Specify the row and column range
workbook.save(filename='yxy.xlsx')
