openpyxl

OpenPyXL模組：基本功能操作

l   在命令提示字元輸入pip install openpyxl，下載並安裝OpenPyXL模組。

l   開啟Excel檔與工作表：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
# 此Workbook物件代表Excel檔
aBook = openpyxl.load_workbook("example.xlsx")
print(type(aBook)) à <class 'openpyxl.workbook.workbook.Workbook'>
print(aBook.sheetnames) à ['Sheet1', 'Sheet2', 'Sheet3']
# 此Worksheet物件代表工作表
aSheet = aBook["Sheet3"]
print(type(aSheet)) à <class 'openpyxl.worksheet.worksheet.Worksheet'>
print(aSheet) à <Worksheet "Sheet3">
print(aSheet.title) à Sheet3
bSheet = aBook.active
print(bSheet) à <Worksheet "Sheet1">

l   取得Excel檔工作表的儲存格：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("example.xlsx")
aSheet = aBook["Sheet1"]
# Cell物件取得儲存格的方法一
aCell = aSheet["B1"]
print(aCell) à <Cell 'Sheet1'.B1>
print("Row " + str(aCell.row) + ", Column " + aCell.column + " is " + aCell.value)à Row 1, Column B is Apples
print("Cell " + aCell.coordinate + " is " + aCell.value) à Cell B1 is Apples
# Cell物件取得儲存格的方法二
print(aSheet.cell(row = 1, column = 2)) à <Cell 'Sheet1'.B1>
print(aSheet.cell(row = 1, column = 2).value) à Apples
# Worksheet物件取得使用中的欄列範圍
print(aSheet.max_row) à 7
print(aSheet.max_column) à 3

l   取得Excel檔工作表的儲存格範圍：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("example.xlsx")
aSheet = aBook["Sheet1"]
# Generator物件含有矩形範圍的Cell物件
print(list(aSheet["A1":"B2"])) à [(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>)]
# 印出Generator物件矩形範圍的所有Cell物件
for rowCell in aSheet["A1":"C2"]:
    for iCell in rowCell:
        print(iCell.coordinate, iCell.value)
    print("---End of Row---")

l   以欄列取得Excel檔工作表的儲存格範圍：
import openpyxl
# 本書範例檔example.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("example.xlsx")
aSheet = aBook.active
# Generator物件含有矩形範圍的Cell物件
print(list(aSheet.rows)) à [(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), …, (<Cell 'Sheet1'.A7>, <Cell 'Sheet1'.B7>, <Cell 'Sheet1'.C7>)]
print(list(aSheet.columns)[1]) à (<Cell 'Sheet1'.B1>, …, <Cell 'Sheet1'.B7>)
# 印出Generator物件矩形範圍的所有Cell物件
for iCell in list(aSheet.columns)[1]:
    print(iCell.coordinate, iCell.value)

l   欄的字母和數字之間的轉換：
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
print(get_column_letter(27)) à AA
print(column_index_from_string("AA")) à 27

l   建立並儲存Excel檔：
import openpyxl
aBook = openpyxl.Workbook()
print(aBook.sheetnames) à ['Sheet']
aSheet = aBook.active
aSheet.title = "Hello World"
print(aBook.sheetnames) à ['Hello World']
aBook.save("test.xlsx")

l   建立與刪除Excel檔工作表：
import openpyxl
aBook = openpyxl.Workbook()
print(aBook.sheetnames) à ['Sheet']
aBook.create_sheet()
print(aBook.sheetnames) à ['Sheet', 'Sheet1']
aBook.create_sheet(index = 0, title = "First Sheet")
print(aBook.sheetnames) à ['First Sheet', 'Sheet', 'Sheet1']
del aBook["Sheet"]
print(aBook.sheetnames) à ['First Sheet', 'Sheet1']
aBook.remove(aBook["Sheet1"])
print(aBook.sheetnames) à ['First Sheet']

l   將值寫入Excel檔工作表的儲存格：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
aSheet["A1"] = "Hello World"
print(aSheet["A1"].value) à Hello World

l   OpenPyXL模組相關內容：
A Python library to read/write Excel2010 xlsx/xlsm files

OpenPyXL模組：視覺化功能操作

l   設定儲存格的字型：
import openpyxl
from openpyxl.styles import Font
aBook = openpyxl.Workbook()
aSheet = aBook.active
# Font物件為字體Times New Roman且粗體，並指定給font屬性
aSheet["A1"].font = Font(name = "Times New Roman", bold = True)
aSheet["A1"] = "Hello World"
# Font物件為大小24點且斜體，並指定給font屬性
aSheet["C3"].font = Font(size = 24, italic = True)
aSheet["C3"] = "Hello Poor World"
aBook.save("test.xlsx")

l   設定儲存格的列高和欄寬：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
# row_dimensions含有RowDimension物件，可設定為0到409之間
aSheet.row_dimensions[1].height = 70
# column_dimensions含有ColumnDimension物件，可設定為0到255之間
aSheet.column_dimensions["C"].width = 50
aBook.save("test.xlsx")
bBook = openpyxl.Workbook()
bSheet = bBook.active
# 可設定隱藏列高
bSheet.row_dimensions[1].hidden = True
# 可設定隱藏欄寬
bSheet.column_dimensions["C"].hidden = True
bBook.save("test2.xlsx")

l   設定儲存格的合併和取消合併：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
aSheet.merge_cells("A1:C3")
aSheet["A1"] = "Hello World"
aBook.save("test.xlsx")
bBook = openpyxl.load_workbook("test.xlsx")
bSheet = bBook.active
bSheet.unmerge_cells("A1:C3")
bBook.save("test2.xlsx")

l   凍結窗格：
import openpyxl
# 本書範例檔produceSales.xlsx在目前工作目錄
aBook = openpyxl.load_workbook("produceSales.xlsx")
aSheet = aBook.active
# 儲存格左方和上方將被凍結，儲存格本身不凍結
aSheet.freeze_panes = "A2"
aBook.save("test.xlsx")
bBook = openpyxl.load_workbook("test.xlsx")
bSheet = bBook.active
# 若要解除凍結窗格，傳入None或"A1"皆可
bSheet.freeze_panes = None
bBook.save("test2.xlsx")

l   繪製長條圖、折線圖、圓餅圖：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
for i in range(1, 11):
    aSheet["A" + str(i)] = i
# Reference物件的5個引數分別為Worksheet物件、範圍區域的左上角儲存格欄列、範圍區域的右下角儲存格欄列，第二至第五引數可只填正整數
aRefer = openpyxl.chart.Reference(aSheet, min_col = 1, min_row = 1, max_col = 1, max_row = 10)
# Series物件
aSerie = openpyxl.chart.Series(aRefer, title = "First Series")
# Chart物件：長條圖
aChart = openpyxl.chart.BarChart()
aChart.title = "Bar Chart Title"
aChart.append(aSerie)
aSheet.add_chart(aChart, "C1")
# Chart物件：折線圖
bChart = openpyxl.chart.LineChart()
bChart.title = "Line Chart Title"
bChart.append(aSerie)
aSheet.add_chart(bChart, "C15")
# Chart物件：圓餅圖
cChart = openpyxl.chart.PieChart()
cChart.title = "Pie Chart Title"
cChart.append(aSerie)
aSheet.add_chart(cChart, "C29")
aBook.save("test.xlsx")

l   繪製散佈圖：
import openpyxl
aBook = openpyxl.Workbook()
aSheet = aBook.active
for i in range(1, 11):
    aSheet["A" + str(i)] = i
    aSheet["B" + str(i)] = i**2 + 5
aRefer = openpyxl.chart.Reference(aSheet, 2, 1, 2, 10)
bRefer = openpyxl.chart.Reference(aSheet, 1, 1, 1, 10)
aSerie = openpyxl.chart.Series(aRefer, bRefer, title = "First Series")
# Chart物件：散佈圖
aChart = openpyxl.chart.ScatterChart()
aChart.title = "Scatter Chart Title"
aChart.append(aSerie)
aSheet.add_chart(aChart, "C1")
aBook.save("test.xlsx")