一，xlrd的使用
xlrd介绍：xlrd是python环境下对excel中的数据进行读取的一个模板，可以进行的操作有：
读取有效单元格的行数、列数
读取指定行（列）的所有单元格的值
读取指定单元格的值
读取指定单元格的数据类型
1.文件打开和获取
import xlrd #导入模块
data = xlrd.open_workbook('excelFiles.xls') #打开一个excel
table = data.sheets()[0] #通过索引顺序获取
table = data.sheet_by_index(0) #通过索引顺序获取
table = data.sheet_by_name(u'Sheet1') #通过名称获取
# 以上三个函数都会返回一个xlrd.sheet.Sheet()对象
names = data.sheet_names() #返回book中所有工作表的名字
data.sheet_loaded(sheet_name or indx) # 检查某个sheet是否导入完毕
2.获取整行和整列的值（数组）
1） 对行进行操作：
nrows = table.nrows #获取该sheet中的有效行数
table.row(rowx) #返回由该行中所有的单元格对象组成的列表
table.row_slice(rowx) #返回由该列中所有的单元格对象组成的列表
table.row_types(rowx, start_colx=0, end_colx=None) #返回由该行中所有单元格的数据类型组成的列表
table.row_values(rowx, start_colx=0, end_colx=None) #返回由该行中所有单元格的数据组成的列表
table.row_len(rowx) #返回该列的有效单元格长度
2） 对列进行操作：
ncols = table.ncols#获取列表的有效列数
table.col(colx, start_rowx=0, end_rowx=None)#返回由该列中所有的单元格对象组成的列表
table.col_slice(colx, start_rowx=0, end_rowx=None)#返回由该列中所有的单元格对象组成的列表
table.col_types(colx, start_rowx=0, end_rowx=None)#返回由该列中所有单元格的数据类型组成的列表
table.col_values(colx, start_rowx=0, end_rowx=None)#返回由该列中所有单元格的数据组成的列表
3） 对单元格进行操作：
table.cell(rowx, colx) # 返回单元格对象
table.cell_type(rowx, colx) # 返回单元格中的数据类型
table.cell_value(rowx,colx) #返回单元格中的数据
table.row_value(i)
table.col_value(i)
4）其它
获取行数和列
nrows = table.nrows
ncols = table.ncols
循环行列表行数
nrows = table.nrows
ncols = table.ncols
循环行列表数据
for i in range(nrows):
print table.row_value(i)
5）单元格
cell_A1 = table.cell(0,0).value
cell_C4 = table.cell(2,3).value
3.示例
示例一 读取excel中的内容
import xdrlib
import xlrd
workbook = xlrd.open_workbook(u'交易所证券余额核对20170725.xls') #打开同级目录下的excel
sheet_names = workbook.sheet_names() #获取列名
for sheet_name in sheet_names:
sheet2 = workbook.sheet_by_name(sheet_name) #打印sheet页名称
print(sheet_name)
rows = sheet2.row_values(3) #打印第四行，从0行开始计数
cols = sheet2.col_values(1) #打印第二列，从0列开始计数
print(rows)
print(cols)
执行效果
============ RESTART: D:python_test20170801_python_test est.py ============
交易所证券余额核对20170725
['26369', 'B883579073', '112493', '16华能资', 100000.0, '', 100000.0, '200(年金产品)', '上交所A']
['股东', 'B882699434', 'B280822031', 'B388579073', 'B883336226', '']
示例二：
import xlrd
from xlutils.copy import copy
workbook = xlrd.open_workbook(u'交易所证券余额核对20170725.xls') #打开excel表
workbooknew = copy(workbook) #复制整个excel内容
ws = workbooknew.get_sheet(0) #获取第一张表单
ws.write(3,0,'changed!') #第一行，第四列，改写为changed!
workbooknew.save(u'交易所证券余额核对20170725_copy.xls') #保存第一张表单
综合使用
注释：Excel 2003 即XLS文件有大小限制即65536行256列，所以不支持大文件，而Excel 2007以上即XLSX文件的限制则为1048576行16384列
下面则为几种模块的使用：
1.xlwt 写入xls文件内容
import xlwt
book = xlwt.Workbook() # 新建工作簿
table = book.add_sheet('Over',cell_overwrite_ok=True) # 如果对同一单元格重复操作会发生overwrite Exception，cell_overwrite_ok为可覆盖
sheet = book.add_sheet('Test') # 添加工作页
sheet.write(1,1,'A') # 行，列，属性值 (1,1)为B2元素，从0开始计数
style = xlwt.XFStyle() # 新建样式
font = xlwt.Font() #新建字体
font.name = 'Times New Roman'
font.bold = True
style.font = font # 将style的字体设置为font
table.write(0,0,'Test',style)
book.save(filename_or_stream='excel_test.xls') # 一定要保存
2.xlrd读取xls文件内容
import xlrd
data = xlrd.open_workbook('excel_test.xls')
print(data.sheet_names()) # 输出所有页的名称
table = data.sheets()[0] # 获取第一页
table = data.sheet_by_index(0) # 通过索引获得第一页
table = data.sheet_by_name('Over') # 通过名称来获取指定页
nrows = table.nrows # 为行数，整形
ncolumns = table.ncols # 为列数，整形
print(type(nrows))
print(table.row_values(0))# 输出第一行值 为一个列表
# 遍历输出所有行值
for row in range(nrows):
print(table.row_values(row))
# 输出某一个单元格值
print(table.cell(0,0).value)
print(table.row(0)[0].value)
3.综合使用python-excel三大模块完成Excel内容追加写入
import xlwt,xlrd
from xlutils.copy import copy
data = xlrd.open_workbook('excel_test.xls',formatting_info=True)
excel = copy(wb=data) # 完成xlrd对象向xlwt对象转换
excel_table = excel.get_sheet(0) # 获得要操作的页
table = data.sheets()[0]
nrows = table.nrows # 获得行数
ncols = table.ncols # 获得列数
values = ["E","X","C","E","L"] # 需要写入的值
for value in values:
excel_table.write(nrows,1,value) # 因为单元格从0开始算，所以row不需要加一
nrows = nrows+1
excel.save('excel_test.xls')
4.使用openpyxl写xlsx文件
import openpyxl
data = openpyxl.Workbook() # 新建工作簿
data.create_sheet('Sheet1') # 添加页
#table = data.get_sheet_by_name('Sheet1') # 获得指定名称页
table = data.active # 获得当前活跃的工作页，默认为第一个工作页
table.cell(1,1,'Test') # 行，列，值 这里是从1开始计数的
data.save('excel_test.xlsx') # 一定要保存
5.使用openpyxl读取xlsx文件
import openpyxl
data = openpyxl.load_workbook('excel_test.xlsx') # 读取xlsx文件
table = data.get_sheet_by_name('Sheet') # 获得指定名称的页
nrows = table.rows # 获得行数 类型为迭代器
ncols = table.columns # 获得列数 类型为迭代器
print(type(nrows))
for row in nrows:
print(row) # 包含了页名，cell，值
line = [col.value for col in row] # 取值
print(line)
# 读取单元格
print(table.cell(1,1).value)
6.综合使用openpyxl对Excel内容追加写入
import openpyxl
data = openpyxl.load_workbook('excel_test.xlsx')
print(data.get_named_ranges()) # 输出工作页索引范围
print(data.get_sheet_names()) # 输出所有工作页的名称
# 取第一张表
sheetnames = data.get_sheet_names()
table = data.get_sheet_by_name(sheetnames[0])
table = data.active
print(table.title) # 输出表名
nrows = table.max_row # 获得行数
ncolumns = table.max_column # 获得行数
values = ['E','X','C','E','L']
for value in values:
table.cell(nrows+1,1).value = value
nrows = nrows + 1
data.save('excel_test.xlsx')
7.XlsxWriter
# !/usr/bin/python
# coding:utf-8
# xlsxwriter的基本用法
import xlsxwriter
# 1. 创建一个Excel文件
workbook = xlsxwriter.Workbook('demo1.xlsx')
# 2. 创建一个工作表sheet对象
worksheet = workbook.add_worksheet()
# 3. 设定第一列（A）宽度为20像素
worksheet.set_column('A:A',20)
# 4. 定义一个加粗的格式对象
bold = workbook.add_format({'bold':True})
# 5. 向单元格写入数据
# 5.1 向A1单元格写入'Hello'
worksheet.write('A1','Hello')
# 5.2 向A2单元格写入'World'并使用bold加粗格式
worksheet.write('A2','World',bold)
# 5.3 向B2单元格写入中文并使用加粗格式
worksheet.write('B2',u'中文字符',bold)
# 5.4 用行列表示法（行列索引都从0开始）向第2行、第0列（即A3单元格）和第3行、第0列（即A4单元格）写入数字
worksheet.write(2,0,10)
worksheet.write(3,0,20)
# 5.5 求A3、A4单元格的和并写入A5单元格，由此可见可以直接使用公式
worksheet.write(4,0,'=SUM(A3:A4)')
# 5.6 在B5单元格插入图片
worksheet.insert_image('B5','./demo.png')
# 5.7 关闭并保存文件
workbook.close()
例子
提取水果报价表中的品名和成本价，
完整代码如下
#!/usr/bin/env python3
# coding: utf-8
import xlrd
# 打开excel文件，创建一个workbook对象,book对象也就是fruits.xlsx文件,表含有sheet名
rbook = xlrd.open_workbook('test.xlsx')
# sheets方法返回对象列表,[<xlrd.sheet.Sheet object at 0x103f147f0>]
rbook.sheets()
# xls默认有3个工作簿,Sheet1,Sheet2,Sheet3
rsheet = rbook.sheet_by_index(0) # 取第一个工作簿
# 循环工作簿的所有行
for row in rsheet.get_rows():
product_column = row[1] # 品名所在的列
product_value = product_column.value # 项目名
if product_value != '品名': # 排除第一行
price_column = row[4] # 价格所在的列
price_value = price_column.value
# 打印
print("品名", product_value, "价格", price_value)