五天学会Python办公自动化（二）之Excel自动化处理

目录

2.1 Excel基本操作

2.2 数据汇总

2.3 表格拆分

2.4 openpyx的使用

2.5 合并多个工作薄中一个工作表

2.6 合并多个工作薄中所有工作表

2.7 文件名快速整理到excel

2.8 一键格行换色

2.9 快速生成工资条

2.10 快速统计加班时间

2.11 快速查找重复数据

视频链接：

---

第二天：《从此做表不加班》Excel自动化处理

office家族其实都可以用VBA解决自动化的问题，但可能很多人不会用。

python针对excel有很多的第三方库可以用，比如xlwings、xlsxwriter、xlrd、xlwt、pandas、xlsxwriter、win32com、xlutils等等。

这些库可以很方便地实现对excel文件的增删改写、格式修改等，当然并不推荐你全部都去尝试一下，这样时间成本太大了。

xlrd：用于读取 Excel 文件；

xlwt：用于写入 Excel 文件；

xlutils：用于操作 Excel 文件的实用工具，比如复制、分割、筛选等

2.1 Excel基本操作

# pip install xlwt
# 导入模块
import xlwt
# 读入文件
wb = xlwt.Workbook()
# 增加工作薄
sh1 = wb.add_sheet('电影')
# 获取单元格
sh1.write(0, 0, '影片')
sh1.write(0, 1, '综合票房')
sh1.write(0, 2, '票房占比')
sh1.write(0, 3, '排片场次')

sh1.write(1, 0, '如果声音记不得')
sh1.write(1, 1, 361.57)
sh1.write(1, 2, 33.3)
sh1.write(1, 3, 95371)

sh1.write(2, 0, '赤狐先生')
sh1.write(2, 1, 194.23)
sh1.write(2, 2, 17.8)
sh1.write(2, 3, 79980)

sh1.write(3, 0, '除暴')
sh1.write(3, 1, 130.05)
sh1.write(3, 2, 11.8)
sh1.write(3, 3, 42457)

sh1.write(4, 0, '疯狂原始人2')
sh1.write(4, 1, 120.72)
sh1.write(4, 2, 10.9)
sh1.write(4, 3, 40697)
wb.save('excel01.xls')

读取数据

	# pip install xlrd
import xlrd

wb = xlrd.open_workbook('excel01.xls')
print(wb)
# 获取并打印 sheet 数量
print( "sheet 数量:", wb.nsheets)
# 获取并打印 sheet 名称
print( "sheet 名称:", wb.sheet_names())
# 根据 sheet 索引获取内容
sh1 = wb.sheet_by_index(0)
# 或者
# 也可根据 sheet 名称获取内容
# sh = wb.sheet_by_name('成绩')
# 获取并打印该 sheet 行数和列数
print( u"sheet %s 共 %d 行 %d 列" % (sh1.name, sh1.nrows, sh1.ncols))

# 获取并打印某个单元格的值
print( "第一行第二列的值为:", sh1.cell_value(0, 1))
print( "第一行第二列的值为:", sh1.cell(0,1).value)
print( "第一行第二列的值为:", sh1.row(1)[2].value)

# 获取整行或整列的值
rows = sh1.row_values(0) # 获取第一行内容
cols = sh1.col_values(1) # 获取第二列内容

# 打印获取的行列值
print( "第一行的值为:", rows)
print( "第二列的值为:", cols)

# 获取单元格内容的数据类型
print( "第二行第一列的值类型为:", sh1.cell(1, 0).ctype)

# 遍历所有表单内容
for sh in wb.sheets():
    for r in range(sh.nrows):
        # 输出指定行
        print( sh.row(r))

更新数据

# pip insatll xlutils
# 导入相应模块
import xlrd
from xlutils.copy import copy

# 打开 excel 文件
readbook = xlrd.open_workbook("excel01.xls")
# 复制一份
wb = copy(readbook)

# 选取第一个表单
sh1 = wb.get_sheet(0)

# 在第五行新增写入数据
sh1.write(5, 0, '保家卫国-抗美援朝')
sh1.write(5, 1, 59.84)
sh1.write(5, 2, 5.1)
sh1.write(5, 3, 488)

# 选取第二个表单
sh2 = wb.add_sheet('汇总')
sh3 = readbook.sheet_by_index(0)
count = 0
for i in range(1,sh3.nrows):
    num = sh3.cell_value(i,3)
    count += num

# 替换总成绩数据
sh2.write(1, 0, count)
# 保存
wb.save('excel01.xls')

设置样式

# 导入 xlwt 库
import xlwt

wb = xlwt.Workbook()
# 新增两个表单页
sh1 = wb.add_sheet('账单')

ft = xlwt.Font()
ft.name = '微软雅黑'
ft.colour_index = 2
# 字体大小，11为字号，20为衡量单位
ft.height = 20*11
# 字体加粗
ft.bold = False
# 下划线
ft.underline = True
# 斜体字
ft.italic = True


# 设置单元格对齐方式
alignment = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 1
# 0x00(上端对)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 2
# 设置自动换行
alignment.wrap = 1


# 设置列宽，一个中文等于两个英文等于两个字符，11为字符数，256为衡量单位
sh1.col(2).width = 6 * 256
sh1.row(0).height_mismatch = True
sh1.row(0).height = 6 * 256

# 设置边框
borders = xlwt.Borders()
# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borders.left = 1
borders.right = 2
borders.top = 3
borders.bottom = 4
borders.left_colour = 3
borders.right_colour = 2
borders.top_colour = 2
borders.bottom_colour = 4

# 设置背景颜色
pattern = xlwt.Pattern()
# 设置背景颜色的模式
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# 背景颜色
pattern.pattern_fore_colour = 5


sy = xlwt.XFStyle()
sy.font = ft
sy.alignment= alignment
sy.borders = borders
sy.pattern = pattern

sy2 = xlwt.easyxf('font: bold on,color-index 4; align: wrap on, vert centre, horiz center')
sy3 = xlwt.easyxf('font: bold on,color-index 4; border: left 1 ,right_colour 3,right 1')

sh1.write(0,0,'吕小布')
sh1.write(0,1,'吕小布',sy)
sh1.write(0,2,'吕小布、貂的蝉、刘的备')
sh1.write(3,3,'鲁班7忠',sy)
sh1.write(3,1,'鲁班7忠',sy3)
wb.save('excel02.xls')

2.2 数据汇总

import re
import xlrd
import xlwt
from xlutils.copy import copy

def read_data():
    wb = xlrd.open_workbook('.