openpyxl库，1秒合并多张表格并设置图表格式

小小詹同学

发布于 2021-10-20 13:50:19

2.1K0

发布于 2021-10-20 13:50:19

文章被收录于专栏：小詹同学

在日常办公中，我们经常有这样的需求，需要重复的合并表格数据，如果数据表不多，通常复制粘贴就足够了，要是有成百上千的表格需要合并，普通的Ctrl+C、Ctrl+V已经难以实现，那么就要考虑用代码去合并。

这里给大家介绍两个Python库，用于表格的数据合并，以及解决代码合并后，图表样式固定的问题，第一个OS库，用于遍历文件目录，第二个openpyxl库用来操作表格，下面逐一介绍这两个库的功能。

OS模块

这里先介绍OS模块的功能，因为在调用的时候，涉及的参数不多，通常都只有一行代码，总结常用的功能如下。

os.getcwd()

得到当前工作目录，即当前Python脚本工作的目录路径。

os.listdir()

os.remove()

用来删除一个文件。

os.removedirs(r”c:\python”)

删除多个目录。

os.path.isfile()

检验给出的路径是否是一个文件。

os.path.isdir()

检验给出的路径是否是一个目录。

os.path.exists()

检验给出的路径是否真地存在。

os.path.dirname()

获取路径名。

os.path.abspath()

获得绝对路径。

os.path.basename()

获取文件名。

os.system()

运行shell命令。

os.rename(old,new)

重命名。

os.makedirs(r”c:\python\test”)

创建多级目录。

os.mkdir(“test”)

创建单个目录。

os.stat(file)

获取文件属性。

os.path.getsize(filename)

获取文件大小。

openpyxl模块

openpyxl库用于操作表格，功能包括新建一个空的表格、给表格添加数据、导入数据表、遍历数据表内容、合并多个表格以及修改单元格格式等。

创建一个空的表格

如何使用python创建一个空的表格，这里使用openpyxl库，包含创建表格，更改表名以及保存空表。

from openpyxl import Workbook
#创建表格
wb=Workbook()

#获取当前active的sheet
sheet=wb.active

#查看sheet名
sheet.title

#改sheet名
sheet.title="表格一"

#保存表
wb.save(r"C:\Users\尚天强\Desktop\测试.xlsx")

通过上面的代码，我们成功在桌面新建了一个名叫测试的工作簿，里面有一张名叫表格一的sheet表。

打开已经存在目录中的表

除了新建一张空的表，还可以使用命令打开已经存在的表格数据。

#打开一个存在的表
from openpyxl import load_workbook
wb2=load_workbook(r"C:\Users\尚天强\Desktop\2017年销售明细\销售明细第1季度.xlsx"")

向表格中写入数据

向空表中写入数据有三种方式，可以依次向单元格填写，也可以按照附加行的方式进行填写，填写的效果如下所示。

from openpyxl import Workbook
import datetime
#创建表格
wb=Workbook()
#获取当前active的sheet
sheet=wb.active

#方式一：数据可以直接分配到单元格中（可以输入公式）
sheet["A1"]="李明"
sheet["B2"]="小红"
sheet["C3"]="小王"

#方式二：可以附加行，从第一列开始附加（从最下方空白处，最左开始）（可以输入多行）
sheet.append(["张三","李四","王五"])

#方式三：Python类型会被自动转换
sheet["A5"]=datetime.datetime.now().strftime("%Y-%m-%d")

#保存表
wb.save(r"C:\Users\尚天强\Desktop\测试.xlsx")

获取已有表格中的数据

要打开一张表，可以先查看这张表的所有sheet名，对sheet赋值，然后获取对应的值，这里得出一月这张表中A1单元格中的值。

from openpyxl import load_workbook
wb2=load_workbook(r"C:\Users\尚天强\Desktop\2017年销售明细\销售明细第1季度.xlsx")

#查看sheet名
wb2.sheetnames

#获取一月这张表
sheet=wb2.get_sheet_by_name("一月")

#查看第一张表中A1单元格中的值
sheet["A1"].value

'销售日期'

借助for循环导入A1到A5单元格中的值。

#打印一列值
for cell in sheet["A1:A5"]:
     print(cell[0].value)

导入全部数据

如果要导入单元格中的全部数据，需要先遍历所有的行，然后遍历所有的单元格，数据导入结果如下所示。

#打印全部值
for row in sheet:
    for cell in row:
        print(cell.value,end=",")
    print()#换行

导入指定行数据

指定导入数据的行数和列数，这里限定导入前五行和前五列数据，数据结果如下所示。

#遍历指定行,第1行开始至第5行，每行打印5列
for row in sheet.iter_rows(min_row=0,max_row=5,max_col=5):
    for cell in row:
        print(cell.value,end="，")
    print()

导入全部列数据

导入全部的列数据，需要先遍历所有的列，然后遍历所有的单元格，数据导入结果如下。

#遍历全部列
for column in sheet.columns:
    for cell in column:
        print(cell.value,end="，")
    print()

导入指定列数据

导入指定的列数据，在参数中限定最小和最大列数，以及最小和最大行数，数据导入结果如下所示。

#遍历指定几列的数据
for col in sheet.iter_cols(min_col=2,max_col=5,min_row=3,max_row=5):
    for i in col:
        print(i.value,end="，")
    print()

删除工作表

若要删除工作表，有remove和del两种方式。

#删除工作表
#方式一
wb.remove(sheet)

#方式二
del wb[sheet]

设置表格样式

设置表格的样式，通常会设置表格的字体、字号大小、颜色、表格边框、行高、列宽、字体位置等，这部分的参数设置较为琐碎，这里举例常用的几个参数设置，结果如下所示。

#导入包
from openpyxl.styles import Font,colors,Alignment,Border,Side
from openpyxl import load_workbook

wb=load_workbook(r"C:\Users\尚天强\Desktop\测试.xlsx")

#获取当前active的sheet
sheet=wb.active

#设置字体样式
sheet['A1'].font=Font(name='微软雅黑',size=10,italic=False,color=colors.BLUE,bold=True)

#设置A1中的数据垂直居中和水平居中
sheet['A1'].alignment=Alignment(horizontal='center',vertical='center')

#第2行行高
sheet.row_dimensions[2].height=15

#C列列宽
sheet.column_dimensions["C"].width=20

#设置边框
border=Border(left=Side(border_style='medium',color=colors.BLACK),
              right=Side(border_style='medium',color=colors.BLACK),
              top=Side(border_style='medium',color=colors.BLACK), 
              bottom=Side(border_style='medium',color=colors.BLACK), 
              diagonal=Side(border_style='medium',color=colors.BLACK), 
              diagonal_direction=0, 
              outline=Side(border_style='medium',color=colors.BLACK),
              vertical=Side(border_style='medium',color=colors.BLACK), 
              horizontal=Side(border_style='medium',color=colors.BLACK))              

sheet["B4"].border=border   

#保存表
wb.save(r"C:\Users\尚天强\Desktop\测试.xlsx")

分析实例

以上部分，逐一介绍了openpyxl部分库的功能，下面举一个实例进行表格的合并以及格式的设置，如下是2017年的销售明细，包含2017年4个季度的销售数据明细。

且每一个季度销售数据里又包含三个月的具体明细，现在需要批量合并4个工作簿，共计12张表，使用Ctrl+C、Ctrl+V功能已经不能很好的解决这里的问题，需要用代码解决。

这里首先使用os库，循环遍历该文件下的目录，使用openpyxl库循环遍历单元格中的数据，并且导入，合并结果如下，共计合并14283行记录。

from openpyxl import Workbook,load_workbook
import os

def concat_data(file_path,save_path):
    files_name=os.listdir(file_path)
    
    #创建新表格
    new_wb=Workbook()
    new_ws=new_wb.active
    header=['销售日期', '员工工号', '销售员', '货号', '销售单编号', '销量', '销售额']
    new_ws.append(header)
    
    #向新的表格写入数据
    for file_name in files_name:
        wb=load_workbook(file_path+"\\"+file_name)
        for sheet in wb.sheetnames:
            ws=wb[sheet]
            for row in ws.iter_rows(min_row=2,values_only=True):
                new_ws.append(row)
    
    #数据保存
    new_wb.save(save_path+"\\"+"数据合并.xlsx")

concat_data(r"C:\Users\尚天强\Desktop\2017年销售明细",r"C:\Users\尚天强\Desktop")

同时给表格设置固定的图表样式，其中的单元格属性值可以直接进行修改。

from openpyxl import Workbook,load_workbook
from openpyxl.styles import Font,PatternFill,Alignment,Border,fills,colors,Side

#导入表格数据
wb=load_workbook(r"C:\Users\尚天强\Desktop\数据合并.xlsx")

#操作单元格
ws=wb.active

#调整列宽
ws.column_dimensions["A"].width=25
ws.column_dimensions["B"].width=10
ws.column_dimensions["C"].width=10
ws.column_dimensions["D"].width=13
ws.column_dimensions["E"].width=35
ws.column_dimensions["F"].width=8
ws.column_dimensions["G"].width=10

#设置单元格格式
#设置字体格式
font=Font("微软雅黑",size=12,color=colors.BLACK,bold=False)

#单元格颜色填充
fill=PatternFill(fill_type="solid",start_color="CDCDCD",end_color="CDCDCD") #CDCDCD浅灰色

#单元格对齐方式
alignment=Alignment(horizontal="center",vertical="center",indent=0) #wrap_text=True文字换行，shrink_to_fit=True自适应宽度

#单元格边框
bd=Border(left=Side(border_style="thin",color=colors.BLACK),
          right=Side(border_style="thin",color=colors.BLACK),
          top=Side(border_style="thin",color=colors.BLACK),
          bottom=Side(border_style="thin",color=colors.BLACK),
          outline=Side(border_style="thin",color=colors.BLACK),
          vertical=Side(border_style="thin",color=colors.BLACK),
          horizontal=Side(border_style="thin",color=colors.BLACK)
         )

#遍历数据
for irow,row in enumerate(ws.rows,start=1):
    font=font
    fill=fill
    alignment=alignment
    border=bd
    for cell in row:
        cell.font=font
        cell.fill=fill
        cell.alignment=alignment
        cell.border=bd
        
#设置表头字体格式
ft=Font("宋体",size=12,color=colors.BLUE,bold=True)#italic=True斜体
ws["A1"].font=ft
ws["B1"].font=ft
ws["C1"].font=ft
ws["D1"].font=ft
ws["E1"].font=ft
ws["F1"].font=ft
ws["G1"].font=ft

#保存数据
wb.save(r"C:\Users\尚天强\Desktop\[格式调整]数据合并.xlsx")