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