使用Python openpyxl模組讀寫Excel檔案

---

Python 提供了 openpyxl 模組用於操作 Excel 檔案。

如何建立 Excel 檔案、如何寫入、讀取等都可以透過此模組實現。

要安裝 openpyxl 模組，可以在命令提示符中輸入此命令

pip install openpyxl

如果要指定工作表標題名稱

示例程式碼

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet_title = my_sheet.title
print("My sheet title: " + my_sheet_title)

輸出

My sheet title:Sheet

更改標題名稱

示例程式碼

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet.title = "My New Sheet"
print("sheet name is : " + sheet.title)

輸出

sheet name is : My New Sheet

插入資料或寫入 Excel 工作表

示例程式碼

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
c1 = my_sheet.cell(row = 1, column = 1)
c1.value = "Aadrika"
c2 = my_sheet.cell(row= 1 , column = 2)
c2.value = "Adwaita"
c3 = my_sheet['A2']
c3.value = "Satyajit"
# B2 = column = 2 & row = 2.
c4 = my_sheet['B2']
c4.value = "Bivas"
my_wb.save("C:\Users\TP\Desktop\Book1.xlsx")

輸出

在工作簿中新增工作表

示例程式碼

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_wb.create_sheet(index = 1 , title = "new sheet")
my_wb.save("C:\Users\TP\Desktop\Book1.xlsx")

輸出

顯示總行數。

示例程式碼

import openpyxl
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
print(my_sheet_obj.max_row)

輸出

2

顯示特定單元格的值

示例程式碼

import openpyxl
# Give the location of the file
My_path = "C:\Users\TP\Desktop\Book1.xlsx"
wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_cell_obj = my_sheet_obj.cell(row = 1, column = 1)
print(my_cell_obj.value)

輸出

Aadrika

顯示總列數

示例程式碼

import openpyxl
# Give the location of the file
My_path = "C:\Users\TP\Desktop\Book1.xlsx"
My_wb_obj = openpyxl.load_workbook(path)
my_sheet_obj = my_wb_obj.active
print(sheet_obj.max_column)

輸出

2

顯示所有列名

示例程式碼

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
# workbook object is created
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column
for i in range(1, my_max_col + 1):
   my_cell_obj = my_sheet_obj.cell(row = 1, column = i)
   print(my_cell_obj.value) 

輸出

Aadrika Adwaita

顯示第一列的值

示例程式碼

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_row = my_sheet_obj.max_row
for i in range(1, my_row + 1):
   cell_obj = my_sheet_obj.cell(row = i, column = 1)
   print(cell_obj.value)

輸出

Aadrika
Satyajit

列印特定行值

示例程式碼

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column
for i in range(1, my_max_col + 1):
   cell_obj = my_sheet_obj.cell(row = 2, column = i)
   print(cell_obj.value, end = " ")

輸出

Satyajit Bivas