[Python3] Read/write EXCEL-OPENPYXL library

What is OPENPYXL

OPENPYXL is a third-party pythonexcel read-write library that supports Excel2010 XLSX/XLSM/XLTX/XLTM file formats.

What capabilities does OPENPYXL offer?

• Basic reading and writing ability of Excel

• Seamless linking capability with Pandas and NumPy

• Chart Management in Excel

• Excel Cell Annotation Management

What do we mainly share in this section?

Mainly share OPENPYXL Excel reading and writing function. Provides a common code instance.

For charts, annotation management, Pandas and NumPy integrated use is not covered in this section, interested friends can study on their own.

How to install OPENPYXL

Use the following command to install the OPENPYXL library

Pip Install OPENPYXL

Import OPENPYXL Module

To read and write to Excel using OPENPYXL, you need to import the workbook class, as follows:

 from Import Workbook

A simple example

Let's start with a simple example of how to write Excel using OPENPYXL.

#-*-coding:utf-8-*-__author__='Gubei' fromOpenpyxlImportWorkbookif __name__=="__main__":        Print("Simple example of writing Excel")        #Create an Excel workspaceWB =Workbook ()#Activate the current workbookWS =wb.active#writes data to cell A1, and other cells write similarXst'A1'] ="Blog Park Gubei"    #write a row of data, and the list element corresponds to each cellWs.append ([1, 2, 3])            #write the time type to Excel, Python automatically converts the type to the date-time type of Excel    Importdatetime ws['A2'] =Datetime.datetime.now ()#Save As Excel fileWb.save ("simple Excel Write example. xlsx")

Reading an existing Excel file

The following example generates a "simple Excel write Sample. xlsx" file that reads and outputs the contents of the console.

#-*-coding:utf-8-*-__author__='Gubei' fromOpenpyxlImportLoad_workbookif __name__=="__main__":        Print("reading an existing Excel file") WB= Load_workbook ("simple Excel Write example. xlsx")            #gets all sheet names, returns the list typeSheets =wb.get_sheet_names ()Print(Type (sheets))#traverse sheets and read its cell contents printout     forShinchSheets:Print("Read Workbook name:", SH)#gets the sheet to readWS =Wb.get_sheet_by_name (sheets[0])#Read Sheet A1, A2, B2, C2 cell contents    #read the value of cell A1A1 = ws['A1'].valuePrint("A1 value of cell:", A1)#read A2, B2, C2     forIndexinch('A2','B2','C2'):                Print(Index,"the value of the cell:", Ws[index].value)#read null cell, OPENPYXL for null cell, return noneF1 = ws['F1'].valuePrint("F1 value of cell:", F1)

Advanced examples

Here you use Urllib to crawl data from the network, write to Excel for a sample demonstration, crawl some book data from Douban, and write to Excel.

#-*-coding:utf-8-*-__author__='Gubei'Importurllib.request fromOpenpyxlImportWorkbookif __name__=="__main__":        Print("crawling Douban Book data to Excel sample")            #Search for Python keywords and collect book data through the Douban search API    #This example captures only the first page of dataURL ="Https://api.douban.com/v2/book/search?q=python"Response=urllib.request.urlopen (URL)#decodes a bytes data stream into a stringEbook_str =Response.read (). Decode ()#convert string to DictEbook_dict =eval (ebook_str)#Building a Workbook objectWB = Workbook ()#Activate the first sheetWS = Wb.active#write to table headerWs.append (["title","author","Description","Publishing House","Price"])            #Write book Information     forBookinchebook_dict["Books"]: Ws.append ([book["title"],             ",". Join (book["author"]), book["Summary"], book["Publisher"], book[" Price"]])            #SaveWb.save ("ebook.xlsx")

Open ebook.xlsx to view the crawl results as follows:

[Python3] Read/write EXCEL-OPENPYXL library