Python3 Read and write to an Excel xlsx file using OPENPYXL

Source: Internet
Author: User


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.