Implementation of Python using Openpyxl to operate Excel files

openpyxl is an excellent library for reading and writing Excel files in Python.
It supports .xlsx and .xlsm formats, which are ideal for Excel data processing and automation on Ubuntu systems.
This blog will introduce some commonly used openpyxl commands.

Install

Install Python3

First, you need to install openpyxl and Python. On Ubuntu, you can install it with the following command:

sudo apt install python3

Install openpyxl

pip3 install openpyxl

Basic Operation

1. Introduce

from openpyxl import Workbook # Introduce new filesfrom openpyxl import load_workbook # Load the import of existing files

2. Create workbooks and worksheets

wb = Workbook() # Create a new workbookws =  # Get the activity worksheet
wb.create_sheet("Sheet2") # Create a new worksheet and name it = "NewSheet" # Modify the worksheet name

3. Write data

ws['A1'] = 'Hello, World!'
ws['B1'] = 42
ws['C1'] = 3.14
cell = (row=1, column=1, value="New Value")

4. Save the workbook

('')

5. Load the existing Excel

wb = load_workbook('')
ws =

6. Read the value of the cell

cell_a1 = ws['A1'].value
cell_b1 = ws['B1'].value
cell_c1 = ws['C1'].value

print(f"A1: {cell_a1}, B1: {cell_b1}, C1: {cell_c1}")

7. Select a worksheet

ws1 = [0] # Select a worksheet by indexws2 = wb["Sheet2"] # Select a worksheet by name

Style and formatting

1. Introduce

from  import Font, Alignment # Introduction of style operations

2. Set fonts

font = Font(name='Arial', size=14, bold=True, italic=False)
ws['A1'].font = font

3. Set borders

border = Border(left=Side(border_style='thin'),
                right=Side(border_style='thin'),
                top=Side(border_style='thin'),
                bottom=Side(border_style='thin'))
ws['A1'].border = border

4. Fill

fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
ws['A1'].fill = fill

5. Set the number format

ws['B1'].number_format = '0.00%'   # Percentage Formatws['C1'].number_format = 'YYYY-MM-DD'  # Date format

6. Data verification

from  import DataValidation

dv = DataValidation(type="list", formula1='"Apple,Banana,Cherry"', allow_blank=True) # Create a data verification object
ws.add_data_validation(dv) 
('A1') # Apply data verification to cells

7. Formula operation

You can insert formulas into cells:

ws['D1'] = "=SUM(A1:A3)"

Performance optimization

1. read_only/write_only

For large Excel files, read_only can be used to improve read efficiency, write_only can be used to optimize write performance

from openpyxl import load_workbook

wb = load_workbook('', read_only=True) # Load workbooks using read-only mode

from openpyxl import Workbook

wb = Workbook(write_only=True) # Create a workbook using write modews = wb.create_sheet()

2. Traverse the cell

You can use .rows and .columns to traverse rows and columns in a worksheet:

for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in row:
        print(, end=" ")

This is the end of this article about the implementation of Python using Openpyxl to operate Excel files. For more related Python Openpyxl to operate Excel content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!