[分享] 使用 Python 自動產出 Excel 報告(Windows 10)

BooksandCareer
Dec 27, 2019 · 7 min read
Image for post
Image for post
一起用 Python 達成 Auto Reporting!

很久沒產文了,在新工作上任期滿三個月的時間,終於又浮出水面 xD

剛好這陣子在研究怎麼用 Python 產出各部門需要的 Excel 數據報表,便起心動念想試試這個「數據報表自動化」的主題。

因為自動化報告也是自己從手邊工具慢慢摸索,所以若是有更好的做法也歡迎提出 :)


這次設定的任務是:

自動產出部門需要的周報與月報,寄給部門主管

現階段做到的自動化:

自動產出部門需要的周報與月報,未來才會再研究自動發信,並納入自動化程序

自動執行步驟:

  1. 連接數據庫:以 Python 連接 MySQL 上的 Raw Data
  2. 建立資料表:以 Python 生成各個報告需要的 data tables (DataFrame)
  3. 儲存資料表:以 Python 指令 data tables 儲存成 Excel,並按品牌分成多個 sheet
  4. 儲存 Python 指令:在 .ipynb 完成上述步驟後,將其轉為 .py
  5. 建立自動生成指令:建立 Windows 批次檔案 (.bat),讓程式可以自動讀取你的 Python 指令
  6. 指定觸動程序:使用工作排程器,設定何時(如何)觸發 .bat 檔案。如:每天幾點、使用者登入時、中斷連線時等等

使用工具 / 環境:

  1. Windows 10
  2. Jupyter notebook (或是其他 Python 編譯環境)
  3. 工作排程器 (Windows 內建)
  4. 若是要連 MySQL,也請確認電腦已經安裝好 connector 了
  5. 確認電腦已經安裝 Python,可在「命令提示字元」中輸入 python,若有回報 Python 版本,即表示已安裝
Image for post
Image for post

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. 指定觸動程序

首先,先叫出工作排程器,接下來依照下面圖片指示點選項目

Image for post
Image for post
(1) 建立工作
Image for post
Image for post
(2) 輸入名稱
Image for post
Image for post
(3) 到觸發程序頁面,點選新增
Image for post
Image for post
(4) 請依照自己的需求設定,完成點選確定
Image for post
Image for post
(5) 點選確定後,會看到觸發程序頁面多了自己新增的排程
Image for post
Image for post
(6) 到動作分頁,點選新增
Image for post
Image for post
(7) 點選瀏覽,選擇剛剛建立好的 .bat 檔案,完成點選確定
Image for post
Image for post
(8) 回到動作分頁,後面兩個分頁可以依照個人需求去做設定。由於我自己只需要做到這邊初步的設定,若所有設定都完成後,點選確定
Image for post
Image for post
(9) 進入工作排程器程式庫中,便可以看到自己的排程顯示於中間的紅框中

完成上述步驟,排程器將在指定時間(觸發條件)進行,進行時便會跳出命令提示字元開始運行。

這邊也要特別注意,若是原本提供的 Python code 有誤(或是 .bat 內容有誤)導致報錯,程式也將無法繼續運行,也就無法自動產出報告了


備註:因我們家公司沒有使用雲端服務的習慣(簡單來說是刻意為之),所以雲端服務像是 Colab 結合 Google Spread Sheet 的做法,自己沒進一步研究。但雲端服務一定也可以做到這樣的自動化,可能甚至可以直接省去寄信這個步驟,直接開個公開權限資料夾分享上就好 XD


在編輯這次程式碼的過程也發現很多很有趣的小眉角,但礙於完整程式碼因涉及公司機密不能公開,若是大家在 Python code 上面遇到什麼問題也歡迎討論~

另外也做個不負責聲明(?)因為自己是商科背景出身,所以關於 .bat 裡面的指令有問題還請大家自己研究了(壞)!

以上就是這次的分享,對自動發信有興趣的讀者可以看看這篇:

想學怎麼用 Python 編輯 Excel 則點這篇:

最後,若有前輩有更推薦的寫法,也很希望可以在這邊告訴我喔~


主題募集中

為了寫出更有質量、讓大家感興趣的文章,
未來我們的文章主題希望也能讓讀者們共同參與~

希望大家花 2–5 分鐘的時間,幫助我們更了解你們:

Views from BI & PM

A Business Intelligence’s and a PM’s observation from work…

BooksandCareer

Written by

一起透過書籍和過來人的經驗談,更了解職場的處世之道。聯絡我們請至 Facebook(https://www.facebook.com/booksandcareer ) 主題募集中➡️ https://pse.is/survey_v1

Views from BI & PM

A Business Intelligence’s and a PM’s observation from work or even trivial things in daily life will all be shared in this publication!

BooksandCareer

Written by

一起透過書籍和過來人的經驗談,更了解職場的處世之道。聯絡我們請至 Facebook(https://www.facebook.com/booksandcareer ) 主題募集中➡️ https://pse.is/survey_v1

Views from BI & PM

A Business Intelligence’s and a PM’s observation from work or even trivial things in daily life will all be shared in this publication!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store