Python办公自动化【合并单元格-openpyxl、增加图表-openpyxl、合并工作薄-openpyxl、合并多个文件工作薄-openpyxl】(三)-全面详解（学习总结---从入门到深化）

墨蓝 2024-02-17 13:02 14阅读 0赞

### ![b26bb5369f1b426391cd889a5e470675.gif][] ###

**目录**

Excel合并单元格-openpyxl

Excel增加图表-openpyxl

Excel合并工作薄-openpyxl

Excel合并多个文件工作薄-openpyxl

Excel快速生成工资条

Excel一键格行换色

Excel统计加班时间

--------------------

### Excel合并单元格-openpyxl ###

![3e98c974f01949e183cbafc2e1751156.png][]

**常用方法与属性**

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数名&amp;属性&nbsp;</strong></td> 
   <td><strong>含义</strong></td> 
  </tr> 
  <tr> 
   <td>sheet.merge_cells()&nbsp;</td> 
   <td>合并单元格</td> 
  </tr> 
 </tbody> 
</table>

**代码**

from openpyxl import Workbook
    
    def merge_cell():
      '''
     合并单元格
     '''
      wb = Workbook()
      ws = wb.active
    
      # 合并操作 A1:A5 合并一列
      ws.merge_cells('A1:A5')
      # 合并操作 A1:A5 合并一行
      ws.merge_cells('c1:h1')
      # 合并操作 A1:A5 合并多行,多列
      ws.merge_cells('c4:h8')
      # 注意：从小到大,从上到下,从前到后
      # 保存数据
      wb.save('./create_data/17_合并单元格.xlsx')
    
    if __name__ =="__main__":
      merge_cell()

### Excel增加图表-openpyxl ###

![4dd8522d64dc44db81bb5fb1c2d28cbd.png][]

**图表的作用:**

1、可直观展示统计信息属性(时间性、数量性等),对知识挖掘和信息直观生动感受起关键作用的图形结构,是一种很好的将对象属性数据直观、形象地"可视化"的手段

2、合理的数据图表,会更直观的反映数据间的关系,比用数据和文字描述更清晰、更易懂

3、将工作表中的数据转换成图表呈现,可以帮助我们更好地了解数据见的比例关系及变化趋势,对研究对象做出合理的推断和预测

**常用方法与属性**

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数名&amp;属性&amp;类&nbsp;</strong></td> 
   <td><strong>含义</strong></td> 
  </tr> 
  <tr> 
   <td>openpyxl.chart.LineChart()&nbsp;</td> 
   <td>折线图</td> 
  </tr> 
  <tr> 
   <td>openpyxl.chart.BarChart()&nbsp;</td> 
   <td>柱状图</td> 
  </tr> 
  <tr> 
   <td>openpyxl.chart.PieChart()&nbsp;</td> 
   <td>饼状图</td> 
  </tr> 
  <tr> 
   <td>openpyxl.chart.Reference(workbook,min_col,min_row,max_col,max_row)</td> 
   <td>设置图表数据的来源<br> workbook数据来源工作薄<br> min_col 开始列<br> min_row 开始行<br> max_col 结束列<br> max_row 结束行</td> 
  </tr> 
  <tr> 
   <td>chart.title&nbsp;</td> 
   <td>设置图表名</td> 
  </tr> 
  <tr> 
   <td>chart.x_axis.title&nbsp;</td> 
   <td>设置x轴名</td> 
  </tr> 
  <tr> 
   <td>chart.y_axis.title&nbsp;</td> 
   <td>设置y轴名</td> 
  </tr> 
  <tr> 
   <td>chart.add_data(data)&nbsp;</td> 
   <td>设置图表数据</td> 
  </tr> 
  <tr> 
   <td>sheet.add_chart(图表,位置)</td> 
   <td>给excel增加图表</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

**代码  
折线图**

def set_line():
      from openpyxl import Workbook
      from datetime import date
      # 创建一个excel文件
      wb = Workbook()
      # 激活工作簿
      sh = wb.active
      # 设置数据
      rows=[
       ['时间','批次1','批次2','批次3'],
       [date(2030,1,1),40,30,25],
       [date(2030,1,2),40,25,30],
       [date(2030,1,3),50,30,45],
       [date(2030,1,4),30,25,40],
       [date(2030,1,5),25,35,35],
       [date(2030,1,6),20,40,35],
     ]
      # 把数据增加到工作薄里
      for r in rows:
        sh.append(r)
      # 创建图表
      from openpyxl.chart import LineChart,Reference
      chart = LineChart()
      # 给图表选择数据
      data = Reference(sh,min_col=2,min_row=1,max_col=4,max_row=7)
      # 给图表增加数据
      chart.add_data(data)
      # 设置图表的名称
      chart.title = 'Line Chart'
      # 设置图表的x轴名
      chart.x_axis.title = '时间'
      # 设置图表的y轴名
      chart.y_axis.title = '数量'
    
    
      # 把图表增加到工作簿
      sh.add_chart(chart,'A9')
      # 保存excel文件
      wb.save('./create_data/18_折线图.xlsx')
    
    if __name__ =='__main__':
      set_line()

**柱状图**

def set_bar():
      from openpyxl import Workbook
      # 创建一个excel文件
      wb = Workbook()
      # 激活工作簿
      sh = wb.active
       # 设置数据
      rows =[
       ('月份','批次1','批次2'),
       (7,10,30),
       (8,40,60),
       (9,60,70),
       (10,20,30),
       (11,50,40),
       (12,10,50),
     ]
      # 把数据增加到工作薄里
      for r in rows:
        sh.append(r)
      # 创建图表
      from openpyxl.chart import BarChart,Reference
      chart = BarChart()
      # 给图表选择数据
      data = Reference(sh,min_col=2,max_col=3,min_row=2,max_row=7)
      cats = Reference(sh,min_col=1,max_col=1,min_row=2,max_row=7)
      # 给图表增加数据
      chart.add_data(data)
      chart.set_categories(cats)
      # 设置图表的名称
      chart.title = 'Bar Chart'
      # 设置图表的x轴名
      chart.x_axis.title = '月'
      # 设置图表的y轴名
      chart.y_axis.title = '数量'
      # 把图表增加到工作簿
      sh.add_chart(chart)
      # 保存excel文件
      wb.save('./create_data/19_柱状图.xlsx')
    
    if __name__ =='__main__':
      set_bar()

**饼状图**

from cProfile import label
    
    def set_pie():
      from openpyxl import Workbook
      # 创建一个excel文件
      wb = Workbook()
      # 激活工作簿
      sh = wb.active
      # 设置数据
      rows = [
       ('名称','数值'),
       ('苹果',50),
       ('草莓',30),
       ('椰子',10),
       ('荔枝',40),
     ]
      # 把数据增加到工作薄里
      for r in rows:
        sh.append(r)
      # 创建图表
      from openpyxl.chart import PieChart,Reference
      chart = PieChart()
      # 给图表选择数据
      data = Reference(sh,min_col=2,max_col=2,min_row=2,max_row=5)
      cate = Reference(sh,min_col=1,min_row=2,max_row=5)
      # 给图表增加数据
      chart.add_data(data)
      chart.set_categories(cate)
      # 设置图表的名称
      chart.title = 'Pie Chart'
      # 把图表增加到工作簿
      sh.add_chart(chart)
      # 保存excel文件
      wb.save('./create_data/20_饼状图.xlsx')
    
    if __name__ =='__main__':
      set_pie()

###  ###

### Excel合并工作薄-openpyxl ###

![9b4525ac048842adb5f5978b13dfda7a.png][]

**代码**

from openpyxl import load_workbook
    
    def merger_file():
      from openpyxl import Workbook,load_workbook
      # 创建一个新的excel文件，用来存储合并好的数据
      wb = Workbook()
      # 激活当前的Sheet
      sh = wb.active
      # 获取文件
      import os
      names = os.listdir('./base_data/销售表')
      for name in names:
        load_path = f'./base_data/销售表/{name}'
        # 读取文件
        tmp_wb = load_workbook(load_path)
        # 激活读取的文件的Sheet
        tmp_sh = tmp_wb.active
        # 获取里面的数据
        for r in range(2,tmp_sh.max_row+1):
          # 建立一个列表来保存数据
          row_data = []
          for c in range(1,tmp_sh.max_column+1):
            # 通过行和列的索引获取数据
            value = tmp_sh.cell(r,c).value
            # 将当前列的数据增加到一行中记录
            row_data.append(value)
          # 将当前行数据增加到新的excel中
          sh.append(row_data)
      # 保存
      wb.save('./create_data/21_合并多个文件.xlsx')
    
    if __name__ == '__main__':
      merger_file()

### Excel合并多个文件工作薄-openpyxl ###

![b5881a812782433fb9431b6719e441c1.png][]

**代码**

def merger_file():
      from openpyxl import Workbook,load_workbook
      # 创建一个excel文件，用来保存合并的数据
      wb = Workbook()
      # 获取要合并的文件的文件名
      import os
      names = os.listdir('./base_data/销售表')
      # 遍历文件名，依次取出
      for name in names:
        # 拼接excel完整地址
        path = f'./base_data/销售表/{name}'
        # 获取新的创建的Sheet的名称
        sheet_name = name[:-5]
        # 加载要合并的excel文件
        tmp_wb = load_workbook(path)
        # 激活要合 并的excel文件的Sheet
        tmp_sh = tmp_wb.active
        # 在新的excel中创建一个新的Sheet，名称为合并的文件名
        new_sh = wb.create_sheet(sheet_name)
        # 获取行数据
        for r in range(1,tmp_sh.max_row+1):
          # 定义一个列表用来存储当前行的所有数据
          all_data = []
          # 获取列数据
          for c in range(1,tmp_sh.max_column+1):
            value = tmp_sh.cell(r,c).value
            all_data.append(value)
         # 将数据保存到新的excel中
          new_sh.append(all_data)
      # 删除自动生成的Sheet
      del wb['Sheet']
      # 保存新Excel文件修改
      wb.save('./create_data/22_合并多个文件2.xlsx')
    
    if __name__ == '__main__':
      merger_file()

### Excel快速生成工资条 ###

![263a55ab95904a2fb2fd89aa2400765c.png][]

**代码**

def create_excel():
      from openpyxl import load_workbook,Workbook
      # 加载文件,data_only=True代表显示值
      wb = load_workbook('./base_data/工资数据.xlsx',data_only=True)
      sh = wb.active
      # 读取数据-遍历每行数据
      # 建立一个title列表，用于存储标题
      title = []
      for i,r in enumerate(sh.rows):
        # 判断是否第一行数据
        if i == 0:
          for c in r:
            title.append(c.value)
        else:
          row_data=[]
          for c in r:
            row_data.append(c.value)  
      
          # 创建一个新的excel文件
          new_wb =  Workbook()
          # 激活Sheet
          new_sh = new_wb.active
          # 增加数据
          new_sh.append(title)
          new_sh.append(row_data)
          # 保存数据
        
     new_wb.save(f'./create_data/gong_zi/{r[1].value}.xlsx')
    if __name__ == '__main__':
      create_excel()

###  ###

### Excel一键格行换色 ###

![9ed483675e894d7fadedd917146a5e51.png][]

**代码**

def create_excel():
      from datetime import date
      from openpyxl import Workbook
      from openpyxl.styles import PatternFill
      # 创建一个excel对象
      wb = Workbook()
      # 激活Sheet
      sh = wb.active
      # 增加数据
      rows = [
       ['Date', 'Batch 1', 'Batch 2','Batch 3'],
       [date(2030,12, 1), 40, 30, 25],
       [date(2030,12, 2), 40, 25, 30],
       [date(2030,12, 3), 50, 30, 45],
       [date(2030,12, 4), 30, 25, 40],
       [date(2030,12, 5), 25, 35, 30],
       [date(2030,12, 6), 20, 40, 35],
     ]
      for r in rows:
       sh.append(r)
      # 创建样式
      bg_color = PatternFill('solid',fgColor='AEEEEE')
      # 设置样式
      for r in range(1,sh.max_row+1):
        if r%2==0:
          for c in range(1,sh.max_column+1):
            sh.cell(r,c).fill = bg_color
      # 保存excel文件
      wb.save('./create_data/23_隔行换色.xlsx')
    
    if __name__ == '__main__':
      create_excel()

###  ###

### Excel统计加班时间 ###

![a6ca4f9600c44e1696821c3e3994c0b7.png][]

**代码**

def create_excel():
      from datetime import date
      from openpyxl import Workbook
      # 创建excel对象
      wb = Workbook()
      # 激活数据
      sh = wb.active
      # 增加数据
      rows = [
       ['Date', '姓名', '打卡时间'],
       [date(2030,12, 1), '吕小布','18:50'],
       [date(2030,12, 2),'貂的蝉','18:10'],
       [date(2030,12, 3),'刘备','18:02'],
       [date(2030,12, 4),'吕小布','18:50'],
       [date(2030,12, 5), '张飞','19:22'],
       [date(2030,12, 6), '吕小布','18:50'],
     ]
      for row in rows:
        sh.append(row)
      wb.save('./create_data/24_统计加班时间.xlsx')
    
    def statistics():
       from openpyxl import load_workbook,Workbook
      # 读取数据
      wb = load_workbook('./create_data/24_统计加班时间.xlsx')
      sh = wb.active
      # 统计
      data = []
      for row in range(2,sh.max_row+1):
        # 一条完整的数据
        row_data = []
        for col in range(1,sh.max_column+1):
          value = sh.cell(row,col).value
          row_data.append(value)
        # 统计时间
        # 获取时间
        h,m = row_data[2].split(':')
        # 计算时间 从0:00到打卡的时间
        full = int(h)*60 + int(m)
        rs = full - 18*60
        # 将结果保存到最后一列
        row_data.append(rs)
        #------处理时间 显示问题------
        row_data[0] = row_data[0].date()
     
        data.append(row_data)
     
      # 保存
      wb = Workbook()
      sh = wb.active
      for d in data:
        sh.append(d)
      wb.save('./create_data/24_统计加班时间.xlsx')
    
    if __name__ == '__main__':
      create_excel()
      statistics()

[b26bb5369f1b426391cd889a5e470675.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/2c7ba20c04b6453cb9320c243e11c206.png
[3e98c974f01949e183cbafc2e1751156.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/1423a76db4db4634928ae5573579d341.png
[4dd8522d64dc44db81bb5fb1c2d28cbd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/dea5b6074fd0446f9790d14fcbf269c2.png
[9b4525ac048842adb5f5978b13dfda7a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/584c530fad9e401b80316fd1226e19e6.png
[b5881a812782433fb9431b6719e441c1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/dfaf716c0787477aa521aa925fddde85.png
[263a55ab95904a2fb2fd89aa2400765c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/f0d0563dc2434e64a0be4175c62c4f95.png
[9ed483675e894d7fadedd917146a5e51.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/e991cc3cf47f429cabef3c66010468bc.png
[a6ca4f9600c44e1696821c3e3994c0b7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/67a2ad9c06f94fea998bacdd3181d6d5.png