openpyxl库,1秒合并多张表格并设置图表格式
共 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(r”c:\python”)
os.path.isfile()
os.path.isdir()
os.path.exists()
os.path.dirname()
os.path.abspath()
os.path.basename()
os.system()
os.rename(old,new)
os.makedirs(r”c:\python\test”)
os.mkdir(“test”)
os.stat(file)
os.path.getsize(filename)
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")
打开已经存在目录中的表
除了新建一张空的表,还可以使用命令打开已经存在的表格数据。
#打开一个存在的表
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")
获取已有表格中的数据
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 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个季度的销售数据明细。
这里首先使用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")
各位伙伴们好,詹帅本帅假期搭建了一个个人博客和小程序,汇集各种干货和资源,也方便大家阅读,感兴趣的小伙伴请移步小程序体验一下哦!(欢迎提建议)
推荐阅读
推荐阅读