Try   HackMD

Excel基礎

tags: python excel

介紹

Excel介紹

Microsoft Excel副檔名為:xlsx或xls,以下為幾本名詞:

  • 活頁簿(workbook):Excel檔案本身又稱為活頁簿。
  • 工作表(worksheet):一個活頁簿由一個或數個工作表組成,一個工作表即為一頁表格文件,可以透過Excel應用程式下方的標籤來切換工作表。
  • 欄位(column):直的方向稱為欄位,名稱由英文字A、B、C等等依序排列。
  • 列(row):橫的方向稱為列,名稱由1、2、3等等依序排列。
  • 儲存格(cell):工作表內的每個存放資料的小格子稱為儲存格,可以用欄+列來定位每個儲存格。

各種Python操作Excel模組功能比較

模組 .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 模組是 xlrdxlwt 之間的橋樑,最核心的作用是複製一份透過 xlrd讀取到記憶體中的 .xls 物件,然後再複製物件上透過 xlwt 修改 .xls 表格的内容。xlutils 可以將 xlrd 的 Book 物件複製轉換為 xlwt 的Workbook 物件,實際使用時通常導入的是模組中的 copy 子模組;例如:import xlutils.copy

安裝

要使用openpyxl模組前必須要經過安裝

# pip3 install openpyxl

使用時須先引入:

import openpyxl

打開一個Excel文件

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) # 將目前的工作表複製

說明:

    1. 只複製值、格式、超連結、註解、大小等屬性
  1. 影像、圖表不會被複製
  2. read only和write only模式無法被複製
  3. 不同活頁簿之間無法進行複製
  4. 新的工作表名稱會是原名稱+Copy

刪除工作表

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參數

回傳row, column, coordinate資訊

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

取得整份文件內容, 使用columns

for column in tab0.columns:
    for cell in column:
        print(cell.value, end='')
    print()

取得整份文件內容, 使用rows

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_style定義

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_type定義

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')

練習

  1. 將一個工作表複製10份

圖表

長條圖

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')

說明:

  1. 需先使用Reference()函式建立資料參考物件。
    • min_col: 資料最小欄位位置(不包含標題欄位)。
    • min_max: 資料最大欄位位置。
    • min_row: 資料最小列位置(包含標題)。
    • max_row: 資料最大的那筆位置。
  2. 使用BarChart() 函式來建立直條圖物件。
  3. add_categories: 設定x座標的標題
  4. chart.type: col=直條圖(預設),bar=橫條圖
  5. chart.title: 圖表的標題
  6. chart.x_axis.title: x座標的標題
  7. chart.y_axis.title: y座標的標題
  8. chart.width: 圖表的寬(單位為公分,預設15公分)
  9. chart.height: 圖表的高(單位為公分,預設7公分)
  10. chart.gapWidth: 每個直條圖之間的間隔(範圍:0~500之間)。
  11. chart.grouping: 長條的的形式
    • standard: 預設,直條圖。
    • stacked: 堆疊直條圖。
    • percentStacked: 百分比堆疊直條圖。
  12. chart.overlap: 堆疊直條圖是否要疊在一起。
輸出

圓餅圖

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')

說明:

  1. 圓餅圖因為一次只處理一欄資料,所以Reference()函式不需要max_col參數。
輸出

環圈圖

如果同時要顯示多組資料,就可以使用環圈圖

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')
輸出