寫入資料到 EXCEL

這篇文章會介紹使用 Python 的 openpyxl 第三方函式庫，新建 Excel 活頁簿或將數據資料寫入 Excel 活頁簿。

快速導覽：

• 安裝 openpyxl
• 建立新 Excel 活頁簿
• 操作 Excel 工作表
• 寫入資料到儲存格

本篇使用的 Python 版本為 3.7.12，所有範例可使用 Google Colab 實作，不用安裝任何軟體 ( 參考：使用 Google Colab )

安裝 openpyxl

輸入下列指令，就能安裝 openpyxl 函式庫，依據個人的作業環境使用 pip 或 pip3 ( Google Colab 和 Anaconda Jupyter 已經內建安裝 openpyxl )。

!pip install openpyxl

建立新 Excel 活頁簿

載入 openpyxl 後，透過 Workbook() 建立空白活頁簿物件，再使用 save 方法儲存為新的 Excel 活頁簿。

import os
os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用

import openpyxl

wb = openpyxl.Workbook()    # 建立空白的 Excel 活頁簿物件
wb.save('empty.xlsx')       # 儲存檔案

如果是使用 load_workbook 方法開啟 Excel 活頁簿，也可利用 save 方法將開啟的檔案儲存為新的 Excel 活頁簿。

import os
os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用

import openpyxl

wb = openpyxl.load_workbook('oxxo.xlsx')    # 開啟現有的 Excel 活頁簿物件
wb.save('new.xlsx')                        # 儲存檔案

操作 Excel 工作表

開啟 Excel 活頁簿後，可以使用 active 屬性取得目前使用的工作表 ( 開啟 Excel 活頁簿時第一個顯示的工作表 )，以及使用字典取值的方法讀取指定名稱的工作表，下方的程式碼執行後，會讀取指定工作表的名稱、最大列數、最大行數以及工作表屬性。

範例使用的 Excel：檔案下載

import os
os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用

import openpyxl
wb = openpyxl.load_workbook('oxxo.xlsx')     # 開啟 Excel 檔案

s1 = wb['工作表1']        # 取得工作表名稱為「工作表1」的內容
s2 = wb.active           # 取得開啟試算表後立刻顯示的工作表 ( 範例為工作表 2 )

print(s1.title, s1.max_row, s1.max_column)  # 印出 title ( 工作表名稱 )、max_row 最大列數、max_column 最大行數
print(s2.title, s2.max_row, s2.max_column)  # 印出 title ( 工作表名稱 )、max_row 最大列數、max_column 最大行數

print(s1.sheet_properties)   # 印出工作表屬性

除了讀取工作表的相關資訊，也可參考下方的程式碼操作工作表：

import os
os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用

import openpyxl
wb = openpyxl.load_workbook('oxxo.xlsx', data_only=True)

s1 = wb['工作表1']                        # 開啟工作表 1
s2 = wb['工作表2']                        # 開啟工作表 2
s1.sheet_properties.tabColor = 'ff0000'  # 修改工作表 1 頁籤顏色為紅色
s2.sheet_properties.tabColor = 'ffff00'  # 修改工作表 2 頁籤顏色為黃色

wb.create_sheet("工作表3")      # 插入工作表 3 在最後方
wb.create_sheet("工作表1.5",1)  # 插入工作表 1.5 在第二個位置 ( 工作表 1 和 2 的中間 )
wb.create_sheet("工作表0", 0)   # 插入工作表 0 在第一個位置

wb.copy_worksheet(s2)          # 複製工作表 2 放到最後方

s1.title='oxxo'                # 修改工作表 1 的名稱為 oxxo
s2.title='studio'              # 修改工作表 2 的名稱為 studio

wb.save('test2.xlsx')

寫入資料到儲存格

能夠開啟工作表之後，透過下列方式，就能將資料寫入儲存格：

• 單一資料

  只要知道單一儲存格的位置，就能將「單一資料」寫入對應的儲存格。

  import os
  os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用
  
  import openpyxl
  wb = openpyxl.load_workbook('oxxo.xlsx', data_only=True)
  
  s1 = wb['工作表1']            # 開啟工作表 1
  s1['A1'].value = 'apple'     # 儲存格 A1 內容為 apple
  s1['A2'].value = 'orange'    # 儲存格 A2 內容為 orange
  s1['A3'].value = 'banana'    # 儲存格 A3 內容為 banana
  s1.cell(1,2).value = 100     # 儲存格 B1 內容 ( row=1, column=2 ) 為 100
  s1.cell(2,2).value = 200     # 儲存格 B2 內容 ( row=2, column=2 ) 為 200
  s1.cell(3,2).value = 300     # 儲存格 B3 內容 ( row=3, column=2 ) 為 300
  
  wb.save('test2.xlsx')
  

  

• 多筆資料

  如果要新增多筆資料，可使用 append 方法，將資料逐筆添加到最後一列 ( 參考 重複迴圈 ( for、while ) )。

  import os
  os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用
  
  import openpyxl
  wb = openpyxl.load_workbook('oxxo.xlsx', data_only=True)
  
  s3 = wb.create_sheet('工作表3')     # 新增工作表 3
  data = [[1,2,3],[4,5,6],[7,8,9]]   # 二維陣列資料
  for i in data:
      s3.append(i)                   # 逐筆添加到最後一列
  
  wb.save('test2.xlsx')
  

  

• 取代資料

  如果要取代某個範圍的資料，可使用迴圈的方法，置換範圍內每個儲存格的內容，或將每個儲存格的內容清空 ( 數值設定 None 表示清空 )。

  import os
  os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用
  
  import openpyxl
  wb = openpyxl.load_workbook('oxxo.xlsx', data_only=True)
  
  s2 = wb['工作表2']        # 開啟工作表 2
  data = [[1,2],[3,4]]     # 二維陣列資料
  for y in range(len(data)):
      for x in range(len(data[y])):
          row = 2 + y      # 寫入資料的範圍從 row=2 開始
          col = 2 + x      # 寫入資料的範圍從 column=2 開始
          s2.cell(row, col).value = data[y][x]
  
  wb.save('test2.xlsx')
  

  

• 設定儲存格公式

  如果要設定儲存格的公式，可以使用字串的方式，將公式寫入儲存格，完成後開啟 Excel，就會自動執行公式。

  import os
  os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用
  
  import openpyxl
  wb = openpyxl.load_workbook('oxxo.xlsx', data_only=True)
  
  s2 = wb['工作表2']
  s2['d1'] = '=sum(a1:c1)'    # 寫入公式
  s2['d2'] = '=sum(a2:c2)'    # 寫入公式
  s2['d3'] = '=sum(a3:c3)'    # 寫入公式
  s2['d4'] = '=sum(a4:c4)'    # 寫入公式
  s2['d5'] = '=sum(a5:c5)'    # 寫入公式
  
  wb.save('test2.xlsx')
  

  

• 設定儲存格樣式

  如果要設定儲存格樣式，可以額外載入 openpyxl.styles 的相關模組 ( 參考 Working with styles )，就能設定儲存格的文字、背景和邊框...等樣式。

  import os
  os.chdir('/content/drive/MyDrive/Colab Notebooks')  # Colab 換路徑使用
  
  import openpyxl
  from openpyxl.styles import Font, PatternFill       # 載入 Font 和 PatternFill 模組
  wb = openpyxl.load_workbook('oxxo.xlsx', data_only=True)
  
  s1 = wb['工作表1']
  s1['e1'].font = Font(name='Arial', color='ff0000', size=30, bold=True) # 設定 g1 儲存格的文字樣式
  s1['f1'].fill = PatternFill(fill_type="solid", fgColor="DDDDDD")       # 設定 f1 儲存格的背景樣式
  
  wb.save('test2.xlsx')
  

  

意見回饋

如果有任何建議或問題，可傳送「意見表單」給我，謝謝～