Excel自动化办公

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

在UI⾃动化或者接⼝⾃动化中数据维护是⼀个核⼼，所以此模块⾮常实⽤。

刚刚装好后会报错【xlrd.biffh.XLRDError: Excel xlsx file; not supported】

默认安装的版本太高，卸载2.0.1并重装1.2.0解决

pip uninstall xlrd;  pip install xlrd==1.2.0

1. 基本操作

data = xlrd.open_workbook(filename) # ⽂件名以 及路径，如果路径或者⽂件名有中⽂给前⾯加⼀个 r

data = xlrd.open_workbook('./data/data.xlsx')
print(data.sheets())
name = data.sheet_names()
print(name)
table1 = data.sheets()[0]            # 通过索引顺序获取
table2 = data.sheet_by_index(0)       # 通过索引顺序获取
table3 = data.sheet_by_name("Sheet1")   # 通过名称获取

2. 行的操作

 nrows = table.nrows #改工作表返回行数

print((table.row(0))) #返第一行数据列表， 元素为类型加值的字典

for i in range(nrows):

        print(table.row(i)) # 返回类型+数据组成的列表

        print(table.row_values(0, start_colx=0, end_colx=None)) # 只返回所有数据组成的列表                   print(table.row_len(i)) #返回第0行数据个数

print(table.row_slice(0)) # 返回由该⾏中所有的单元格对象组成的列表,和nrows相同

原始表格：

3. 列的操作

data = xlrd.open_workbook('./data/data.xlsx')
table = data.sheets()[0]
ncols = table.ncols
print(ncols)
print(table.col(0, start_rowx=0, end_rowx=None))

for i in range(ncols):
    values = table.col_values(i, start_rowx=0,end_rowx=None)
    # print(table.col_slice(i, start_rowx=0, end_rowx=None)) #和table.col相同
    print(values)

4. 单元格操作

rowx, colx = 1,1
# 返回单元格对象
obj = table.cell(rowx, colx)
# 返回对应位置单元格中的数据类型
typ = table.cell_type(rowx, colx)
# 返回对应位置单元格中的数据
values = table.cell_value(rowx, colx)

number:98.0 2 98.0

5. xlwt 写⼊ 操作 Excel(仅限xls格式！)

官方文档：xlwt documentation — xlwt 1.3.0 documentation

装包：pip install xlwt==1.3.0

5.1 一般写入数据

def fun():
    workbook = xlwt.Workbook(encoding='ascii')
    worksheet = workbook.add_sheet("my test sheet")
    worksheet.write(0,0,'rob')
    worksheet.write(2,1,'handsome',style)
    workbook.save("./data/新创建表格1.xls")
fun()

5.2 设置字体格式

font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
font.underline = True
font.italic = True
#把字体赋给样式的字体
style.font = font

5.3 设置列宽

xlwt中列宽的值表示⽅法：默认字体0的1/256为衡量单位。 xlwt创建时使⽤的默认宽度为2960，既11个字符0的宽度所以 我们在设置列宽时可以⽤如下⽅法：width = 256 * 20 256为 衡量单位，20表示20个字符宽度

# 设置列宽
worksheet.col(0).width = 256 * 20

5.4 设置行高

⾼⾏⾼是在单 元格的样式中设置的，可以通过⾃动换⾏通过输⼊⽂字的多 少来确定⾏⾼ ：

style = xlwt.easyxf('font:height 360;') # 设置⾏⾼

5.5 合并列和行

# 合并 第2⾏到第3⾏ 的 第1列到第4列

worksheet.write_merge(1, 2, 0, 3, '办公⾃ 动化'）

5.6添加边框

borders = xlwt.Borders()

borders.left = xlwt.Borders.THIN
 borders.right = xlwt.Borders.THIN
 borders.top = xlwt.Borders.DASHED
 borders.bottom = xlwt.Borders.DASHED
 borders.left_colour = 0x40
 borders.right_colour = 0x40
 borders.top_colour = 0x40
 borders.bottom_colour = 0x40

style2 = xlwt.XFStyle() # Create Style

style2.borders = borders # Add Borders to Style

# 往表格写⼊内容

worksheet.write(1, 1, 'rob is', style2)

worksheet.write(2, 1, "a good man")

整体代码和效果：

import xlwt
def fun():
    workbook = xlwt.Workbook(encoding='ascii')
    worksheet = workbook.add_sheet("my test sheet")
    #设置样式
    style1 = xlwt.XFStyle()
    #为样式设置字体
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = True
    font.underline = True
    font.italic = True
    #把字体赋给样式的字体
    style1.font = font
    # 设置列宽
    worksheet.col(0).width = 256 * 20
    worksheet.write(0,0,'rob')
    worksheet.write(0,1,'is handsome',style1)

    worksheet.col(0).width = 256 * 20 #列宽
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.DASHED
    borders.bottom = xlwt.Borders.DASHED
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style2 = xlwt.XFStyle()  # Create Style
    style2 = xlwt.easyxf('font:height 360;') # 设置⾏⾼
    style2.borders = borders  # Add Borders to Style
    # 往表格写⼊内容
    worksheet.write(2, 2, 'rob is', style2)
    worksheet.write(2, 3, "a good man")
    workbook.save("./data/新创建表格1.xls")
fun()

6. xlutils修改Excel

6.1 安装xlutils ： pip install xlutils

6.2 拷⻉源⽂件(需配合xlrd使⽤)

new_workbook = copy(workbook)  # 将xlrd对象拷⻉转化为xlwt对象
new_workbook.save("./data/新创建表格2.xls")  # 保存⼯作簿

拷贝后的文件样式会消失：

6.3 写入

write_save.write(0, 1, "xlutils写⼊！")  #1⾏2列写⼊

7. openpyxl 读取 写⼊ 修改 操作Excel

在openpyxl中，主要⽤到三个概念：Workbooks，Sheets， Cells。

7.1  Workbook就是⼀个excel⼯作表； Sheet是⼯作表中的⼀张表⻚； Cell就是简单的⼀个格。 openpyxl就是围绕着这三个概念进⾏的，不管读写都是：打开Workbook，定位Sheet，操作Cell。

from openpyxl import Workbook
import datetime
wb = Workbook()
# 抓取活动⼯作表
ws = wb.active
# 数据可以直接分配给单元格
ws['A1'] = 42
# 下⼀⾏⾏每列添加元素
ws.append([1, 2, 3])
# Python类型将⾃动转换
ws['A3'] = datetime.datetime.now()
# Save the file
wb.save("./data/pyxl_sample.xlsx")

7.2 创建和选择表(sheet)

# ⽅式⼀：插⼊到最后(default) ws1 = wb.create_sheet("Mysheet")

# ⽅式⼆：插⼊到最开始的位置 ws2 = wb.create_sheet("Mysheet", 0)

worksheet ws = wb.active # 激活

ws3 = wb["New Title"]  # sheet 名称可以作为 key 进⾏索引

ws4 = wb.get_sheet_by_name("New Title")  #deprecated

7.3 循环获取单元格数据

from openpyxl import load_workbook
# 实例化
wb = load_workbook('./data/pyxl_sample.xlsx')
sheet = wb['Sheet']
# A1, A2, A3这样的顺序
for column in sheet.columns:
    for cell in column:
        print(cell.value)

7.4 打印2D图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series,Reference
from copy import deepcopy
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [
 ('Number', 'Batch 1', 'Batch 2'),
 (2, 10, 30),
 (3, 40, 60),
 (4, 50, 70),
 (5, 20, 10),
 (6, 10, 40),
 (7, 50, 30),
]
for row in rows:
    ws.append(row)
## 创建图表 柱形图表
chart1 = BarChart()
# 图表类型 竖着
chart1.type = "col"
# 使⽤ 1 到 48 的⼀个数字来设置图表样式。
chart1.style = 48
## 设置标题
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
## X轴描述
chart1.x_axis.title = 'Sample length (mm)'
## 数据
# 引⽤⼯作表的单元范围，⽤作图表添加数据
data = Reference(ws, min_col=2, min_row=1,max_row=7, max_col=3)
# 引⽤⼯作表的单元范围，⽤作X轴标签使⽤
labels = Reference(ws, min_col=1, min_row=2,max_row=7)
# 添加被引⽤的数据到 BarChart 对象
chart1.add_data(data, titles_from_data=True)
# 设置类别轴的标签(也就是x轴对应标注的值为Number列的数据)
chart1.set_categories(labels)
## 图表开始位置 A10
ws.add_chart(chart1, "A10")
## 复制图表
chart2 = deepcopy(chart1)
# 使⽤ 1 到 48 的⼀个数字来设置图表样式。
chart2.style = 11
# 图表类型 横着
chart2.type = "bar"
# 图表标题
chart2.title = "Horizontal Bar Chart"
## 图表开始位置 G10
ws.add_chart(chart2, "G10")
chart3 = deepcopy(chart1)
# 图表类型
chart3.type = "col"
# 使⽤ 1 到 48 的⼀个数字来设置图表样式。
chart3.style = 12
# 堆积
chart3.grouping = "stacked"
# 指定位于列和栏的⽅式。 可以是介于1-100 和100之间的值。 仅适⽤于2D 横条图和2D 直条图。 可读写的Long。
chart3.overlap = 100
chart3.title = 'Stacked Chart'
## 图表开始位置 A27
ws.add_chart(chart3, "A27")
chart4 = deepcopy(chart1)
# 图表类型
chart4.type = "bar"
# 使⽤ 1 到 48 的⼀个数字来设置图表样式。
chart4.style = 13
# 堆积
chart4.grouping = "percentStacked"
# 会指定位于列和栏的⽅式。 可以是介于-100 和100之间的值。 仅适⽤于2D 横条图和2D 直条图。 可读写的
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")
wb.save("./data/bar.xlsx")

效果图：