worksheet.worksheet【ワークシート】モジュール

from openpyxl import Workbook

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# ワークシート名
print(ws.title)
# 出力：Sheet
ws.title = 'SheetX'
wb.create_sheet('SheetY')
print(wb.sheetnames)
# 出力：['SheetX', 'SheetY']

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()

# 列固定 (セルアドレス指定)
ws = wb.active
ws.title = 'SheetA-1'
ws['C2'] = '列固定 (セルアドレス指定)'
print(ws.freeze_panes)
# 出力：None
ws.freeze_panes = 'C1'
print(ws.freeze_panes)
# 出力：C1

# 列固定 (Cell【セル】指定)
ws = wb.create_sheet('SheetA-2')
ws['C2'] = '列固定：Cell【セル】指定'
print(ws.freeze_panes)
# 出力：None
cell = ws['C1']
ws.freeze_panes = cell
print(ws.freeze_panes)
# 出力：C1

# 行固定 (セルアドレス指定)
ws = wb.create_sheet('SheetB-1')
ws['B2'] = '行固定 (セルアドレス指定)'
ws.freeze_panes = 'A3'
print(ws.freeze_panes)
# 出力：A3

# 行固定 (Cell【セル】指定)
ws = wb.create_sheet('SheetB-2')
ws['B2'] = '行固定 (Cell【セル】指定)'
cell = ws['A3']
ws.freeze_panes = cell
print(ws.freeze_panes)
# 出力：A3

# 行・列 固定 (セルアドレス指定)
ws = wb.create_sheet('SheetC-1')
ws['B2'] = '行・列 固定 (セルアドレス指定)'
ws.freeze_panes = 'B3'
print(ws.freeze_panes)
# 出力：B3

# 行・列 固定 (Cell【セル】指定)
ws = wb.create_sheet('SheetC-2')
ws['B2'] = '行・列 固定：Cell【セル】指定'
cell = ws['B3']
ws.freeze_panes = cell
print(ws.freeze_panes)
# 出力：B3

# ウィンドウ枠固定 → 解除 (セルアドレス指定)
ws = wb.create_sheet('SheetD-1')
ws['B2'] = 'ウィンドウ枠固定 → 解除 (セルアドレス指定)'
ws.freeze_panes = 'B3'
print(ws.freeze_panes)
# 出力：B3
ws.freeze_panes = 'A1'
ws.sheet_view.selection = [ws.sheet_view.selection[0]]
ws.sheet_view.selection[0].pane = 'topLeft'
print(ws.freeze_panes)
# 出力：None

# ウィンドウ枠固定 → 解除 (Cell【セル】指定)
ws = wb.create_sheet('SheetD-2')
ws['B2'] = 'ウィンドウ枠固定 → 解除 (Cell【セル】指定)'
cell = ws['B3']
ws.freeze_panes = cell
print(ws.freeze_panes)
# 出力：B3
cell = ws['A1']
ws.freeze_panes = cell
ws.sheet_view.selection = [ws.sheet_view.selection[0]]
ws.sheet_view.selection[0].pane = 'topLeft'
print(ws.freeze_panes)
# 出力：None

# ウィンドウ枠固定 → 解除 (None 指定)
ws = wb.create_sheet('SheetD-3')
ws['B2'] = 'ウィンドウ枠固定 → 解除 (None 指定)'
ws.freeze_panes = 'B3'
print(ws.freeze_panes)
# 出力：B3
ws.freeze_panes = None
ws.sheet_view.selection = [ws.sheet_view.selection[0]]
ws.sheet_view.selection[0].pane = 'topLeft'
print(ws.freeze_panes)
# 出力：None

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.worksheet.worksheet import Worksheet

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()

# タブ色 設定
sheet = wb.create_sheet('SheetRed')
sheet.sheet_properties.tabColor = '00FF0000'
sheet = wb.create_sheet('SheetGreen')
sheet.sheet_properties.tabColor = '00008000'
sheet = wb.create_sheet('SheetBlue')
sheet.sheet_properties.tabColor = '000000FF'

# タブ色 参照
for sheet in wb:
    tabcolor = sheet.sheet_properties.tabColor
    print(f'[{sheet.title}]')
    if tabcolor is not None:
        print(f'{tabcolor.type} {tabcolor.rgb}')
# 出力：
# [Sheet]
# [SheetRed]
# rgb 00FF0000
# [SheetGreen]
# rgb 00008000
# [SheetBlue]
# rgb 000000FF

# 表示状態
sheet = wb.create_sheet('Sheet (hidden)')
sheet.sheet_state = Worksheet.SHEETSTATE_HIDDEN
sheet = wb.create_sheet('Sheet (veryHidden)')
sheet.sheet_state = Worksheet.SHEETSTATE_VERYHIDDEN
for sheet in wb.worksheets:
    print(f'{sheet.title}：{sheet.sheet_state}')
# 出力：
# Sheet：visible
# SheetRed：visible
# SheetGreen：visible
# SheetBlue：visible
# Sheet (hidden)：hidden
# Sheet (veryHidden)：veryHidden

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# データ設定
for i in range(3, 12):
    ws[f'B{i}'] = f'B-{i}'
for i in range(2, 11):
    letter = get_column_letter(i)
    ws[f'{letter}2'] = f'{letter}-2'
font = Font(color='00FF0000')
for adrs in ['B4', 'B7', 'B8', 'B11', 'B2', 'F2', 'G2', 'J2']:
    ws[adrs].font = font

# 行 削除
ws.delete_rows(5, 2)
# 行 追加
ws.insert_rows(6, 3)
# 行 非表示
ws.row_dimensions[10].hidden = True
ws.row_dimensions[11].hidden = True

# 列 削除
ws.delete_cols(3, 3)  # C D E
# 列 追加
ws.insert_cols(4, 2)
# 列 非表示
ws.column_dimensions['G'].hidden = True
ws.column_dimensions['H'].hidden = True

# データ追加
data_1 = ['Data1-1', 'Data1-2', 'Data1-3']
ws.append(data_1)
data_2 = {
    2: 'Data2-2',
    3: 'Data2-3',
    4: 'Data2-4',
}
ws.append(data_2)
data_3 = {
    'C': 'Data3-3',
    'D': 'Data3-4',
    'E': 'Data3-5',
}
ws.append(data_3)

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 単一セル参照
cell = ws['B2']
print(cell)
# 出力：<Cell 'Sheet'.B2>
cell.value = '単一セル'

# 単一セル参照 (Cell)
cell = ws.cell(4, 2)
print(cell)
# 出力：<Cell 'Sheet'.B4>
cell.value = 'Cell'

# セル範囲選択
cells = ws['D2:F4']
print(type(cells))
# 出力：<class 'tuple'>
print(cells)
# 出力：
# ((< Cell 'Sheet'.D2 >, < Cell 'Sheet'.E2 >, < Cell 'Sheet'.F2 >),
#  (< Cell 'Sheet'.D3 >, < Cell 'Sheet'.E3 >, < Cell 'Sheet'.F3 >),
#  (< Cell 'Sheet'.D4 >, < Cell 'Sheet'.E4 >, < Cell 'Sheet'.F4 >))
for cols in cells:
    for cell in cols:
        cell.value = 'セル範囲'

# 単一行参照
cells = ws['6']
print(cells)
# 出力：
# (<Cell 'Sheet'.A6>, <Cell 'Sheet'.B6>, <Cell 'Sheet'.C6>,
#  <Cell 'Sheet'.D6>, <Cell 'Sheet'.E6>, <Cell 'Sheet'.F6>)
for cell in cells:
    cell.value = '単一行'

# 複数行参照
cells = ws['8:10']
print(cells)
# 出力：
# ((<Cell 'Sheet'.A8>, <Cell 'Sheet'.B8>, <Cell 'Sheet'.C8>,
#   <Cell 'Sheet'.D8>, <Cell 'Sheet'.E8>, <Cell 'Sheet'.F8>),
#  (<Cell 'Sheet'.A9>, <Cell 'Sheet'.B9>, <Cell 'Sheet'.C9>,
#   <Cell 'Sheet'.D9>, <Cell 'Sheet'.E9>, <Cell 'Sheet'.F9>),
#  (<Cell 'Sheet'.A10>, <Cell 'Sheet'.B10>, <Cell 'Sheet'.C10>,
#   <Cell 'Sheet'.D10>, <Cell 'Sheet'.E10>, <Cell 'Sheet'.F10>))
for cols in cells:
    for cell in cols:
        cell.value = '複数行'

# 単一列参照
cells = ws['H']
print(cells)
# 出力：
# (<Cell 'Sheet'.H1>, <Cell 'Sheet'.H2>, <Cell 'Sheet'.H3>,
#  <Cell 'Sheet'.H4>, <Cell 'Sheet'.H5>, <Cell 'Sheet'.H6>,
#  <Cell 'Sheet'.H7>, <Cell 'Sheet'.H8>, <Cell 'Sheet'.H9>,
#  <Cell 'Sheet'.H10>)
for cell in cells:
    cell.value = '単一列'

# 複数列参照
cells = ws['J:L']
print(cells)
# 出力：
# ((<Cell 'Sheet'.J1>, <Cell 'Sheet'.J2>, <Cell 'Sheet'.J3>,
#   <Cell 'Sheet'.J4>, <Cell 'Sheet'.J5>, <Cell 'Sheet'.J6>,
#   <Cell 'Sheet'.J7>, <Cell 'Sheet'.J8>, <Cell 'Sheet'.J9>,
#   <Cell 'Sheet'.J10>),
#  (<Cell 'Sheet'.K1>, <Cell 'Sheet'.K2>, <Cell 'Sheet'.K3>,
#   <Cell 'Sheet'.K4>, <Cell 'Sheet'.K5>, <Cell 'Sheet'.K6>,
#   <Cell 'Sheet'.K7>, <Cell 'Sheet'.K8>, <Cell 'Sheet'.K9>,
#   <Cell 'Sheet'.K10>),
#  (<Cell 'Sheet'.L1>, <Cell 'Sheet'.L2>, <Cell 'Sheet'.L3>,
#   <Cell 'Sheet'.L4>, <Cell 'Sheet'.L5>, <Cell 'Sheet'.L6>,
#   <Cell 'Sheet'.L7>, <Cell 'Sheet'.L8>, <Cell 'Sheet'.L9>,
#   <Cell 'Sheet'.L10>))
for cols in cells:
    for cell in cols:
        cell.value = '複数列'

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# ワークシート繰り返し処理
for row in ws:
    print(row)
# 出力：(なし)
ws['C3'] = 'dummy'
for row in ws:
    print(row)
    for cell in row:
        cell.value = 'VALUE'
# 出力：
# (<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>)
# (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>)
# (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>)

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# １列
idx = 1
for col in ws.iter_cols(2, 2, 2, 4):
    print(col)
    for cell in col:
        cell.value = f'{idx} {cell.column_letter}-{cell.row}'
        idx += 1
# 出力：
# (<Cell 'Sheet'.B2>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.B4>)

# 複数列
idx = 1
for col in ws.iter_cols(4, 5, 2, 4):
    print(col)
    for cell in col:
        cell.value = f'{idx} {cell.column_letter}-{cell.row}'
        idx += 1
# 出力：
# (<Cell 'Sheet'.D2>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.D4>)
# (<Cell 'Sheet'.E2>, <Cell 'Sheet'.E3>, <Cell 'Sheet'.E4>)

# １行
idx = 1
for row in ws.iter_rows(7, 7, 2, 4):
    print(row)
    for cell in row:
        cell.value = f'{idx} {cell.column_letter}-{cell.row}'
        idx += 1
# 出力：
# (<Cell 'Sheet'.B7>, <Cell 'Sheet'.C7>, <Cell 'Sheet'.D7>)

# 複数行
idx = 1
for row in ws.iter_rows(9, 10, 2, 4):
    print(row)
    for cell in row:
        cell.value = f'{idx} {cell.column_letter}-{cell.row}'
        idx += 1
# 出力：
# (<Cell 'Sheet'.B9>, <Cell 'Sheet'.C9>, <Cell 'Sheet'.D9>)
# (<Cell 'Sheet'.B10>, <Cell 'Sheet'.C10>, <Cell 'Sheet'.D10>)

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.styles import Border, Side, borders

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 罫線
ws.column_dimensions['B'].width = 30
COLOR_BORDER = '000000FF'
side = Side(
    borders.BORDER_THICK,
    COLOR_BORDER)
border = Border(side, side, side, side)

# セル結合 (範囲文字列)
ws.merge_cells('B2:D4')
ws['B2'] = 'セル結合 (範囲文字列)'
for row in ws['B2:D4']:
    for cell in row:
        cell.border = border

# セル結合 (行・列 指定)
ws.merge_cells(
    start_row=6,
    start_column=2,
    end_row=8,
    end_column=4)
ws.cell(6, 2, 'セル結合 (行・列 指定)')
for row in range(6, 9):
    for col in range(2, 5):
        cell = ws.cell(row, col)
        cell.border = border

# セル結合解除 (範囲文字列)
ws.merge_cells('B10:D12')
ws['B10'] = 'セル結合'
ws.unmerge_cells('B10:D12')
ws['B10'] = 'セル結合解除 (範囲文字列)'
for row in ws['B10:D12']:
    for cell in row:
        cell.border = border

# セル結合解除 (行・列 指定)
ws.merge_cells(
    start_row=14,
    start_column=2,
    end_row=16,
    end_column=4)
ws.cell(14, 2, 'セル結合')
ws.unmerge_cells(
    start_row=14,
    start_column=2,
    end_row=16,
    end_column=4)
ws.cell(14, 2, 'セル結合解除 (行・列 指定)')
for row in range(14, 17):
    for col in range(2, 5):
        cell = ws.cell(row, col)
        cell.border = border

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# セルのサイズ (列の幅・行の高さ)
ws['B2'] = 'セルのサイズ'
# 列の幅 (文字数単位)
ws.column_dimensions['B'].width = 20
letter = get_column_letter(3)
print(letter)
# 出力：C
ws.column_dimensions[letter].width = 2
# 行の高さ (ポイント単位)
ws.row_dimensions[2].height = 40

# ワークブック保存
wb.save(WORKBOOK)