读取 EXCEL 内容

这篇文章会介绍使用 Python 的 openpyxl 第三方函数库，读取并显示 Office Excel 活页簿内容以及基本信息 ( 工作表名称、最大列数和行数...等 )，最后会利用简单的函数，将读取到的所有内容转换成串列格式。

快速导览：

• 安装 openpyxl
• 读取 Excel 活页簿信息
• 读取储存格内容
• 转换储存格座标与名称

本篇使用的 Python 版本为 3.7.12，所有范例可使用 Google Colab 实作，不用安装任何软件 ( 参考：使用 Google Colab )

安装 openpyxl

输入下列指令，就能安装 openpyxl 函数库，依据个人的作业环境使用 pip 或 pip3 ( Google Colab 和 Anaconda Jupyter 已经内建安装 openpyxl )。

!pip install openpyxl

范例使用的 Excel

下图为范例所使用的 Ecxel 活页簿 ( 范例文件下载 )，工作表 1 的 E1、E2、F1 和 F2 为简单的公式所计算的数值。

读取 Excel 活页簿信息

加载 openpyxl 函数库后，使用 load_workbook 方法打开 Excel 活页簿，就能读取所有工作表的名称以及各个工作表的内容 ( 垂直方向为 row 列，水平方向为 columne 行 )。

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

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

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

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

读取储存格内容

已经能读取 Excel 之后，就能用两种方法读取储存格的内容，第一种方法直接使用字典的方式，读取特定名称的储存格并取出内容，第二种方法使用 cell(row, column) 的方式，读取特定行列的储存格内容，下方的程序码执行后，会读取工作表 1 的 A1 储存格，以及工作表 2 的 B2 储存格。

注意！在 load_workbook 中，使用了 data_only=True 的参数设定，设定 True 表示读取“储存格显示的结果”，也就是若储存格为“公式”，则会回传计算后的结果，如果设定 False ( 默认 ) 表示读取“储存格内容”，若储存格为“公式”，就会回传公式内容而非计算后的结果。

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

import openpyxl
wb = openpyxl.load_workbook('test.xlsx', data_only=True)  # 設定 data_only=True 只讀取計算後的數值

s1 = wb['工作表1']
s2 = wb['工作表2']

print(s1['A1'].value)        # 取出 A1 的內容
print(s1.cell(1, 1).value)   # 等同取出 A1 的內容
print(s2['B2'].value)        # 取出 B2 的內容
print(s2.cell(2, 2).value)   # 等同取出 B2 的內容

如果要一次显示工作表所有的内容，可以定义一个函数，将读取到的数据转换成二维串列的形式 ( 读取到的数据为二维的 tuple 格式 )。

其他参考：函数 function、重复循环 ( for、while )

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

import openpyxl
wb = openpyxl.load_workbook('test.xlsx', data_only=True)  # 設定 data_only=True 只讀取計算後的數值

s1 = wb['工作表1']
s2 = wb['工作表2']

def get_values(sheet):
    arr = []                      # 第一層串列
    for row in sheet:
        arr2 = []                 # 第二層串列
        for column in row:
            arr2.append(column.value)  # 寫入內容
        arr.append(arr2)
    return arr

print(get_values(s1))       # 印出工作表 1 所有內容
print(get_values(s2))       # 印出工作表 2 所有內容

'''
[[12, 34, 56, 78, 180, 180], [11, 22, 33, 44, 110, 110]]
[['a1', 'b1', 'c1'], ['a2', 'b2', 'c2'], ['a3', 'b3', 'c3'], ['a4', 'b4', 'c4'], ['a5', 'b5', 'c5']]
'''

如果只想取出某个范围的数据，可以透过 iter_rows 方法，输入起始 row、columne 以及结束的 row、 column，就能取出范围中的内容。

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

import openpyxl

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

s1 = wb['工作表1']
v = s1.iter_rows(min_row=1, min_col=1, max_col=2, max_row=2)  # 取出四格內容
print(v)
for i in v:
    for j in i:
        print(j.value)
'''
12
34
11
22
'''

转换储存格座标与名称

加载 openpyxl.utils 的 get_column_letter 和 column_index_from_string 模组，就可以将 column 的英文代号转换成数字，或将数字转换成英文代号。

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

print(column_index_from_string('A'))    # 1
print(column_index_from_string('AA'))   # 27

print(get_column_letter(5))             # E
print(get_column_letter(100))           # CV

意见反馈