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!
