Python Module Learning-OPENPYXL

OPENPYXL Module Introduction

The OPENPYXL module is a Python library that reads and writes Excel 2010 documents, and if you want to work with an earlier format Excel document that requires additional libraries, OPENPYXL is a more comprehensive tool that can read and modify Excel documents at the same time. Many other Excel-related projects basically only support reading or writing a feature of Excel.

Installing the OPENPYXL module

OPENPYXL is an open source project where the OPENPYXL module is installed using the following command

PIP3 Install OPENPYXL

OPENPYXL Basic Usage

To work with Excel, you first want to understand the basic concepts of Excel, in Excel The column is named after the title, the row is named after the number, such as the upper left corner of the first cell coordinates A1, the following is A2, the right of B1.

There are three different levels of classes in OPENPYXL, workbook is the abstraction of the workbook, worksheet is the abstraction of the table, cell is the abstraction of the cell, and each class contains many properties and methods.

General scenarios for manipulating Excel:

1. Open or create an Excel need to create a workbook object
2. Getting a table requires creating a Workbook object and then using that object's method to get a Worksheet object
3. If you want to get the data in the table, then get the worksheet object and get the Cell object that represents the cells later

Workbook object

A Workbook object represents an Excel document, so you should create a workbook object before you can manipulate Excel. For creating a new Excel document, make a call to the workbook class directly, and for an existing Excel document, you can read it using the Load_workbook function of the OPENPYXL module, which includes multiple parameters. But only the filename parameter is a required argument. FileName is either a filename or an open file object.

Ps:workbook and Load_workbook are the same, returning a Workbook object.

　　The Workbook object provides many properties and methods, most of which are related to sheet, some of which are as follows:

• Active: Gets the currently active worksheet
• worksheets: Returns all worksheet as a list (table)
• read_only: Determine whether to Read_ Only mode opens the Excel document
• encoding: Gets the character set encoding of the document
• properties: Gets the metadata for the document, such as title, creator, creation date, etc.
• Sheetnames: Gets the table (list) in the workbook

　　Workbook provides the following methods:

• get_sheet_names: Gets the names of all the tables (the new version is not recommended and is available through the Sheetnames property of workbook)
• get_sheet_by_ Name: Get the Worksheet object through the table name (also not recommended in the new version, obtained by worksheet[' table name ')
• get_active_sheet: Get the active table (the new version is recommended for active properties)
• remove_sheet: Delete a table
• create_sheet: Create an empty table
• copy_worksheet: Copy a table within workbook

>>> excel2.get_sheet_names () Warning (from warnings module):  File ' __main__ ', line 1DeprecationWarning: Call to deprecated function get_sheet_names (use wb.sheetnames). [' abc ', ' Def ']>>> excel2.sheetnames[' abc ', ' Def ']>>> excel2.get_sheet_by_name (' def ') Warning (from Warnings module):  File "__main__", line 1deprecationwarning:call to deprecated function get_sheet_by_name (use Wb[sh Eetname]). <worksheet "def" >>>> excel2[' def ']<worksheet "def" >>>> Excel2.get_active_ Sheet () Warning (from warnings module):  File ' __main__ ', line 1deprecationwarning:call to deprecated function get_act Ive_sheet (use of the. Active property). <worksheet "abc" >>>> Excel2.create_sheet (' GHK ') <worksheet "GHK ">

Worksheet Object

With the worksheet object, we can get the properties of the table through this Worksheet object, get the data in the cell, and modify the contents of the table. OPENPYXL provides a very flexible way to access cells and data in a table, and the commonly used worksheet properties are as follows:

• Title: The caption of the table
• Dimensions: The size of the table, where the size refers to the size of the table containing the data, that is: the coordinates of the upper-left corner: the lower-right corner of the coordinates
• Max_row: Maximum row for table
• Min_row: The smallest row of a table
• Max_column: The largest column of a table
• Min_column: The smallest column of a table
• Rows: Getting cells by row (Cell object)-generator
• Columns: Get cell (Cell object) by column-generator
• Freeze_panes: Freeze Panes
• Values: Get table contents by row (data)-builder

Ps:freeze_panes, parameters are especially useful for freezing the top row or the left row when the table is large. For frozen rows, when the user scrolls, it is always visible, a string that can be set to a Cell object or a single-ended coordinate, and the row above the cell and the left column will freeze (the row and column where the cell is located will not be frozen). For example, we want to freeze the first row then set A2 to Freeze_panes, if you want to freeze the first column, Freeze_panes value is B1, if you want to freeze both the first row and the first column, then you need to set B2 to Freeze_panes,freeze_ A panes value of none indicates that no columns are frozen.

　　The common worksheet methods are as follows:

• Iter_rows: Get all cells by row with built-in properties (Min_row,max_row,min_col,max_col)
• Iter_columns: Get all cells by column
• Append: Adding data at the end of a table
• Merged_cells: Merging multiple cells
• Unmerged_cells: Remove merged cells

>>> for row in excel2[' financial '].iter_rows (min_row=2,max_row=4,min_col=2,max_col=4):p rint (Row) (<cell ' abc '). B2>, <cell ' abc '. C2>, <cell ' abc '. d2>) (<cell ' abc '). B3>, <cell ' abc '. C3>, <cell ' abc '. d3>) (<cell ' abc '). B4>, <cell ' abc '. C4>, <cell ' abc '. d4>)

PS: As you can see from the properties and methods of the worksheet object, most of them return a Cell object, a Cell object that represents a cells, and we can use Excel coordinates to get the cell object. You can also use the worksheet cell method to get the cell object.

Cell Object

Cell objects are relatively simple and commonly used in the following properties:

• Row: The line where the cell is located
• Column: Cells sitting in columns
• Value: Cell values
• Coordinate: Cell coordinates

>>> excel2[' abc '].cell (row=1,column=2) coordinate ' B1 ' >>> excel2[' abc '].cell (row=1,column=2). Value ' Test ' >>> excel2[' abc '].cell (row=1,column=2) .row1>>> excel2[' abc '].cell (row=1,column=2). Column ' B '

Several ways to print data in a table

#----------Way 1---------->>> for row in excel2[' abc '].rows:print (*[cell.value for cell in row]) #---------- Mode 2---------->>> for row in excel2[' abc '].values:print (*row)

　　

　

Python Module Learning-OPENPYXL