Python openPyXL module

“Offer comes, ask friends to take it! I am participating in the 2022 Spring Recruit Punch card campaign. Click here for more details.”

This is the third blog post about Python operating Excel. The core module to learn is OpenPyXL, another very good wheel.

Before the formal start is still the module installation, use the following command to achieve

pip install openpyxl
Copy the code

The official description of the library is:

A Python library to read/write Excel 2010 xlsx/xlsm files

Excel 2010 XLSX/XLSM library for reading and writing Excel 2010 XLSX/XLSM files.

Open an existing Excel file

Once you have the library, try opening a native Excel, and then control the data in it.

from openpyxl  import load_workbook
wb = load_workbook('Test form.xlsx')

# output
# <openpyxl.workbook.workbook.Workbook object at 0x00000195680F96D0>
Copy the code

There are a few things you need to know about Excel before you continue coding, as follows:

In Excel, column names are letters and row numbers are numbers. For example, lock the first cell A1, then A2, then A3;
Excel has three concepts of workbook, worksheet and cell, corresponding toopenpyxlIs in theWorkbookClass,WorksheetClass,CellClass.

Operating Excel basically follows a three-step strategy

Open Excel and generate a Workbook object.
Get the table object, the Worksheet object;
Work on the Cell object in the table, the Cell object.

With these concepts in mind, you’ll find that the rest of the code is much easier to understand.

Create an Excel file and modify the Sheet

The next code will create an Excel file and change the first default sheet to [test]

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active

    Change the name of the first sheet
    ws.title = "Test"

    # save
    wb.save('example.xlsx')
Copy the code

The following file is generated in the Python file’s run directory. Open the first sheet and follow it.

If you want to create a second table page (sheet), just append the sheet to the Workbook object.

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active

    Change the name of the first sheet page
    ws.title = "Sheet1"
    Add a second sheet
    ws2 = wb.create_sheet("Sheet2")

    Add a third sheet
    # Define the second argument in create_sheet() as 0, indicating that the sheet is first
    ws3 = wb.create_sheet("Sheet3".0)
    # save
    wb.save('example.xlsx')
Copy the code

The function used to append sheet is create_sheet(), which can be set in the order of the second argument.

If you want to change the sheet sheet in more detail, you can configure properties, such as changing the sheet sheet background

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active

    Change the name of the first sheet page
    ws.title = "Sheet1"

    ws.sheet_properties.tabColor = "03a9f4"
    Add a second sheet
    ws2 = wb.create_sheet("Sheet2")

    Add a third sheet
    # Define the second argument in create_sheet() as 0, indicating that the sheet is first
    ws3 = wb.create_sheet("Sheet3".0)
    # save
    wb.save('example.xlsx')
Copy the code

Different ways to choose sheet

Sheet can be read using wb[“Sheet1”] in class dictionary mode or called using the get_sheet_by_name() method, which is deprecated.

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active

    Change the name of the first sheet page
    ws.title = "Sheet1"

    ws_1 = wb["Sheet1"]
    ws_1_1 = wb.get_sheet_by_name("Sheet1")  # This method is deprecated and is recommended to be called in the form above

    print(ws_1)
    print(ws_1_1)

    # wb.save('example.xlsx')
Copy the code

Worksheet object

The Worksheet object is a table object that is used to retrieve data in cells and modify the contents of the table. The related attributes of priority learning are as follows:

title: Table title;
dimensions: Table data area size;
max_row: the largest row of the table;
min_row: the smallest row of the table;
max_column: the largest column of the table;
min_column: the smallest column of the table;
rows: Get Cell object by row, return generator;
columns: Retrieves cells (Cell objects) by column, returning generators.

The code is shown below, and the table used is shown in the screenshot below

from openpyxl  import load_workbook
wb = load_workbook('Test form.xlsx')

if __name__ == '__main__':

    ws = wb.active

    Change the name of the first sheet page
    ws.title = "My_Sheet"

    ws_1 = wb["My_Sheet"]

    print(ws_1.title)  # get the title
    print(ws_1.dimensions)  # Size of region with data, no data is A1:A1
    print(ws_1.max_row) # maximum number of rows
    print(ws_1.min_row) Minimum number of rows

    print(ws_1.max_column)
    print(ws_1.min_column)

    print(ws_1.rows)
    print(ws_1.columns)
Copy the code

In addition to attributes, the Worksheet object has the following methods:

iter_rows: Retrieves all cells in a row, whose values can be invoked by Cell properties (min_ROW, max_ROW, min_col, max_col);
iter_columns: Gets all cells by column
append: Adds data to the end of the table
merged_cells: Merges multiple cells
unmerged_cells: Removes the merged cells

The properties and methods of the Worksheet object generally return a Cell object, or Cell object.

The Cell object

The Cell object is simple and has the following properties:

row: The row on which the cell resides

column: The column in which the cell sits
value: Cell value
coordinate: Coordinates of the cell

from openpyxl import load_workbook

wb = load_workbook('Test form.xlsx')

if __name__ == '__main__':

    ws = wb.active

    for item in ws.rows:
        print(item)  Output by line
        print(item[0].value)  Print the value of the cell

Copy the code

Operations Modify cell data Once a cell is retrieved, you can operate on its values, as shown in the following code

from openpyxl import load_workbook

wb = load_workbook('Test form.xlsx')

if __name__ == '__main__':

    ws = wb.active
    # output before modification
    for item in ws.rows:
        print(item)  Output by line
        print(item[0].value)  Print the value of the cell
        item[0].value = 1
    # print the modified value. Note that the file is not saved and the data is not stored
    for item in ws.rows:
        print(item[0].value)  Print the value of the cell
    # wb.save('example.xlsx')
Copy the code

After the cell contents are modified, you need to use the wb.save() method to save the modified contents of the file.

Using determinants to locate cells Using the cell() method of the worksheet, you can locate cells as shown below

from openpyxl import load_workbook

wb = load_workbook('Test form.xlsx')

if __name__ == '__main__':

    ws = wb.active
    # output before modification
    for item in ws.rows:
        print(item)  Output by line
        print(item[0].value)  Print the value of the cell
        item[0].value = 1
    # print the modified value. Note that the file is not saved and the data is not stored
    for item in ws.rows:
        print(item[0].value)  Print the value of the cell

    Locate the cell directly
    c = ws.cell(row=2, column=3)
    print(c)
    print("What's in cells A2 and B3?",c.value)
    wb.save('example.xlsx')
Copy the code

Cells insert images

You can add images to cells in Excel using the following code. Before formal coding, enter the following modules

from openpyxl.drawing.image import Image
Copy the code

The next step is to add an image to the corresponding cell, as shown below, the most important of which is the add_image() method.

 Create an image with the image path
img = Image('cup_PNG2001.png')
# add_image(image, where to add cells)
ws.add_image(img, 'A2')

wb.save('example.xlsx')
Copy the code

Set the cell style

In Excel, we can manipulate cell styles, such as bold, slant, and background colors.

Related classes that need to be imported in advance

from openpyxl.styles import Font, colors, Alignment
Copy the code

You can then modify the font accordingly

from openpyxl import load_workbook
from openpyxl.styles import Font, colors, Alignment

wb = load_workbook('Test form.xlsx')

if __name__ == '__main__':
    ws = wb.active
 
    c_style = Font(size=20, bold=True, italic=True, color='ff0000')

    ws['A1'].font = c_style

    wb.save('example.xlsx')
Copy the code

The running result is as follows, notice the changes related to cell A1. It’s important to note here that as long as there’s one bug in the code, the new style won’t work.

Other things you can expand on yourself, such as setting the alignment

ws['B1'].alignment = Alignment(horizontal='center', vertical='center')
Copy the code

Row height, column width, cell merge and unmerge are up to you.

The more knowledge you can use with the official manual to learn: openpyxl. Readthedocs. IO/en/stable /

Recording time

In 2022, 579/1024 articles were written by Flag. You can follow me, like me, comment on me, favorites me.

Open an existing Excel file

Create an Excel file and modify the Sheet

Different ways to choose sheet

Worksheet object

The Cell object

Cells insert images

Set the cell style

Recording time

Related Posts

What is the difference between semi-automatic ORM and fully automatic ORM frameworks?

Spring annotations @Component, @controller, @service, @repository

Analysis of Spring IOC Container Initialization Principle (Section 3)