How python uses the xlrd module to read excel

This article mainly introduces how python uses the xlrd module to read excel, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

First, install xlrd module:

1. Open the terminal under mac and enter the command:

Pip install xlrd

2. Verify that the installation is successful:

Enter python at the mac terminal to enter the python environment

Then enter import xlrd

If no error is reported, the module is installed successfully.

2. Common methods: 1. Import module: import xlrd2, open file: x1 = xlrd.open_workbook ("data.xlsx") 3, get sheet:

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: [4sheet_object: [,] By_name: By_index: 4. Get 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: planrow num: 31col num: 115, batch reading of cells:

A) Line operation:

Sheet1.row_values (0) # gets all the contents of the first line, merges the cells, the first line displays the value, and the rest is blank.

Sheet1.row (0) # get cell value type and content

Sheet1.row_types (0) # get cell data type

#-*-coding:utf-8-*-import xlrdimport osfrom datetime import date,datetimefilename = "demo.xlsx" filePath = os.path.join (os.getcwd (), filename) x1 = xlrd.open_workbook (filePath) sheet1 = x1.sheet_by_name ("plan") # Cell batch reads print sheet1.row_values (0) # gets all the contents of the first row, merges the cells, the first line displays the value, and the rest is blank. Print sheet1.row (0) # get cell value type and content print sheet1.row_types (0) # get cell data type

Output:

[u'learning plan', text:u'learning plan', empty:u'', empty:u'', empty:u'', empty:u'', empty:u'', empty:u'', empty:u'', number:123.0, xldate:42916.0, bool:0] array ('Bamboo, [1,0,0,0,0,0) 0,0,2,3,4])

B) Table operation

Sheet1.row_values (0,6,10) # take row 1, column 6-10 (excluding table 10)

Sheet1.col_values (0,0,5) # take the first column, lines 0-5 (excluding line 5)

Sheet1.row_slice (2,0,2) # get cell value type and content

Sheet1.row_types (1,0,2) # get the cell data type

#-*-coding:utf-8-*-import xlrdimport osfrom datetime import date,datetimefilename = "demo.xlsx" filePath = os.path.join (os.getcwd (), filename) print filePath# 1, open file x1 = xlrd.open_workbook (filePath) sheet1 = x1.sheet_by_name ("plan") # column operation print sheet1.row_values (0,6,10) # take line 1 Columns 6-10 (excluding table 10) print sheet1.col_values (0,0,5) # take the first column, rows 0-5 (excluding row 5) print sheet1.row_slice (2,0,2) # get the cell value type and content, and get the cell data type as sheet1.row (0) print sheet1.row_types (1,0,2) #

Output:

[upright reading, 123.0, 42916.0] [u'learning plan', u'\ u7f16\ u53f7 reading, 1.0,2.0,3.0] [number:1.0, text:u'\ u7ba1\ u7406\ u5b66\ u4e60'] array ('breadth, [1,1]) 6, specific cell reading:

A) get the cell value:

Sheet1.cell_value (1,2)

Sheet1.cell (1,2). Value

Sheet1.row (1) [2] .value

B) get the cell type:

Sheet1.cell (1,2). Ctype

Sheet1.cell_type (1,2)

Sheet1.row (1) [2] .ctype

#-*-coding:utf-8-*-import xlrdimport osfrom datetime import date,datetimefilename = "demo.xlsx" filePath = os.path.join (os.getcwd (), filename) x1 = xlrd.open_workbook (filePath) sheet1 = x1.sheet_by_name ("plan") # specific cell read # take value print sheet1.cell_value (1,2) print sheet1.cell (1,2). Valueprint sheet1.row (1) [2] .value # take type print sheet1.cell (1) 2). Ctypeprint sheet1.cell_type (1,2) print sheet1.row (1) [2] .ctype7, (0Power0) conversion A1:

Xlrd.cellname (0penol 0) # (0jin0) is converted to A1

Xlrd.cellnameabs (0penol 0) # (0jin0) is converted to $Aqum1

Xlrd.colname (30) # converts columns from numbers to letters

#-*-coding:utf-8-*-import xlrdimport osfilename = "demo.xlsx" filePath = os.path.join (os.getcwd (), filename) # Open the file x1 = xlrd.open_workbook (filePath) sheet1 = x1.sheet_by_name ("plan") # (0L0) convert to A1print xlrd.cellname (0L0) # (0L0) convert to A1print xlrd.cellnameabs (0 0) # (0Cool 0) convert to $A$1print xlrd.colname (30) # convert columns from numbers to letters

Output:

A1 $A$1AE8, data type:

Null: 0

String: 1

Number: 2

Date: 3

Boolean: 4

Error:5

Attachment: write a class that automatically gets the contents of the excel table

This code has achieved automatic conversion of cell data types, shaping numbers will not be displayed as floating-point numbers, Boolean True or False will be displayed as 1 ~ 0; date and time will be displayed as a series of decimal problems

All the data types and numbers of cells in import xlrdfrom xlrd import xldate_as_tupleimport datetime'''xlrd are output as floating point, the date is output as a series of decimals, and Boolean output is 0 or 1 So we have to do judgment processing in the program and convert it to the data type we want: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error'''class ExcelData (): # initialization method def _ _ init__ (self, data_path Sheetname): # define an attribute receive file path self.data_path = data_path # define an attribute receive worksheet name self.sheetname = sheetname # use the xlrd module to open the excel table to read data self.data = xlrd.open_workbook (self.data_path) # get the worksheet according to the name of the worksheet Content (①) self.table = self.data.sheet_by_name (self.sheetname) # get the contents of the worksheet according to the index of the worksheet (②) # self.table = self.data.sheet_by_name (0) # get all the contents of the first row If 1 in parentheses is the second line This is similar to the list index self.keys = self.table.row_values (0) # get the valid rows of the worksheet self.rowNum = self.table.nrows # get the valid columns of the worksheet self.colNum = self.table.ncols # define a method to read the excel table def readExcel (self): # define an empty column Table datas = [] for i in range (1 Self.rowNum): # define an empty dictionary sheet_data = {} for j in range (self.colNum): # get cell data type c_type = self.table.cell (iLJ). Ctype # get cell data c_cell = self.table.cell_value (I J) if c_type = = 2 and c_cell% 1 = 0: # if it is shaping c_cell = int (c_cell) elif c_type = = 3: # convert to datetime object date = datetime.datetime (* xldate_as_tuple (c_cell) 0) c_cell = date.strftime ('% Y/%d/%m% HGV% M15% S') elif c_type = = 4: c_cell = True if c_cell = = 1 else False sheet_ data [self.keys [j]] = c_cell # Loop each valid cell The field corresponding to the value is stored in the dictionary # the key in the dictionary is the field in the first row of each column in the excel table # sheet_ data [self.keys [j]] = self.table.row_values (I) [j] # then append the dictionary to the list datas.append (sheet_data) # return to get the Data: return return datasif _ _ name__ = = "_ _ main__": data_path = "ttt.xlsx" sheetname = "Sheet1" get_data = ExcelData in the form of list storage dictionary (data_path Sheetname) datas = get_data.readExcel () print (datas) Thank you for reading this article carefully I hope the article "how to use the xlrd module to read excel in python" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!