Excel × Python

--- tags: Python description: 中興大學資訊科學研習社主題社課 image: 'https://i.imgur.com/iC74nNK.jpg' slideOptions: allottedMinutes: 160 # 預計整份簡報花幾分鐘 --- <style> .markdown-body hr, .markdown-body table br, .slides [title^='*'], .markdown-body .slideONLY, h1 br, .slides .slide, .slides summary h2 {display:none} summary h1{display:inline;border-bottom:0!important} .slides h1 br,.slides .slideONLY{display:inline!important} .slides details p{font-size:66%!important} </style>   ![](https://img.shields.io/badge/dynamic/json?color=aqua&query=%24.viewcount&label=%E8%A7%80%E7%9C%8B%E6%AC%A1%E6%95%B8&suffix=%E6%AC%A1&url=https%3A%2F%2Fhackmd.io%2F5rX9DydFTfq6InGcKu4VdA%2Finfo '*') ###### [@NCHUIT/](/@NCHUIT '*')[Python 教學/](/@NCHUIT/py '*') :::spoiler {state=open}<h1>Excel × Python</h1> <div class='slideONLY'> <i class="fa fa-fw fa-google"></i>meet.google.com/czn-pbav-zvr <i class='fa fa-fw fa-google'></i>colab.research.google.com <i class="fa fa-book"></i> 網頁 md.nchuit.cc/excel/edit?view <i class="fa fa-tv"></i> 簡報 md.nchuit.cc/excel  </div> [ToC] ::: > [name=huixillya, VJ] ---- ## 讀寫檔在程式中開啟檔案，~~眾所周知~~電腦儲存資料的方式是以數位(0與1)的方式儲存。所以我們在對檔案操作的時候，需要注意檔案的編碼。 ---- ### 文字檔編碼介紹 #### [ASCII<small>(點我看wiki)</small>](https://zh.wikipedia.org/wiki/ASCII) 一般英數字會使用的編碼，使用一個 [Byte](https://zh.wikipedia.org/wiki/%E5%AD%97%E8%8A%82)，若使用正整數表示，範圍為 0~255 舉例來說 A = 0100 0001 = 65 B = 0100 0010 = 66 C = 0100 0011 = 67 ---- #### [Big5](https://zh.wikipedia.org/wiki/%E5%A4%A7%E4%BA%94%E7%A2%BC) 一般來說用在繁體中文，尤其在以前系統中最為常見。現已逐漸不被使用 #### [UTF-8](https://zh.wikipedia.org/wiki/UTF-8) [編碼方式](https://www.ibm.com/docs/en/db2/11.5?topic=support-unicode-character-encoding) 現今最常見之文字編碼方式，適用於所有語言，當然啦就是用更多位元來表示一個字。前128個字符與ASCII編碼一一對應，使得原來處理ASCII字元的軟體不太需要修改就可沿用。所以它現在通常是首選的編碼方式。 ---- ### 開啟檔案 [參考 Python 官方文件](https://docs.python.org/zh-tw/3/library/functions.html?highlight=open#open)、[編碼宣告](https://docs.python.org/zh-tw/3/reference/lexical_analysis.html#encoding-declarations) 或 [w3schools](https://www.w3schools.com/python/python_file_handling.asp) 範例 ```python= # -*- coding: utf-8 -*- 檔案 = open('檔名.txt','r') # 檔名.txt 這部分要填你要開啟的檔案名稱(路徑) # r 這個是代表你要開啟的方式 r 代表讀檔 w 代表寫檔檔案 = open('檔名.txt') # 是'r'不填也可以 ``` 指定編碼 ```python= 檔案 = open('檔名.txt', encoding='utf-8') ``` ---- | mode | 說明 | | ------- | ---- | | `w` | 覆寫 (不存在會新建) | | `a` | 續寫 (不存在會新建) | | `r` | 唯讀 (不存在會報錯) | | `x` | 新建 (已存在會報錯) | | `*b` | 二維模式，常用於開圖片(選用) | | `+` | ~~看起來比較屌~~怎樣都以讀寫開檔(選用) | --- 模板 ```python= 檔案 = open('檔案路徑(含名稱)','模式') ``` [路徑](https://zh.wikipedia.org/wiki/%E8%B7%AF%E5%BE%84_(%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%A7%91%E5%AD%A6)) ```python= #相對路徑開啟檔案(同一個資料夾) 檔案 = open('檔名.txt','w') #絕對路徑(完整路徑)開啟檔案: 檔案 = open(r'C:\Users\user\Desktop\檔名.txt','w') ``` ---- ### 讀檔 ```python= [|1-3|5,6|8,9] # 讀取指定大小: 檔案.read(1) #一次讀一個字 (字元) 檔案.read() #一次讀完(剩下的)全部 # 讀一行: 檔案.readline() # 所有行讀成一個串列: 檔案.readlines() ``` ---- 假如 `檔名.txt` 如下 ``` 1 2 3 4 5 6 ``` ---- #### `.read([size])` ###### `read([size])`( `[]`: 選用 )從檔案當前位置起讀取 `size` 個位元組，回傳的是字串，如果沒有引數 `size` 則讀取到檔案的最後為止。 ```python= [|1-4|5-7] 檔案 = open('檔名.txt') 讀取 = 檔案.read() print(讀取) #讀取='1\n2\n3\n4\n5\n6' print(type(讀取)) #看它的資料型態: 字串所有行 = 讀取.splitlines() print(所有行) #所有行=['1','2','3','4','5','6'] print(type(所有行)) #看它的資料型態: 串列 ``` ###### 通常我們都不會給 `size`，直接讀取全部的檔案 ###### `讀取.splitlines()`可拆分成每行內容的串列 ---- #### `.readline()` ###### `readline()`回傳的是字串。從字面意思可以看出，它每次只讀一行，所以讀檔的時後佔用的記憶體空間比較小，適合大檔案。 ```python= 檔案 = open('檔名.txt') 讀取 = 檔案.readline() print(讀取) #讀取=1 print(type(讀取)) ``` ---- 一行一行讀完整個檔案 ```python= 檔案 = open(r'檔名.txt') 讀取 = 檔案.readline() while(讀取 != ''): print(讀取) 讀取 = 檔案.readline() ``` ---- #### `.readlines()` 一次讀完所有行，但包含換行符 ```python= [|1-4|5-7] 檔案 = open('檔名.txt') 讀取 = 檔案.readlines() print(讀取) #讀取=['1\n','2\n','3\n','4\n','5\n','6'] print(type(讀取)) #看它的資料型態: 串列 ``` --- ### 寫檔 ```python= 檔案 = open('檔名.txt','w',encoding='utf-8') ``` 以二維的形式寫入(圖片/影片/多媒體) ```python= 檔案 = open('檔名.png','wb') #多加了一個b ``` 依據副檔名不同我們也可以編輯各種的檔案，例如等下的 Excel 活頁簿 ---- #### 連續寫入 write() ```python= 字串 = '測試' 檔案.write(字串) #放入你要寫入的東西(字串)，會直接接續在檔案後方 ``` 除了字串，多媒體同理，直接餵進去`()`就好，它很能吃 #### 單行寫入 writelines() ```python= 檔案.writelines([字串1,字串2,...]) #一次寫入串列所有字串 ``` ---- ### 存檔&關檔 `.close()` ```python= 檔案.close() #關閉檔案並儲存 ``` 程式跑完都會自動關，中途操作記得要關檔，不然我也不知道會有什麼後果 #### 不想關檔? `with...as...:` ```python= with open('檔名.txt','w') as 檔案: #檔案操作 ``` ---- :::spoiler 練習: `平均數.txt` ```python= [1-14|15-23] # 方法一檔案 = open('myfile.txt') 讀取 = '開始' 總和 = 0 數量 = 0 while 讀取 != '': 讀取 = 檔案.readline() if 讀取 != '': #print(int(讀取)) 總和 = 總和 + int(讀取) 數量 = 數量 + 1 print(f'總和:{總和},數量:{數量},平均數:{總和/數量}') 輸出檔案 = open('平均數.txt','w') 輸出檔案.write(str(總和/數量)) # 方法二檔案 = open('myfile.txt') 讀取 = 檔案.readlines() 數量 = len(讀取) 總和 = 0 for 數字 in 讀取: 總和 += int(數字) print(f'總和:{總和},數量:{數量},平均數:{總和/數量}') 輸出檔案 = open('平均數.txt','w') 輸出檔案.write(str(總和/數量)) ``` ::: 1. 計算[這份文字檔](https://drive.google.com/u/1/uc?id=1wsUFF51XQLkJVFRaWwPtYvLh0QbSB5jo&export=download)裡面所有數字的平均數 2. 生成另一個檔案取名為`平均數.txt`然後儲存該數值提示: 一行一行讀完整個檔案或使用串列檔案路徑為`C:\Users\user\Desktop\*.txt` ![](https://i.imgur.com/bNLsHES.jpg) ---- ### [<i class='fa fa-fw fa-google'></i>colab](https://colab.research.google.com) 丟檔案 ![](https://i.imgur.com/XVCPEFg.png) 每次重新開啟 [<i class='fa fa-fw fa-google'></i>colab](https://colab.research.google.com) 都要重丟 ![](https://i.imgur.com/XgZkf9E.png) ---- 可以考慮丟雲端硬碟然後掛接(點左上那個按鈕) ![](https://i.imgur.com/WYpaND3.png) 之後會要一些權限，同意允許即可 ![](https://i.imgur.com/DhV8QcL.png) ---- 丟到你指定的雲端硬碟資料夾即可每次掛接存取 ![](https://i.imgur.com/aFexVom.png) --- ## 函式庫 openpyxl 更新函式庫目錄&安裝 openpyxl ```bash pip install --upgrade pip pip install openpyxl ``` ### 引用/使用 from where 哪裡? ```python= import sys print(sys.path) ``` 假設你模組安裝在別的地方呢? 使用`sys.path.append(r'你的路徑')` ![](https://i.imgur.com/ByWbcQS.png) ---- ### 新建活頁簿(試算表) ```python= from openpyxl import Workbook 新建活頁簿 = Workbook() #用程式新建 ``` ### 讀取活頁簿 ```python= from openpyxl import load_workbook 檔案路徑 = '檔名.xlsx' 讀取活頁簿 = load_workbook(檔案路徑) ``` ### 查看工作表(分頁) ```python= [3,4] from openpyxl import Workbook 活頁簿 = Workbook() #用程式新建 print(活頁簿.sheetnames) #用程式新建預設會有名為'Sheet'的工作表 ``` ---- ### 工作表操作 ```python= [|3,4|5,6|7,8|9,10] from openpyxl import Workbook 活頁簿 = Workbook() #用程式新建 # 新建工作表工作表 = 活頁簿.create_sheet('工作表') # 讀取工作表工作表 = 活頁簿['工作表'] # 重新命名工作表: 工作表.title = '重新命名工作表' # 刪除工作表: del 活頁簿['Sheet'] ``` ---- ### 讀取儲存格 ```python= [4-6] from openpyxl import Workbook 活頁簿 = Workbook() #用程式新建工作表 = 活頁簿['Sheet'] #依工作表名稱開啟儲存格 = 工作表['A1'] #依欄列讀取儲存格 = 工作表.cell(1,1) #依座標讀取，跟上面等價 print(儲存格.value) #.value 取得儲存格的所有內容 ``` ### 儲存格操作 ```python= [|1,2|3,4|5.6|7,8] #直接替換內容，可以打公式，但只在讀檔時加 data_only=True 才讀得到: 工作表['A1'] = '=1+1' #插入一列: 工作表.append(['A', 'B', 'C']) #按欄(字母)插入一列: 工作表.append({'A':'A[-1]','C':'C[-1]'}) #按欄(數字)插入一列: 工作表.append({1 : 'This is A1', 3 : 'This is C1'}) ``` ---- ### 印出內容 ```python= [4-6] from openpyxl import load_workbook 活頁簿 = load_workbook('活頁簿.xlsx') 工作表 = 活頁簿['Sheet'] for 欄 in 工作表.values: for 儲存格 in 欄: print(儲存格, end=' ') print() ``` 一行印出全部內容 ```python= [2] from openpyxl import load_workbook [[print(儲存格,end=' ') for 儲存格 in 欄]+[print()] for 欄 in load_workbook('活頁簿.xlsx')['Sheet'].values] ``` ### 儲存活頁簿 ```python= [4,5] from openpyxl import Workbook 活頁簿 = Workbook() 工作表 = 活頁簿['Sheet'] 工作表.append(['A', 'B', 'C']) 活頁簿.save(r'C:\Users\user\Desktop\檔名.xlsx') ``` ---- > 只要能開啟 Excel 就能為文書處理帶來無限的可能性 > (真的，想想那些整天 key Excel key 到死的上班族) > 當然 openpyxl 還有非常多的功能，不過就等各位自己去使用了 > [name=@huixillya] *[無限的可能性]: 自動化處理的部分 [參考文章](https://www.itread01.com/content/1528559012.html)、[openpyxl 官方文件-教學(英文)](https://openpyxl.readthedocs.io/en/stable/tutorial.html) ---- :::spoiler 練習: 將下面兩份檔案整合為 `姓名年齡.xlsx` ```python= [|1-4|6-10|12-16|18-24] with open('與會人員名單.txt',encoding='utf-8') as 名單檔案: 讀取第一行 = 名單檔案.readline() 切分姓名清單 = 讀取第一行.split('、') print(切分姓名清單) with open('年齡.txt') as 年齡檔案: 有換行符年齡清單 = 年齡檔案.readlines() 去換行符年齡清單 = [行.rstrip() for 行 in 有換行符年齡清單] # 二選一去換行符年齡清單 = list(map(str.strip, 有換行符年齡清單)) # 二選一 print(去換行符年齡清單) from openpyxl import Workbook 活頁簿 = Workbook() print('打開活頁簿，有工作表:',活頁簿.sheetnames) 工作表 = 活頁簿['Sheet'] # 預設工作表工作表.title = '姓名年齡' # 重新命名工作表姓名清單 = 切分姓名清單年齡清單 = 去換行符年齡清單 for 序號, 姓名 in enumerate(姓名清單): 工作表[f'A{序號+1}'] = 姓名 # +1: enumerate 會從 0 開始 for 序號, 年齡 in enumerate(年齡清單): 工作表[f'B{序號+1}'] = 年齡活頁簿.save('姓名年齡.xlsx') ``` ::: 1. [檔案一](https://drive.google.com/u/1/uc?id=1efNHOpb0dL4SWaxkgoSPsGdza-nr3J4n&export=download) 為姓名 2. [檔案二](https://drive.google.com/u/1/uc?id=1utde7hkUniNGJ74Cd3VoW_XdkFnxkhe9&export=download) 為年齡提示: [<i class='fa fa-fw fa-google'></i>colab](https://colab.research.google.com) 請丟上去 1. 分割用: `讀取名單第一行.split('、')` 2. `[行.rstrip() for 行 in 有換行符年齡清單]` 3. `list(map(str.strip, 有換行符年齡清單))` --- ## [Google 試算表 × Python](https://yanwei-liu.medium.com/%E5%A6%82%E4%BD%95%E9%80%8F%E9%81%8Epython%E5%BB%BA%E7%AB%8Bgoogle%E8%A1%A8%E5%96%AE-%E4%BD%BF%E7%94%A8google-sheet-api-314927f7a601) ---- 1. [新增專案](https://console.cloud.google.com/projectcreate)，初次使用會問候你 ![](https://i.imgur.com/8BbOQP7.png) ---- 輸入專案名稱後按「建立」 ![](https://i.imgur.com/loCczSa.png) ---- 到側邊欄指到「API 和服務」後點選「憑證」 ![](https://i.imgur.com/pBpdpCy.png) ---- 在上方點選「建立憑證」>「服務帳號」 ![](https://i.imgur.com/zu7Oebk.png) ---- 輸入「服務帳號 ID」後直接按下面的「完成」 ![](https://i.imgur.com/Wpmxtdi.png =500x) 得到服務帳號`nchuit@nchuit.iam.gserviceaccount.com` --- 2. 在憑證頁面最下方，點選剛註冊的電子郵件 ![](https://i.imgur.com/IvHcPSJ.png) ---- 上方點選「金鑰」 ![](https://i.imgur.com/ZgY2IpX.png) ---- 點「新增金鑰」>「建立新的金鑰」 ![](https://i.imgur.com/BSqcXXy.png) ---- 「建立」`JSON`金鑰 ![](https://i.imgur.com/2enzyoo.png) ---- ***只能載一次***，會叫你存檔，可以重新命名，記得存哪等等要用，[這邊下載](https://drive.google.com/u/1/uc?id=1jR5Fago53qER6oDjtr_Gbyhc25P-Ab8o&export=download)這個服務帳號的 ![](https://i.imgur.com/yY3bMWK.png) --- 3. [啟用 Google Sheet API](https://console.cloud.google.com/apis/library/sheets.googleapis.com) ![](https://i.imgur.com/2EAKPmR.png) ---- 4. [新建試算表(或打開現有試算表)](https://docs.google.com/spreadsheets/u/0/create)，點右上角的共用 ![](https://i.imgur.com/0bN1Jvc.png) ---- 新建的直接按共用會出現這個對話框，看你要不要命名，不影響 ![](https://i.imgur.com/7bo00Gq.png) ---- 跳到共用對話框，點「新增使用者和群組」輸入框 ![](https://i.imgur.com/iRGgBme.png) ---- 輸入剛剛得到的服務帳號`nchuit@nchuit.iam.gserviceaccount.com` ![](https://i.imgur.com/JDX8p2e.png) ---- 複製試算表網址上的檔案ID ![](https://i.imgur.com/odBGHcO.png) ---- 現在你有:~~冰淇淋~~ 1. [啟用了試算表 API](https://console.cloud.google.com/apis/library/sheets.googleapis.com) 的[專案](https://console.cloud.google.com/projectcreate)服務帳號: `nchuit@nchuit.iam.gserviceaccount.com` 2. 服務帳號金鑰JSON檔，[點我下載](https://drive.google.com/u/1/uc?id=1jR5Fago53qER6oDjtr_Gbyhc25P-Ab8o&export=download)上面服務帳號的 3. [Google 試算表](https://docs.google.com/spreadsheets/d/1mLuYzFZp-zuLn1w8OMAo9XT99kzyMYVd3Zq299FYNlw) ID: `1mLuYzFZp-zuLn1w8OMAo9XT99kzyMYVd3Zq299FYNlw` 搞了這麼多帳號跟檔案處理，我是來寫程式的欸(?) ---- ### 函式庫 gspread 更新函式庫目錄&安裝 gspread, oauth2client(驗證用) ```bash= pip install --upgrade pip pip install gspread oauth2client ``` ---- ### 引用，用金鑰驗證 ```python= 金鑰JSON檔案路徑 = 'nchuit@nchuit.iam.gserviceaccount.com.json' 作用網域 = ['https://www.googleapis.com/auth/spreadsheets'] from google.oauth2.service_account import Credentials 簽證=Credentials.from_service_account_file(金鑰JSON檔案路徑,scopes=作用網域) from gspread import authorize 客戶端 = authorize(簽證) ``` ### 打開試算表 ```python= [8-10] 金鑰JSON檔案路徑 = 'nchuit@nchuit.iam.gserviceaccount.com.json' 作用網域 = ['https://www.googleapis.com/auth/spreadsheets'] from google.oauth2.service_account import Credentials 簽證=Credentials.from_service_account_file(金鑰JSON檔案路徑,scopes=作用網域) from gspread import authorize 客戶端 = authorize(簽證) 試算表ID = '1ptQs-wtOVTvcIbRSpugGQkZCJPHWh8s3GrIJKFitg3Y' # 打開 Google 試算表試算表 = 客戶端.open_by_key(試算表ID) ``` ---- ### 工作表操作 ```python= [|1,2|3,4|5,6|7,8|9,10] # 讀取第一個工作表: 工作表 = 試算表.sheet1 # 讀取工作表: 工作表 = 試算表.get_worksheet('工作表') # 新建工作表: 工作表 = 試算表.add_worksheet('工作表') # 刪除工作表: 工作表 = 試算表.del_worksheet('工作表') # 清空工作表: 工作表.clear() ``` ### 更新儲存格 ```python= 工作表.update_acell('D2', 'ABC') #D2加入ABC 工作表.update_cell(2, 4, 'ABC') #D2加入ABC(顛倒，第2列第4行即D2) 工作表.append_row(['A','B','C']) #寫入一整列(串列) ``` ###### 更多操作見[官方文件](https://docs.gspread.org) ---- :::spoiler 練習: [將上面整合的 `姓名年齡` 寫到下面的試算表裡叫做 `你的暱稱` 的工作表](https://colab.research.google.com/drive/1Js_bLFx0zTTiEmsXM1VhtHIjH2kEPdz7?usp=sharing) ```python= with open('與會人員名單.txt',encoding='utf-8') as 名單檔案: 讀取第一行 = 名單檔案.readline() 切分姓名清單 = 讀取第一行.split('、') print(切分姓名清單) with open('年齡.txt') as 年齡檔案: 有換行符年齡清單 = 年齡檔案.readlines() 去換行符年齡清單 = [行.rstrip() for 行 in 有換行符年齡清單] # 二選一去換行符年齡清單 = list(map(str.strip, 有換行符年齡清單)) # 二選一 print(去換行符年齡清單) 金鑰JSON檔案路徑 = 'nchuit@nchuit.iam.gserviceaccount.com.json' 作用網域 = ['https://www.googleapis.com/auth/spreadsheets'] from google.oauth2.service_account import Credentials 簽證=Credentials.from_service_account_file(金鑰JSON檔案路徑,scopes=作用網域) from gspread import authorize 客戶端 = authorize(簽證) 試算表ID = '1mLuYzFZp-zuLn1w8OMAo9XT99kzyMYVd3Zq299FYNlw' 試算表 = 客戶端.open_by_key(試算表ID) 工作表 = 試算表.add_worksheet('你的暱稱',1000,26) 工作表.append_row(['姓名','年齡']) 姓名清單 = 切分姓名清單年齡清單 = 去換行符年齡清單暫存 = [] for 序號, 姓名 in enumerate(姓名清單): 暫存.append([姓名,年齡清單[序號]]) 工作表.append_rows(暫存) ``` ::: 現在你有:~~冰淇淋~~ 1. [啟用了試算表 API](https://console.cloud.google.com/apis/library/sheets.googleapis.com) 的[專案](https://console.cloud.google.com/projectcreate)服務帳號: `nchuit@nchuit.iam.gserviceaccount.com` 2. 服務帳號金鑰JSON檔，[點我下載](https://drive.google.com/u/1/uc?id=1jR5Fago53qER6oDjtr_Gbyhc25P-Ab8o&export=download)上面服務帳號的 3. [Google 試算表](https://docs.google.com/spreadsheets/d/1mLuYzFZp-zuLn1w8OMAo9XT99kzyMYVd3Zq299FYNlw) ID: `1mLuYzFZp-zuLn1w8OMAo9XT99kzyMYVd3Zq299FYNlw` ![](https://i.imgur.com/lDsAr1h.png)

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.