Python Openpyxl 與 EZSheets Google Sheets 操作

– 13 分鐘閱讀

Python 提供了多種函式庫來操作試算表，其中 openpyxl 適用於處理 Excel 檔案（.xlsx），而 EZSheets 則專為 Google Sheets 設計。openpyxl 允許讀取和修改 Excel 檔案的內容，包含單元格值、公式、樣式等。透過 openpyxl，開發者可以自動化 Excel 報表生成、資料分析等任務。EZSheets 則簡化了 Google Sheets 的操作，提供了 Python 介面來存取和修改 Google Sheets 資料，包含建立新試算表、讀取和寫入資料、管理試算表和工作表等。對於需要與 Google Sheets 整合的應用程式，EZSheets 提供了便捷的解決方案。

6. 如何設定 C5 單元格的值為 “Hello”？

可以使用 sheet['C5'].value = 'Hello' 來設定 C5 單元格的值為 “Hello”。

7. 如何檢索單元格的行和列號？

可以使用 cell.row 和 cell.column 來檢索單元格的行和列號。

8. sheet.max_column 和 sheet.max_row 工作表屬性包含什麼？

sheet.max_column 和 sheet.max_row 工作表屬性包含工作表中的最大列號和最大行號。

9. 如果需要取得列 ‘M’ 的整數索引，需要呼叫什麼函式？

需要呼叫 openpyxl.utils.column_index_from_string('M') 函式來取得列 ‘M’ 的整數索引。

10. 如果需要取得第 14 行的字串名稱，需要呼叫什麼函式？

需要呼叫 openpyxl.utils.get_column_letter(14) 函式來取得第 14 行的字串名稱。

11. 如何檢索 A1 至 F1 的所有單元格物件？

可以使用 sheet['A1:F1'] 來檢索 A1 至 F1 的所有單元格物件。

12. 如何儲存工作簿到檔案 example3.xlsx？

可以使用 wb.save('example3.xlsx') 來儲存工作簿到檔案 example3.xlsx。

13. 如何設定單元格的公式？

可以使用 sheet['C5'].value = '=A1+B1' 來設定單元格的公式。

14. 如果需要檢索單元格公式的結果，而不是公式本身，需要先做什麼？

需要先呼叫 wb.data_only = True 來設定工作簿只讀取資料，不讀取公式。

15. 如何設定第 5 行的高度為 100？

可以使用 sheet.row_dimensions[5].height = 100 來設定第 5 行的高度為 100。

16. 如何隱藏 C 列？

可以使用 sheet.column_dimensions['C'].hidden = True 來隱藏 C 列。

17. 什麼是凍結窗格？

凍結窗格是指在 Excel 中，可以凍結某些列或行，使其不會滾動，而其他列或行可以滾動。

18. 建立條形圖需要呼叫哪五個函式和方法？

需要呼叫 openpyxl.chart.Reference、openpyxl.chart.BarChart、openpyxl.chart.Series、openpyxl.chart.Axis 和 sheet.add_chart 函式和方法來建立條形圖。

使用 EZSheets 存取 Google Sheets

要使用 EZSheets 存取 Google Sheets，首先需要建立一個 Google Cloud 專案。這個專案將用於存取您的 Google Sheets 資料。

建立 Google Cloud 專案

  1. 前往 Google Cloud Console 並登入您的 Google 帳戶。
  2. 點選「選擇專案」按鈕，然後點選「新建專案」。
  3. 輸入您的專案名稱和選擇位置，然後點選「建立」按鈕。

啟用 Google Sheets 和 Google Drive API

  1. 在導航側邊欄中，點選「API 和服務」>「函式庫」。
  2. 搜尋「Google Sheets API」和「Google Drive API」，然後點選啟用它們。

設定 OAuth 同意畫面

  1. 在導航側邊欄中，點選「API 和服務」>「OAuth 同意畫面」。
  2. 選擇「外部」並點選「建立」按鈕。
  3. 輸入您的應用程式名稱和電子郵件地址，然後點選「儲存並繼續」按鈕。
  4. 在「範圍」頁面中，選擇「…/auth/drive」和「…/auth/spreadsheets」範圍，然後點選「更新」按鈕。
  5. 在「測試使用者」頁面中，新增您的 Gmail 電子郵件地址，然後點選「儲存並繼續」按鈕。

建立憑證

  1. 在導航側邊欄中，點選「API 和服務」>「憑證」。
  2. 點選「建立憑證」按鈕，然後選擇「OAuth 使用者端 ID」。
  3. 選擇「桌面應用程式」作為應用程式型別，然後點選「建立」按鈕。
  4. 下載憑證 JSON 檔案，並將其儲存在您的 Python 指令碼目錄中。

登入憑證檔案

  1. 執行 Python 互動式 shell，並導航到憑證 JSON 檔案所在的目錄。
  2. 執行 import ezsheets 來啟動 EZSheets。
  3. EZSheets 會自動檢查當前工作目錄中的憑證 JSON 檔案，如果找到則啟動 OAuth 同意畫面。

復原憑證檔案

如果您不小心分享了憑證或 token 檔案，您可以復原它們以防止未經授權的存取。只需在 Google Cloud Console 中找到憑證檔案，然後點選垃圾桶圖示即可復原。

Spreadsheet 物件

在 Google Sheets 中，一個 spreadsheet 可以包含多個 sheets（也稱為工作表），每個 sheet 包含列和行的儲存格。儲存格可以包含資料，如數字、日期或文字。儲存格也具有屬性，如字型、寬度和高度、背景顏色等。

建立、上傳和列出Google Sheets試算表

您可以使用ezsheets函式庫建立新的Google Sheets試算表、上傳現有的Excel試算表或從現有的Google Sheets試算表建立一個新的試算表物件。每個Google Sheets試算表都有一個唯一的ID，可以在其URL中找到，位於spreadsheets/d/部分和/edit部分之間。

建立新的試算表

您可以使用ezsheets.Spreadsheet()函式建立一個新的空白試算表：

import ezsheets
ss = ezsheets.Spreadsheet()
ss.title = '我的新試算表'
print(ss.title)  # 輸出：我的新試算表
print(ss.url)  # 輸出：https://docs.google.com/spreadsheets/d/.../edit
print(ss.id)  # 輸出：1gxz-Qr2-RNtqi_d7wWlsDlbtPLRQigcEXvCtdVwmH40

上傳現有的試算表

您可以使用ezsheets.upload()函式上傳現有的Excel試算表：

import ezsheets
ss = ezsheets.upload('my_spreadsheet.xlsx')
print(ss.title)  # 輸出：my_spreadsheet

列出試算表

您可以使用ezsheets.listSpreadsheets()函式列出您帳戶中的所有試算表：

import ezsheets
spreadsheets = ezsheets.listSpreadsheets()
print(spreadsheets)  # 輸出：{'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU': 'Education'}

存取試算表屬性

試算表物件具有以下屬性：title、id、url、sheetTitles和sheets。您可以使用這些屬性來存取和修改試算表：

import ezsheets
ss = ezsheets.Spreadsheet()
print(ss.title)  # 輸出：Untitled spreadsheet
ss.title = 'Sweigart Books'  # 修改試算表標題
print(ss.title)  # 輸出：Sweigart Books

重新整理試算表

如果有人在瀏覽器中修改了試算表，您可以使用refresh()方法更新試算表物件以匹配線上資料：

import ezsheets
ss = ezsheets.Spreadsheet()
ss.refresh()  # 重新整理試算表

這將更新試算表物件的屬性和包含的工作表物件中的資料。您將看到對試算表物件所做的更改線上上實時應用。

使用 EZSheets 進行 Google Sheets 操作

EZSheets 是一個 Python 函式庫，允許您輕鬆地與 Google Sheets 進行互動。以下是使用 EZSheets 進行 Google Sheets 操作的。

建立和管理 Google Sheets

您可以使用 ezsheets.Spreadsheet() 函式建立一個新的 Google Sheets 檔案。這個函式會傳回一個 Spreadsheet 物件，您可以使用它來管理您的 Google Sheets 檔案。

import ezsheets
ss = ezsheets.Spreadsheet()

您也可以使用 ss.title 屬性來設定您的 Google Sheets 檔案的標題。

ss.title = 'My Spreadsheet'

存取和管理 Sheet 物件

每個 Google Sheets 檔案都有一個或多個 Sheet 物件。您可以使用 ss.sheets 屬性來存取這些 Sheet 物件。

sheet = ss.sheets[0]

您也可以使用 ss.sheetTitles 屬性來存取所有 Sheet 物件的標題。

print(ss.sheetTitles)

讀寫資料

您可以使用 sheet['A1'] 來讀寫資料。例如，您可以設定 cell A1 的值為 ‘Name’。

sheet['A1'] = 'Name'

您也可以讀取 cell A1 的值。

print(sheet['A1'])

下載和上傳 Google Sheets

您可以使用 ss.downloadAsExcel() 函式下載您的 Google Sheets 檔案為 Excel 檔案。

ss.downloadAsExcel()

您也可以使用 ss.upload() 函式上傳您的 Excel 檔案到 Google Sheets。

ss.upload('example.xlsx')

刪除 Google Sheets

您可以使用 ss.delete() 函式刪除您的 Google Sheets 檔案。

ss.delete()

注意：刪除 Google Sheets 檔案後，您將無法還原它。因此，請謹慎使用此函式。

使用 EZSheets 進行 Google Sheets 資料操作

EZSheets 是一個 Python 函式庫，允許您輕鬆地與 Google Sheets 進行互動。以下是使用 EZSheets 進行資料操作的範例。

地址轉換

在 Google Sheets 中，地址轉換與 Excel 相同，但索引從 1 開始，而不是 0。您可以使用 convertAddress() 函式將 ‘A2’ 樣式的地址轉換為 (column, row) 樣式的地址。另外，getColumnLetterOf() 和 getColumnNumberOf() 函式可以將列地址之間的字母和數字進行轉換。

import ezsheets

# 轉換地址
print(ezsheets.convertAddress('A2'))  # (1, 2)
print(ezsheets.getColumnLetterOf(2))  # 'B'
print(ezsheets.getColumnNumberOf('B'))  # 2

讀取和寫入整列和整行

EZSheets 提供了 getColumn()、getRow()、updateColumn() 和 updateRow() 方法，分別用於讀取和寫入整列和整行。這些方法需要網際網路連線，以便更新 Google Sheets。

# 上傳 produceSales3.xlsx 至 Google Sheets
ss = ezsheets.upload('produceSales3.xlsx')

# 讀取第一列
print(ss.getColumn(1))

# 寫入第一列
ss.updateColumn(1, ['新資料', '新資料2', '新資料3'])

資料操作範例

以下是使用 EZSheets 進行資料操作的範例。首先，上傳 produceSales3.xlsx 至 Google Sheets。然後，讀取第一列和第一行，最後寫入新的資料至第一列和第一行。

import ezsheets

# 上傳 produceSales3.xlsx 至 Google Sheets
ss = ezsheets.upload('produceSales3.xlsx')

# 讀取第一列
column_data = ss.getColumn(1)
print(column_data)

# 讀取第一行
row_data = ss.getRow(1)
print(row_data)

# 寫入第一列
ss.updateColumn(1, ['新資料', '新資料2', '新資料3'])

# 寫入第一行
ss.updateRow(1, ['新資料', '新資料2', '新資料3'])

圖表翻譯：

圖表翻譯：此圖表顯示使用 EZSheets 進行資料操作的流程。首先，上傳檔案至 Google Sheets。然後，讀取資料並進行寫入操作。最後，更新 Google Sheets 以反映新的資料。

內容解密：

以上範例展示瞭如何使用 EZSheets 進行 Google Sheets 資料操作。首先，上傳檔案至 Google Sheets。然後，使用 getColumn() 和 getRow() 方法讀取資料，並使用 updateColumn() 和 updateRow() 方法寫入新的資料。這些方法需要網際網路連線，以便更新 Google Sheets。

使用ezsheets函式庫操作Google Sheets

ezsheets函式庫是一個Python函式庫，允許您輕鬆地與Google Sheets合作。以下是如何使用ezsheets函式庫來操作Google Sheets的範例。

安裝ezsheets函式庫

要使用ezsheets函式庫，您需要先安裝它。您可以使用pip安裝ezsheets函式庫：

pip install ezsheets

上傳Google Sheets檔案

要上傳Google Sheets檔案，您需要先建立一個ezsheets物件，然後使用upload()方法上傳檔案。以下是範例：

import ezsheets

# 建立一個ezsheets物件
ss = ezsheets.upload('produceSales3.xlsx')

取得Google Sheets資料

要取得Google Sheets資料，您可以使用getRow()和getColumn()方法。以下是範例：

# 取得第一行資料
row1 = ss.sheets[0].getRow(1)
print(row1)  # 輸出：['PRODUCE', 'COST PER POUND', 'POUNDS SOLD', 'TOTAL']

# 取得第二行資料
row2 = ss.sheets[0].getRow(2)
print(row2)  # 輸出：['Potatoes', '0.86', '21.6', '18.58', '', '']

# 取得第一列資料
column1 = ss.sheets[0].getColumn(1)
print(column1)  # 輸出：['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon',...]

更新Google Sheets資料

要更新Google Sheets資料，您可以使用updateRow()和updateColumn()方法。以下是範例：

# 更新第三行資料
ss.sheets[0].updateRow(3, ['Pumpkin', '11.50', '20', '230', '', ''])

# 取得更新後的第三行資料
row3 = ss.sheets[0].getRow(3)
print(row3)  # 輸出：['Pumpkin', '11.50', '20', '230', '', '']

迭代Google Sheets資料

要迭代Google Sheets資料，您可以使用enumerate()函式。以下是範例：

# 迭代第一列資料
for i, value in enumerate(column1):
    print(f"第{i}行：{value}")

這些範例展示瞭如何使用ezsheets函式庫來操作Google Sheets。您可以使用ezsheets函式庫來讀取和寫入Google Sheets資料，並進行各種操作。

使用 ezSheets 更新 Google Sheets 資料

ezSheets 是一個 Python 函式庫，允許您輕鬆地更新 Google Sheets 資料。以下是使用 ezSheets 更新 Google Sheets 資料的步驟：

步驟 1：安裝 ezSheets

首先，您需要安裝 ezSheets 函式庫。您可以使用 pip 安裝：

pip install ezsheets

步驟 2：匯入 ezSheets

匯入 ezSheets 函式庫：

import ezsheets

步驟 3：建立 Google Sheets 連線

建立 Google Sheets 連線：

ss = ezsheets.Spreadsheet()

步驟 4：更新資料

您可以使用 updateColumn() 方法更新整個欄位的資料。例如：

columnOne = ss.getColumn(1)
for i, value in enumerate(columnOne):
    columnOne[i] = value.upper()
ss.updateColumn(1, columnOne)

這將更新第一欄的所有資料為大寫。

步驟 5：更新多行資料

您也可以使用 getRows() 方法更新多行資料。例如：

rows = ss.getRows()
rows[1][0] = 'PUMPKIN'  # 更新第二行第一欄的資料
rows[10][2] = '400'  # 更新第 11 行第三欄的資料
ss.updateRows(rows)

這將更新第二行第一欄和第 11 行第三欄的資料。

步驟 6：建立新表單

您可以使用 Sheet() 方法建立新表單。例如：

ss.Sheet('Spam')  # 建立新表單，名稱為 "Spam"

這將建立一個新的表單，名稱為 “Spam”。

步驟 7：移動表單

您可以使用 Sheet() 方法移動表單。例如：

ss.Sheet('Bacon', 0)  # 移動表單 "Bacon" 到索引 0

這將移動表單 “Bacon” 到索引 0。

步驟 8：刪除表單

您可以使用 deleteSheet() 方法刪除表單。例如：

ss.deleteSheet('Sheet1')  # 刪除表單 "Sheet1"

這將刪除表單 “Sheet1”。

內容解密：

上述程式碼示範瞭如何使用 ezSheets 更新 Google Sheets 資料。您可以使用 updateColumn() 方法更新整個欄位的資料，使用 getRows() 方法更新多行資料，使用 Sheet() 方法建立新表單，使用 deleteSheet() 方法刪除表單。

圖表翻譯：

以下是 ezSheets 的架構圖： 這個圖表展示了 ezSheets 的架構，包括 Spreadsheet、Sheet、Row 和 Cell。

使用EZSheets管理Google Sheets

EZSheets是一個Python函式庫，允許您以程式設計的方式管理Google Sheets。您可以使用它來建立、編輯和管理Google Sheets，並且還可以使用它來複製和刪除工作表。

刪除工作表

如果您想要刪除一個工作表，可以使用delete()方法。例如：

>>> ss = ezsheets.Spreadsheet()
>>> ss.title = 'My Spreadsheet'
>>> ss.sheets[0].title = 'Sheet1'
>>> ss.sheets[0].delete()  # 刪除工作表

刪除工作表是永久性的，無法還原。

複製工作表

如果您想要複製一個工作表，可以使用copyTo()方法。例如：

>>> ss1 = ezsheets.Spreadsheet()
>>> ss1.title = 'First Spreadsheet'
>>> ss1.sheets[0].title = 'Spam'
>>> ss2 = ezsheets.Spreadsheet()
>>> ss2.title = 'Second Spreadsheet'
>>> ss2.sheets[0].title = 'Eggs'
>>> ss1[0].copyTo(ss2)  # 複製工作表

複製的工作表會以「Copy of」為字首新增到目的工作表的列表中。

Google Forms

您可以使用Google Forms建立表單，並將提交的答案儲存到Google Sheets中。使用EZSheets，您可以存取這些資料並在Python中進行處理。

自動化流程

您可以使用Python將多個軟體系統連線起來，建立自動化流程。例如，您可以撰寫一個程式，定期檢查Google Forms的提交內容，並在檢測到新內容時傳送通知。

內容解密：

在上述程式碼中，我們使用ezsheets函式庫來建立和管理Google Sheets。delete()方法用於刪除工作表，而copyTo()方法用於複製工作表。這些方法使我們可以以程式設計的方式管理Google Sheets，並將其與其他軟體系統連線起來。

圖表翻譯：

下面的Plantuml圖表展示瞭如何使用EZSheets複製工作表：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Python Openpyxl 與 EZSheets Google Sheets 操作

package "Python 應用架構" {
    package "應用層" {
        component [主程式] as main
        component [模組/套件] as modules
        component [設定檔] as config
    }

    package "框架層" {
        component [Web 框架] as web
        component [ORM] as orm
        component [非同步處理] as async
    }

    package "資料層" {
        database [資料庫] as db
        component [快取] as cache
        component [檔案系統] as fs
    }
}

main --> modules : 匯入模組
main --> config : 載入設定
modules --> web : HTTP 處理
web --> orm : 資料操作
orm --> db : 持久化
web --> cache : 快取查詢
web --> async : 背景任務
async --> fs : 檔案處理

note right of web
  Flask / FastAPI / Django
end note

@enduml

這個圖表展示瞭如何使用EZSheets複製工作表的步驟。首先，建立源工作表和目的工作表。然後，使用copyTo()方法複製源工作表到目的工作表。最後，更新目的工作表以反映更改。

從技術架構視角來看，OpenPyxl 和 EZSheets 提供了便捷的 Python 介面，方便操作 Excel 和 Google Sheets。OpenPyxl 允許讀取、寫入和修改本地 Excel 檔案，涵蓋了單元格操作、公式設定、樣式調整和圖表建立等功能。EZSheets 則簡化了 Google Sheets 的線上操作，實作了資料讀寫、工作表管理和與 Google 服務整合等功能。兩種工具各有千秋，OpenPyxl 適用於本地檔案處理，而 EZSheets 則更適合雲端協作和線上資料分析。然而，兩者都存在一定的技術限制。OpenPyxl 對大型 Excel 檔案的處理效率較低，且部分 Excel 功能尚未完全支援。EZSheets 則依賴於穩定的網路連線和 Google API 授權，可能存在資料安全和隱私方面的風險。展望未來，隨著 Python 資料科學生態的蓬勃發展，預計 OpenPyxl 和 EZSheets 將持續最佳化效能，並擴充套件更多功能，以更好地滿足日益增長的資料處理需求。對於需要處理表格資料的 Python 開發者而言，根據實際應用場景選擇合適的工具至關重要。如果著重於本地檔案處理和複雜的 Excel 功能，OpenPyxl 是更佳選擇；如果需要線上協作和與 Google 服務整合，則 EZSheets 更為適合。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。