使用PythonOpenPyXL模組控制Excel-2

Photo by Mahkeo on Unsplash

接續上次提到的，我們使用OpenPyXL模組來操控Excel的各種編修，這裡繼續說明其它的操控方式。在這篇，會介紹如何讀取工作表裡面的儲存格資料，如何移動儲存格的內容等等。

讀取儲存格中的資料

要如何讀取Excel儲存格裡面的資料？

讀取的方式有兩種，一種是絕對參照，另一種是相對參照。

相對參照

我們可以透過直接指定儲存格的名稱，來讀取裡面的資料。在此之前，我們先建立一個活頁簿，並指定第一個工作表為作用中工作表，程式碼如下：

import openpyxl
wb = openpyxl.Workbook();
# 指定第一個sheet
ws = wb.worksheets[0]

指定工作表之後，我們就可以透過value來指定儲存格的資料。先示範相對名稱，比如說我們想在A1置入56這個數字，在C3置入abc這個字串，可以使用如下的程式碼：

ws["A1"].value = 56
ws["C3"].value = "abc"

印出來看看結果如何：

print(ws["A1"].value)
print(ws["C3"].value)

如果想要讀取既存之Excel活頁簿的工作表，可以使用這個方式：

wb = load_workbook(filename)
worksheets = wb.sheetnames
ws = wb[worksheets[0]]
SheetTitle = ws["A1"].value
print(SheetTitle)

首先，取得活頁簿並且指定要讀取的工作表（上面的例子是第一個工作表）然後指定工作表裡面的某個欄位（上面的例子是A1），取得該欄位的值（value）。最後，把他印出來。

絕對參照

除了使用相對參照來指定外，也可以使用絕對方式來指定儲存格位置。使用絕對方式來指定儲存格有幾種方式：

1.指定絕對row與column的值

ws.cell(row=1, column=1).value = 123
ws.cell(row=3, column=3).value = "345"

可以列出來看看，

print(ws.cell(row=1, column=1).value)
print(ws.cell(row=3, column=3).value)

注意，我們在下面的程式碼使用的是相對參照（A1與C3），顯示出來的結果會一樣：

print(ws["A1"].value)
print(ws["C3"].value)

2.指定絕對座標

也可以用座標的方式來指定儲存格位置，第一格是( 1, 1 )。

ws.cell(1, 1).value = 9999
ws.cell(3,3).value = "Again"

可以列出來看看，結果是否一樣？

print(ws["A1"].value)
print(ws["C3"].value)

此外，我們也可以取得直欄（column）的字母，再進而指定儲存格。要這樣做之前，需要先匯入get_column_letter套件：

from openpyxl.utils.cell import get_column_letter

試著取得第一與第三欄（column）的字母，並且為了方便列印，分別指定給A、B兩個變數：

A = get_column_letter(1)
B = get_column_letter(3)

印出來看看，結果如何？

print(A)
print(B)

答案應該是「Ａ」與「Ｃ」吧。

既然已經取得直欄的代表字母，我們就可以進而取得指定的儲存格位置，並且賦予儲存格數值。例如：我們可以透過下面方式指定A2的值為34。

ws[get_column_letter(1)+"2"].value =34

印出來看看，結果如何？

print(ws["A2"].value)

除了可以取得直欄的代表字母外，我們也可以反向取得固定名稱欄位代表的數字：不過，在此之前需要先載入column_index_from_string套件。

from openpyxl.utils.cell import column_index_from_string

試著取得A欄與HH欄代表的數字，並且為了方便列印，分別指定給C、D兩個變數：

C = column_index_from_string("A")
D = column_index_from_string("HH")

列印看看：

print(C)
print(D)

Offset：儲存格位移方法

可以也使用offset來位移儲存格的位置。首先，建立一個空白活頁簿，並且指定一個工作表。

from openpyxl import Workbook
wb = Workbook()
# 指定第一個sheet
ws = wb.worksheets[0]

在A1儲存格裡面，加入一些數值

ws["A1"].value = "Car"

以A1儲存格為準，向右位移一欄（row位移為0，column位移為1），並加入一些數值

ws.cell(1,1).offset(0,1).value = "Car Park"

印出來看看。

print(ws["A1"].value, ws["B1"].value)

除了採用上面的方式外，也可以這樣子寫：

ws["A1"].offset(0,1).value = "Car Park"

印出來的結果應該是跟上面一樣的。

我們可以指定一個儲存格為起點位置。例如把它命名為mother_cell。其他的儲存格乃基於這個儲存格進行位移。

mother_cell = ws.cell(3,3)
child_cell = mother_cell.offset(0,1)

分別對欄位指定數值：

mother_cell.value = "Mother Mary"
child_cell.value = "John Lennon the son"

讀取一定範圍的儲存格

假定我們有這樣一張工作表：裡面有三欄與三列。

指定一定範圍的儲存格可以用下面方式：

cell_range = ws1['A1':'C3']

這是指，從A1到C3，這個方式與我們平時在Excel裡面使用的方式類似。不過你也可以用這個方式：

cell_range = ws1[get_column_letter(1)+"1":get_column_letter(3)+"3"]

結果是一樣的。我們可以試著用for迴圈把儲存格裡面的值全部印出來。

for cell1, cell2, cell3 in cell_range:
print(cell1.value, cell2.value, cell3.value)

此外，也可以看看cell_range的資料型態：

print(type(cell_range))

你會發現cell_range是個 tupple：

<class 'tuple'>

游移儲存格

我們可以透過下面方式遞迴的讀取一定範圍內工作表儲存格的值。在下面的例子裡，我們讀取的範圍是直欄從第一欄到第10欄、橫列從第一列到第四列所圍起來的範圍區。

for row in range(1,10):
    for col in range(1,5):
        cell = ws.cell(row = row, column = col)
        print(cell.value, end=" ")   
    print()

如果把print(cell.value, end=” “)改成print(cell.coordinate, end=” “)執行結果會如何？

for row in range(1,10):
    for col in range(1,5):
        cell = ws.cell(row = row, column = col)
        print(cell.coordinate, end="    ")
    print()

執行結果如下，顯示出所有的欄位儲存格：

iter_rows

iter_rows可以依照row的順序，一列列的取得資料。使用iter_rows時，要先指定最小與最大值。包含最大（max_col）與最小（min_col）的欄與最大（max_row）、最小（min_row）的列，如下面程式碼：

for row in ws.iter_rows(min_row = 1, min_col = 1, max_col = 3, max_row = 5):
    print(row)

執行結果是個 tuple：

若要進一步想要取得裡面的每個儲存格，可以再利用一組for迴圈，針對每一列裡面的儲存格（cell）進行讀取：

print("iter_rows:")
for row in ws.iter_rows(min_row = 1, min_col = 1, max_col = 10, max_row = 5):
    for cell in row:
        print(cell.coordinate, end = " ")
print("-"*40)

結果：

如果都是從第一欄與第一列開始巡迴的話，可以省略最小欄與最小列。下面的程式碼與上面的結果是一樣的。

print("iter_rows:")
for row in ws.iter_rows(max_col = 10, max_row = 5):
for cell in row:
        print(cell.coordinate, end = " ")
    print()
print("-"*40)

iter_cols

我們也可以依照欄（column）的順序，逐一取得資料。

print("iter_cols:")
for column in ws.iter_cols(max_col = 4, max_row = 3):
    print(column)
print("-"*40)

結果一樣是tuple：

print("iter_cols:")
for column in ws.iter_cols(max_col=4, max_row=3):
    for cell in column:
        print(cell.coordinate, end = " ")
    print()
print("-"*40)

執行結果，欄、列對調了：

max_column與max_row

如果我們可以知道整個工作表裡面有資料的最大的欄與列。就不需要每次都要特別指定工作表儲存格的範圍。

max_row可以用來取得工作表的最大列數、max_column可以取得最大欄數。

ws.max_row
ws.max_column

也可以在每次載入工作表時，顯示最寬的欄數與最長的列數。

print(f"工作表的最大列在第 {ws.max_row} 列，最大欄在第 {ws.max_column} 欄")

下面程式碼可以把所有有值的儲存格印出來：

for row in ws.iter_rows(min_row = 1, max_col= ws.max_column, max_row = ws.max_row):
    for cell in row:
        print("{0: <3}".format(cell.coordinate), end = " ")
print()
print("-"*50)

透過以上的說明，基本上已經可以上面的方式用來操作Excel。不過，如果要再對Excel進行更佳細緻的操作，就還需要更多的方式。下一篇我們再說明其他的操作方式。

相關文章：使用PythonOpenPyXL模組控制Excel-1