[分享] 使用 Python 自動產出 Excel 報告(Windows 10)
很久沒產文了,在新工作上任期滿三個月的時間,終於又浮出水面 xD
剛好這陣子在研究怎麼用 Python 產出各部門需要的 Excel 數據報表,便起心動念想試試這個「數據報表自動化」的主題。
因為自動化報告也是自己從手邊工具慢慢摸索,所以若是有更好的做法也歡迎提出 :)
這次設定的任務是:
自動產出部門需要的周報與月報,寄給部門主管
現階段做到的自動化:
自動產出部門需要的周報與月報,未來才會再研究自動發信,並納入自動化程序
自動執行步驟:
- 連接數據庫:以 Python 連接 MySQL 上的 Raw Data
- 建立資料表:以 Python 生成各個報告需要的 data tables (DataFrame)
- 儲存資料表:以 Python 指令 data tables 儲存成 Excel,並按品牌分成多個 sheet
- 儲存 Python 指令:在 .ipynb 完成上述步驟後,將其轉為 .py
- 建立自動生成指令:建立 Windows 批次檔案 (.bat),讓程式可以自動讀取你的 Python 指令
- 指定觸動程序:使用工作排程器,設定何時(如何)觸發 .bat 檔案。如:每天幾點、使用者登入時、中斷連線時等等
使用工具 / 環境:
- Windows 10
- Jupyter notebook (或是其他 Python 編譯環境)
- 工作排程器 (Windows 內建)
- 若是要連 MySQL,也請確認電腦已經安裝好 connector 了
- 確認電腦已經安裝 Python,可在「命令提示字元」中輸入 python,若有回報 Python 版本,即表示已安裝
I. 連接數據庫
在 Python 連接 MySQL 數據庫,我自己使用的是 mysql.connector,再用 df.to_sql() 讀取並存成 DataFrame,範例如下:
若不需要 ssl,則可以直接拿掉那三行(ssl_ca, ssl_cert, ssl_key)
接下來就坐等數據庫成功連上後,視大家的資料狀況做一下資料清洗
II. 建立資料表
接下來建立資料表則是看大家的需求,建議是可以包成一個 function 之後去跑,之後要改動的話比較不會麻煩:
III. 儲存資料表 & 儲存 Python 指令
因為已經在第二個步驟把所有需要的資料表的 function 寫好了,接下來就是用 for loop 去跑所有品牌的資料表,並將不同品牌放在同一個 xlsx 的不同的 sheet 裡面
完成上述工作後,記得把 .ipynb 轉存為 .py(在 Jupyter Notebook 介面中選擇 File > Download as > Python)
IV. 建立自動生成指令
這個步驟主要是要讓下一個步驟所使用的「工作排程器」讀取指令,去運行剛剛的 Python code(自動生成圖表),所以我們要告訴他:
(1) 讀哪裡的哪份檔案 以及
(3) 事先按裝會用到的 Python Library 即可
cd C:\Users\booksandcareer\Documents\schedule_reportcall pip install XlsxWritercall pip install pandascall pip install mysql-connector-pythoncall python python_automation.pypause
建立 Windows 批次檔案 (.bat) 很簡單,只要開啟空白記事本,輸入上方指令,存檔時手動更改副檔名為 .bat 即可
為確保 Windows 批次檔案可以成功運行,存檔後我們可以先雙擊看看命令提示字元是否開始運行,並且最後順利輸出 Excel 檔案在指定路徑
V. 指定觸動程序
首先,先叫出工作排程器,接下來依照下面圖片指示點選項目
完成上述步驟,排程器將在指定時間(觸發條件)進行,進行時便會跳出命令提示字元開始運行。
這邊也要特別注意,若是原本提供的 Python code 有誤(或是 .bat 內容有誤)導致報錯,程式也將無法繼續運行,也就無法自動產出報告了
備註:因我們家公司沒有使用雲端服務的習慣(簡單來說是刻意為之),所以雲端服務像是 Colab 結合 Google Spread Sheet 的做法,自己沒進一步研究。但雲端服務一定也可以做到這樣的自動化,可能甚至可以直接省去寄信這個步驟,直接開個公開權限資料夾分享上就好 XD
在編輯這次程式碼的過程也發現很多很有趣的小眉角,但礙於完整程式碼因涉及公司機密不能公開,若是大家在 Python code 上面遇到什麼問題也歡迎討論~
另外也做個不負責聲明(?)因為自己是商科背景出身,所以關於 .bat 裡面的指令有問題還請大家自己研究了(壞)!
以上就是這次的分享,對自動發信有興趣的讀者可以看看這篇:
想學怎麼用 Python 編輯 Excel 則點這篇
最後,如果喜歡我的文章,歡迎到新網站持續關注我: