Detailed explanation of the installation and processing of Excel tables in xlrd package in Python

1. Install xlrd

Address

After downloading, use pip install .whl to install it.

View help:

>>> import xlrd
>>> help(xlrd)
Help on package xlrd:

NAME 
 xlrd

PACKAGE CONTENTS 
 biffh
 book
 compdoc
 formatting
 formula
 info
 licences
 sheet
 timemachine
 xldate
 xlsx

FUNCTIONS 
 count_records(filename, outfile=<idlelib.PyShell.PseudoOutputFile object at 0x0287E730>)

 dump(filename, outfile=<idlelib.PyShell.PseudoOutputFile object at 0x0287E730>, unnumbered=False)

 open_workbook(filename=None, logfile=<idlelib.PyShell.PseudoOutputFile object at 0x0287E730>, verbosity=0, use_mmap=1, file_contents=None, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False)

DATA 
 FMLA_TYPE_ARRAY = 4
 FMLA_TYPE_CELL = 1
 FMLA_TYPE_COND_FMT = 8
 FMLA_TYPE_DATA_VAL = 16
 FMLA_TYPE_NAME = 32
 FMLA_TYPE_SHARED = 2
 MMAP_AVAILABLE = 1
 USE_MMAP = 1
 XL_CELL_BLANK = 6
 XL_CELL_BOOLEAN = 4
 XL_CELL_DATE = 3
 XL_CELL_EMPTY = 0
 XL_CELL_ERROR = 5
 XL_CELL_NUMBER = 2
 XL_CELL_TEXT = 1
 __VERSION__ = &#39;1.0.0&#39;
 biff_text_from_num = {0: &#39;(not BIFF)&#39;, 20: &#39;2.0&#39;, 21: &#39;2.1&#39;, 30: &#39;3&#39;, ...
 empty_cell = empty:&#39;&#39;
 error_text_from_code = {0: &#39;#NULL!&#39;, 7: &#39;#p/0!&#39;, 15: &#39;#VALUE!&#39;, 23: ...
 oBOOL = 3
 oERR = 4
 oNUM = 2
 oREF = -1
 oREL = -2
 oSTRG = 1
 oUNK = 0
 okind_dict = {-2: &#39;oREL&#39;, -1: &#39;oREF&#39;, 0: &#39;oUNK&#39;, 1: &#39;oSTRG&#39;, 2: &#39;oNUM&#39;...

FILE 
 c:\python34\lib\site-packages\xlrd\__init__.py

You can view the help information of xlrd through the above method, which contains the xlrd package Some modules and some member variables, constants, and functions.

2. Python processing Excel table

1. Open the Excel table

import xlrd

# 获取一个Book对象
book = xlrd.open_workbook("1.xls")

# 获取一个sheet对象的列表
sheets = book.sheets()

# 遍历每一个sheet，输出这个sheet的名字（如果是新建的一个xls表，可能是sheet1、sheet2、sheet3）
for sheet in sheets: 
 print(sheet.name)

This function appears in the above help message: open_workbook() , open the workbook, which opens the Excel table.

What is returned is a Book object. Through the Book object we can get a list of Sheets. The above program simply outputs the name of each sheet.

2. Read the data in the specified cell

import xlrd

# 获取一个Book对象
book = xlrd.open_workbook("1.xls")

# 获取一个sheet对象的列表
sheets = book.sheets()

# 遍历每一个sheet，输出这个sheet的名字（如果是新建的一个xls表，可能是sheet1、sheet2、sheet3）
for sheet in sheets: 
 print(sheet.cell_value(0, 0))

Read the data in the cell Function cell_value(row, col) , both rows and columns start from 0.

In addition, you can pass:

sheet.cell(row, col) # 获取单元格对象 
sheet.cell_type(row, col) # 获取单元格类型

3. Read date data

If a certain cell data stored in Excel is a date, it needs to be processed and converted to the datetime type

from datetime import datetime 
from xlrd import xldate_as_tuple

# 获取一个Book对象
book = xlrd.open_workbook("1.xls")

# 获取一个sheet对象的列表
sheets = book.sheets()

timeVal = sheets[0].cell_value(0,0)

timestamp = datetime(*xldate_as_tuple(timestamp, 0))

print(timestamp)

4. Traverse the data of each row

rows = sheet.get_rows() 
for row in rows: 
 print(row[0].value) # 输出此行第一列的数据

More detailed explanations in python For articles related to the installation and processing of Excel tables of the xlrd package, please pay attention to the PHP Chinese website!