五天学会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('./data/data01.xlsx')
    sh = wb.sheet_by_index(0)
    das = []
    fen_type={}
    for r in range(sh.nrows):
        count = sh.cell_value(r,3)*sh.cell_value(r,4)
        das.append(count)
        key = sh.cell_value(r,0)
        if fen_type.get(key):
            fen_type[key] = fen_type.get(key)+count
        else:
            fen_type[key] = count
    return das,fen_type

def save_simple(data,fen):
    wb = xlrd.open_workbook('./data/data01.xlsx')
    sh_t = wb.sheet_by_index(0)
    wb2 = copy(wb)
    sh = wb2.get_sheet(0)
    for r in range(sh_t.nrows):
        sh.write(r,sh_t.ncols,data[r])
    
    sh2 = wb2.add_sheet('汇总')
    for i,key in enumerate(fen.keys()):
        sh2.write(i,0,key)
        sh2.write(i,1,fen.get(key))
    wb2.save('./销售表/data01_t.xlsx')

if __name__ == "__main__":
    d,f = read_data()
    save_simple(d,f)

2.3 表格拆分

import xlrd
from xlutils.copy import copy
def get_data():
    data = {}
    wb = xlrd.open_workbook('data01.xlsx')
    sh = wb.sheet_by_index(0)
    
    for i in range(sh.nrows):
        key = sh.cell_value(i,0)
        d = {'name':sh.cell_value(i,2),'type':sh.cell_value(i,1),'price':sh.cell_value(i,3),'count':sh.cell_value(i,3)}
        if data.get(key):
            data[key].append(d)
        else:
            t = [d]
            data[key] = t
    return data

def create_data(data):
    wb = xlrd.open_workbook('data01.xlsx')
    wb2 = copy(wb)
    for key in data.keys():
        tw = wb2.add_sheet(key)
        for i,d in enumerate(data.get(key)):
            tw.write(i,0,d.get('type'))
            tw.write(i,1,d.get('name'))
            tw.write(i,2,d.get('price'))
            tw.write(i,3,d.get('count'))
    wb2.save('data01_t.xlsx')


if __name__ == "__main__":
    d = get_data()
    create_data(d)

# 导入模块
# 读入文件
# 获取活动表
# 获取单元格
# 单元格内容管理
# 设置行与高
# 背景颜色设置
# 公式的写入与获取
# 批量读写数据
# numpy与pandas管理数据

2.4 openpyx的使用

def new():
    # 创建
    from openpyxl import Workbook 
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    # 方式一：插入到最后(default)
    ws1 = wb.create_sheet("Mysheet")
    # 方式二：插入到最开始的位置
    ws2 = wb.create_sheet("Mysheet", 0)
    # 保存
    wb.save('new.xlsx')
def open():
    # 打开已有
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    # 选择已有表
    # sheet 名称可以作为 key 进行索引
    ws1 = wb.active
    ws3 = wb["Sheet1"]
    ws4 = wb.get_sheet_by_name("Sheet1")
    print(ws1 is ws3 is ws4)

def show_sheet():
     # 查看表名
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    print(wb.sheetnames)
    for s in wb:
        print(s.title)

def get_one_value():
    # 获取值
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    ws = wb.active
    v1 = ws['B3']
    v2 = ws.cell(row=4,column=3)
    print(v1.value,v2.value)
def get_many_value():
    # 获取值
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    ws = wb.active
    # 通过切片
    cell_range = ws['A1':'C2']
    # 通过行(列)
    colC = ws['C']
    colCD = ws['C':'D']
    row10 = ws[10]
    row_range = ws[5:10]
    print(cell_range)
    print(colC)
    print(colCD)
    print(row10)
    print(row_range)
    # 通过指定范围(行 → 行) 3行3列
    for row in ws.iter_rows(min_row=2,max_row=5, max_col=3):
        for cell in row:
            print(cell.value)
    # 通过指定范围(列 → 列)
    for row in ws.iter_rows(min_col=3, max_col=5,min_row=1, max_row=5):
        for cell in row:
            print(cell.value)
    # 遍历所有 方法一
    print(tuple(ws.rows))
    # 遍历所有 方法二
    print(tuple(ws.columns))

def get_many_value2():
        # 获取值
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    ws = wb.active
    # sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。
    # sheet.columns类似，不过里面是每个tuple是每一列的单元格。
    # 因为按行，所以返回A1, B1, C1这样的顺序
    for row in ws.rows:
        for cell in row:
            print(cell.value)
    
    # A1, A2, A3这样的顺序
    for column in ws.columns:
        for cell in column:
            print(cell.value)
def get_num():
    # 获取值
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    ws = wb.active
    # 获得最大列和最大行
    print(ws.max_row)
    print(ws.max_column)

def remove_sheet():
     # 获取值
    from openpyxl import load_workbook
    wb = load_workbook('data01.xlsx')
    wb.remove('Sheet1')
    del wb['Sheet1']

def set_value_style():
    from openpyxl.styles import Font, colors, Alignment
    bold_itatic_24_font = Font(name='微软雅黑', size=34, italic=True, color=colors.BLUE, bold=True)
    
    # 创建
    from openpyxl import Workbook 
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    # 方式一：插入到最后(default)
    ws = wb.create_sheet("Mysheet")
    ws['B2'] = 'Hello!'
    ws['B2'].font = bold_itatic_24_font
    # 保存
    wb.save('new2.xlsx')
def set_value():
     # 创建
    from openpyxl import Workbook 
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    data = ['养老','医疗','公积金']
    for i,d in enumerate(data):
        ws.cell(i+1,1).value = d
    wb.save('new3.xlsx')
def set_style2():
    from openpyxl.styles import Font, colors, Alignment
   # 创建
    from openpyxl import Workbook 
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    ws.row_dimensions[1].height = 40
    ws.column_dimensions['C'].width = 40
    data = ['养老','医疗','公积金']
    for i,d in enumerate(data):
        ws.cell(i+1,1).value = d
        ws.cell(i+1,1).alignment = Alignment(horizontal='center', vertical='center')
        
    wb.save('new4.xlsx')
def set_value2():
   # 创建
    from openpyxl import Workbook 
    from datetime import date
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [date(2020,12, 1), 40, 30, 25],
        [date(2020,12, 2), 40, 25, 30],
        [date(2020,12, 3), 50, 30, 45],
        [date(2020,12, 4), 30, 25, 40],
        [date(2020,12, 5), 25, 35, 30],
        [date(2020,12, 6), 20, 40, 35],
    ]
    for row in rows:
        ws.append(row)
    wb.save('new6.xlsx')

def set_merge():
    from openpyxl.styles import  Alignment
    # 创建
    from openpyxl import Workbook 
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    ws.merge_cells('A1:c1')
    ws.merge_cells('D2:E5')
    ws['A1'] = '横向合并'
    ws.cell(1,1).alignment = Alignment(horizontal='center', vertical='center')
        
    ws['D2'] = '综合合并'
    ws['D2'].alignment = Alignment(horizontal='center', vertical='center')
    wb.save('new5.xlsx')
    # sheet.unmerge_cells('A1:C3')

def set_img():
     # 创建
    from openpyxl.chart import LineChart,Reference
    from openpyxl import Workbook 
    from datetime import date
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [date(2020,12, 1), 40, 30, 25],
        [date(2020,12, 2), 40, 25, 30],
        [date(2020,12, 3), 50, 30, 45],
        [date(2020,12, 4), 30, 25, 40],
        [date(2020,12, 5), 25, 35, 30],
        [date(2020,12, 6), 20, 40, 35],
    ]
    for row in rows:
        ws.append(row)

    c1 = LineChart()
    c1.title = "Line Chart"
    # c1.style = 2
    c1.y_axis.title = 'Size'
    c1.x_axis.title = 'Test Number'
    # min_row 第几行进行分类  min_col 第几列开始  max_col 取到第几列数据 max_row 取到第几行数据
    data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
    # titles_from_data 是否启用标题
    c1.add_data(data, titles_from_data=True)
    ws.add_chart(c1,'A9')
    wb.save('new7.xlsx')

def set_img2():
    from openpyxl.chart import PieChart,Reference
    from openpyxl import Workbook 
    from openpyxl.chart.series import DataPoint

    data = [
        ['名称', '数值'],
        ['苹果', 50],
        ['草莓', 30],
        ['椰子', 10],
        ['荔枝', 40],
    ]
    wb = Workbook()
    ws = wb.active

    for row in data:
        ws.append(row)

    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)
    data = Reference(ws, min_col=2, min_row=1, max_row=5)
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "Pies sold by category"

    # Cut the first slice out of the pie
    # slice = DataPoint(idx=0, explosion=20)
    # pie.series[0].data_points = [slice]

    ws.add_chart(pie, "D1")
    wb.save('new8.xlsx')
def set_img3():
    from openpyxl import Workbook
    from openpyxl.chart import BarChart, Series, Reference

    wb = Workbook()
    ws = wb.active

    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"
        chart1.style = 10
        chart1.title = "Bar Chart"
        chart1.y_axis.title = 'Test number'
        chart1.x_axis.title = 'Sample length (mm)'

        data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
        cats = Reference(ws, min_col=1, min_row=2, max_row=7)
        chart1.add_data(data, titles_from_data=True)
        chart1.set_categories(cats)
        chart1.shape = 4
        ws.add_chart(chart1, "A10")
        wb.save('new9.xlsx')

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

# 打开已有
from openpyxl import load_workbook,Workbook
import os

def copy_data():
    wb = Workbook()
    ws = wb.active
    ta = []
    for i in os.listdir('./销售表'):
        tb = load_workbook(f'./销售表/{i}')
        ts = tb.active
        for x in range(1,ts.max_row):
            td = []
            for y in range(1,ts.max_column):
                d = ts.cell(x,y).value
                td.append(d)
            if td not in ta:
                ta.append(td)
    for a in ta:
        ws.append(a)
    wb.save('new10.xlsx')
if __name__ == "__main__":
    copy_data()

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

# 打开已有
from openpyxl import load_workbook,Workbook
import os

def copy_data():
    wb = Workbook()
    for i in os.listdir('./销售表'):
        tb = load_workbook(f'./销售表/{i}')
        sheet_name = tb.sheetnames[0]
        print(sheet_name)
        ts = tb.active
        ta = wb.create_sheet(sheet_name)
        for x in range(1,ts.max_row):
            td = []
            for y in range(1,ts.max_column):
                d = ts.cell(x,y).value
                td.append(d)
            ta.append(td)
    del wb['Sheet']
    wb.save('new11.xlsx')
if __name__ == "__main__":
    copy_data()

2.7 文件名快速整理到excel

2.8 一键格行换色

# 创建
from openpyxl import Workbook 
from datetime import date
from openpyxl.styles import PatternFill
# 实例化
wb = Workbook()
# 激活 worksheet
ws = wb.active
rows = [
    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2020,12, 1), 40, 30, 25],
    [date(2020,12, 2), 40, 25, 30],
    [date(2020,12, 3), 50, 30, 45],
    [date(2020,12, 4), 30, 25, 40],
    [date(2020,12, 5), 25, 35, 30],
    [date(2020,12, 6), 20, 40, 35],
]
for row in rows:
    ws.append(row)

fille = PatternFill('solid',fgColor='fff000')

for i in range(1,ws.max_row+1): #遍历行号
    if i%2==0:  #如果行号除于2余数为0，即为偶数时
        for j in range(1,ws.max_column+1): # 遍历当前行的所有表格
            ws.cell(i,j).fill=fille  #将当前行的每一个表格填充颜色
    i=i+1 #遍历下一行

wb.save('new12.xlsx')

2.9 快速生成工资条

from openpyxl import load_workbook,Workbook

wb = load_workbook('工资数据.xlsx')
ws = wb.active
title = ['工号','姓名','部门','基本工资','提成','加班工资','社保扣除','考勤扣除','应发工资']
for i ,row in enumerate(ws.rows):
    if i == 0:
        continue
    tb = Workbook()
    ts = tb.active
    ts.append(title)
    td = [cell.value for cell in row]
    ts.append(td)
    tb.save(f'./工资/{td[1]}.xlsx')

2.10 快速统计加班时间

# 创建
from openpyxl import Workbook,load_workbook
from datetime import date

from openpyxl.xml.constants import MAX_COLUMN

def create_data():
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    rows = [
        ['日期','姓名', '打卡时间'],
        [date(2020,12,1),'吕小布', '18:50'],
        [date(2020,12,2),'貂的蝉', '18:55'],
        [date(2020,12,3),'刘备', '19:50'],
        [date(2020,12,2),'吕小布', '20:10'],
        [date(2020,12,3),'吕小布', '19:30'],
    ]
    for row in rows:
        ws.append(row)
    wb.save('new13.xlsx')

def statistics():
    wb = load_workbook('new13.xlsx')
    ws = wb.active
    data = []
    for i in range(2,ws.max_row+1):
        t = []
        for j in range(1,ws.max_column+1):
            t.append(ws.cell(i,j).value)
        h,m = t[2].split(':')
        full =  int(h)*60+int(m)
        temp = full - 18*60
        t.append(temp)
        t[0] = t[0].date()
        data.append(t)
    wb2 = Workbook()
    ws2 = wb2.active
    for d in data:
        ws2.append(d)
    wb2.save('new14.xlsx')
if __name__ == "__main__":
    statistics()

2.11 快速查找重复数据

# 创建
from openpyxl import Workbook,load_workbook
from datetime import date

from openpyxl.xml.constants import MAX_COLUMN

def create_data():
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    rows = [
        ['日期','姓名', '打卡时间'],
        [date(2020,12,1),'吕小布', '18:50'],
        [date(2020,12,2),'貂的蝉', '18:55'],
        [date(2020,12,3),'刘备', '19:50'],
        [date(2020,12,2),'吕小布', '20:10'],
        [date(2020,12,3),'吕小布', '19:30'],
    ]
    for row in rows:
        ws.append(row)
    wb.save('new13.xlsx')

def statistics():
    wb = load_workbook('new13.xlsx')
    ws = wb.active
    data = []
    for i in range(2,ws.max_row+1):
        t = []
        for j in range(1,ws.max_column+1):
            t.append(ws.cell(i,j).value)
        h,m = t[2].split(':')
        full =  int(h)*60+int(m)
        temp = full - 18*60
        t.append(temp)
        t[0] = t[0].date()
        data.append(t)
    wb2 = Workbook()
    ws2 = wb2.active
    for d in data:
        ws2.append(d)
    wb2.save('new14.xlsx')
if __name__ == "__main__":
    statistics()

视频链接：

清华教授Python自动化教学，再也不用熬夜做表了_哔哩哔哩_bilibili