xlwings自动化帮「房东」生成房租单！

龙哥

发布于 2022-04-12 21:32:20

1.3K0

发布于 2022-04-12 21:32:20

文章被收录于专栏：Python绿色通道 Python绿色通道Python绿色通道

需求介绍

房东将整栋楼各房间的应缴房租详情用一个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工作簿。

xlwings本质上只是Windows上Pywin32和Mac上appscript的智能包装，可以通过调用api属性来访问基础对象。

参考文档： 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("程序已经运行结束，回车后确认")