xlwings自动化帮「房东」生成房租单!
印象Python
共 6618字，需浏览 14分钟
·
2022-03-06 15:27
文 | 酷头
来源：印象python「ID: python_logic」
嗨！大家好，我是酷头
欢迎来到学习python的宝藏基地~~~
长按下方二维码可以添加我为好友哦
目录
- 需求介绍
- 数据示例
- 为每个房间都生成一个excel
- 使用xlwings调整结果
- xlwings简介
- 开始使用
- 设置自动列宽和行高
- 设置边框
- 生成图片
- 完整代码实现
- 打包成exe文件
- 使用效果
需求介绍
房东将整栋楼各房间的应缴房租详情用一个excel表记录了下来，现在需要给每个房间都以图片或excel表形式发送一个房租单。本程序需要做的事就是，将一个excel按照指定字段分组，分解成n个不同的excel文件，然后再对这些excel文件，批量的调整列宽，设置边框，再截图并保存图片。数据示例
import pandas as pd
data = pd.read_excel(r"F:/pandas/item_img/dist/15栋6月单.xlsx", header=1)
data.head()结果如图所示：
为每个房间都生成一个excel
现在我将结果放入result的子文件夹中。for field, df in data.head().groupby('房号'):
print(field)
df.to_excel(f"F:/pandas/item_img/dist/result/{field}.xlsx", index=False)xlwings简介
xlwings包括以下4个模块：- Scripting: 使用接近VBA的语法从Python自动化/与Excel交互。
- Macros: 用干净而强大的Python代码替换VBA宏。
- UDFs: 在Python中编写用户定义函数（UDF）（仅限Windows）。
- REST API: 通过REST API操作Excel工作簿。
https://www.kancloud.cn/gnefnuy/xlwings-docs/1127474
开始使用
先导包：import xlwings as xw
# 打开office的excel组件，设置应用为不可见
app = xw.App(visible=False, add_book=False) # 设置应用,关闭可视化，取消添加book
app为了提升处理速度，设置两个参数：# 将此属性设置为false可在代码运行时抑制提示和警报消息；当消息需要响应时，Excel将选择默认响应。
app.display_alerts = False
# 关闭屏幕更新，可视模式下将无法看到执行情况，需要看的时候重新设置为True即可
app.screen_updating=False上面两个参数，先关闭的警告，让excel自动选择默认的，再关闭了屏幕自动更新，执行效率就提高了。以201房间的房租单为例，演示如何调整excel文件样式。wb = app.books.open("F:/pandas/item_img/dist/result/201.xlsx") # 打开文件
wb读取活动表格：ws = wb.sheets.active
ws获取表格的数据范围：last_column = ws.range('A1').end('right').get_address(0, 0)[0] # 获取最后一列
last_row = ws.range('A1').end('down').row # 获取最后一行
a_range = f'A1:{last_column}{last_row}'
a_range
# 'A1:P2'上述代码获取到201.xlsx的数据范围是A1:P2，事实也是如此。
设置自动列宽和行高
range_val = ws.range(a_range)
range_val.autofit()设置边框
range_val.api.Borders(8).LineStyle = 1# 上边框
range_val.api.Borders(9).LineStyle = 1# 下边框
range_val.api.Borders(7).LineStyle = 1# 左边框
range_val.api.Borders(10).LineStyle = 1# 右边框
range_val.api.Borders(12).LineStyle = 1# 内横边框
range_val.api.Borders(11).LineStyle = 1# 内纵边框生成图片
range_val.api.CopyPicture() # 复制图片区域
ws.api.Paste() # 粘贴
pic = ws.pictures[0] # 当前图片
pic.api.Copy() # 复制图片现在使用Pillow获取剪贴板中的图片并保存起来，没有Pillow库，可以通过pip install Pillow安装。from PIL import ImageGrab
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img结果如图所示：
img.save("F:/pandas/item_img/dist/result/201.png") # 保存图片删除粘贴到excel应用中的图片：pic.delete()保存设置好列宽和边框的excel表：wb.save("F:/pandas/item_img/dist/result/201.xlsx")关闭表格文件：wb.close()退出后台excel应用：app.quit()完整代码实现
import os
import sys
import pandas as pd
import xlwings as xw
from PIL import ImageGrab
def format_group_fields(group_field: str, columns: list):
if group_field isNone:
return columns[0]
if group_field.isdigit():
return columns[int(group_field) - 1]
group_fields = group_field.split("|")
result = []
for group_field in group_fields:
if group_field in columns:
result.append(group_field)
if len(result) == 0:
return columns[0]
else:
return result
def table_cut(data_file_path, group_field, head_line=1, sheet_name=1):
if data_file_path == "":
returnf"请输入要处理的excel文件"
ifnot os.path.exists(data_file_path):
returnf"{data_file_path}不存在，请输入正确的文件名"
print("启动系统默认的Office Excel应用程序")
app = xw.App(visible=False, add_book=False) # 设置应用,关闭可视化，取消添加book
try:
# 默认值为true。将此属性设置为false可在代码运行时抑制提示和警报消息；当消息需要响应时，Excel将选择默认响应。
app.display_alerts = False
# 关闭屏幕更新以加快脚本速度。 将无法看到脚本正在执行的操作，但它将运行得更快。 可在脚本结束时将screen_updating属性设置回True。
app.screen_updating = False
path = os.path.dirname(data_file_path)
result_path = os.path.join(path, "result")
ifnot os.path.exists(result_path):
os.mkdir(result_path)
if isinstance(sheet_name, int):
sheet_name = sheet_name - 1
df = pd.read_excel(data_file_path, header=head_line - 1, sheet_name=sheet_name)
print(f"{data_file_path}读取完毕")
group_fields = format_group_fields(group_field, df.columns)
print("使用的分组字段为:", group_fields)
for field, df_g in df.groupby(group_fields):
if isinstance(field, tuple):
field = '-'.join(field)
print(field)
excel_result_path = f"{result_path}/{field}.xlsx"
df_g.to_excel(excel_result_path, index=False)
print("生成excel文件：", excel_result_path)
wb = app.books.open(excel_result_path) # 打开文件
print("打开该文件", end=",")
try:
ws = wb.sheets.active
last_column = ws.range('A1').end('right').get_address(0, 0)[0] # 获取最后一列
last_row = ws.range('A1').end('down').row # 获取最后一行
a_range = f'A1:{last_column}{last_row}'# 生成表格的数据范围
print(f"该excel文件活动范围是{a_range}", end=",")
range_val = ws.range(a_range)
# 设置自动列宽和行高
range_val.autofit()
print("列宽和行高调整完毕")
# 设置边框
for i in range(7, 13):
range_val.api.Borders(i).LineStyle = 1
print("边框设置完成", end=",")
# 生成图片
range_val.api.CopyPicture() # 复制图片区域
ws.api.Paste() # 粘贴
pic = ws.pictures[0] # 当前图片
pic.api.Copy() # 复制图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img.save(f"{result_path}/{field}.png") # 保存图片
print("截图保存完成", end=",")
pic.delete()
# 保存并关闭 Excel
wb.save(excel_result_path)
print("调整之后的excel文件已保存")
finally:
wb.close()
finally:
app.quit()
if __name__ == "__main__":
msg = """需要输入的字段分别为：
excel文件路径 - 必填项，被处理的excel文件
分组字段 - 默认为第1个字段，同时要使用多个字段分组时可以使用|分割
第几行作为表头 - 默认从第1行开始读取，表头不在第一行时应该指定该参数
sheet名 - 默认为第1张表，对于存在多个sheet的excel文件，可以指定该参数
例如：xxx.xlsx 房号 2 6月
"""
print(msg)
data_file_path, group_field, head_line, sheet_name = "", None, 1, 1
if len(sys.argv) > 1:
data_file_path = sys.argv[1]
if len(sys.argv) > 2:
group_field = sys.argv[2]
if len(sys.argv) > 3:
head_line = int(sys.argv[3])
if len(sys.argv) > 4:
sheet_name = sys.argv[4]
if sheet_name.isdigit():
sheet_name = int(sheet_name)
result = table_cut(data_file_path, group_field, head_line, sheet_name)
if result:
print(result)
input("程序已经运行结束，回车后确认")打包成exe文件
打包成exe文件，可以供没有安装python的windows电脑使用。pyinstaller -F table_cut.py -i a.ico- -F :指定打包为单个exe文件，而不是一个文件夹。
- -i :指定exe文件的图标。
使用效果
往期精彩回顾▼ 全国结婚率连续5年下降，这届年轻人，为什么不敢结婚？最详细Python打包exe教程，并修改图标，30秒搞定！简单实用，Python代码调试利器~抖音上好看的跳舞小姐姐，Python都给你都下载好了!爬虫实战：英雄联盟手游能“干掉”王者荣耀？微博4.3亿网友吵翻了……
分享
收藏
点赞
在看
评论