写入数据到 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')
  

  

意见反馈