Python read Excel (XLRD)

First, install the XLRD module:

1. Open the Terminal input command under Mac:

Pip Install Xlrd

2. Verify the installation is successful:

• Enter python into the Python environment at the Mac Terminal
• Then enter import xlrd

No error description module installation is successful

Second, the commonly used methods:

1. Import Module:

Import xlrd

2. Open File:

X1 = Xlrd.open_workbook ("Data.xlsx")

3. Get sheet:

• Get all sheet names: X1.sheet_names ()
• Number of sheet obtained: 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 Filep ath# 1, open file X1 = Xlrd.open_workbook (filePath) # 2, Get Sheet object print ' Sheet_names: ', X1.sheet_names ()  # Get all sheet name print ' Sheet_number: ', x1.nsheets        # get sheet number print ' Sheet_object: ', X1.sheets ()       # Get all Sheet object print ' By_name: ', X1.sheet_by_name ("test")  # Find print ' By_index by sheet name: ', X1.sheet_by_index (3)  # Find by index

Output:

Sheet_names: [u ' plan ', U ' team building ', U ' modile ', u ' Test ']sheet_number:4sheet_object: [<xlrd.sheet.sheet Object at 0x10244c190>, <xlrd.sheet.sheet object at 0x10244c150>, <xlrd.sheet.sheet object at 0x10244c110>, < Xlrd.sheet.Sheet object at 0x10244c290>]by_name: <xlrd.sheet.sheet object at 0x10244c290>by_index: < Xlrd.sheet.Sheet Object at 0x10244c290>

4. Get aggregated data for sheet:

• Get sheet Name: Sheet1.name
• Get total number of rows: sheet1.nrows
• Get 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 File X1 = Xlrd.open_workbook (filePath) # Gets the summary data for sheet Sheet1 = x1.sheet_by_name (" Plan ") print" sheet name: ", sheet1.name   # Get sheet nameprint" row num: ", sheet1.nrows  # Get sheet All rows Numberp Rint "Col num:", sheet1.ncols  # Get sheet All columns number

Output:

Sheet Name:planrow Num:31col num:11

5, Cell Bulk read: A) line operation:

• Sheet1.row_values (0) # gets all the contents of the first row, merges the cells, the first row displays the values, and the others are empty.
• Sheet1.row (0) # Get cell value types and contents
• 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 bulk Read Print Sheet1.row_ VALUES (0)  # gets all the contents of the first row, merges the cells, the first row displays the values, and the others are empty. Print Sheet1.row (0)         # Get cell value type and content print sheet1.row_types (0)   # get cell data type

Output:

[u ' Learning Plan ', U ', U ', U ', U ', U ', U ', U ', 123.0, 42916.0, 0] [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 (' B ', [1, 0, 0, 0, 0, 0, 0, 0, 2, 3, 4])

b) Table operation

• Sheet1.row_values (0, 6, 10) # Take line 1th, column 6th to 10th (not including table 10th)
• Sheet1.col_values (0, 0, 5) # Take the 1th column, line No. 0 to 5th (excluding line 5th)
• Sheet1.row_slice (2, 0, 2) # Get cell value types and contents
• Sheet1.row_types (1, 0, 2) # Get 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 Action print sheet1.row_values (0, 6, ten)   # Take line 1th, column 6th to 10th (excluding 10th table) print sheet1.col_values (0, 0, 5)    # Take 1th column, section 0~ 5 lines (excluding line 5th) print Sheet1.row_slice (2, 0, 2)     # Gets the cell value type and contents, same as Sheet1.row (0) Print sheet1.row_types (1, 0, 2)     # Get cell data type

Output:

[U ', U ', 123.0, 42916.0] [u ' Learning Plan ', U ' \u7f16\u53f7 ', 1.0, 2.0, 3.0] [number:1.0, Text:u ' \u7ba1\u7406\u5b66\u4e60 ']array (' B ', [1, 1])

6. Specific cell reads: a) Gets 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 reads # VALUE print Sheet1.cell _value (1, 2) print Sheet1.cell (1, 2). Valueprint Sheet1.row (1) [2].value# Fetch type print Sheet1.cell (1, 2). Ctypeprint Sheet1.cell_type (1, 2) print Sheet1.row (1) [2].ctype

7, (0,0) conversion A1:

• Xlrd.cellname (0, 0) # (0,0) converted to A1
• Xlrd.cellnameabs (0, 0) # (0,0) converted to $a$1
• Xlrd.colname (30) # Converts a column from a number to a letter representation

#-*-Coding:utf-8-*-import xlrdimport osfilename = "demo.xlsx" FilePath = Os.path.join (OS.GETCWD (), filename) # Open File X1 = X Lrd.open_workbook (FilePath) Sheet1 = x1.sheet_by_name ("plan") # (0,0) converted to A1print xlrd.cellname (0, 0)   # (0,0) Convert to A1print xlrd.cellnameabs (0, 0) # (0,0) Convert to $a$1print xlrd.colname ($)  # Converts a column from a number to a letter representation

Output:

A1$a$1ae

8. Data type:

• Empty: 0
• String: 1
• Numbers: 2
• Date: 3
• BOOL: 4
• Error:5

Python read Excel (XLRD)