読者です 読者をやめる 読者になる 読者になる

PythonでExcel操作

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.4.0-a1 documentation

import openpyxl

# ファイル読み込み
wb = openpyxl.load_workbook('excel.xlsx', data_only = True)

# read_only Trueにすると読み取り専用
# data_only Trueにすると計算式の結果を取得

# シート一覧取得
wb.get_sheet_names()
[https://openpyxl.readthedocs.org/en/latest/:title]

# シート指定
ws = wb.get_sheet_by_name('Sheet1')

# アクティブシート
ws = wb.active

# 最終列の列番号
# ws.get_highest_column()
ws.max_column

# 最終行の行番号
# ws.get_highest_row()
ws.max_row

# セル名
ws['A1'].value

# セル名でセルを指定
a2 = ws.cell('A2')
a2.value

# 行順
ws.rows

# 列順
ws.colmuns

# rowとcolumnでセルを指定
a3 = ws.cell(row = 3, column = 1)
a3.value

d = ws.cell(row=4, column=2, value=10)

# 挿入
ws.append([1, 2, 3])


# 範囲
cell_range = ws['A1':'C2']
cell_range = ws['A1:C2']

# 使える
ws.iter_rows()
ws.iter_rows('A1:C2')


# 使えない
ws.iter_cols()
ws.iter_cols('A1:C2')
ws.iter_rows(min_row=1, max_col=10, max_row=10)
ws.iter_cols(min_row=1, max_col=3, max_row=2)

>>> tuple(ws.rows)
((<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>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))


>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))

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

for column in ws.columns:
	for cell in column:
		print(cell.value)

# ファイル書き込み
wb.save(filename = 'excel.xlsx')