Python笔记 之 xlrd模块

本文是对官方说明文档的简单摘抄防止自己遗忘，简单介绍使用xlrd模块操作Excel表格的方法。
官方说明文档
xlwt模块简单例子

使用pip安装xlwt模块

$ pip install xlrd

简单的例子

import xlrd
workbook = xlrd.open_workbook(r'F:\Pythonfiles\pythonprogram\NewPaper.xls',encoding_override='utf-8',on_demand=False)
# 显示文件字符集
print(workbook.encoding)
# 显示文件的页面数和页面名列表
print(workbook.nsheets,workbook.sheet_names())
# 根据页索引获取sheet页
worksheet = workbook.sheet_by_index(0)
# 根据页名称获取sheet页
worksheet = workbook.sheet_by_name('Paper1')
# 判断指定的sheet页是否加载成功，成功返回True，失败返回False
# 按页索引
print(workbook.sheet_loaded(0))
# 按名称
print(workbook.sheet_loaded('Paper1'))
print(worksheet.nrows,worksheet.ncols)
for row in worksheet.get_rows():
    for i in range(worksheet.ncols):
        print(row[i].ctype,row[i].value)

输出结果

utf-8
1 ['Paper1']
True
True
26 4
1 姓名：
1 班级：
1 用时：
1 评分：
1  (   ) +  1  =  2  
1   2  +  0  =     
1   3  +  4  =     
1  (   ) -  8  =  1  

属性方法摘抄

import xlrd,xlrd.formatting,xlrd.formula
# 打开文件
workbook = xlrd.open_workbook(r'F:\Pythonfiles\pythonprogram\NewPaper.xls',encoding_override='utf-8',on_demand=False)
# 文件最后一次被保存的日期系统，0=微软，1=苹果
print(workbook.datemode)
# 显示二进制交换文件格式版本
print(workbook.biff_version)
# 显示文件字符集的整数代码
print(workbook.codepage)
# 显示文件字符集
print(workbook.encoding)
# 显示区域代码元祖
print(workbook.countries)
# 如果有的话，显示最后一个保存文件的用户名
print(workbook.user_name)
# 显示字符集列表
print(workbook.font_list)
# 显示格式对象的列表
print(workbook.format_list)
# 显示格式对象的字典
print(workbook.format_map)
# 提取XLS图像为连续字符串的时间
print(workbook.load_time_stage_1)
# 解析连续字符串中的数据所需的时间
print(workbook.load_time_stage_2)
# 加载文件所有sheet页
workbook.sheets()
# 文件的sheet页数量和sheet名列表
print(workbook.nsheets,workbook.sheet_names())
# 根据页索引获取sheet页
worksheet = workbook.sheet_by_index(0)
# 根据页名称获取sheet页
worksheet = workbook.sheet_by_name('Paper1')
# 获取文件所有sheet页
worksheets = workbook.sheets()
# 判断指定的sheet页是否加载成功，成功返回True，失败返回False
# 按页索引
workbook.sheet_loaded(0)
# 按名称
workbook.sheet_loaded('Paper1')
# 取消指定sheet页的加载，返回None
# 按页索引
#workbook.unload_sheet(0)
# 按名称
#workbook.unload_sheet('Paper1')
# 释放文件，返回None
#workbook.release_resources()
# 文本类型设置
style = xlrd.formatting.Font
# 字符是否为粗体，0=否，1=是
print(style.bold)
# 返回字符集代码
print(style.character_set)
'''0 = ANSI Latin 1 = System default 2 = Symbol, 77 = Apple Roman, 128 = ANSI Japanese Shift-JIS,
 129 = ANSI Korean (Hangul), 130 = ANSI Korean (Johab), 134 = ANSI Chinese Simplified GBK, 
 136 = ANSI Chinese Traditional BIG5, 161 = ANSI Greek, 162 = ANSI Turkish, 163 = ANSI Vietnamese,
  177 = ANSI Hebrew, 178 = ANSI Arabic, 186 = ANSI Baltic, 204 = ANSI Cyrillic, 222 = ANSI Thai, 
  238 = ANSI Latin II (Central European), 255 = OEM Latin I'''
#  返回字体颜色的16进制编码
print(style.colour_index)
# 字体转义类型
print(style.escapement)
# 字体族谱
print(style.family)
'''0 = None (unknown or don’t care)
 1 = Roman (variable width, serifed) 
 2 = Swiss (variable width, sans-serifed) 
 3 = Modern (fixed width, serifed or sans-serifed) 
 4 = Script (cursive) 
 5 = Decorative (specialised, for example Old English, Fraktur)'''
#
print(style.font_index)
# 字体大小，单位20
print(style.height)
# 是否斜体,0=否，1=是
print(style.italic)
# 字体名称
print(style.name)
# 下划线类型，0 = None 1 = Single; 0x21 (33) = Single accounting 2 = Double; 0x22 (34) = Double accounting
print(style.underline_type)
# 是否带下滑线，0=否，1=是
print(style.underlined)
# 字体分量，（100-1000），正常字体400,加粗700
print(style.weight)
# 字体轮廓，0=否，1=4,仅使用苹果
print(style.outline)
# 字体阴影，0=否，1=4,仅使用苹果
print(style.shadow)
# excel数据格式
xlrd.formatting.Format(format_key=0, ty=1, format_str='D/MM/YYYY')
'''FUN = 0 # unknown
FDT = 1 # date
FNU = 2 # number
FGE = 3 # general
FTX = 4 # text'''
# excel表格边框类型
xlrd.formatting.XFBorder
'''0 = No line,
1 = Thin,
2 = Medium,
3 = Dashed,
4 = Dotted,
5 = Thick,
6 = Double,
7 = Hair,
8 = Medium dashed,
9 = Thin dash-dotted,
10 = Medium dash-dotted,
11 = Thin dash-dot-dotted,
12 = Medium dash-dot-dotted,
13 = Slanted medium dash-dotted.'''
# 单元格顶部边框的颜色索引
xlrd.formatting.XFBorder.top_colour_index
xlrd.formatting.XFBorder.bottom_colour_index
xlrd.formatting.XFBorder.left_colour_index
xlrd.formatting.XFBorder.right_colour_index= 0
# 对角线颜色索引
xlrd.formatting.XFBorder.diag_colour_index= 0
# 单元格顶部边框线条样式
xlrd.formatting.XFBorder.top_line_style= 0
xlrd.formatting.XFBorder.bottom_line_style= 0
xlrd.formatting.XFBorder.left_line_style= 0
xlrd.formatting.XFBorder.right_line_style= 0
# 单元格对角线线条样式
xlrd.formatting.XFBorder.diag_line_style= 0
# 单元格从左上至右下的对角线
xlrd.formatting.XFBorder.diag_down= 1
# 单元格从右下至左上的对角线
xlrd.formatting.XFBorder.diag_up= 1
# 单元格背景设置
# 是否全填充
xlrd.formatting.XFBackground.fill_pattern
xlrd.formatting.XFBackground.background_colour_index
xlrd.formatting.XFBackground.pattern_colour_index
# 单元格对齐方式
xlrd.formatting.XFAlignment.hor_align
xlrd.formatting.XFAlignment.vert_align
# 旋转方式
xlrd.formatting.XFAlignment.rotation
# 自动换行设置，0=否，1=是
xlrd.formatting.XFAlignment.text_wrapped
# 缩进设置（0-15）
xlrd.formatting.XFAlignment.indent_level
# 字体自适应,0=否，1=是
xlrd.formatting.XFAlignment.shrink_to_fit
#文本方向 0 = according to context; 1 = left-to-right; 2 = right-to-left
xlrd.formatting.XFAlignment.text_direction
#单元格保护 1=禁止更改、移动、调整单元格大小或删除单元格
xlrd.formatting.XFProtection.cell_locked
# 是否隐藏公式 0=否，1=是
xlrd.formatting.XFProtection.formula_hidden
# 扩展格式
# 0 = cell XF, 1 = style XF
print(xlrd.formatting.XF.is_style)
#
xlrd.formatting.XF.parent_style_index
xlrd.formatting.XF.xf_index
xlrd.formatting.XF.font_index
xlrd.formatting.XF.format_key
xlrd.formatting.XF.protection
xlrd.formatting.XF.background
xlrd.formatting.XF.alignment
xlrd.formatting.XF.border
# MSexcel公式模块
# 运算对象
xlrd.formula.Operand.value
xlrd.formula.Operand.kind
xlrd.formula.Operand.text

xlrd.formula.cellname(rowx=0,colx=0)
xlrd.formula.cellnameabs(rowx=0,colx=0,r1c1=0)
# 工作表
# 返回给定列中单元格对象的序列
xlrd.sheet.Sheet.col
# 单元格左侧的列数
xlrd.sheet.Sheet.vert_split_pos
# 单元格上方的行数
xlrd.sheet.Sheet.horz_split_pos
# 第一个可见行索引
xlrd.sheet.Sheet.horz_split_first_visible
# 第一个可见列索引
xlrd.sheet.Sheet.vert_split_first_visible
# 引用页面的文件对象
xlrd.sheet.Sheet.book
# 页面的名称
xlrd.sheet.Sheet.name
# 页面的行数
xlrd.sheet.Sheet.nrows
# 页面的列数
xlrd.sheet.Sheet.ncols
# 默认列宽度
xlrd.sheet.Sheet.defcolwidth
# 标准宽度
xlrd.sheet.Sheet.standardwidth
# 默认行高
xlrd.sheet.Sheet.default_row_height
xlrd.sheet.Sheet.default_row_height_mismatch
xlrd.sheet.Sheet.default_row_hidden
xlrd.sheet.Sheet.default_additional_space_above
xlrd.sheet.Sheet.default_additional_space_below
# 指定单元格对象
xlrd.sheet.Sheet.cell
# 指定单元格值
xlrd.sheet.Sheet.cell_value
# 指定单元格值类型
xlrd.sheet.Sheet.cell_type
# 指定单元格扩展格式索引
xlrd.sheet.Sheet.cell_xf_index
# 指定行的有效单元格数量
xlrd.sheet.Sheet.row_len
# 指定行序列
xlrd.sheet.Sheet.row
# 页面行纪录迭代
xlrd.sheet.Sheet.get_rows()
# 返回指定行的指定列范围的类型向量
xlrd.sheet.Sheet.row_types(rowx=0, start_colx=0, end_colx=None)
# 返回指定行指定列范围的值向量
xlrd.sheet.Sheet.row_values(rowx=0, start_colx=0, end_colx=None)
# 返回指定行指定列范围的单元格对象向量
xlrd.sheet.Sheet.row_slice(rowx=0, start_colx=0, end_colx=None)
# 返回指定列指定行范围的单元格对象向量
xlrd.sheet.Sheet.col_slice(colx=0, start_rowx=0, end_rowx=None)
# 返回指定列指定行范围的单元格值向量
xlrd.sheet.Sheet.col_values(colx=0, start_rowx=0, end_rowx=None)
# 返回指定列指定行范围的单元格类型向量
xlrd.sheet.Sheet.col_types(colx=0, start_rowx=0, end_rowx=None)
# 标注对象
xlrd.sheet.Note
# 标识的作者
xlrd.sheet.Note.author
# 列隐藏属性
xlrd.sheet.Note.col_hidden
# 列索引
xlrd.sheet.Note.colx
# 行隐藏属性
xlrd.sheet.Note.row_hidden
# 行索引
xlrd.sheet.Note.rowx
# 显示属性
xlrd.sheet.Note.show
# 标识的内容
xlrd.sheet.Note.text
# 单元格对象
xlrd.sheet.Cell
'''
Type symbol	Type number	Python value
XL_CELL_EMPTY	0	empty string ''
XL_CELL_TEXT	1	a Unicode string
XL_CELL_NUMBER	2	float
XL_CELL_DATE	3	float
XL_CELL_BOOLEAN	4	int; 1 means TRUE, 0 means FALSE
XL_CELL_ERROR	5	int representing internal Excel codes; for a text representation, refer to the supplied dictionary error_text_from_code
XL_CELL_BLANK	6	empty string ''. Note: this type will appear only when open_workbook(..., formatting_info=True) is used.'''