Shulou(Shulou.com)06/02 Report--

This article mainly introduces the relevant knowledge of how to use openpyxl in the Python operation Excel method, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will gain something after reading this Python operation Excel method. Let's take a look at it.

Python operates the openpyxl of Excel

Openpyxl is a Python library for reading and writing Excel2010 xlsx/xlsm/xltx/xltm type files.

Openpyxl cannot manipulate earlier Excel files in xls format, and other libraries such as xlwings can be used.

Openpyxl is a non-standard library that needs to be installed by itself: pip install openpyxl

Premise

Since we want to operate Excel, we must have a basic understanding of Excel, such as:

An Excel workbook (workbook) consists of one or more worksheets (sheet), a worksheet (sheet) contains multiple rows (row) and columns (column), and each row (row) or column (column) consists of multiple cells (cell).

Create from openpyxl import Workbook# create a workbook object wb = Workbook () # create a sheet page named first [the second parameter can specify the index, that is, the location where the sheet is created] ws = wb.create_sheet ('first') # Save the created workbook as Mytest.xlsxwb.save (' Eric_01.xlsx') # and finally close the file wb.close ()

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

Some attributes are as follows:

Active: get the currently active Worksheet

Worksheets: returns all Worksheet (tables) as a list

Read_only: determines whether to open an Excel document in read_only mode

Encoding: gets the character set encoding of a document

Properties: get the metadata of the document, such as title, creator, creation date, etc.

Sheetnames: get the table (list) in the workbook

Some of the methods are as follows:

Get_sheet_names: get the names of all forms (no longer recommended in the new version, but can be obtained through the sheetnames attribute of Workbook)

Get_sheet_by_name: get the Worksheet object through the table name (not recommended in the new version, via Worksheet ['table name'])

Get_active_sheet: get the active table (in the new version, it is recommended to get it through the active attribute)

Remove_sheet: delete a table

Create_sheet: create an empty table

Copy_worksheet: copying tables within Workbook

Read from openpyxl import load_workbook# openpyxl.load_workbook () Open the existing workbook wb = load_workbook ('Eric.xlsx') # print the worksheet name that exists in the workbook print (wb.sheetnames) # get the worksheet sheet = wb [' sheet1'] # if there is only one worksheet, you can also read the table contents as follows: sheet = wb.active# in the range sheet.dimensions# get cell Capacity # specify coordinates cell = sheet ['A1'] # specify row and column cell = sheet.cell (row=3 Column=4) # print the value of the cell print (cell.value) # get the row, column and coordinate print (cell.row, cell.column) of the cell Cell.coordinate) # get the value of the range cell # specify the coordinate range cells = sheet ['A1purpura C3'] # specify the column range cells = sheet [' ApurC'] # cells = sheet ['A'] # specify the row range cells = sheet [2:5] # cells = sheet [3] # iterate through the value of the cell for cell in cells: print (cell.value) # specific range for row in sheet.iter_rows (min_row = 1 Max_row = 10 min_col = 1, max_col = 26): print (row) # iterate to get the value of the cell for cell in row: print (cell.value) # read all lines for row in sheet.rows: print (row) on the article "how to use openpyxl in the Excel method of Python manipulation" ends here Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to use openpyxl in the Python operation Excel method". 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.