Python3 Read and write to an Excel xlsx file using OPENPYXL

Python handles Excel already has a large number of packages, mainstream representatives are:

Xlwings: Simple and powerful alternative to VBA

OPENPYXL: Easy to use and versatile

Pandas: Use needs to combine with other libraries, data processing is the pandas of the body

Win32com: It's not just Excel that can handle office, but it's a package of Windows COM, which is a bit of a pain for novices to use.

? Xlsxwriter: A variety of features, the disadvantage is that you cannot open/modify existing files, meaning that the use of xlsxwriter needs to start from scratch.

? Datanitro: embedded in Excel as a plugin, instead of VBA, uses Python gracefully in Excel

Xlutils: Combined with XLRD/XLWT, the old Python package, it should be noted that you have to install the three libraries at the same time

1.OPENPYXL Use

OPENPYXL is a library that Python can use to work with xlsx!

2.OPENPYXL installation

Pip Install OPENPYXL

3. tips for use

(1) Open Excel file, get worksheet

Import OPENPYXL

Wb=openpyxl.load_workbook (' ttt.xlsx ') #打开excel文件

Print (Wb.get_sheet_names ()) #获取工作簿所有工作表名

Sheet=wb.get_sheet_by_name (' Sheet1 ') #获取工作表

Print (Sheet.title)

Sheet02=wb.get_active_sheet () #获取活动的工作表

Print (Sheet02.title)

(2) Operation cell

Print (sheet[' A1 '].value) #获取单元格A1值

Print (sheet[' A1 '].column) #获取单元格列值

Print (sheet[' A1 '].row) #获取单元格行号

Print (Sheet.cell (row=1,column=1). Value) #获取单元格A1值, column and row are still available

For I in Range (1,4,1):

Print (Sheet.cell (row=i,column=1). Value) #更加方便实用

Print (Sheet.max_column) #获取最大列数

Print (Sheet.max_row) #获取最大行数

(3) Read Excel file

#wbname = = File name, sheetname== sheet name, can be empty, default to first worksheet

def READWB (wbname,sheetname):

Wb=openpyxl.load_workbook (Filename=wbname,read_only=true)

if (sheetname== ""):

Ws=wb.active

Else

Ws=wb[sheetname]

Data=[]

For row in Ws.rows:

List=[]

For cell in row:

Aa=str (Cell.value)

if (aa== ""):

Aa= "1"

List.append (AA)

Data.append (list)

Print (Wbname + "-" +sheetname+ "-read successfully)

Return data

(4) New Excel , and write Data

#新建excel

def CREATWB (wbname):

Wb=openpyxl. Workbook ()

Wb.save (Filename=wbname)

Print ("New Excel:" +wbname+ "Success")

# Write date data in Excel file, date is List data type, fields table header

def savetoexcel (data,fields,sheetname,wbname):

Print ("Write to Excel:")

Wb=openpyxl.load_workbook (Filename=wbname)

Sheet=wb.active

Sheet.title=sheetname

Field=1

For field in range (1,len (fields) +1): # Write to table header

_=sheet.cell (Row=1,column=field,value=str (fields[field-1]))

Row1=1

Col1=0

For Row1 in range (2,len (data) +2): # Write Data

For col1 in range (1,len (Data[row1-2]) +1):

_=sheet.cell (Row=row1,column=col1,value=str (Data[row1-2][col1-1]))

Wb.save (Filename=wbname)

Print ("Saved successfully")

Python3 Read and write to an Excel xlsx file using OPENPYXL