Automating Excel Rental Statement Generation with Python, pandas, xlwings, and PyInstaller

Requirement Introduction

The landlord records rent details for all rooms in a single Excel workbook and needs to generate a separate rent statement for each room, either as an Excel file or an image.

Data Example

Read the source Excel using pandas:

import pandas as pd
data = pd.read_excel(r"F:/pandas/item_img/dist/15栋6月单.xlsx", header=1)
data.head()

Generate an Excel per Room

Group the data by the "房号" (room number) column and write each group to its own file in a result folder:

for field, df in data.head().groupby('房号'):
    print(field)
    df.to_excel(f"F:/pandas/item_img/dist/result/{field}.xlsx", index=False)

xlwings Overview

xlwings provides four modules for Python‑Excel interaction: Scripting, Macros, UDFs, and REST API. It wraps Pywin32 on Windows and appscript on macOS, exposing Excel objects via an API.

Getting Started

Import xlwings and launch Excel in the background:

import xlwings as xw
app = xw.App(visible=False, add_book=False)

Disable alerts and screen updating to speed up processing:

app.display_alerts = False
app.screen_updating = False

Adjust Column Width and Row Height

Open a generated workbook, determine its used range, and auto‑fit columns and rows:

wb = app.books.open("F:/pandas/item_img/dist/result/201.xlsx")
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}'
range_val = ws.range(a_range)
range_val.autofit()

Set Borders

Apply borders to the range using the underlying COM API:

for i in range(7, 13):
    range_val.api.Borders(i).LineStyle = 1

Generate Image

Copy the range as a picture, paste it back, retrieve the image from the clipboard with Pillow, and save it:

range_val.api.CopyPicture()
ws.api.Paste()
pic = ws.pictures[0]
pic.api.Copy()
from PIL import ImageGrab
img = ImageGrab.grabclipboard()
img.save("F:/pandas/item_img/dist/result/201.png")
pic.delete()

Save and Close

Save the formatted workbook, close it, and quit the Excel application:

wb.save("F:/pandas/item_img/dist/result/201.xlsx")
wb.close()
app.quit()

Full Script

The complete implementation combines argument parsing, data grouping, Excel formatting, image capture, and optional packaging:

import os
import sys
import pandas as pd
import xlwings as xw
from PIL import ImageGrab

# ... (functions format_group_fields and table_cut as shown in the source) ...

if __name__ == "__main__":
    # parse command‑line arguments and call table_cut()
    pass

Packaging as an Executable

Use PyInstaller to create a single‑file executable that can run on Windows machines without Python installed:

pyinstaller -F table_cut.py -i a.ico

Result

The script produces per‑room Excel files with auto‑adjusted layouts, bordered tables, and corresponding PNG images, all ready for distribution.