[python 3] Excel with Python3

README.md

Excel with Python3

OpenPyXLを使って、Excelを操作する。

Usage

1.Prepare the excel file

ほげ.xlsx

2.Excecution

python ex.py

Installation

pip install openpyxl

ex.py

# pip install openpyxl
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

file = 'ほげ.xlsx'

wb = load_workbook(filename = file)

# セル値表示
print(wb['Sheet1']['a1'].value)

# セル値設定
wb['Sheet1']['a2'].value = 'Hello World from Python3' # not save
print(wb['Sheet1']['a2'].value)

wb.save(filename = file) # file save with overwrite !

# 複数行 / .iter_rows is deprecated.
for id_area in wb['Sheet1']['A1:A3']:
    for data in id_area:
        print(data.value)

print('----------------')

for id_area in wb['Sheet1']['A1:B3']:
    for data in id_area:
        print(data.value)

# Sheet
for sheet in wb:
    print(sheet.title)
    print(sheet['A1'].value)

print('----------------')

# iterate all cell
# 値が入っているRowがTuple(列)で返却される。
# for cell in wb['Sheet1']:
#     x, y, z, _ = cell
#     print(x.value)
#     print(y.value)
#     print(z.value)

print('----------------')

# 結合セルは、左上のセル番号で値を取得できる
sheet = wb['Sheet1']
print(sheet.cell(row=5, column=1).value)
print(sheet.cell(row=6, column=1).value)
print('~')
print(sheet.cell(row=13, column=1).value)
print(sheet.cell(row=14, column=1).value)
print('~')
print(sheet.cell(row=15, column=1).value)
print(sheet.cell(row=15, column=2).value)
print(sheet.cell(row=16, column=1).value)
print(sheet.cell(row=16, column=2).value)

# 背景に色付け
def cell_fill(cell_range, fill):
    rows = sheet[cell_range]
    for row in rows:
        for c in row:
            if fill:
                c.fill = PatternFill("solid", fgColor="DDDDDD")
            else:
                c.fill = PatternFill(fgColor="000000")
#cell_fill("G1:H2", False)
#wb.save(file)

# セル結合
def cell_merge(merge):
    if merge:
        sheet.merge_cells('G1:H2')
    else:
        sheet.unmerge_cells('G1:H2')
cell_merge(True)
wb.save(file)

ほげ.xlsx

https://tonari-it.com/python-openpyxl-beginner/

https://qiita.com/tftf/items/07e4332293c2c59799d1