又是一陣拖更,今天我們來討論一下如何用Python更新Excel檔案吧!
許多公司在做報表的時候,都會需要週期性地將原始資料丟入Excel Dashboard 中,讓寫好的Excel Dashboard 去吃新丟進來的資料,再重新整理, Dashboard就能夠呈現最新的資料了。
然而週期性的手動複製新資料、刪除上次的raw data、貼上新資料到Dashboard的其他分頁中,非常的耗時且人工,有時候還會因為以前的資料沒刪乾淨而導致最終呈現的 Dashboard 出現錯誤。因此如果能夠使用Python或是其他工具來自動化這一個步驟那真是太好不過了!
本文將說明如何使用 Python 中的 xlwings 套件進行 Excel 資料的抽換,希望能幫助讀者們在熟悉這個方法後,在進行Dashboard 的維護上可以更加順利。
📕 補充:我想在 Python 中最常見於操作 Excel的套件是 openpyxl
,不過由於 openpyxl
是一行一行的讀取數據,在操作大型檔案的處理速度會變得非常慢,於是在嘗試其他方法後 xlwings
是我覺得處理起來最直觀也方便的。
📍xlwings的官網說明在此:https://docs.xlwings.org/zh_TW/latest/index.html
首先我們先在終端機中安裝此套件:
pip install xlwings
接著我們進入 Python 程式中:
import xlwings as xw
wb = xw.Book() # 開啟一個新的 Excel file
wb = xw.Book('Dashboard.xlsx') # 連結檔案(與python檔案在同一個資料夾中)
wb = xw.Book(r'C:\path\to\file.xlsx') # 使用路徑連結檔案(記得加r在路徑前)
App
在xlwings 中可以想像成文件管理器,幫助 Excel 被關閉與正確地重新整理
# visible=True 表示能看見python自動打開excel, add_book表示是否自動創建 Excel file
app=xw.App(visible=True,add_book=False)
# 開啟 Excel file
wb=app.books.open(r'C:\path\to\file.xlsx')
# 選擇 Excel file 中的工作表
sheet=wb.sheets('sheet1')
# 清空工作表內容
sheet.clear_contents()
# 從 A1 格開始貼上整理好的 dataframe 的內容 (如 raw data)
sheet.range('A1').options(index=False).value=df1
# 在Excel上按下重新整理,確保pivot有跟著跑
wb.api.RefreshAll()
# 將檔案儲存成 test.xlsx
wb.save('test.xlsx')
#關閉檔案
wb.close()
# 關閉App
app.quit
在執行這個段落時,我們也能看到 Excel 檔案被自動打開, sheet1 被抽換成df1的資料。
這邊要特別提醒的是,在寫完一個段落記得以wb.close()
關閉Excel檔案,否在在電腦背景會一直run這個excel檔案,甚至用滑鼠重新打開檔案時,檔案會顯示「Read-only」而無法編輯原始檔案。
若已經遇到檔案只能以「Read only」打開時,建議可以將所有的 Excel檔案關閉,以Windows操作的情境下,可以打開「Task Manager」找到有Excel檔案的圖示,按下「End Task」來關閉運行中的 Excel 檔案。
總結希望以以上這個簡單的段落為基礎,讀者們可以將其發展成各種 Dashboard 的自動化更新!
📍更多內容可以參考知乎大神寫的系列文章(寫得非常清楚):https://zhuanlan.zhihu.com/p/120415076
對於Python其他應用有興趣的話,也可參考下列文章: