【Python】Excelを操作してみよう！

openpyxlは、拡張子が.xlsx、.xlsmのブックを読み書きできますが、拡張子が.xlsは使用出来ません。また、Excel2010以降のExcelファイルを対象に開発しているので、Excel2007のブックは正常に読み書き出来ない可能性があります。

ブックの操作

ブックの読み込み

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
print(wb.sheetnames)

>>> ['1月売上', '2月売上']

ブックの上書き保存

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
#読み込みと同じファイルリンク
wb.save("/Users/TY/Downloads/data/売上データ.xlsx")

ブックの別名で保存

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
#読み込みと違うファイルリンク
wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

新規フォルダの作成とブック保存

import openpyxl
from pathlib import Path

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
#フォルダを作成
Path("NewFolder").mkdir(exist_ok=True)
wb.save("/Users/TY/Downloads/data/NewFolder/売上データ.xlsx")

シートの操作

シートの読み込み

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

#インデックス番号で指定
ws1 = wb.worksheets[0]
ws2 = wb.worksheets[1]

print(ws1.title, ws2.title)

>>> 1月売上 2月売上

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

#シート名で指定
ws1 = wb["1月売上"]
ws2 = wb["2月売上"]

print(ws1.title, ws2.title)

>>> 1月売上 2月売上

シートの挿入

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

# 先頭のシートに挿入
ws_new1 = wb.create_sheet(index=0)
# 末尾のシートに挿入
ws_new2 = wb.create_sheet()

wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

シートの削除

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb.worksheets[0]
#ws = wb["1月売上"]

wb.remove(ws)
wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

シート名の変更

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
#ws = wb.worksheets[0]
ws = wb["1月売上"]
ws.title = "睦月"

wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

シートの移動

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

#[-1]で末尾のシートを指定
ws = wb.worksheets[-1]
#ws = wb["2月売上"]

#offsetはマイナスで左、プラスで右に移動
wb.move_sheet(ws, offset=-1)
wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

末尾から先頭まで移動する場合は、シートの枚数を計算して移動枚数を決めます。

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

#[-1]で末尾のシートを指定
ws = wb.worksheets[-1]
#ws = wb["2月売上"]

end_to_top = 1 - len(wb.worksheets)
wb.move_sheet(ws, offset=end_to_top)

wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

シートのコピー（同一ブック内）

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

#ws = wb.worksheets[0]
ws = wb["1月売上"]

ws_copy = wb.copy_worksheet(ws)
# シート名変更
ws_copy.title = "1月売上_copy"

wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

シートのコピー（新規ブック内）

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")

#コピーしたいシート以外は削除
for ws in wb.worksheets:
    if ws.title != "1月売上":
        wb.remove(ws)

#別名で名前を付けて保存
wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

シートの結合（複数ブック）

import openpyxl
from pathlib import Path

# 各ブックの読み込み
wb_list = []
for file in Path("/Users/TY/Downloads/data/NewFolder").glob("*.xlsx"):
    wb = openpyxl.load_workbook(file, data_only=True)
    wb_list.append(wb)

# 保存先の新しいブック
wb_new = openpyxl.Workbook()

# 1つ目のブックからシート名のリストを取得
sheet_names = wb_list[0].sheetnames

# シート名ごとに処理
for sheet_name in sheet_names:
    # このシートに書き込む
    ws_new = wb_new.create_sheet(sheet_name)

    # 1つ目のブック有無の変数定義
    is_first_book = True

    # このリストに全ブックから読み取ったデータを格納
    row_list = []
    
    # 各ブックごとにデータ読み込み
    for wb in wb_list:
        # 読み込むシート
        ws = wb[sheet_name]

        if is_first_book:
            start_row = 1
        else:
            start_row = 2

        # シートから１行ずつデータを読み込む
        for row in ws.iter_rows(min_row=start_row):
            # ヘッダーより下で空欄になったら読み込み終了
            if row[0].row > 1 and row[0].value is None:
                break
            value_list = []
            for c in row:
                value_list.append(c.value)
            row_list.append(value_list)

        is_first_book = False

    # 書き込み時の行番号
    row_num = 1

    # シートに１行ずつデータを書き込む
    for row in row_list:
        # １行分のデータを書き込む
        ws_new.append(row)
        
        # データ部分のA列に日付の表示形式を設定し、F列の数式を書き換え
        if row_num > 1:
            ws_new.cell(row_num, 2).number_format = "yyyy/m/d"
            ws_new.cell(row_num, 6).value = "=D" + str(row_num) + "*E" + str(row_num)
        row_num = row_num + 1

# 作成時の既存シートを取り除く
ws_first = wb_new.worksheets[0]
wb_new.remove(ws_first)

wb_new.save("/Users/TY/Downloads/data/NewFolder/売上_merge.xlsx")

セルの操作

セルの指定

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

#セル番地で指定
cell_1 = ws["A1"]
print(cell_1.coordinate, cell_1.row, cell_1.column, sep=", ")

>>> A1, 1, 1

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

#行列の番号で指定
cell_2 = ws.cell(1, 1)
print(cell_2.coordinate, cell_2.row, cell_2.column, sep=", ")

>>> A1, 1, 1

セルの読み込み

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

cell_1 = ws["A4"]
cell_2 = ws["B4"]
cell_3 = ws["C4"]
cell_4 = ws["D4"]
cell_5 = ws["E4"]
cell_6 = ws["F4"]
print(cell_1.value, cell_2.value,cell_3.value, cell_4.value, cell_5.value, cell_6.value, sep=", ")

>>> A社, 2022-01-03 00:00:00, 文具, 800, 7, =D4*E4

日付と数式はExcelのセル表示と違う結果が出力されていることが確認出来ます。
日付が入ったセル：datetime型（日付専用の型）で時刻まで取得される
数式が入ったセル：数式が取得される

data_only = Trueを追加すると、計算結果の数値を取得出来ます。
実際にやってみると、Noneと出力されています。これは、Pythonでブックを保存した場合に起こる現象で、Excelを再度開き直して、手動で保存すれば解消されます。

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx", data_only=True)
ws = wb["1月売上"]

cell_1 = ws["A4"]
cell_2 = ws["B4"]
cell_3 = ws["C4"]
cell_4 = ws["D4"]
cell_5 = ws["E4"]
cell_6 = ws["F4"]
print(cell_1.value, cell_2.value,cell_3.value, cell_4.value, cell_5.value, cell_6.value, sep=", ")

>>> A社, 2022-01-03 00:00:00, 文具, 800, 7, None

該当のExcelブックを開き、手動で保存して閉じる。

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx", data_only=True)
ws = wb["1月売上"]

cell_1 = ws["A4"]
cell_2 = ws["B4"]
cell_3 = ws["C4"]
cell_4 = ws["D4"]
cell_5 = ws["E4"]
cell_6 = ws["F4"]
print(cell_1.value, cell_2.value,cell_3.value, cell_4.value, cell_5.value, cell_6.value, sep=", ")

>>> A社, 2022-01-03 00:00:00, 文具, 800, 7, 5600

数式の値が出力されていることが確認出来ます。

セルに値を入れる

import datetime
dt = datetime.datetime(年, 月, 日, 時, 分, 秒)

import openpyxl
import datetime

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

cell_1 = ws["A8"]
cell_1.value = "D社"
cell_2 = ws["B8"]
cell_2.number_format = "yyyy/m/d" 
cell_2.value = datetime.datetime(2022, 1, 7)
cell_3 = ws["C8"]
cell_3.value = "玩具"
cell_4 = ws["D8"]
cell_4.value = 1000
cell_5 = ws["E8"]
cell_5.value = 8
cell_6 = ws["F8"]
cell_6.value = "=D8*E8"

wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

セルを1行ずつ読み込む

ループ処理をするには、for文を使用します。
for row in ws.iter_rows(min_row=行番号, min_col=列番号):
処理
iter_rows()はデータが入力されている最終行を自動的に認識してそこまで1行ずつ列挙する関数です。そのため、空白であっても書式などが残っていると認識してしまうため、空白があればスキップする処理を追加します。

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

for row in ws.iter_rows(min_row=1):
    
    #1列目が空白であればスキップ
    if row[0].value is None:
        break
    
    #値を格納するための空のリストを作成
    value_list = []
    
    #セルの値を1行ずつリストに追加
    for cell in row:
        value_list.append(cell.value)
    
    print(value_list)

>>>
['会社名', '売上日', '商品名', '単価', '数量', '小計']
['A社', datetime.datetime(2022, 1, 1, 0, 0), 'お菓子', 100, 300, '=D2*E2']
['B社', datetime.datetime(2022, 1, 2, 0, 0), '文具', 500, 4, '=D3*E3']
['A社', datetime.datetime(2022, 1, 3, 0, 0), '文具', 800, 7, '=D4*E4']
['C社', datetime.datetime(2022, 1, 4, 0, 0), 'お菓子', 300, 5, '=D5*E5']
['B社', datetime.datetime(2022, 1, 5, 0, 0), '寝具', 20000, 1, '=D6*E6']
['C社', datetime.datetime(2022, 1, 6, 0, 0), '寝具', 3000, 6, '=D7*E7']

セルに1行ずつ書き込み

import openpyxl
from datetime import datetime

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

# 書き込むデータ
data_list = [
["D社",datetime(2022,1,7),  "玩具", 250, 10],
["D社",datetime(2022,1,8),  "玩具", 50, 6],
["D社",datetime(2022,1,9),  "玩具", 300, 4],
]

# 書き込み開始行番号
row_num = 8

for row in data_list:
    cell_1 = ws["A" + str(row_num)]
    cell_1.value = row[0]
    cell_2 = ws["B" + str(row_num)]
    cell_2.value = row[1]
    cell_2.number_format = "yyyy/m/d"
    cell_3 = ws["C" + str(row_num)]
    cell_3.value = row[2]
    cell_4 = ws["D" + str(row_num)]
    cell_4.value = row[3]
    cell_5 = ws["E" + str(row_num)]
    cell_5.value = row[4]
    cell_6 = ws["F" + str(row_num)]
    
    # 数式
    c6.value = "=D" + str(row_num) + "*E" + str(row_num)
    # 次の行に進む
    row_num = row_num + 1

wb.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

応用テクニック

特定の行を除いて、読み込む

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

# 全データリスト
data_list = []

for row in ws.iter_rows(min_row=2):
    if row[0].value is None:
        break
    
    # 何か入力されている行は除外
    #if row[4].value is not None:
    
    #単価が1000以上の行は除外
    if int(row[3].value) >= 1000:  
        continue
    value_list = []
    for cell in row:
        value_list.append(cell.value)
    data_list.append(value_list)

# 確認
for data in data_list:
    print(data)

>>>
['A社', datetime.datetime(2022, 1, 1, 0, 0), 'お菓子', 100, 300, '=D2*E2']
['B社', datetime.datetime(2022, 1, 2, 0, 0), '文具', 500, 4, '=D3*E3']
['A社', datetime.datetime(2022, 1, 3, 0, 0), '文具', 800, 7, '=D4*E4']
['C社', datetime.datetime(2022, 1, 4, 0, 0), 'お菓子', 300, 5, '=D5*E5']

条件に一致する行の抽出

import openpyxl

wb = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws = wb["1月売上"]

# 全データリスト
data_list = []

for row in ws.iter_rows(min_row=2):
    if row[0].value is None:
        break
    value_list = []
    for cell in row:
        value_list.append(cell.value)
    data_list.append(value_list)

# 検索結果リスト
target_list = []

# 顧客の検索
for data in data_list:
    # 先頭検索条件：会社名がAから始まる（末尾検索：endswitch）
    if data[0].startswith("A"):
        target_list.append(data)

# 確認
for target in target_list:
    print(target)

>>>
['A社', datetime.datetime(2022, 1, 1, 0, 0), 'お菓子', 100, 300, '=D2*E2']
['A社', datetime.datetime(2022, 1, 3, 0, 0), '文具', 800, 7, '=D4*E4']

別ブックから条件に合うデータを転記

import openpyxl

# マスタのブック、シート
wb_master = openpyxl.load_workbook("/Users/TY/Downloads/data/会社所在.xlsx")
ws_master = wb_master["所在地"]

# 売上データのブック、シート
wb_data = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws_data = wb_data["1月売上"]

# マスタの全データリスト
data_list = []

for row in ws_master.iter_rows(min_row=2):
    if row[0].value is None:
        break
    value_list = []
    for cell in row:
        value_list.append(cell.value)
    data_list.append(value_list)

# データ行番号
row_num = 1
# 列名追加
ws_data["G" + str(row_num)].value = "所在地"

# データを1行ずつ処理
for row in ws_data.iter_rows(min_row=2):
    row_num = row_num + 1

    # 転記先の会社名「A列」
    data_id = row[0].value

    # 検索
    for data in data_list:
        # 検索条件（転記元の会社名=転記先の会社名）
        if data[0] == data_id:
            # 転記元の所在地を転記先の「G列」に追加
            ws_data["G" + str(row_num)].value = data[1]
            break

# 別名で保存
wb_data.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

条件毎にシートを分けして転記

import openpyxl

# マスタのブック、シート
wb_master = openpyxl.load_workbook("/Users/TY/Downloads/data/会社所在.xlsx")
ws_master = wb_master["所在地"]

# 売上データのブック、シート
wb_data = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx", data_only=True)
ws_data = wb_data["1月売上"]

# マスタの全データリスト
data_list = []

for row in ws_master.iter_rows(min_row=2):
    if row[0].value is None:
        break
    value_list = []
    for cell in row:
        value_list.append(cell.value)
    data_list.append(value_list)

# 会社名ごとに処理
for data in data_list:
    data_id = data[0]
    data_name = data[1]
    # データの検索
    data_list = []
    for row in ws_data.iter_rows(min_row=2):
        value_list = []
        for cell in row:
            value_list.append(cell.value)
        # 検索条件
        if value_list[0] == data_id:
            data_list.append(value_list)

    if len(data_list) > 0:
        # 会社名をシート名にしてシート追加
        ws_new = wb_data.create_sheet(title=data_name)
        # ヘッダー書き込み
        ws_new.append(["会社名","売上日","商品名","単価","数量","小計"])
        # データ書き込み
        row_num = 2
        for data in data_list:
            ws_new.append(data)
            ws_new.cell(row_num,2).number_format = "yyyy/m/d"
            row_num = row_num + 1

# 別ブックで保存
wb_data.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

条件毎に集計（件数）

import openpyxl

# マスタのブック、シート
wb_master = openpyxl.load_workbook("/Users/TY/Downloads/data/会社所在.xlsx")
ws_master = wb_master["所在地"]

# データのブック、シート
wb_data = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx")
ws_data = wb_data["1月売上"]

# マスタの全データリスト
data_list = []

for row in ws_master.iter_rows(min_row=2):
    if row[0].value is None:
        break
    value_list = []
    for cell in row:
        value_list.append(cell.value)
    data_list.append(value_list)

# 集計結果を入れるリスト
result_list = []

# 顧客ごとに処理
for data in data_list:
    data_id = data[0]
    data_name = data[1]
    # 売上件数のカウント
    count_sales = 0
    # 売上データの検索
    for row in ws_data.iter_rows(min_row=2):
        # 検索条件
        if row[0].value == data_id:
            count_sales = count_sales + 1

    if count_sales > 0:
        # 会社名、売上日、売上件数の3項目を追加
        result_list.append([data_id, data_name, count_sales])

# 集計結果用シートを追加
ws_new = wb_data.create_sheet(title="売上件数")
# ヘッダー書き込み
ws_new.append(["会社名","売上日","売上件数"])
# 集計結果書き込み
for result in result_list:
    ws_new.append(result)

# 別名で保存
wb_data.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")

条件毎に集計（金額）

import openpyxl

# マスタのブック、シート
wb_master = openpyxl.load_workbook("/Users/TY/Downloads/data/会社所在.xlsx")
ws_master = wb_master["所在地"]

# データのブック、シート
wb_data = openpyxl.load_workbook("/Users/TY/Downloads/data/売上データ.xlsx", data_only=True)
ws_data = wb_data["1月売上"]

# マスタの全データリスト
data_list = []

for row in ws_master.iter_rows(min_row=2):
    if row[0].value is None:
        break
    value_list = []
    for cell in row:
        value_list.append(cell.value)
    data_list.append(value_list)

# 集計結果を入れるリスト
result_list = []

# 条件ごとに処理
for data in data_list:
    data_id = data[0]
    data_name = data[1]
    # 売上の集計
    sum_sales = 0
    # 売上データの検索
    for row in ws_data.iter_rows(min_row=2):
        # 検索条件
        if row[0].value == data_id:
            # 売上の額は「F列（インデックス=5）」
            sum_sales = sum_sales + row[5].value

    if sum_sales > 0:
        # 会社名、売上日、売上合計の3項目を追加
        result_list.append([data_id, data_name, sum_sales])

# 集計結果用シートを追加
ws_new = wb_data.create_sheet(title="売上別")
# ヘッダー書き込み
ws_new.append(["会社名","売上日","売上計"])
# 集計結果書き込み
for result in result_list:
    ws_new.append(result)

# 別名で保存
wb_data.save("/Users/TY/Downloads/data/更新_売上データ.xlsx")