python
excel
Microsoft Excel副檔名為:xlsx或xls,以下為幾本名詞:
模組 | .xls | .xlsx | 讀取 | 寫入 | 修改 | 儲存 | 格式調整 | 插入圖片 |
---|---|---|---|---|---|---|---|---|
xlrd | V | V | V | X | X | X | X | X |
xlwt | V | X | X | V | V | V | V | V |
xluntils | V | X | X | V | V | V | X | X |
xlwings | V | V | V | V | V | V | V | V |
XlsxWriter | X | V | X | V | X | V | V | V |
openpyxl | X | V | V | V | V | V | V | V |
Pandas | V | V | V | V | X | V | X | X |
備註:
xlutils
模組是xlrd
和xlwt
之間的橋樑,最核心的作用是複製一份透過xlrd
讀取到記憶體中的.xls
物件,然後再複製物件上透過xlwt
修改.xls
表格的内容。xlutils
可以將xlrd
的 Book 物件複製轉換為xlwt
的Workbook 物件,實際使用時通常導入的是模組中的copy
子模組;例如:import xlutils.copy
要使用openpyxl模組前必須要經過安裝
# pip3 install openpyxl
使用時須先引入:
import openpyxl
wb = openpyxl.load_workbook('縣市人口按性別及五齡組(63).xlsx')
print(type(wb))
備註:
不支援舊的xls格式
print(wb.sheetnames) # 所有工作表名稱
print(wb.active) # 目前工作表
print(wb.active.title) # 目前工作表名稱
wb.active = wb['100']
ws101 = wb['101'] # 使用工作表名稱來取得工作表
print(ws101.title)
ws101.title = '101-OK'
wb.save('複製的Excel檔案.xlsx') # 將修改過的Excel儲存為另一個檔案
wb.close()
ws_index0 = wb.worksheets[0]
print(ws_index0.title)
new_wb = openpyxl.Workbook()
new_wb.active.title = "Hello"
new_wb.create_sheet() # 將新的工作表放在最後面
new_wb.create_sheet(title='New', index=0) # title=名稱,index=將新的工作表放在第一個位置
new_wb.copy_worksheet(new_wb.active) # 將目前的工作表複製
說明:
new_wb.remove(new_wb['New']) # 另一個方式:del wb['New']
new_wb.remove(new_wb.worksheets[0]) # 另一個方式:del new_wb.worksheets[0]
tab0 = new_wb.worksheets[0]
tab0.title = 'Blue'
tab0.sheet_properties.tabColor = '0000FF'
tab0.sheet_state = 'hidden' # 隱藏,隱藏後仍然可以透過wb.sheetnames取得工作表
tab0.sheet_state = 'visible' # 顯示
tab0.protection.sheet = True
tab0.protection.enable()
tab0.protection.password = 'password'
tab0.protection.disable()
tab0['B2'] = 'Aaron'
tab0.cell(row=2, column=1, value='Andy')
tab0.cell(row=3, column=1).value = 'Abner'
print(tab0['B2'].value)
print(tab0.cell(row=2, column=1).value) # row和column都是從1開始
補充:
如果該儲存格是公式,則會取得公式,如果希望得到公式計算結果,在開啟活頁簿時需加上data_only=True
參數
print(tab0['A1'].value, tab0['A1'].column, tab0['A1'].row, tab0['A1'].coordinate)
print('max_column:', tab0.max_column)
print('max_row:', tab0.max_row)
for r in range(1, tab0.max_row+1):
for c in range(1, tab0.max_column+1):
print(tab0.cell(row=r, column=c).value, end='')
print()
補充:
沒有資料會回傳None
for column in tab0.columns:
for cell in column:
print(cell.value, end='')
print()
for row in tab0.rows:
for cell in row:
print(cell.value, end='')
print()
print(tab0['A'])
print(tab0['1'])
tab0['A1':'B2'] # 取得A1:B2儲存格區間的資料
tab0['B:D'] # 取得B欄位到D欄位資料
tab0[1:2] # 取得第1例到第2列資料
print(tab0.dimensions)
row1 = ['Ben', 'Bonnie', 'Bernie']
tab0.append(row1)
tab0.insert_rows(1, 1) # 在第1列插入一個新的空白列(第2個參數省略預設為1)
tab0.delete_rows(1) # 刪除第1列
tab0.delete_rows(1, 2) # 起始列號,列數
tab0.delete_rows(1, tab0.max_column) # 刪除整張工作表的資料
tab0.insert_cols(1, 1) # 在第1欄插入一個新的空白欄(第2個參數省略預設為1)
tab0.delete_cols(1) # 刪除第一欄
tab0.delete_cols(1, 2) # 從第一欄開始刪除兩欄
tab0.move_range('A1:B2', rows=5, cols=5, translate=False) # 把A1到B2的資料移動到第5列第5欄的位置,但不移動公式
補充:
如果要移動公式,則須設定:translate=True
tab0.row_dimensions[1].height = 30
tab0.row_dimensions[1].width = 20
from openpyxl.styles import Font
Font(name='Calibri', # 設定字型名稱
size=11, # 字型大小
bold=False, # 粗體字
italic=False, # 斜體字
vertAlign=None, # 垂直對齊
underline=None, # single/double
strike=False, # 刪除線
color='000000') # 顏色
tab0['A1'].font = Font(size=20, color='008855')
需先引入框線模組
from openpyxl.styles import Border, Side
BORDER_NONE = None
BORDER_DASHDOT = 'dashDot'
BORDER_DASHDOTDOT = 'dashDotDot'
BORDER_DASHED = 'dashed'
BORDER_DOTTED = 'dotted'
BORDER_DOUBLE = 'double'
BORDER_HAIR = 'hair'
BORDER_MEDIUM = 'medium'
BORDER_MEDIUMDASHDOT = 'mediumDashDot'
BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot'
BORDER_MEDIUMDASHED = 'mediumDashed'
BORDER_SLANTDASHDOT = 'slantDashDot'
BORDER_THICK = 'thick'
BORDER_THIN = 'thin'
Border(left=Side(border_style=None, color='000000'), # 左邊框線
right=Side(border_style=None, color='000000'), # 右邊框線
top=Side(border_style=None, color='000000'), # 上面框線
bottom=Side(border_style=None, color='000000'), # 下面框線
diagonal=Side(border_style=None, color='000000'), # 對角線
diagonalDown=False, # 左上到右下對角線,預設不顯示
diagonalUp=False, # 右上到左下對角線,預設不顯示
outline=Side(border_style=None, color='000000'), #
vertical=Side(border_style=None, color='000000'), # 垂直框線
horizontal=Side(border_style=None, color='000000')) # 水平框線
side = Side(border_style='double')
tab0['A1'].border = Border(left=side, right=side, top=side, bottom=side)
備註:
框線只能一次設定一個,要設定多個只能用迴圈
需先引入PatternFill模組
from openpyxl.styles import PatternFill
FILL_NONE = 'none'
FILL_SOLID = 'solid'
FILL_PATTERN_DARKDOWN = 'darkDown'
FILL_PATTERN_DARKGRAY = 'darkGray'
FILL_PATTERN_DARKGRID = 'darkGrid'
FILL_PATTERN_DARKHORIZONTAL = 'darkHorizontal'
FILL_PATTERN_DARKTRELLIS = 'darkTrellis'
FILL_PATTERN_DARKUP = 'darkUp'
FILL_PATTERN_DARKVERTICAL = 'darkVertical'
FILL_PATTERN_GRAY0625 = 'gray0625'
FILL_PATTERN_GRAY125 = 'gray125'
FILL_PATTERN_LIGHTDOWN = 'lightDown'
FILL_PATTERN_LIGHTGRAY = 'lightGray'
FILL_PATTERN_LIGHTGRID = 'lightGrid'
FILL_PATTERN_LIGHTHORIZONTAL = 'lightHorizontal'
FILL_PATTERN_LIGHTTRELLIS = 'lightTrellis'
FILL_PATTERN_LIGHTUP = 'lightUp'
FILL_PATTERN_LIGHTVERTICAL = 'lightVertical'
FILL_PATTERN_MEDIUMGRAY = 'mediumGray'
PatternFill(fill_type=None,
fgColor='000000',
bgColor='000000',
start_color='000000',
end_color='000000')
tab0['A1'].fill = PatternFill(fill_type='solid', fgColor='009922', bgColor='770000')
備註:
只能一次設定一個,要設定多個只能用迴圈
需先引入Alignment模組
from openpyxl.styles import Alignment
horizontal_alignments = (
"general", "left", "center", "right", "fill", "justify", "centerContinuous",
"distributed", )
vertical_aligments = (
"top", "center", "bottom", "justify", "distributed",
)
Alignment(horizontal='general', # 水平對齊
vertical='bottom', # 垂直對齊
text_rotation=0, # 方向、旋轉文字
wrap_text=False, # 自動換行
shrink_to_fit=False, # 縮小字型以適應欄寬
indent=0) # 縮排
tab0['A1'].alignment = Alignment(horizontal='center', vertical='center')
備註:
只能一次設定一個,要設定多個只能用迴圈
tab0.merge_cells('A1:B2') # 合併A1到B2的儲存格
tab0.unmerge_cells('A1:B2') # 取消合併A1到B2的儲存格
new_wb.save('MyNewExcel.xlsx')
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.Workbook()
ws = wb.active
rows = [
['', '107年', '108年', '109年', '110年', '111年'],
['台北店', 10, 12, 13, 15, 14],
['新北店', 9, 13, 13, 15, 18],
['桃園店', 7, 10, 14, 12, 11],
['新竹店', 7, 8, 10, 12, 16],
['台中店', 5, 8, 11, 13, 16]
]
for row in rows:
ws.append(row)
chart = BarChart()
# col": 直條圖 , "bar": 橫條圖
chart.type = 'col'
data = Reference(ws, min_col=2, max_col=6, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.title = 'XX公司業績表'
chart.x_axis.title = '分店名稱'
chart.y_axis.title = '年度'
xtitle = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.set_categories(xtitle)
# 預設15x7公分
chart.width = 15 # 單位: 公分
chart.height = 10 # 單位: 公分
chart.gapWidth = 50 # 0~500之間
chart.grouping = 'stacked'
chart.overlap = 100
ws.add_chart(chart, 'G2')
wb.save('company.xlsx')
說明:
Reference()
函式建立資料參考物件。
col
=直條圖(預設),bar
=橫條圖
import openpyxl
from openpyxl.chart import PieChart, Reference
wb = openpyxl.Workbook()
ws = wb.active
rows = [
['', '107年', '108年', '109年', '110年', '111年'],
['台北店', 10, 12, 13, 15, 14],
['新北店', 9, 13, 13, 15, 18],
['桃園店', 7, 10, 14, 12, 11],
['新竹店', 7, 8, 10, 12, 16],
['台中店', 5, 8, 11, 13, 16]
]
for row in rows:
ws.append(row)
# 圓餅圖
chart = PieChart()
data = Reference(ws, min_col=3, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.title = 'XX公司業績表'
xtitle = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.set_categories(xtitle)
# 預設15x7公分
chart.width = 15 # 單位: 公分
chart.height = 10 # 單位: 公分
ws.add_chart(chart, 'B15')
wb.save('company.xlsx')
說明:
如果同時要顯示多組資料,就可以使用環圈圖
import openpyxl
from openpyxl.chart import DoughnutChart, Reference
from openpyxl.chart.series import DataPoint
wb = openpyxl.Workbook()
ws = wb.active
rows = [
['店名', '107年', '108年', '109年', '110年', '111年'],
['台北店', 10, 12, 13, 15, 14],
['新北店', 9, 13, 13, 15, 18],
['桃園店', 7, 10, 14, 12, 11],
['新竹店', 7, 8, 10, 12, 16],
['台中店', 5, 8, 11, 13, 16]
]
for row in rows:
ws.append(row)
# 環圈圖
chart = DoughnutChart()
# min_col=起始欄位(外圈),max_col=結束欄位(外圈)
data = Reference(ws, min_col=2, max_col=6, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.title = 'XX公司業績表'
labels = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.set_categories(labels)
# 切片分離, explosion為分離距離
slice = DataPoint(idx=1, explosion=10)
# 索引必須是最外圈的索引
chart.series[4].data_points = [slice]
# 預設15x7公分
chart.width = 15 # 單位: 公分
chart.height = 10 # 單位: 公分
ws.add_chart(chart, 'B15')
wb.save('company.xlsx')
用來顯示某個時段內,資料的變動及趨勢,可以用來很快地看出該時段趨勢的變化
import openpyxl
from openpyxl.chart import LineChart, Reference
wb = openpyxl.Workbook()
ws = wb.active
rows = [
['', '台北店', '新北店', '桃園店', '新竹店', '台中店'],
['108年', 10, 12, 13, 15, 14],
['109年', 9, 13, 13, 15, 18],
['110年', 7, 10, 14, 12, 11],
['111年', 7, 8, 10, 12, 16],
['112年', 5, 8, 11, 13, 16]
]
for row in rows:
ws.append(row)
# 建立折線圖物件
chart = LineChart()
# 資料來源
data = Reference(ws, min_col=2, max_col=6, min_row=1, max_row=6)
# 資料加入圖表
chart.add_data(data, titles_from_data=True)
# 圖表標題
chart.title = 'XX公司業績表'
# X軸標題
chart.x_axis.title = '分店名稱'
# Y軸標題
chart.y_axis.title = '年度'
# X軸資料標籤
xlabels = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.set_categories(xlabels)
# # 預設15x7公分
chart.width = 15 # 單位: 公分
chart.height = 10 # 單位: 公分
# 修改樣式
chart.style = 15
# 將圖表放在B15位置
ws.add_chart(chart, 'B15')
wb.save('sales.xlsx')
區域圖與折線圖非常類似,差別是區域圖的下面會被顏色填充
import openpyxl
from openpyxl.chart import AreaChart, Reference
wb = openpyxl.Workbook()
ws = wb.active
rows = [
['', '台北店', '新北店', '桃園店', '新竹店', '台中店'],
['108年', 20, 12, 13, 15, 4],
['109年', 19, 13, 13, 15, 8],
['110年', 17, 10, 14, 12, 7],
['111年', 17, 8, 10, 12, 6],
['112年', 15, 8, 11, 13, 6]
]
for row in rows:
ws.append(row)
# 建立折線圖物件
chart = AreaChart()
# 資料來源
data = Reference(ws, min_col=2, max_col=6, min_row=1, max_row=6)
# 資料加入圖表
chart.add_data(data, titles_from_data=True)
# 圖表標題
chart.title = 'XX公司業績表'
# X軸標題
chart.x_axis.title = '分店名稱'
# Y軸標題
chart.y_axis.title = '年度'
# X軸資料標籤
xlabels = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.set_categories(xlabels)
# # 預設15x7公分
chart.width = 15 # 單位: 公分
chart.height = 10 # 單位: 公分
# 修改樣式
chart.style = 40
# 將圖表放在B15位置
ws.add_chart(chart, 'B15')
wb.save('sales.xlsx')
or
By clicking below, you agree to our terms of service.
New to HackMD? Sign up