Shulou(Shulou.com)05/31 Report--

This article mainly introduces Python how to use the openpyxl module to operate Excel related knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that after reading this Python how to use the openpyxl module to operate Excel article will have a harvest, let's take a look at it.

Before the official start, the module is still installed, which can be achieved by using the following command:

Pip install openpyxl

The official description of the library is:

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

A library for reading and writing Excel 2010 xlsx/xlsm files.

1. Open an existing Excel file

Once you have the library, try to open a local Excel directly before you can control the data in it.

From openpyxl import load_workbookwb = load_workbook ('test form .xlsx') # output #

Before proceeding with coding, you still need to know some relevant knowledge points about Excel, as follows:

In Excel, the column names are letters and the line numbers are numbers, for example, locking the first cell is A1, then A2, then A3

There are three concepts of workbook, worksheet and cell in Excel. Corresponding to openpyxl are Workbook class, Worksheet class and Cell class.

The operation of Excel basically follows a three-step strategy:

Open Excel and generate a Workbook object

Get the table object, that is, Worksheet object

Manipulate the cell object in the table, that is, the Cell object.

With these concepts, you will find that the subsequent code will become very easy to understand.

two。 Create an Excel file and modify sheet

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

Import openpyxlif _ _ name__ = ='_ main__': wb = openpyxl.Workbook () ws = wb.active # change the first sheet name ws.title = "test" # Save wb.save ('example.xlsx')

The following file will be generated in the running directory of the Python file, just open and follow the first sheet.

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

Import openpyxlif _ _ name__ = ='_ main__': wb = openpyxl.Workbook () ws = wb.active # change the name of the first sheet page ws.title = "Sheet1" # add a second sheet page ws2 = wb.create_sheet ("Sheet2") # add a third sheet page # define the second parameter in the create_sheet () function to 0 Indicates that the sheet saves the wb.save ('example.xlsx') at the first ws3 = wb.create_sheet ("Sheet3", 0) #

The function used to append sheet is create_sheet (), which can be set sequentially according to the second parameter.

If you want to set the sheet tab in more detail, you can configure the properties, such as modifying the background of the sheet tab.

Import openpyxlif _ _ 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 page ws2 = wb.create_sheet ("Sheet2") # add a third sheet page # define the second parameter in the create_sheet () function to 0 Indicates that the sheet saves the wb.save ('example.xlsx') at the first ws3 = wb.create_sheet ("Sheet3", 0) #

3. Different ways to choose sheet

Using wb ["Sheet1"], you can do sheet reading through class dictionary mode, and you can also use the get_sheet_by_name () method to make calls, but this method has been deprecated, and it is recommended that you master the first usage.

Import openpyxlif _ _ name__ = ='_ main__': wb = openpyxl.Workbook () ws = wb.active # change the first sheet page name ws.title = "Sheet1" ws_1 = wb ["Sheet1"] ws_1_1 = wb.get_sheet_by_name ("Sheet1") # this method will be deprecated It is recommended to call the print (ws_1) print (ws_1_1) # wb.save ('example.xlsx') 4.Worksheet object in the above form

The Worksheet object is the table object, which is used to get the data in the cell and modify the contents of the table.

The relevant attributes of priority learning are as follows:

Title: the title of the table

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: gets the Cell object by row and returns the generator

Columns: gets the cell (Cell object) by column and returns the generator.

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

From openpyxl import load_workbookwb = 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) # the size of the area with data Numerous 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)

In addition to the properties, the methods of the Worksheet object are as follows: the Worksheet method is as follows:

Iter_rows: get all cells by row, whose values can call the properties of Cell (min_row, max_row, min_col, max_col)

Iter_columns: get all cells by column

Append: add data at the end of the table

Merged_cells: merging multiple cells

Unmerged_cells: removing merged cells

The properties and methods of the Worksheet object basically return the Cell object, that is, the cell object.

5.Cell object

The Cell object is relatively simple, and the common properties are as follows:

Row: the row in which the cell is located

Column: the column in which the cell sits

Value: value of the cell

Coordinate: the coordinates of the cell

From openpyxl import load_workbookwb = load_workbook ('test form .xlsx') if _ _ name__ ='_ _ main__': ws = wb.active for item in ws.rows: print (item) # output the value of the cell by line print (item [0] .value) #

After the operation modifies the cell data to get the cell, you can manipulate the values in it, such as the following code

From openpyxl import load_workbookwb = load_workbook ('test form .xlsx') if _ _ name__ = ='_ main__': ws = wb.active # output pre-modified for item in ws.rows: print (item) # output the value of the cell item [0] .value = 1 # according to the line output item (item [0] .value) # output the modified value, be careful not to save the file The data will not store for item in ws.rows: print (item [0] .value) # output cell value # wb.save ('example.xlsx')

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

Use determinant positioning cells to navigate to cells using the worksheet's cell () method, as shown below

From openpyxl import load_workbookwb = load_workbook ('test form .xlsx') if _ _ name__ = ='_ main__': ws = wb.active # output pre-modified for item in ws.rows: print (item) # output the value of the cell item [0] .value = 1 # according to the line output item (item [0] .value) # output the modified value, be careful not to save the file The data will not be stored in for item in ws.rows: print (item [0] .value) # output cell value # directly locate cell c = ws.cell (row=2, column=3) print (c) print ("the content in cell A2JI B3 is", c.value) wb.save ('example.xlsx') 6. The cell inserts the image

Using the following code, you can add a picture to a cell in Excel. Before formally coding, import the following modules

From openpyxl.drawing.image import Image

Then you can add the corresponding picture to the corresponding cell, the specific code is shown below, the more important of which is the add_image () method.

# create an image. The parameter is image path img = Image ('cup_PNG2001.png') # add_image (picture, the location of the cell to be added) ws.add_image (img,' A2') wb.save ('example.xlsx') 7. Set the cell style

In Excel, we can manipulate cell styles, such as bold, tilt, and background color.

Related classes that need to be imported in advance:

From openpyxl.styles import Font, colors, Alignment

Next, you can modify the appropriate font:

From openpyxl import load_workbookfrom openpyxl.styles import Font, colors, Alignmentwb = 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')

The running results are shown below, and notice the changes related to the A1 cell. It is important to note here that as long as there is an error in the code, the new style will not work.

You can expand other content by yourself, such as setting alignment

Ws ['B1'] .alignment = Alignment (horizontal='center', vertical='center')

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

This is the end of the article on "how Python uses the openpyxl module to operate Excel". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how Python operates Excel with openpyxl module". If you want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.