Python_Openpyxl module compilation collection

Python_Openpyxl Module Collection#

1. Installation#

pip install openpyxl

2. Open File#

① Create#

from openpyxl import Workbook # Instantiate
wb = Workbook() # Activate worksheet
ws = wb.active

② Open Existing#

from openpyxl import load_workbook 

wb2 = load_workbook('file_name.xlsx')

3. Store Data#

# Method 1: Data can be directly assigned to cells (can input formulas)
ws['A1'] = 42
# Method 2: Rows can be appended, starting from the first column (from the bottom blank space, starting from the left) (can input multiple rows)
ws.append([1, 2, 3]) 
# Method 3: Python types will be automatically converted
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")

4. Create Sheet#

# Method 1: Insert at the end (default)
ws1 = wb.create_sheet("Mysheet") 
# Method 2: Insert at the beginning
ws2 = wb.create_sheet("Mysheet", 0)

5. Select Sheet#

# Sheet name can be used as key for indexing
ws3 = wb["New Title"] 
ws4 = wb.get_sheet_by_name("New Title") 
ws is ws3 is ws4
# Result
True

6. View Sheet Names#

# Display all sheet names
>>> print(wb.sheetnames)
['Sheet2', 'New Title',  'Sheet1'] # Traverse all sheets
>>> for sheet in wb:
... print(sheet.title)

7. Access Cells (call)#

① Accessing a Single Cell#

# Method 1
>>> c = ws['A4']
# Method 2: row row; column column
>>> d = ws.cell(row=4, column=2, value=10)
# Method 3: Create whenever accessed
>>> for i in range(1,101):
...         for j in range(1,101):
...            ws.cell(row=i, column=j)

② Accessing Multiple Cells#

# Using slicing
>>> cell_range = ws['A1':'C2']
# Using rows (columns)
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]
# Using specified range (row → row)
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...    for cell in row:
...        print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>　
# Using specified range (column → column)
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...    for cell in row:
...        print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
# Traverse all cells Method 1
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
...
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
# Traverse all cells Method 2
>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
...
<Cell Sheet.C8>,
<Cell Sheet.C9>))

8. Save Data#

>>> wb.save('file_name.xlsx')

9. Inserting and Deleting Rows and Columns#

# 1-① Insert 1 row before the second row
load_ws.insert_rows(2,1)
load_wb.save('test.xlsx')
# 1-② Delete 1 row starting from the second row
load_ws.delete_rows(2,1)
load_wb.save('test.xlsx')
# 2-① Insert 2 columns before the second column
load_ws.insert_cols(2,2)
load_wb.save('test.xlsx')
# 2-② Delete 1 column starting from the second column
load_ws.delete_cols(2,1)
load_wb.save('test.xlsx')
# Get the total number of rows (with data)
load_wb.save('data/test.xlsx')
rows = load_ws.max_row
cols = load_ws.max_column
print('The table has a total of: '+str(rows)+' rows')
print('The table has a total of: '+str(cols)+' columns')
exit()

10. Others#

① Change sheet tab button color#

ws.sheet_properties.tabColor = "1072BA"

② Get the maximum row and maximum column#

# Get the maximum row and maximum column
print(sheet.max_row) print(sheet.max_column)

③ Get each row and each column#

sheet.rows is a generator, with each row's data wrapped in a tuple.
sheet.columns is similar, but each tuple contains the cells of each column.

# Because it is by row, it returns A1, B1, C1 in this order
for row in sheet.rows:
    for cell in row:
        print(cell.value)

# A1, A2, A3 in this order
for column in sheet.columns:
    for cell in column:
        print(cell.value)

④ Get the letter based on the number, and get the number based on the letter (Important for projects)#

from openpyxl.utils import get_column_letter, column_index_from_string

# Get the letter based on the column number
print(get_column_letter(2))  # B
# Get the column number based on the letter
print(column_index_from_string('D'))  # 4

⑤ Delete a worksheet#

# Method 1
wb.remove(sheet) # Method 2
del wb[sheet]

⑥ Matrix Transposition (row → column)#

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10]]

list(zip(*rows))

# out
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]

# Note that the method will discard columns (rows) with missing data
rows = [
    ['Number', 'data1', 'data2'],
    [2, 40      ],    # Missing one data here
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]
# out
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]

10. Set Cell Styles#

① Classes to import#

from openpyxl.styles import Font, colors, Alignment

② Font#

The following code specifies ' 等线 24pt', 'bold italic', and 'red' font color. Assign the Font object to the cell's font attribute.

bold_italic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)

sheet['A1'].font = bold_italic_24_font

③ Alignment#

Also directly use the cell's alignment attribute, here specifying vertical and horizontal center alignment. Besides center, other parameters like right, left can also be used.

# Set B1 to be vertically and horizontally centered
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

④ Set Row Height and Column Width#

# Set the height of the second row
sheet.row_dimensions[2].height = 40
# Set the width of column C
sheet.column_dimensions['C'].width = 30

⑤ Merge and Unmerge Cells#

Merging cells means covering other cells with the cell in the top left corner of the merged range, making it a larger cell.
Conversely, unmerging cells returns the value of the merged cell to the original top left position.

# Merge cells, write data to the top left corner
sheet.merge_cells('B1:G1') # Merge several cells in a row
sheet.merge_cells('A1:C3') # Merge cells in a rectangular range

After merging, data can only be written to the top left corner, which is the coordinate to the left of the colon in the range.
If all the cells to be merged have data, only the data in the top left corner will be retained, and the others will be discarded. In other words, if data is not written to the top left corner before merging, there will be no data in the merged cell.
The following is the code to unmerge cells. After unmerging, the value returns to position A1.

sheet.unmerge_cells('A1:C3')

Finally, an example#

import datetime
from random import choice
from time import time
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# Set file name
addr = "openpyxl.xlsx"
# Open file
wb = load_workbook(addr)
# Create a new sheet
ws = wb.create_sheet()
# Input the first row
ws.append(['TIME', 'TITLE', 'A-Z'])

# Input content (500 rows of data)
for i in range(500):
    TIME = datetime.datetime.now().strftime("%H:%M:%S")
    TITLE = str(time())
    A_Z = get_column_letter(choice(range(1, 50)))
    ws.append([TIME, TITLE, A_Z])

# Get the maximum row
row_max = ws.max_row
# Get the maximum column
con_max = ws.max_column
# Print the written content on the console
for j in ws.rows:    # we.rows gets each row of data
    for n in j:
        print(n.value, end="\t")   # n.value gets the value of the cell
    print()
# Save (must write the file name (absolute address), default in the same directory as the .py file, only supports xlsx format)
wb.save(addr)