既存のWorkbookのopen

import openpyxl
wb = openpyxl.load_workbook(pass_obj)

新規のWorkbookのopen

wb = openpyxl.Workbook()

アクティブなWorksheetを参照する

sh = wb.active

WorkbookからWorksheetを参照する

for sh in wb:

Worksheetのセルを参照する

sh.cell(dt_row, dt_column).value

Workbookの保存

wb.save(".\test.xlsx")

CSVファイルを書き込みモードで開く

with open(".\test.csv","w",encoding="utf_8_sig") as fp:

CSVファイルWriterを取得する

writer = csv.writer(fp, lineterminator="\n")

CSVファイルへ1行ずつ書き込む

for row in sh.rows:
    writer.writerow([col.value for col in row]) #リスト内包表記によるリスト作成

Excelの参照

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

wb = openpyxl.load_workbook('example.xlsx')
print(wb.get_sheet_names())

sheet = wb.get_sheet_by_name('Sheet3')
print(sheet)
print(sheet.title)

another_sheet = wb.active
print(another_sheet)

sheet = wb.get_sheet_by_name('Sheet1')
print(sheet['A1'])
print(sheet['A1'].value)

c = sheet['B1']
print(c.value)
print('行' + str(c.row) + ', 列 ' + str(c.column) + ' は ' + c.value)
print('セル ' + c.coordinate + ' は ' + c.value)
print(sheet['C1'].value)

print(sheet.cell(row=1, column=2))
print(sheet.cell(row=1, column=2).value)

for i in range(1, 8, 2):
    print(i, sheet.cell(row=i, column=2).value)

print('Max Row:' + str(sheet.max_row))
print('Max Column:' + str(sheet.max_column))

print(get_column_letter(1))
print(column_index_from_string('A'))
print(get_column_letter(27))
print(column_index_from_string('AA'))
print(get_column_letter(900))
print(column_index_from_string('AHP'))

print(tuple(sheet['A1':'C3']))

for row_of_cell_objects in sheet['A1':'C3']:
    for cell_obj in row_of_cell_objects:
        print(cell_obj.coordinate, cell_obj.value)
    print('--- END OF ROW ---')

sheet = wb.active
for cell_obj in list(sheet.columns)[1]:
    print(cell_obj.value)

シートの集計の例

import openpyxl, pprint
print('ワークブックを開いています...')
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet_name = 'Population by Census Tract'
sheet = wb[sheet_name]
county_data = {}

print('行を読み込んでいます...')
for row in range(2, sheet.max_row + 1):
    state = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop = sheet['D' + str(row)].value

    # キーが確実に存在するようにする
    county_data.setdefault(state, {})
    county_data[state].setdefault(county, {'tracts': 0, 'pop': 0})

    county_data[state][county]['tracts'] += 1
    county_data[state][county]['pop'] += int(pop)

#print(county_data[state][county]['tracts'])
#print(county_data[state][county]['pop'])
print('結果を書き込み中...')
result_file = open('census2010.py', 'w')
result_file.write('all_data = ' + pprint.pformat(county_data))
result_file.close()
print('完了')

集計データのインポートの例

import census2010
print(census2010.all_data['AK']['Anchorage'])
anchorage_pop = census2010.all_data['AK']['Anchorage']['pop']
print('2010年のアンカレッジの人口は' + str(anchorage_pop))

シートの追加・変更

import openpyxl

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
sheet.title = 'Sheet4'

wb.create_sheet(index=0, title='First Sheet')
wb.create_sheet(index=2, title='Middle Sheet')
print(wb.get_sheet_names())

sheet['A1'] = 'Hello world!'

wb.save('example_copy.xlsx')

セルの更新(条件一致したものを置き換える)

import openpyxl

wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']

PRICE_UPDATES = {'Garlic': 3.07,
                'Celery': 1.19,
                'Lemon': 1.27}

for row_num in range(2, sheet.max_row + 1):
    produce_name = sheet.cell(row=row_num, column=1).value
    if produce_name in PRICE_UPDATES:
        sheet.cell(row=row_num, column=2).value = PRICE_UPDATES[produce_name]

wb.save('updatedProduceSales.xlsx')

数式の更新

import openpyxl
import win32com.client as win32
import os

wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')

# 数式のまま開く
wb_formulas = openpyxl.load_workbook('writeFormula.xlsx')
sheet = wb_formulas.active
print(sheet['A3'].value)

# 数式の計算結果を反映するために一度Excelを開く
excel = win32.gencache.EnsureDispatch("Excel.Application")
path = os.getcwd() + '\writeFormula.xlsx'
workbook = excel.Workbooks.Open(path)
workbook.Save()
workbook.Close()
excel.Quit()

# 数式の計算結果を反映した状態で開く
wb_data_only = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)
sheet = wb_data_only.active
print(sheet['A3'].value)