學習原因：

進行自動化測試很多時候需要搭配 Excel 的應用，例如利用 Excel 寫 Test Data 再匯入 Test Case，或是針對產品系統進行 匯入 (Import)、匯出 (Export) 的測試等等，因此必須知道如何讀寫 Excel 檔案。

學習目標：

• 認識 Openpyxl 和 Pandas 套件
• 學習產出 Excel 檔案
• 學習讀取 Excel 的資料，並進行資料處理

Openpyxl 和 Pandas 套件

openpyxl 是一個用於操作 Microsoft Excel 文件（以 .xlsx 格式保存的 Excel 文件）的Python套件。可以讀取、編輯和創建Excel文件，使你能夠自動化處理和生成 Excel 數據。
而 Pandas 套件用來作數據處理，蠻常用作處理 Excel 和 SQL 資料表這種二維資料。

Openpyxl 和 Pandas 都是第三方套件，需要自行安裝

pip install pandas
pip install openpyxl

Pandas 的 3 種資料結構：

1. Series 處理一維資料 (1-d Array)
2. DataFrame 處理二維資料 (2-d Array)
3. Panel 處理多維資料 (n-d Array)

圖片來源：https://towardsdatascience.com/numpy-array-cookbook-generating-and-manipulating-arrays-in-python-2195c3988b09

一般處理 Excel 和 Database 的資料，幾乎都是二維資料，因此應用 DataFrame 比較多。所以我會集中說明 DataFrame 的應用。

DataFrame

• 由 列 (Row) 和 行 (Column) 組成
• 每一列可以包含不同類型的數據，但每一行的數據類型必須是相同的。

建立 DataFrame 的方法：

1. 由 dict 所組成的 list 建立 DataFrame:

   每個 List Item 都是 dict ，作為一個 列 (Row) 的資料。

   dict 中的 key 作為 Column 的名稱，而 value 即為 Column 的值。

   每個 List Item 的 dict 資料需要對齊，若 dict 中缺少的 key，該對應的值會自動補為 nan。

   # import 套件是應用 as 可建立 **簡稱 (alias)**, pandas 習慣簡稱為 pd
   import pandas as pd
   
   data = [
           {'a': 1, 'b': 2, 'c': 3}, 
           {'a': 4, 'b': 5, 'c': 6, 'd': 7}
          ]
   
   print("--- DataFrame 1 ---")
   df = pd.DataFrame(data)
   print(df)
   
   print("--- DataFrame 2 ---")
   # index 為列標籤，預設是流水號 0, 1, 2...
   # columns 為行標籤，可定義讀取哪些行資料，也決定顯示的順序
   df2 = pd.DataFrame(data, index=['first', 'second'], columns=['d', 'b', 'a'])
   print(df2)
   

   Output:

   # 第一列的資料，缺了 'd' 的值，會補為 nan
   # nan 的型別為 float，而其他列的同行的型別為 int
   # 由於**同行的型別必須一致**，會取用 int 和 float 相容的型別，因此同行的值都會變為 float。
   --- DataFrame 1 ---
      a  b  c    d
   0  1  2  3  NaN
   1  4  5  6  7.0
   
   --- DataFrame 2 ---
             d  b  a
   first   NaN  2  1
   second  7.0  5  4
   

2. 由 list 所組成的 dict 建立 DataFrame

   同樣 key 為 Column 的名稱，對應的 list 為每一列的值。

   注意： 每個 key 的 list 數量必須一致，否則會出現錯誤。 因此若該欄位沒有資料時，需自行補上 nan 的值，可以填入 None 來生成 nan 值。

   import pandas as pd
   
   print("--- DataFrame 3 ---")
   data = {
   	'drink': ['Beer', 'Coffee', 'Tea'], 
   	'dessert': ['Cake', 'Cookies', 'Pudding']
   }
   df3 = pd.DataFrame(data)
   print(df3)
   
   print("--- DataFrame 4 ---")
   data = {
   	'drink': ['Beer', 'Coffee', 'Tea', None], 
   	'dessert': ['Cake', 'Cookies', 'Pudding', 'Donut']
   }
   df4 = pd.DataFrame(data)
   print(df4)
   
   print("--- DataFrame 5 ---")
   data = {
   	'drink': ['Beer', 'Coffee', 'Tea'], 
   	'dessert': ['Cake', 'Cookies', 'Pudding', 'Donut']
   }
   df5 = pd.DataFrame(data)
   print(df5)
   

   Output:

   --- DataFrame 3 ---
      	drink  dessert
   0    Beer     Cake
   1  Coffee  Cookies
   2     Tea  Pudding
   
   # 在 Python 的世界，所有資料都是物件(object)，這個在後續介紹 class 和 object 會提到。
   # float 和 str 會取 object 為相容型別，因此同行的值都變為 object
   # nan 為 object 時顯示為 None, str 則以原來的形式顯示
   --- DataFrame 4 ---
       drink  dessert
   0    Beer     Cake
   1  Coffee  Cookies
   2     Tea  Pudding
   3    None    Donut
   
   --- DataFrame 5 ---
   ...
   ...
   ValueError: All arrays must be of the same length
   

生成 Excel File

透過 Pandas 可以應用 DataFrame 生成 Excel File，以下為一範例：

把這個學生成續表生成一個 Excel File。

# 應用 Pandas 處理 Excel 的資料時，Openpyxl 套件會被 Pandas 應用
# 因此無須 Import Openpyxl，也能作 Excel 的處理
import pandas as pd

# 以 list 組成 dict 來建 DataFrame 資料，並給予行標籤 (index)
score_df = pd.DataFrame(
    {
        "Reading": [80, 88, 92, 81, 75],
        "Listening": [75, 86, 85, 88, 80],
        "Speaking": [80, 90, 92, 80, 78],
        "Writing": [80, 95, 98, 82, 80]
    },
    index=["Student A", "Student B",  "Student C", "Student D", "Student E"],
)

# 把 DataFrame 生成 Excel 檔案
score_df.to_excel("score_table.xlsx")

在同一個資料夾下，會發現多生成了一個 score_table.xlsx 內容會跟上表一致。

存取及應用 Excel 資料

現在再寫另一個程式檔來讀取 Excel 的資料，想要根據各分卷的權重來計算每位學生的成績。

當中 Reading 佔 20%，Listening 佔 25%，Speaking 佔 30%，Writing 佔 25%。

import pandas as pd

df = pd.read_excel('score_table.xlsx')

# 讀取每一行的 index 和 row 資料
for index, row in score_df.iterrows():
    score = row["Reading"] * 0.2 + row['Listening'] * 0.25 \
            + row['Speaking'] * 0.3 + row['Writing'] * 0.25

    print(f"{index}: {score}")

Output:

Student A: 81.15
Student B: 89.85
Student C: 91.75
Student D: 82.7
Student E: 78.4

讀取 Excel 資料的方法有很多種，可以參考 Pandas 的官方文件。

但要注意 可讀性，盡量避免應用 index 去讀取資料。

for row in df.iloc():
    score = row[2] * 0.2 + row[3] * 0.25 + row[4] * 0.3 + row[5] * 0.25
    print(f"{row['Student']}: {score}")

雖然做到同樣的效果，但 可讀性很低，上例中 index 2, 3, 4, 5 分別代表什麼？ 可能需要打開 Excel 比對一下才知道。另一方面是 可維護性也很差，欄位的位置有變的時候，就有得改了。

為了自己和團隊的著想，盡量用可讀性高的資訊寫程式。

Nan 的處理

讀取 Excel 檔案，蠻常遇到儲存格的資料為空值，在 Python 讀取的時候會取得 nan 的數值。
為了後續的數據處理，或許需要改變為其他型別，以方便統一處理。

針對 DataFrame 內的 nan 值可以作置換 :

# 把 DataFrame 內所有 nan 值換成數值 0，並生成一個新的 DataFrame
new_df = df.fillna(0)
print(new_df)

# 把 DataFrame 內所有 nan 值換成字串 N/A
# 應用 inplace=True 是直接更換原有的 DataFrame，不會生成新的
df.fillna("N/A", inplace=True)
print(df)

小練習：嘗試建一個含有空值的 Excel File，再讀取該 Excel File 的資料，並把空值置換成 0。

以上簡單介紹了 Pandas 針對 Excel 的數據常用的處理方式，日後可以自動生成 Excel File 作匯入資料測試，也可以讀取 Excel File 作匯出資料測試，甚至應用 Excel 列出所有 Test Data 的組合再匯入到自動化測試的 Test Case 來執行，這會是對於開發自動化測試非常實用的技能。

想要知道更多更複雜的應用，需要時可查閱 Pandas 的官方文件。