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

共 6618字,需浏览 14分钟

 ·

2022-03-06 15:27

a33a8f596d33d2fd1fc4cca4376dcdb4.webp


文 | 酷头

来源:印象python「ID: python_logic」


嗨!大家好,我是酷头
欢迎来到学习python的宝藏基地~~~
a2a0af4ac5df5b6b4b28c6047767aee7.webp
长按下方二维码可以添加我为好友哦

1b3b727beab25d3d40ff9393d9223f70.webp

目录

  1. 需求介绍
  2. 数据示例
  3. 为每个房间都生成一个excel
  4. 使用xlwings调整结果
  5. xlwings简介
  6. 开始使用
  7. 设置自动列宽和行高
  8. 设置边框
  9. 生成图片
  10. 完整代码实现
  11. 打包成exe文件
  12. 使用效果

需求介绍

房东将整栋楼各房间的应缴房租详情用一个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()
结果如图所示:

2ca579aadc3b09a7a9b0ac782c1c7cd1.webp

为每个房间都生成一个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,事实也是如此。

14d8f95e35861f623242624c2178428c.webp

设置自动列宽和行高

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
结果如图所示:

2bf0e5866565cf939c175c17d0fbeaca.webp

保存图片:
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文件的图标。

使用效果

61d0cb03f2ef39ee3dca87a264b4cc75.webp
往期精彩回顾 全国结婚率连续5年下降,这届年轻人,为什么不敢结婚?最详细Python打包exe教程,并修改图标,30秒搞定!简单实用,Python代码调试利器~抖音上好看的跳舞小姐姐,Python都给你都下载好了!爬虫实战:英雄联盟手游能“干掉”王者荣耀?微博4.3亿网友吵翻了……




c961997fcfdc20b8d05def95f7de746c.webp

分享

2395294c64f29ad2b06b6325b459c961.webp

收藏

6ed80628ba47b140f1de318bdcac492c.webp

点赞

cafe819c57c63d6235b1929e2d17dfd8.webp

在看

浏览 119
点赞
评论
收藏
分享

手机扫一扫分享

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

手机扫一扫分享

分享
举报