xlrd 和openpyxl

1.什么是xlrd模块？

　　♦python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库。

今天就先来说一下xlrd模块：

一、安装xlrd模块

　　♦ 到python官网下载http://pypi.python.org/pypi/xlrd模块安装，前提是已经安装了python 环境。

　　♦或者在cmd窗口  pip install  xlrd

关于xlrd/xlwt和openpyxl的差别

两者都是对于excel文件的操作插件，两者的主要区别在于写入操作，其中xlwt针对Ecxec2007之前的版本，即.xls文件，其要求单个sheet不超过65535行，而openpyxl则主要针对Excel2007之后的版本（.xlsx），它对文件大小没有限制。另外还有区别就是二者在读写速度上的差异，xlrd/xlwt在读写方面的速度都要优于openpyxl，但因为xlwt无法生成xlsx是个硬伤，所以想要尽量提高效率又不影响结果时，可以考虑用xlrd读取，用openpyxl写入。

 

 

二、使用介绍

 1、常用单元格中的数据类型

   　　♦ 0. empty（空的）,1 string（text）, 2 number, 3 date, 4 boolean, 5 error， 6 blank（空白表格）

 2、导入模块

 import xlrd

  3、打开Excel文件读取数据

data = xlrd.open_workbook(filename)#文件名以及路径，如果路径或者文件名有中文给前面加一个r拜师原生字符。

　4、常用的函数

       ♦ excel中最重要的方法就是book和sheet的操作

      1）获取book中一个工作表

如：

 

        2）行的操作(row)

nrows = self.work_sheet.nrows  # 获取该sheet中的有效行数
print(nrows)

row_lst=self.work_sheet.row(0)  # 返回由该行中所有的单元格对象组成的列表
print(row_lst)

row_lst=self.work_sheet.row_values(0)  # 返回由该行中所有单元格的数据组成的列表
print(row_lst)

 

       3）列(colnum)的操作

如：

 

      4）单元格的操作  

lst = self.work_sheet.cell(行, 列)  # 返回单元格对象
print(lst)

lst = self.work_sheet.cell_value(行,列)  # 返回单元格中的数据
print(lst)

♦单元格：单元格是表格中行与列的交叉部分，它是组成表格的最小单位，可拆分或者合并。单个数据的输入和修改都是在单元格中进行的

如：

注意：注意作用域问题，之前获取的sheet之后，都在获取到这个sheet值后，在进行，行和列以及单元格的操作。

 

合并成数据字典

      # 拼成数据字典的格式
        key=self.work_sheet.row_values(0)
        value =self.work_sheet.row_values(1)
        dic=dict(zip(key,value))
        print(dic)

 

写入方法

  excelpath = r"../excel/接口.xls"
    # 打开excel
    workBook = xlrd.open_workbook(excelpath, formatting_info=True)  # formatting_info 按原样式打开
    sheets = workBook.sheet_names()  # 获取所有的表名
    workSheet = workBook.sheet_by_name('Sheet1')  # 需要执行的sheet

    # 取数据
    # cellData = workSheet.row_values(1) # 取一行
    # cellData = workSheet.cell(1,0) # 取 第一行第6列 单元格
    cellData = workSheet.cell(1, 0).value  # 取 第一行第6列 单元格的值

　　 print(self.workSheet.cell_value(1, 0))

 # 获取excel行数
    print(workSheet.nrows)

    # 1.拷贝
    newWorkBook = copy.copy(workBook)  # 这里workSheet还是workBook 要看下

    # 2.取拷贝的excel的sheet---sheet 下标(默认从0开始)
    newSheet = newWorkBook.get_sheet(0)

     # 3. 写入数据--info--newSheet,write(行下标,列下标,内容)
        newSheet.write(case, 9, "pass")

        # 4.保存excel
        newWorkBook.save("../excel/result.xls")

 

python解决open()函数、xlrd.open_workbook()函数文件名包含中文，sheet名包含中文报错的问题

问题现象： 

　　♦1、使用open()函数、xlrd.open_workbook()函数打开文件，文件名若包含中文，会报错找不到这个文件或目录。 

　　♦2、获取sheet时若包含中文，也会报错。

 

解决方案：

　　♦对参数进行转码即可。如：

filename = filename.decode('utf-8')

　

openpyxl

https://openpyxl.readthedocs.io/en/stable/

openpyxl是Python的第三方库，专门用于读/写excel文件的，通常支持xlsx/xlsm/xltx/xltm结尾的excel文件。

pip install openpyxl
# pip install lxml   # 创建大文件时会用到
# pip install pillow  # 在excel中处理图片，会用到

读excel表格

假如我有这样的一个excel表格：

基础操作

from openpyxl import load_workbook

wb = load_workbook('./xxx.xlsx')

# 获取所有sheet
# print(wb.sheetnames)  # ['mysqld', 'mysqld_safe', 'client', 'Sheet']

# 获取已经存在的sheet

# 基于sheet名称获取sheet对象
# sheet = wb['mysqld']
# print(sheet)  # <Worksheet "mysqld">

# 基于索引获取sheet对象
sheet = wb.worksheets[0]

# 获取指定单元格的对象，有以下两种方式获取
# cell = sheet.cell(1, 1)  # 法1
# cell = sheet['A1']  # 法2

# 可以通过单元格对象来获取其内容、样式、字体等
# print(cell.value)  # 获取单元格的内容  key
# print(cell.alignment)  # 对齐方式  https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.alignment.html?highlight=alignment
# print(cell.font)  # 字体  https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.fonts.html?highlight=font#openpyxl.styles.fonts.Font
# print(cell.style)  # 字体样式


# 获取指定行
# print(sheet[1])  # (<Cell 'mysqld'.A1>, <Cell 'mysqld'.B1>)
# print(list(map(lambda x: x.value, sheet[1])))  # ['key', 'value']

# 获取所有行
# print(sheet.rows)  # 获取所有行，返回一个生成器 <generator object Worksheet._cells_by_row at 0x000001FBB9936DD0>
# for row in sheet.rows:
#     # 获取每一行的内容
#     print(list(map(lambda x: x.value, row)))
#     # 获取每行指定列的内容
#     print(row[0].value, row[1].value)  # 获取每行第一列、第二列内容

# 获取所有列
# print(sheet.columns)
# for col in sheet.columns:
#     # 获取每一列的内容
#     print(list(map(lambda x: x.value, col)))
#     # 以列的形式，获取指定行的内容
#     print(col[0].value)   # 获取第一行的数据

# 获取所有行，用的也比较多
# for row in sheet.iter_rows():
#     print(row)

# 也可以指定从哪行哪列循环到哪行哪列
# for row in sheet.iter_rows(min_row=3, min_col=3, max_row=5, max_col=5):
#     print(row)
#     print(row[0].row)  # 返回当前行的行号

读取合并单元格
如果遇到合并的单元格的话，就需要注意了：

• 水平合并，以最左侧的单元格为主，即合并的单元格的内容来自于最左侧的单元格，其他被合并的单元格，内容为空，且特点是merge cell，表示该单元被合并了。
• 垂直合并，内容来自与最上面的单元格，其他单元格内容为空，且特点是merge cell。

如有这样的一个表格：

from openpyxl import load_workbook

wb = load_workbook('./xxx.xlsx')
sheet = wb.worksheets[-1]

# 先来看水平合并的单元格的特点
# 下面两个输出结果，非常正常，因为它是最左侧的单元格
# print(sheet['B1'])  # <Cell 'Sheet'.B1>
# print(sheet['B1'].value)  # 出版书籍

# 被合并的单元格,它是merge cell，且值为None
# print(sheet['C1'], sheet['C1'].value)  # <MergedCell 'Sheet'.C1> None
# print(sheet['D1'], sheet['D1'].value)  # <MergedCell 'Sheet'.D1> None

# 再来看垂直合并的单元格
# 先来看最上面的单元格，也是非常正常的
# print(sheet['A2'])  # <Cell 'Sheet'.A2>
# print(sheet['A2'].value)  # 机械工业出版社

# 被合并的单元格,它是merge cell，且值为None
# print(sheet['A3'], sheet['A3'].value)  # <MergedCell 'Sheet'.A3> None

# 在循环中也能看到这个现象
for row in sheet.rows:
    print(row)
"""
(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <MergedCell 'Sheet'.C1>, <MergedCell 'Sheet'.D1>, <Cell 'Sheet'.E1>)
(<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.E2>)
(<MergedCell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.E3>)
(<Cell 'Sheet'.A4>, <Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>, <Cell 'Sheet'.E4>)
"""

写excel表格

注意：在Windows平台，只要牵扯到保存修改的操作，都必须保证要操作的excel表格是关闭的，否则操作失败。提示权限问题： PermissionError: [Errno 13] Permission denied: 'xxx.xlsx'

写excel表格，就分为两种，文件存在，然后改写；文件不存在，打开文件写。
原excel表格写内容

from openpyxl import load_workbook

file_path = './xxx.xlsx'
wb = load_workbook(file_path)
sheet = wb.worksheets[0]
# 拿到单元格对象
cell = sheet['A1']
# 有了单元格对象，就可以一顿操作了，改内容、字体、边框.....
cell.value = 'new_key'

# 最后要保存修改
wb.save(file_path)

新建excel表格，写内容

from openpyxl.workbook import Workbook

file_path = './x1.xlsx'
# Workbook会自动创建一个excel文件，然后默认也会创建一个sheet，sheet名叫Sheet，注意，是Sheet不是sheet，这俩不是一回事
wb = Workbook()

# 拿到sheet
sheet = wb['Sheet']

# 拿到单元格对象
cell = sheet['A1']
# 有了单元格对象，就可以一顿操作了，改内容、字体、边框.....
cell.value = 'new_key'

# 最后要保存修改
wb.save(file_path)

更多细节操作

在来研究下关于sheet和cell的更多细节操作。
sheet的操作

from openpyxl.workbook import Workbook

file_path = './x1.xlsx'
# Workbook会自动创建一个excel文件，然后默认也会创建一个sheet，sheet名叫Sheet，注意，是Sheet不是sheet，这俩不是一回事
wb = Workbook()
sheet = wb['Sheet']

# 修改sheet名
sheet.title = "new_sheet"

# 设置sheet颜色
sheet.sheet_properties.tabColor = "ffcc66"  # rgb颜色：http://tools.jb51.net/static/colorpicker/

# 创建新的sheet
# 0表示索引，相当于新创建的sheet插入到原有(多个)sheet中的那个位置，0就是新创建的sheet放在最左边
s2_sheet = wb.create_sheet('s2', 0)

# 当你打开excel时，指定默认打开的sheet，默认是打开最左侧的sheet
wb.active = 1


# 拷贝sheet
s3_sheet = wb.copy_worksheet(wb['s2'])  # sheet s2也必须存在
s3_sheet.title = 's3'

# 删除sheet
del wb['new_sheet']   # 不存在则报错

# 最后都要记得保存
wb.save(file_path)

cell操作

from openpyxl.workbook import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill

file_path = './x1.xlsx'
# Workbook会自动创建一个excel文件，然后默认也会创建一个sheet，sheet名叫Sheet，注意，是Sheet不是sheet，这俩不是一回事
wb = load_workbook(file_path)
sheet = wb['Sheet']

# 修改单元格的值
# 法1
# cell = sheet['A1']
# cell.value = '单元格A1'
# 法2
# sheet['A1'] = '单元格A1'


# for row in sheet['A1': 'B3']:
#     # 每一行的row，相当于有两个值，是元组类型 ('A1', 'B1')
#     row[0].value, row[1].value = 'x', 'y'

"""
上面的for循环结果，表格中的效果如下
    A   B
1   x   y
2   x   y
3   x   y
"""

# 对齐方式
# cell = sheet['A1']
# horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"
# vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed"
# text_rotation，旋转角度。
# wrap_text，是否自动换行。
# cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)


# 边框
# side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin'
# cell = sheet['A1']
# cell.border = Border(
#     top=Side(style="thin", color="FFB6C1"),
#     bottom=Side(style="dashed", color="FFB6C1"),
#     left=Side(style="dashed", color="FFB6C1"),
#     right=Side(style="dashed", color="9932CC"),
#     diagonal=Side(style="thin", color="483D8B"),  # 对角线
#     diagonalUp=True,  # 左下 ~ 右上
#     diagonalDown=True  # 左上 ~ 右下
# )


# 字体
# cell = sheet['A1']
# cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")


# 背景色
# cell = sheet['A1']
# cell.fill = PatternFill("solid", fgColor="99ccff")


# 渐变背景色
# cell = sheet['A1']
# cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000"))  # stop：从左到右依次进行渐变


# 宽高设置，在sheet中，序号从1开始
# sheet.row_dimensions[1].height = 50
# sheet.column_dimensions["E"].width = 100


# 合并单元格
# sheet.merge_cells("B2:D8")
# sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)


# 写入公式
# sheet["D2"] = "=B2*C2"
# sheet["D3"] = "=SUM(B3,C3)"

# 删除
# idx：删除行或者列的起始位置
# amount：总共删amount行/列，默认是1行/列
# sheet.delete_rows(idx=1, amount=2)   # 从第一行开始，总共删除2行
# sheet.delete_cols(idx=1, amount=2)  # 从第一列开始，总共删除2列


# 插入
# sheet.insert_rows(idx=1, amount=2)  # 插入两行
# sheet.insert_cols(idx=1, amount=2)  # 插入两列


# 移动
# 将指定范围内的单元格进行移动
# row 正值表示向下移动；负值表示向上移动
# col 正值表示向有移动；负值表示向左移动
# translate:True表示公式自动调整，默认为False
# sheet.move_range("B1:D3", rows=3, cols=3, translate=True)  # 将 B1到B3范围内的单元格整体向下移动3行向右移动3列


# 打印区域
# sheet.print_area = "A1:D8"

# 打印时，每个页面都固定表头
# sheet.print_title_cols = 'A:D'
# sheet.print_title_rows = '1:2'


# 最后都要记得保存
wb.save(file_path)