xlwings自動化幫「房東」生成房租單!

文 | 酷頭

來源：印象python「ID: python_logic」

嗨！大家好，我是酷頭
歡迎來到學習python的寶藏基地~~~

長按下方二維碼可以添加我為好友哦

目錄

1. 需求介紹
2. 數(shù)據(jù)示例
3. 為每個房間都生成一個excel
4. 使用xlwings調(diào)整結(jié)果
5. xlwings簡介
6. 開始使用
7. 設置自動列寬和行高
8. 設置邊框
9. 生成圖片
10. 完整代碼實現(xiàn)
11. 打包成exe文件
12. 使用效果

需求介紹

房東將整棟樓各房間的應繳房租詳情用一個excel表記錄了下來，現(xiàn)在需要給每個房間都以圖片或excel表形式發(fā)送一個房租單。本程序需要做的事就是，將一個excel按照指定字段分組，分解成n個不同的excel文件，然后再對這些excel文件，批量的調(diào)整列寬，設置邊框，再截圖并保存圖片。

數(shù)據(jù)示例

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

結(jié)果如圖所示：

為每個房間都生成一個excel

現(xiàn)在我將結(jié)果放入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中編寫用戶定義函數(shù)（UDF）（僅限Windows）。
• REST API: 通過REST API操作Excel工作簿。

xlwings本質(zhì)上只是Windows上Pywin32和Mac上appscript的智能包裝，可以通過調(diào)用api屬性來訪問基礎對象。參考文檔：
https://www.kancloud.cn/gnefnuy/xlwings-docs/1127474

開始使用

先導包：

import?xlwings?as?xw
#?打開office的excel組件，設置應用為不可見
app?=?xw.App(visible=False,?add_book=False)??#?設置應用,關(guān)閉可視化，取消添加book
app

為了提升處理速度，設置兩個參數(shù)：

#?將此屬性設置為false可在代碼運行時抑制提示和警報消息；當消息需要響應時，Excel將選擇默認響應。
app.display_alerts?=?False
#?關(guān)閉屏幕更新，可視模式下將無法看到執(zhí)行情況，需要看的時候重新設置為True即可
app.screen_updating=False

上面兩個參數(shù)，先關(guān)閉的警告，讓excel自動選擇默認的，再關(guān)閉了屏幕自動更新，執(zhí)行效率就提高了。以201房間的房租單為例，演示如何調(diào)整excel文件樣式。

wb?=?app.books.open("F:/pandas/item_img/dist/result/201.xlsx")??#?打開文件
wb

讀取活動表格：

ws?=?wb.sheets.active
ws

獲取表格的數(shù)據(jù)范圍：

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的數(shù)據(jù)范圍是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#?內(nèi)橫邊框
range_val.api.Borders(11).LineStyle?=?1#?內(nèi)縱邊框

生成圖片

range_val.api.CopyPicture()??#?復制圖片區(qū)域
ws.api.Paste()??#?粘貼
pic?=?ws.pictures[0]??#?當前圖片
pic.api.Copy()??#?復制圖片

現(xiàn)在使用Pillow獲取剪貼板中的圖片并保存起來，沒有Pillow庫，可以通過pip install Pillow安裝。

from?PIL?import?ImageGrab
img?=?ImageGrab.grabclipboard()??#?獲取剪貼板的圖片數(shù)據(jù)
img

結(jié)果如圖所示：

保存圖片：

img.save("F:/pandas/item_img/dist/result/201.png")??#?保存圖片

刪除粘貼到excel應用中的圖片：

pic.delete()

保存設置好列寬和邊框的excel表：

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

關(guān)閉表格文件：

wb.close()

退出后臺excel應用：

app.quit()

完整代碼實現(xiàn)

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("啟動系統(tǒng)默認的Office?Excel應用程序")
????app?=?xw.App(visible=False,?add_book=False)??#?設置應用,關(guān)閉可視化，取消添加book
????try:
????????#?默認值為true。將此屬性設置為false可在代碼運行時抑制提示和警報消息；當消息需要響應時，Excel將選擇默認響應。
????????app.display_alerts?=?False
????????#?關(guān)閉屏幕更新以加快腳本速度。?將無法看到腳本正在執(zhí)行的操作，但它將運行得更快。?可在腳本結(jié)束時將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}'#?生成表格的數(shù)據(jù)范圍
????????????????print(f"該excel文件活動范圍是{a_range}",?end=",")
????????????????range_val?=?ws.range(a_range)

????????????????#?設置自動列寬和行高
????????????????range_val.autofit()
????????????????print("列寬和行高調(diào)整完畢")
????????????????#?設置邊框
????????????????for?i?in?range(7,?13):
????????????????????range_val.api.Borders(i).LineStyle?=?1
????????????????print("邊框設置完成",?end=",")
????????????????#?生成圖片
????????????????range_val.api.CopyPicture()??#?復制圖片區(qū)域
????????????????ws.api.Paste()??#?粘貼
????????????????pic?=?ws.pictures[0]??#?當前圖片
????????????????pic.api.Copy()??#?復制圖片
????????????????img?=?ImageGrab.grabclipboard()??#?獲取剪貼板的圖片數(shù)據(jù)
????????????????img.save(f"{result_path}/{field}.png")??#?保存圖片
????????????????print("截圖保存完成",?end=",")

????????????????pic.delete()
????????????????#?保存并關(guān)閉?Excel
????????????????wb.save(excel_result_path)
????????????????print("調(diào)整之后的excel文件已保存")
????????????finally:
????????????????wb.close()
????finally:
????????app.quit()


if?__name__?==?"__main__":
????msg?=?"""需要輸入的字段分別為：
????????????excel文件路徑?-?必填項，被處理的excel文件
????????????分組字段?-?默認為第1個字段，同時要使用多個字段分組時可以使用|分割
????????????第幾行作為表頭?-?默認從第1行開始讀取，表頭不在第一行時應該指定該參數(shù)
????????????sheet名?-?默認為第1張表，對于存在多個sheet的excel文件，可以指定該參數(shù)
????????????例如：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("程序已經(jīng)運行結(jié)束，回車后確認")

打包成exe文件

打包成exe文件，可以供沒有安裝python的windows電腦使用。

pyinstaller?-F?table_cut.py?-i?a.ico

• -F :指定打包為單個exe文件，而不是一個文件夾。
• -i :指定exe文件的圖標。

使用效果

往期精彩回顧▼?全國結(jié)婚率連續(xù)5年下降，這屆年輕人，為什么不敢結(jié)婚？最詳細Python打包exe教程，并修改圖標，30秒搞定！簡單實用，Python代碼調(diào)試利器~抖音上好看的跳舞小姐姐，Python都給你都下載好了!爬蟲實戰(zhàn)：英雄聯(lián)盟手游能“干掉”王者榮耀？微博4.3億網(wǎng)友吵翻了……

分享

收藏

點贊

在看