###### tags: `python`

# python讀取/修改excel

pip3 install openpyxl

## 專有名詞
**workbook（活頁簿）**：也就是一個excel檔案
**worksheet（工作表）**：一個excel可以有很多個工作表，目前正在觀看或處理的工作表又稱作「活動中的工作表（active sheet）」
**欄（column）**：工作表中的直欄，以A、B、C...代表
**行（row）**：工作表中的橫列，以1、2、3...代表
**儲存格（cell）**：工作表中的每一個都是一個儲存格

---
## 新增空白excel

import openpyxl

fn = 'new_excel.xlsx'

wb = openpyxl.Workbook()
wb.save(fn)

---
## 新增工作表並指定放置位置

import openpyxl

fn = 'new_excel.xlsx'

wb = openpyxl.Workbook()
wb.create_sheet("Mysheet1", 1) # 新增工作表並指定放置位置
wb.create_sheet("Mysheet0", 0)

wb.save(fn)

---
## 讀取excel檔案每個工作表的名稱

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

print(wb.sheetnames)

print(wb.active)
print(wb.active.title)

## 修改工作表名稱

import openpyxl

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook['AAA']
sheet.title = 'Fruit'
workbook.save("sample.xlsx")

## 修改工作表顏色

import openpyxl

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook.active
sheet.sheet_properties.tabColor = "1072BA"
workbook.save("sample.xlsx")

## 隱藏/取消隱藏工作表

import openpyxl

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook['BBB']
sheet.sheet_state = 'hidden'

# sheet = workbook['BBB']
# sheet.sheet_state = 'visible'

workbook.save("sample.xlsx")

## 修改目前工作表

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

print('excel活動工作表： ', wb.active)

wb.active = 0
print('excel活動工作表： ', wb.active)

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

print('目前工作表： ', wb.active.title)

wb.active = wb['銷售明細表']
print('目前工作表： ', wb.active.title)

## 複製工作表

import openpyxl

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook['BBB']
target = workbook.copy_worksheet(sheet)
target.title = 'new'
workbook.save("sample.xlsx")

## 刪除工作表

import openpyxl

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook['new']
workbook.remove(sheet)
workbook.save("sample.xlsx")

## 讀取工作表所有內容

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

wb.active = 0
ws = wb.active
print('excel活動工作表： ', ws)


for row in ws:
    for cell in row:
        print(cell.value)

print()
print('D1內容： ', ws['D1'].value)

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active

range = ws['A1': 'B6']

for a, b in range:
    print("{0} {1}".format(a.value, b.value))

print()
for a, b, c in ws[ws.dimensions]:
    print(a.value, b.value, c.value)

## 寫入儲存格
所有的修改都只是針對記憶體中的excel檔
只有save才會把修改的內容寫到儲存媒體上

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

wb.active = 0
ws = wb.active

print('B2內容： ', ws['B2'].value)

ws['B2'].value =  20
print('B2內容： ', ws['B2'].value)

ws.cell(column=2, row=3).value = 999

wb.save(fn) # 若給予不同檔名代表另存新檔的意思

## 顯示最大、最小 欄數、列數

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

wb.active = 0
ws = wb.active

print('儲存格 欄名', ws['A1'].column)
print('儲存格 列名', ws['A1'].row)
print('儲存格名', ws['A1'].coordinate)

print('工作表有資料最大欄數', ws.max_column)
print('工作表有資料最小欄數', ws.min_column)
print('工作表有資料最大列數', ws.max_row)
print('工作表有資料最小列數', ws.min_row)

## 循欄列印 ＆ 循列列印

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True) # 要excel開啟才可以看到值，否則會顯示None

wb.active = 0
ws = wb.active

for cell in list(ws.columns)[1]:
    print(cell.value)

print()
for cell in list(ws.rows)[1]:
    print(cell.value)

## 欄名 vs 欄數 轉換

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn, data_only=False) # 要excel開啟才可以看到值，否則會顯示None

wb.active = 0
ws = wb.active

for i in range(1, ws.max_column+1):
    print(i, ' = ', get_column_letter(i))

print("A = ", column_index_from_string('A'))
print("B = ", column_index_from_string('B'))

## 讀取指定區域內容

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn, data_only=False) # 要excel開啟才可以看到值，否則會顯示None

wb.active = 0
ws = wb.active

for row in ws['A2':'D5']:
    for cell in row:
        print(cell.value, end=' ')
    print()

## 新增工作表 ＆ 修改工作表名稱

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn, data_only=False) # 要excel開啟才可以看到值，否則會顯示None

wb.active = 0
ws = wb.active

ws.title = 'hello'
wb.create_sheet('新工作表')
wb.save(fn)

## 新增、修改、刪除工作表

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

wb.active = 0
ws = wb.active

# 新增工作表，若名稱已經存在則原本名稱之後加數字
wb.create_sheet(title='amos') 

# 修改工作表
wb['amos'].title = 'carol'

# 刪除工作表
wb.remove(wb['carol'])

wb.save(fn)

----
##

import openpyxl

fn = 'test5.xlsx'

wb = openpyxl.load_workbook(fn)

ws = wb['hi']

for cell in ws['C']: # 讀取C欄所有資料，並列印出來
    print(cell.value)
    
for cell in ws['2']: # 讀取第2列所有資料，並列印出來
    print(cell.value)

## 合併、解除合併儲存格

import openpyxl

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)

ws = wb.active
# ws.merge_cells('A5:D9')
# ws.unmerge_cells('A5:D9')

# or equivalently
# ws.merge_cells(start_row=5, start_column=1, end_row=9, end_column=4)
ws.unmerge_cells(start_row=5, start_column=1, end_row=9, end_column=4)

wb.save(fn)

## 插入圖片

import openpyxl
from openpyxl.drawing.image import Image

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active

img = Image('logo.png')
ws.add_image(img, 'B5')

wb.save(fn)

## 建立大綱並折疊

import openpyxl
from openpyxl.drawing.image import Image

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active

ws.column_dimensions.group('A','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)

wb.save(fn)

## 改變字體 ＆ 加上註解

import openpyxl
from openpyxl.styles import Font
from openpyxl.comments import Comment

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active

ws['A1'].font = Font(name='Courier', size=36, color='EF0A1D')
ws['A2'].comment = Comment(text="這是註解", author="Amos")

print(ws['A2'].comment.text)
print(ws['A2'].comment.author)

ws['A2'].comment = None # 取消註解

wb.save(fn)

## 新增/刪除 欄、列

import openpyxl
from openpyxl.styles import Font

fn = 'test.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active

# ws.insert_rows(1)
# ws.insert_cols(1, 2)
ws.delete_rows(1, 2)
ws.delete_cols(1, 2)

wb.save(fn)

## 畫統計圖(openpyxl範例）
[官方範例](https://openpyxl.readthedocs.io/en/stable/charts/introduction.html)

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]


for row in rows:
    ws.append(row)


chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"

ws.add_chart(chart2, "G10")


chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'

ws.add_chart(chart3, "A27")


chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'

ws.add_chart(chart4, "G27")

wb.save("bar.xlsx")

## 鎖定、解除鎖定

import openpyxl
from openpyxl.styles import Alignment

fn = 'test5.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active

# ws.freeze_panes = 'B2'
ws.freeze_panes = None

wb.save(fn)

## 設定日期格式

import openpyxl
import datetime

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook['BBB']
sheet['A1'] = datetime.datetime(2010, 7, 21)

# sheet['A1'].number_format = 'yyyy-mm-dd h:mm:ss'
# sheet['A1'].number_format = 'yyyy-mm-dd'
sheet['A1'].number_format = 'dd-mm-yyyy'

workbook.save("sample.xlsx")

## 設定公式

import openpyxl
import datetime

workbook = openpyxl.load_workbook("sample.xlsx")

sheet = workbook['BBB']
sheet['A1'] = 6
sheet['A2'] = 10

sheet["A3"] = "=SUM(A1:A2)"
workbook.save("sample.xlsx")

## 移動資料

import openpyxl

wb = openpyxl.load_workbook('sample.xlsx')
ws = wb.active

ws.move_range("D4:F10", rows=-1, cols=2)

wb.save('sample.xlsx')

## 參考資料
[HTML Color Codes](https://html-color-codes.info/)