How to use Python to deal with data in excel Table

This article mainly introduces how to use Python to deal with the data in the excel table, the article is very detailed, has a certain reference value, interested friends must read it!

I. basic and common methods

1. Read excel

1. Import module:

Import xlrd

2. Open the file:

X1 = xlrd.open_workbook ("data.xlsx")

3. Obtain sheet:

Sheet is the name of the worksheet because an excel has more than one worksheet

Get all sheet names: x1.sheet_names ()

Get the number of sheet: x1.nsheets

Get all sheet objects: x1.sheets ()

Find by sheet name: x1.sheet_by_name ("test")

Search by index: x1.sheet_by_index (3)

#-*-coding:utf-8-*-import xlrdimport osfilename = "demo.xlsx" filePath = os.path.join (os.getcwd (), filename) print filePath# 1, open file x1 = xlrd.open_workbook (filePath) # 2, get sheet object print 'sheet_names:', x1.sheet_names () # get all sheet names print' sheet_number:', x1.nsheets # get sheet quantity print 'sheet_object:' X1.sheets () # get all sheet objects print 'By_name:', x1.sheet_by_name ("test") # find print' By_index:', x1.sheet_by_index by sheet name (3) # find by index

Output:

Sheet_names: [u' plan', u'team building', upright modulated, upright test'] sheet_number: 4sheet_object: [,] By_name: By_index:

4. Obtain the summary data of sheet:

Get sheet name: sheet1.name

Get total number of rows: sheet1.nrows

Get the total number of columns: sheet1.ncols

#-*-coding:utf-8-*-import xlrdimport osfrom datetime import date,datetimefilename = "demo.xlsx" filePath = os.path.join (os.getcwd (), filename) print filePath# Open the file x1 = xlrd.open_workbook (filePath) # to get the summary data of sheet sheet1 = x1.sheet_by_name ("plan") print "sheet name:", sheet1.name # get sheet nameprint "row num:", sheet1.nrows # get sheet all rows numberprint "col num:", sheet1.ncols # get sheet all columns number

Output:

Sheet name: plan

Row num: 31

Col num: 11

Data: https://www.yisu.com/article/239873.htm

Https://www.yisu.com/article/187025.htm

Second, improve third, make mistakes

1. Cannot open .xlsx file pandas cannot open .xlsx file, xlrd.biffh.XLRDError: Excel xlsx file; not supported

The installed version is too high, and the lower version supports it.

You can install an older version of xlrd and run it in cmd:

Pip uninstall xlrdpip install xlrd==1.2.0

You can also use openpyxl instead of xlrd to open the .xlsx file:

Df=pandas.read_excel ('data.xlsx',engine='openpyxl') above are all the contents of the article "how to use Python to deal with data in excel tables". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!