HackMD
tags: python
Excel 檔案讀寫
安裝
- 開始/cmd
- pip install openpyxl
參考資料
儲存excel檔案
from openpyxl import Workbook
workbook = Workbook() # 實作一個excel物件
sheet = workbook.active # 將目前工作表指到sheet變數
sheet["A1"] = "hello" # 在A1儲存格放入指定內容
sheet["B1"] = "world!"
workbook.save(filename="hello_world.xlsx") # 儲存檔案
讀取excel中工作表的名稱
先建立一個excel檔(.xlsx)，修改工作表名稱
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx")
sheetnames = workbook.sheetnames # 取得所有工作表物件
print(sheetnames)
sheet = workbook.active
print(sheet)
print(sheet.title) # 取得工作表名稱
print(sheet["A1"]) # 取得該儲存格物件
print(sheet["A1"].value) # 取得該儲存格的內容
print(sheet.cell(row=1, column=2)) # 取得該儲存格物件
print(sheet.cell(row=1, column=2).value) # 取得該儲存格的內容
開檔模式
唯讀模式：讀取大檔
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", read_only=True)
sheet = workbook.active
sheet["A1"].value = 'test'
workbook.save(filename="hello_world.xlsx")
資料模式：讀取公式計算結果
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", data_only=True)
sheet = workbook.active
print(sheet["A1"].value)
print(sheet["B1"].value)
讀取某一範圍的資料
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", data_only=True)
sheet = workbook.active
# 讀取某一範圍
rangearea = sheet["A1:C2"]
print(rangearea, '\n')
for row in rangearea:
for cell in row:
print(cell, cell.value)
# 讀取整欄
rangearea = sheet["A"] # 取得該欄所有有資料的內容
print(rangearea, '\n')
for cell in rangearea:
print(cell, cell.value)
# 讀取幾欄
rangearea = sheet["A:B"]
print(rangearea, '\n')
for row in rangearea:
for cell in row:
print(cell, cell.value)
# 讀取整列
rangearea = sheet[1] # 取得該列所有有資料的內容
print(rangearea, '\n')
for cell in rangearea:
print(cell, cell.value)
# 讀取幾列
rangearea = sheet["1:2"]
print(rangearea, '\n')
for row in rangearea:
for cell in row:
print(cell, cell.value)
循列讀取
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", data_only=True)
sheet = workbook.active
for row in sheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=3):
for cell in row:
print(cell, cell.value)
循欄讀取
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", data_only=True)
sheet = workbook.active
for row in sheet.iter_cols(min_row=1, max_row=2, min_col=1, max_col=3):
for cell in row:
print(cell, cell.value)
循欄讀取值
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", data_only=True)
sheet = workbook.active
for row in sheet.iter_cols(min_row=1, max_row=2, min_col=1, max_col=3, values_only=True):
for cell in row:
print(cell)
循列/循欄讀取所有資料
from openpyxl import load_workbook
workbook = load_workbook(filename="hello_world.xlsx", data_only=True)
sheet = workbook.active
for row in sheet.rows: # 循列讀取所有資料
for cell in row:
print(cell, cell.value)
for row in sheet.columns: # 循列讀取所有資料
for cell in row:
print(cell, cell.value)
建立工作表、命名工作表、寫入儲存格、儲存檔案
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook() # 開啟一個空白的excel檔，以wb代表它
ws1 = wb.active # 把使用中的工作表以ws1代表
ws1.title = "range names" # 將工作表命名為range names
for row in range(1, 40): # 執行39次迴圈
ws1.append(range(600)) # 每次塞入599筆資料到該工作表
ws2 = wb.create_sheet(title="Pi") # 新增一個工作表叫Pi
ws2['F5'] = 3.14 # 將這個工作表第F5位置塞入3.14資料
ws3 = wb.create_sheet(title="Data")
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
] # 建立一個叫data的二維陣列，內容如上
ws3.append(["Fruit", "2011", "2012", "2013", "2014"]) # 在工作表3塞入一列（一個陣列）
for row in data:
ws3.append(row) # 依序讀取data中每筆一維陣列，並塞入第3工作表
print(ws3['A10'].value) # 讀取並列印工作表3中A10位置的內容
wb.save(filename = 'sample2.xlsx') # 將以上變更寫入sample2.xlsx
