# Excel基礎
###### tags: `python` `excel`
## 介紹
#### Excel介紹
Microsoft Excel副檔名為：xlsx或xls，以下為幾本名詞：
- 活頁簿（workbook）：Excel檔案本身又稱為活頁簿。
- 工作表（worksheet）：一個活頁簿由一個或數個工作表組成，一個工作表即為一頁表格文件，可以透過Excel應用程式下方的標籤來切換工作表。
- 欄位（column）：直的方向稱為欄位，名稱由英文字A、B、C...等等依序排列。
- 列（row）：橫的方向稱為列，名稱由１、２、３...等等依序排列。
- 儲存格（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` 模組是 `xlrd` 和 `xlwt` 之間的橋樑，最核心的作用是複製一份透過 `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. 影像、圖表不會被複製
3. read only和write only模式無法被複製
4. 不同活頁簿之間無法進行複製
5. 新的工作表名稱會是原名稱＋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列插入一個新的空白列(第２個參數省略預設為1)
```
#### 刪除列
```
tab0.delete_rows(1) # 刪除第1列
tab0.delete_rows(1, 2) # 起始列號，列數
tab0.delete_rows(1, tab0.max_column) # 刪除整張工作表的資料
```
#### 插入欄
```
tab0.insert_cols(1, 1) # 在第1欄插入一個新的空白欄(第２個參數省略預設為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
```
#### 字型的參數定義
```python=
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定義
```python=
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定義
```python=
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
```
#### 定義
```python=
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份
## 圖表
#### 長條圖
```python=
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: 堆疊直條圖是否要疊在一起。
###### 輸出
#### 圓餅圖
```python=
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參數。
###### 輸出
#### 環圈圖
如果同時要顯示多組資料，就可以使用環圈圖
```python=
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')
```
###### 輸出
#### 折線圖
用來顯示某個時段內，資料的變動及趨勢，可以用來很快地看出該時段趨勢的變化
```python=
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')
```
###### 輸出
#### 區域圖
區域圖與折線圖非常類似，差別是區域圖的下面會被顏色填充
```python=
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')
```
###### 輸出
Sign in with Wallet
Connect another wallet