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

小詹学Python

共 6781字,需浏览 14分钟

 ·

2021-10-12 22:55

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

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

OS模块


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

os.getcwd()

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

os.listdir()

返回指定目录下的所有文件和目录名。
os.remove()
用来删除一个文件。
os.removedirs(rc:\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的sheetsheet=wb.active
#查看sheet名sheet.title
#改sheet名sheet.title="表格一"
#保存表wb.save(r"C:\Users\尚天强\Desktop\测试.xlsx")
通过上面的代码,我们成功在桌面新建了一个名叫测试的工作簿,里面有一张名叫表格一的sheet表。

打开已经存在目录中的表

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

#打开一个存在的表from openpyxl import load_workbookwb2=load_workbook(r"C:\Users\尚天强\Desktop\2017年销售明细\销售明细第1季度.xlsx"")
向表格中写入数据
向空表中写入数据有三种方式,可以依次向单元格填写,也可以按照附加行的方式进行填写,填写的效果如下所示。
from openpyxl import Workbookimport datetime#创建表格wb=Workbook()#获取当前active的sheetsheet=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_workbookwb2=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,Sidefrom openpyxl import load_workbook
wb=load_workbook(r"C:\Users\尚天强\Desktop\测试.xlsx")
#获取当前active的sheetsheet=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_workbookimport 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_workbookfrom 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=25ws.column_dimensions["B"].width=10ws.column_dimensions["C"].width=10ws.column_dimensions["D"].width=13ws.column_dimensions["E"].width=35ws.column_dimensions["F"].width=8ws.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=ftws["B1"].font=ftws["C1"].font=ftws["D1"].font=ftws["E1"].font=ftws["F1"].font=ftws["G1"].font=ft
#保存数据wb.save(r"C:\Users\尚天强\Desktop\[格式调整]数据合并.xlsx")


各位伙伴们好,詹帅本帅假期搭建了一个个人博客和小程序,汇集各种干货和资源,也方便大家阅读,感兴趣的小伙伴请移步小程序体验一下哦!(欢迎提建议)

推荐阅读


牛逼!Python常用数据类型的基本操作(长文系列第①篇)

牛逼!Python的判断、循环和各种表达式(长文系列第②篇)

牛逼!Python函数和文件操作(长文系列第③篇)

牛逼!Python错误、异常和模块(长文系列第④篇)


浏览 19
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报