SoFunction
Updated on 2025-03-04

Python's operation guide for using OpenPyXL library to operate Excel tables

introduction

In modern offices, Excel forms are undoubtedly a good assistant for processing data, generating reports, and analyzing information. Whether it is financial statistics, data sorting or business analysis, Excel plays an indispensable role. However, manually handling Excel tables is time-consuming and error-prone. Is there a way to automate these tedious operations? The answer is yes! Among the many libraries in Python, OpenPyXL is a powerful tool for operating Excel files. It can easily help us realize automated office work and greatly improve data processing efficiency!

In this article, we will cover the basic features of the OpenPyXL library and how to use it to efficiently operate Excel files.

1. Install OpenPyXL

Before you start, you need to install the OpenPyXL library. You can use the pip command to install:

pip install openpyxl

Once the installation is complete, you can start processing Excel tables using OpenPyXL.

2. Basic functions of OpenPyXL

OpenPyXL allows us to use Excel files (to.xlsxis a suffix) performs a variety of operations, including creating, reading, editing, and saving worksheets. Here are some of its main features:

  1. Create and load workbooks
  2. Operation worksheet
  3. Cell read and write
  4. Operation format
  5. Save Modify

Let's learn more about how to use these features through the sample code.

1. Create and load workbooks

In OpenPyXL,Workbook(Workbook) is the body of an Excel file and contains multiple worksheets (Worksheets). First, let's look at how to create a new workbook and load an existing Excel file.

from openpyxl import Workbook, load_workbook

# Create a new workbookwb = Workbook()
ws =   # Get the default worksheet
# Load an existing workbookwb = load_workbook("")
ws =

Here, we can choose to open an existing file or create a new file.

2. Operation worksheet

In Excel, a worksheet is a table used to store data. We can create, rename, or delete worksheets:

# Create a new worksheetws_new = wb.create_sheet("NewSheet")

# Rename the worksheet = "MainSheet"

# Delete worksheets(ws_new)

Creating and deleting worksheets can help us manage data partitions flexibly and keep files clear and orderly.

3. Read and write operations of cells

OpenPyXL provides a simple interface to manipulate cell data. We can read or write data through the coordinates of the cells:

# Write dataws["A1"] = "Hello, OpenPyXL!"

# Read datadata = ws["A1"].value
print(data)  # Output: Hello, OpenPyXL!
# Use row and column index access(row=2, column=1, value="This is row 2, column 1")
print((row=2, column=1).value)  # Output: This is row 2, column 1

As you can see, OpenPyXL allows us to read and write cells through coordinates and indexes. This allows easy modification, addition or deletion of data.

4. Operation format: Change style and format

Format is one of the important features of Excel tables. In OpenPyXL, we can set fonts, colors, borders, etc. for cells to achieve a more beautiful display effect:

from  import Font, Color, Alignment

# Set fontsws["A1"].font = Font(name="Arial", size=12, bold=True, color="FF0000")  # Red bold
# Set the alignmentws["A1"].alignment = Alignment(horizontal="center", vertical="center")

# Set cell colorfrom  import PatternFill
ws["A1"].fill = PatternFill("solid", fgColor="FFFF00")  # Yellow fill

Through the above code, we can customize the cell format and create clearer and more professional reports.

5. Save and modify

After you complete all the operations, remember to save the workbook:

("modified_example.xlsx")

This will save all modifications to the specified file.

3. Comprehensive example: batch filling of data

Here is a complete sample code for batch-filling data into an Excel file and adding formatting to specific cells. This example simulates a simple data table filling process.

from openpyxl import Workbook
from  import Font, PatternFill

# Create new workbooks and worksheetswb = Workbook()
ws = 
 = "Sales Report"

# Add a title line(["Product", "Region", "Sales"])
ws["A1"].font = Font(bold=True, color="FFFFFF")
ws["B1"].font = Font(bold=True, color="FFFFFF")
ws["C1"].font = Font(bold=True, color="FFFFFF")

# Set the title background colorws["A1"].fill = PatternFill("solid", fgColor="4F81BD")
ws["B1"].fill = PatternFill("solid", fgColor="4F81BD")
ws["C1"].fill = PatternFill("solid", fgColor="4F81BD")

# Fill in datadata = [
    ["Widget", "North", 1200],
    ["Widget", "South", 900],
    ["Gadget", "North", 500],
    ["Gadget", "South", 300],
]

for row in data:
    (row)

# Save the workbook("sales_report.xlsx")
print("Sales report created and saved as sales_report.xlsx")

Example description

In this example, we create a sales report with the product name, sales area, and sales. passappendMethods add data in batches and format the title rows to make the table more beautiful. After all the filling is completed, save the file.

4. Advantages and application scenarios of OpenPyXL

OpenPyXL is a powerful and easy-to-use Python library that is especially suitable for automated processing of Excel files. Compared to manual operation of Excel, OpenPyXL provides many advantages:

  • Process data in batches: Can process large amounts of data quickly and save time in manual operation.
  • Custom format: You can automatically add formats to the data to generate reports that meet company standards.
  • Highly automated: Suitable for regular generation of file reports, which can be completed through scripts to avoid repeated labor.

Application scenarios

  • Financial statement generation: Automatically generate and format financial statements and sales reports.
  • Data processing and cleaning: Process data in batches and generate sorted data tables.
  • Statistical analysis report: Combining Python data analysis libraries (such as Pandas) and OpenPyXL, the analysis results are written directly to an Excel file.

OpenPyXL not only brings simple data writing and reading functions, but also a comprehensive Excel processing tool. It not only allows us to automate the processing of large quantities of data, but also provides us with flexible formatting and customization functions, suitable for a variety of office scenarios. Let's learn more about the features below, such asFormula application, data verificationandChart creation, help you gain a more comprehensive understanding of the power of OpenPyXL.

5. Advanced functions

1. Use formulas

Excel's formula functionality is very powerful in data calculation and analysis. In OpenPyXL, we can also use formulas directly in cells, which allows Excel files to automatically calculate the results when they are opened.

from openpyxl import Workbook

# Create workbooks and worksheetswb = Workbook()
ws = 

# Fill in dataws["A1"] = "Value 1"
ws["A2"] = 10
ws["B1"] = "Value 2"
ws["B2"] = 20

# Add formulaws["C1"] = "Total"
ws["C2"] = "=A2+B2"

# Save the file("formula_example.xlsx")

In this example,C2The formula is inserted into the cell=A2+B2, when you open Excel after saving the file,C2It will be displayed automaticallyA2andB2and  . This allows you to set up dynamic calculations flexibly, making reports more practical.

2. Data verification

Data verification is a feature in Excel that limits input. Use OpenPyXL to add data validation rules to specific cells, such as limiting input ranges, specifying radio-choice options, etc. Here is an example of setting the input of a cell to a specific option through data validation:

from openpyxl import Workbook
from  import DataValidation

# Create workbooks and worksheetswb = Workbook()
ws = 

# Create data verification rules: drop-down menudv = DataValidation(type="list", formula1='"Option1,Option2,Option3"', showDropDown=True)
ws.add_data_validation(dv)

# Apply validation to cell range(ws["A1"])

# Save the file("data_validation_example.xlsx")

After opening in an Excel file,A1A drop-down menu will be displayed in the cell, and the user can only select "Option1", "Option2" or "Option3". This feature is very useful when collecting normalized data.

3. Create a chart

Excel's charting capabilities can visually display the trends and changes of data. You can use the built-in one in OpenPyXLchartThe module generates common charts such as line charts, bar charts and pie charts. Here is an example of creating a bar chart:

from openpyxl import Workbook
from  import BarChart, Reference

# Create workbooks and worksheetswb = Workbook()
ws = 

# Fill in datadata = [
    ["Product", "Sales"],
    ["Widget", 1200],
    ["Gadget", 900],
    ["Doohickey", 700],
]
for row in data:
    (row)

# Create a bar chartchart = BarChart()
 = "Sales Data"
chart.x_axis.title = "Product"
chart.y_axis.title = "Sales"

# Set the data rangedata_ref = Reference(ws, min_col=2, min_row=2, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)

# Add a chart to the worksheetws.add_chart(chart, "E5")

# Save the file("chart_example.xlsx")

In this example, we create a bar chart and insert it into the worksheet.add_chart()The method displays the chart at the specified position ("E5" cell position). After saving the file, open Excel and see the generated chart. This feature is ideal for automatically generating data visual reports.

4. Merge and split cells

In Excel, in order to beautify tables or summarize data, we often need to merge or split cells. OpenPyXL also supports this feature:

from openpyxl import Workbook

# Create workbooks and worksheetswb = Workbook()
ws = 

# Merge cellsws.merge_cells("A1:D1")
ws["A1"] = "Merged Cells Example"

# Split cellsws.unmerge_cells("A1:D1")

# Save the file("merge_cells_example.xlsx")

In this example, we first putA1:D1Merge the range of cells into one cell and enter the text content. If there is no need to merge in the future, you can use itunmerge_cells()Restore it to a separate cell.

5. Adjust row height and column width

We can also adjust the row height and column width in Excel to make the table content clearer and more neat:

# Set column widthws.column_dimensions["A"].width = 20

# Set the row heightws.row_dimensions[1].height = 30

passcolumn_dimensionsandrow_dimensionsThe size of columns and rows can be controlled separately to make the table look more beautiful.

6. Examples of automated office applications

Here is a comprehensive application sample code showing how to generate an automated sales report with OpenPyXL, including formatting, formulas, data validation, and charts:

from openpyxl import Workbook
from  import Font, PatternFill
from  import BarChart, Reference
from  import DataValidation

# Create workbooks and worksheetswb = Workbook()
ws = 
 = "Monthly Sales Report"

# Add a title line and format it(["Product", "Region", "Sales"])
for cell in ws[1]:
     = Font(bold=True)
     = PatternFill("solid", fgColor="ADD8E6")  # light blue background
# Add data and data verificationproducts = ["Widget", "Gadget", "Doohickey"]
dv = DataValidation(type="list", formula1=f'"{",".join(products)}"', showDropDown=True)
ws.add_data_validation(dv)

data = [
    ["Widget", "North", 1200],
    ["Widget", "South", 900],
    ["Gadget", "North", 700],
    ["Doohickey", "South", 300],
]

for row in data:
    (row)
    (ws[f"A{ws.max_row}"])

# Add formula: sumws["D1"] = "Total Sales"
ws["D2"] = "=SUM(C2:C5)"

# Create a bar chartchart = BarChart()
 = "Sales by Product"
chart.x_axis.title = "Product"
chart.y_axis.title = "Sales"
data_ref = Reference(ws, min_col=3, min_row=2, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
ws.add_chart(chart, "F5")

# Save the file("automated_sales_report.xlsx")
print("Automated sales report created successfully!")

Example Analysis

This sample code shows how to generate a sales report form using OpenPyXL:

  • Title line formatting: Set the font and background color of the table title row to make the table clearer.
  • Data Verification: Data verification is applied in the product column, so that only the specified product can be selected when entering data.
  • Formula calculation: Automatically calculate the sum of sales data and display it inD2Cell.
  • Chart generation: Generate a bar chart to show the sales distribution of different products.

Through such an automatically generated Excel file, data statistics and display can be quickly completed, greatly improving office efficiency.

7. Summary and Outlook

OpenPyXL is a very practical Python library that can greatly improve our automation level when processing Excel tables. Whether it is simple data entry, formatting, or complex formula calculation and chart generation, it can be easily implemented. With OpenPyXL, you can quickly generate professional reports that meet your company's needs, saving time while ensuring data is accurate.

The above is the detailed information of the Python operation guide for using the OpenPyXL library to operate Excel tables. For more information about Python OpenPyXL library to operate Excel, please pay attention to my other related articles!