Python 處理 Excel

星期五, 10月 01, 2021

Python 處理 Excel

前言

在Python第三方套件中，對於新舊版的Excel文件，有不同的套件。 openpyxl (Excel 2010版本以上)，xlwt (Excel 95 到 2003)。在 Python 中處理 Excel 文件的所有相關套件資料索引，請參閱 : python-excel.org。

openpyxl : openpyxl(外連結) : 安裝指令=> pip install openpyxl ，用於讀取/寫入 Excel 2010版本以上之 xlsx/xlsm/xltx/xltm 文件。
xlwt，xlrd : xlwt(外連結) xlrd(外連結) : 安裝指令=> pip install xlwt ，pip install xlrd 用於讀取/寫入 Excel 95 到 2003版本之 xls 文件。

大綱

openpyxl 模組，讀取、寫入 Excel 檔案

Python 的 openpyxl 模組可用來讀取或寫入 Office Open XML 格式的 Excel 檔案，支援的檔案類型有 xlsx、xlsm、xltx、xltm，以下將示範如何使用 openpyxl 模組來讀取並修改 Excel 檔案。

讀取、寫入 Excel 檔案

若要讀取 Excel 檔案，可以利用 openpyxl 中的 load_workbook 函數：

from openpyxl import load_workbook

# 讀取 Excel 檔案
wb = load_workbook('test.xlsx')

load_workbook 載入 Excel 檔案之後，會得到一個活頁簿（workbook）的物件。若要將活頁簿物件儲存至 Excel 檔案中，則可使用活頁簿的 save 函數：

# 將活頁簿儲存至 Excel 檔案
wb.save('output.xlsx')

建立新活頁簿

除了從 Excel 檔案讀取活頁簿之外，也可以直接在 Python 建立新的活頁簿：

# 建立新活頁簿
wb2 = Workbook()

工作表操作

一本活頁簿中會包含一張或多張工作表（worksheet），我們可以透過活頁簿的 sheetnames 來取的所有工作表的名稱：

# 顯示工作表名稱
print(wb.sheetnames)

或是透過 for 迴圈逐一處理每一張工作表：

# 以 for 迴圈逐一處理每張工作表
for sheet in wb:
    print(sheet.title)

我們可以透過活頁簿的 active 屬性取得目前作用中的工作表：

# 取得目前作用中的工作表
actSheet = wb.active
print(actSheet.title)

我們可以透過工作表的名稱，直接取得指定的工作表：

# 透過名稱取得工作表
sheet = wb['工作表1']

工作表的名稱與顏色也都可以任意修改：

# 更改工作表名稱
sheet.title = "我的工作表"

# 更改工作表標籤顏色
sheet.sheet_properties.tabColor = "1072BA"

若要新增工作表，可以使用活頁簿的 create_sheet 函數：

# 新增工作表（放在最後方）
ws1 = wb.create_sheet("新增工作表1")

# 新增工作表（放在最前方）
ws2 = wb.create_sheet("新增工作表2", 0)

若要複製工作表，可以使用活頁簿的 copy_worksheet 函數：

# 複製工作表
source = wb.active
target = wb.copy_worksheet(source)

單一儲存格操作

假設我們的 Excel 檔案 test.xlsx 內容如下：

若要讀取表格中的內容，可以先載入這張工作表之後，以儲存格位置來存取資料：

範例 pythonExcel-03.py : openpyxl : 單一儲存格操作

# pythonExcel-03.py
from openpyxl import load_workbook

# 讀取 Excel 檔案
wb = load_workbook('test.xlsx')
sheet = wb['工作表1']

# 根據位置取得儲存格
c = sheet['A4']

# 得取儲存格資料
print(c.value)

執行結果

2020-10-04

取得儲存格之後，也可以修改其中的資料：

# 修改儲存格資料
c.value = "2020-06-23 00:00:00"

另外也可以直接使用工作表搭配位置索引來修改儲存格的資料：

# 修改儲存格資料
sheet['A4'] = "2020-06-23 00:00:00"

除了以文字的 Excel 儲存格位置之外，也可以使用行號與列號來指定儲存格：

# 讀取 Excel 檔案
wb = load_workbook('test.xlsx')
sheet = wb['工作表1']

# 以行號、列號指定儲存格
c = sheet.cell(row=4, column=1)
print(c.value)

執行結果

2020-10-04

# 修改儲存格內容
sheet.cell(row=4, column=1, value="2020-06-23 00:00:00")

多儲存格操作

若要一次對指定範圍內的所有儲存格進行操作，可以使用以下幾種方式來指定範圍，取得儲存格物件：

範例 pythonExcel-04.py: openpyxl 多儲存格操作

# pythonExcel-04.py
from openpyxl import load_workbook

# 透過名稱取得工作表
mywb = load_workbook('test.xlsx')
sheet = mywb['工作表1']

# 取得指定範圍內儲存格物件
cellRange = sheet['B2':'C3']

# 以 for 迴圈逐一處理每個儲存格
for row in cellRange:
    for c in row:
        print(c.value)

執行結果

4
34
5
53

以下是其他各種指定範圍的方式：

# 整個 C 欄
colC = sheet['C']

# C 欄與 D 欄
cols = sheet['C:D']

# 第 10 列
row10 = sheet[10]

# 第 5 列到第 10 列
rows = sheet[5:10]

另外也可以使用工作表的 iter_rows 或 iter_cols 來處理：

# 以列（row）方向逐一疊代處理
for row in sheet.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)

# 以行（column）方向逐一疊代處理
for col in sheet.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)

只取得資料

若只需要讀取儲存格中的資料，可以透過工作表的 values 來直接取得資料：

# 只取得儲存格資料
for row in sheet.values:
   for value in row:
       print(value)

工作表的 iter_rows 或 iter_cols 也可以只取用資料：

# 只取得儲存格資料
for row in sheet.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    for value in row:
       print(value)

xlwt 模組，將資料寫入 Excel

安裝指令

pip install xlwt

建立活頁簿

workBookObj = xlwt.Workbook()

建立工作表

workSheetObj = workBookObj.add_sheet(sheet, cell_overwrite_ok=True)

第2個參數設為True，表示可以重設Excel儲存格內容。

將資料寫入儲存格

workSheetObj .write(row, col, data)

表示將 data 寫入工作表(row, col)的位置

範例 pythonExcel-01.py : 將Python 串列資料，儲存為 Excel 檔案 'output-pythonExcel-01.xls'

# pythonExcel-01.py
import xlwt

fileName = 'output-pythonExcel-01.xls'
datahead = ['Phone', 'TV', 'Notebook']
price = ['35000', '18000', '28000']
wworkbook = xlwt.Workbook()
worksheet = wworkbook.add_sheet('sheet1', cell_overwrite_ok=True)
for i in range(len(datahead)):
    worksheet.write(0, i, datahead[i])     # 寫入datahead list
for j in range(len(price)):
    worksheet.write(1, j, price[j])        # 寫入price list

wworkbook.save(fileName)

執行結果

xlrd 模組，讀取 Excel 檔案

安裝指令

pip install xlrd

開啟Excel檔案

workBookObj = xlrd.open_workbook(file, encoding_override='utf-8')

以上指令，讀取檔案 "file" 建立活頁簿物件 workBookObj。注意 : 當內容有中文字，需要設定第二個參數 encoding_override='utf-8' 。

建立工作表物件

workSheetObj = workBookObj.sheets()[index]

以上指令，可以建立工作表物件 workSheetObj

傳回工作表 row數

rows = workSheetObj.nrows

傳回工作表 col數

cols = workSheetObj.ncols

讀取某行 rows 的數據

list_data = workSheetObj.row_values(rows)

範例 pythonExcel-02.py : 讀取 'output-pythonExcel-01.xls' 檔案，並印出。

# pythonExcel-02.py
import xlrd

fileName = 'output-pythonExcel-02.xls'
wb = xlrd.open_workbook(fileName,encoding_override='utf-8')
sh = wb.sheets()[0]
rows = sh.nrows
for row in range(rows):
    print(sh.row_values(row))

執行結果

['Phone', 'TV', 'Notebook']
['35000', '18000', '28000']

參考資料

python-excel.org : 在 Python 中處理 Excel 文件的所有相關套件資料索引。

特色、摘要，Feature、Summary:

關鍵字、標籤，Keyword、Tag:

Excel,Data-Model,Python,Python-Tutorial,