Python

❏ 参考書

PythonでExcel、メール、Webを自動化する本

≫ 0-1　Pythonをインストール

◎ 最新版のインストール

◯ ダウンロードページ

https://www.python.org/downloads/

◯ ダウンロードファイル

python-3.12.0-amd64.exe


≫ 0-2　Pythonに機能を追加

◎ コマンドでPythonに機能を追加

◯ コマンドプロンプトを起動してコマンド実行

py -m pip install openpyel

py -m pip install selenium

◯ インストール確認

py -m pip list 

C:\Users\user>py -m pip list
Package          Version
---------------- ---------
attrs            23.1.0
certifi          2023.7.22
cffi             1.16.0
et-xmlfile       1.1.0
h11              0.14.0
idna             3.4
openpyxl         3.1.2
outcome          1.2.0
pip              23.2.1
pycparser        2.21
PySocks          1.7.1
selenium         4.14.0
sniffio          1.3.0

≫ 2-2　ブックの開き方、保存方法

◎ ブックの読み込む

◯ wb = openpyxl.load_workbook("売上データ.xlsx")

ブックの読み込み

◯ wb.sheetnames

シート名の一覧を取得 

import openpyxl
wb = openpyxl.load_workbook("売上データ.xlsx")
print(wb.sheetnames)
実行結果 
['4月売上', '5月売上', '6月売上']

◎ ブックを上書く

◯ wb.save("売上データ.xlsx")

ブックを保存

  

import openpyxl
wb = openpyxl.load_workbook("売上データ.xlsx")
wb.save("売上データ.xlsx")

◎ 保存時にフォルダーを指定

◯ from pathlib import Path

fromの後ろにモジュール名、importの後ろにモジュール内の使いたい機能を指定

◯ Path("Copy").mkdir(exist_ok=True)

フォルダーを作成。exist_ok=Trueで既存の場合は作成されない 

import openpyxl
from pathlib import Path
wb = openpyxl.load_workbook("売上データ.xlsx")
Path("Copy").mkdir(exist_ok=True)
wb.save("Copy/売上データ_コピー.xlsx")

≫ 2-3　プログラムでセルの値を読み書きする

◎ シートの指定

◯ ws = wb["4月売上"]

シート名でシートを指定

◯ ws2 = wb.worksheets[1]

0から始まる番号（インデックス）でシートを指定

◯ ws.title

シート名を取得 

import openpyxl
wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["4月売上"]
print(ws.title)
ws2 = wb.worksheets[1]
print(ws2.title)
実行結果 
4月売上
5月売上

◎ セルの取得

◯ c = ws["A1"]

セルの取得をセル番地で指定

◯ c2 = ws.cell(1, 1)

セルの取得を1から始まる行列番号で指定

◯ c.coordinate

セル番地を取得

◯ c.row

セルの行番号を取得

◯ c.column

セルの列番号を取得 

import openpyxl

wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["4月売上"]

c = ws["A1"]
print(c.coordinate)
print(c.row)
print(c.column)
c2 = ws.cell(1, 1)
print(c2.coordinate)
print(c2.row)
print(c2.column)
実行結果 
A1
1
1
A1
1
1

◎ セルの値を読み取る

◯ c1.value

セルの値の読み取り

◯ 実行結果

数値、文字列はセルの表示のまま取得されるが、日付、数式はセルの表示と異なった値が取得される。

・日付はDatetime型で取得される。セル表示が年月日

　だけでもPythonの出力では時刻まで表示される

・数式が入力されているセルからは、数式の計算結果

　ではなく数式そのものが取得される。

　ただしload_workbook()のかっこ内にdata_only=True

　を記入すると計算結果の数値が取得できる

　Pythonで保存しただけのブックの場合、計算が実行

　されてないので「None」と表示される。この場合、

　Excelを開き上書き保存する（計算を実行した状態で

　保存する） 

import openpyxl

wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["4月売上"]

c1 = ws["A4"]
print(c1.value)
c2 = ws["B4"]
print(c2.value)
c3 = ws["C4"]
print(c3.value)
c4 = ws["D4"]
print(c4.value)
c5 = ws["E4"]
print(c5.value)
c6 = ws["F4"]
print(c6.value)
実行結果 
2020-04-01 00:00:00
株式会社 鈴木商店
商品C
1200
20
=D4*E4

◎ セルに値を書き込む、セルに表示形式を設定

◯ c1.value = 値

セルに値を書き込む

◯ c1.value = datetime.datetime(2020, 4, 30)

Datetime型の日付を書き込む

 

◯ セルに数式を書き込む

c6.value = "=D10*E10"

◯ c1.number_format = "yyyy/mm/dd"

日付の表示形式を設定

◯ c4.number_format = "#,##0"

数値の表示形式を設定 

import openpyxl
import datetime

wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["4月売上"]

c1 = ws["A10"]
c1.value = datetime.datetime(2020, 4, 30)
c1.number_format = "yyyy/mm/dd"
c2 = ws["B10"]
c2.value = "株式会社 鈴木商店"
c3 = ws["C10"]
c3.value = "商品B"
c4 = ws["D10"]
c4.value = 3800
c4.number_format = "#,##0"
c5 = ws["E10"]
c5.value = 12
c6 = ws["F10"]
c6.value = "=D10*E10"
c6.number_format = "#,##0"

wb.save("売上データ_4月修正書式.xlsx")
実行結果

≫ 2-4　セルを１行づつ読み書きする

◎ Excelファイルを１行づつ読み込む

◯ for row in ws.iter_rows(min_row=4):

for文で１行づつ読み込む。（）内は開始行、開始列

◯ for c in row:

 １行の中のセルの値を１つづつ取り出す

◯ value_list.append(c.value)

セルをリストに格納

◯ if row[0].value is None:

１列目がNone（ナン）の場合 

import openpyxl

wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["4月売上"]

for row in ws.iter_rows(min_row=4):
    if row[0].value is None:
        break
    value_list = []
    for c in row:
        value_list.append(c.value)
    print(value_list)
実行結果 
[datetime.datetime(2020, 4, 1, 0, 0), '株式会社 鈴木商店', '商品C', 1200, 20, '=D4*E4']
[datetime.datetime(2020, 4, 8, 0, 0), 'サン企画 有限会社', '商品A', 7200, 5, '=D5*E5']
[datetime.datetime(2020, 4, 14, 0, 0), '株式会社 鈴木商店', '商品A', 7200, 3, '=D6*E6']
[datetime.datetime(2020, 4, 17, 0, 0), '三和商事 株式会社', '商品B', 3800, 10, '=D7*E7']
[datetime.datetime(2020, 4, 23, 0, 0), '三和商事 株式会社', '商品C', 1200, 50, '=D8*E8']
[datetime.datetime(2020, 4, 27, 0, 0), 'サン企画 有限会社', '商品A', 7200, 8, '=D9*E9']

◎ Excelファイルを１行づつ書き込む

import openpyxl
from datetime import datetime

wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["6月売上"]

# 書き込むデータ
data_list = [
[datetime(2020, 6, 1), "株式会社 鈴木商店", "商品A", 7200, 30],
[datetime(2020, 6, 3), "サン企画 有限会社", "商品A", 7200, 15],
[datetime(2020, 6, 9), "株式会社 鈴木商店", "商品C", 1200, 20]
]

# 書き込み開始番号
row_num = 4

for row in data_list:
    c1 = ws["A" + str(row_num)]
    c1.value = row[0]
    c1.number_format = "yyyy/mm/dd"
    c2 = ws["B" + str(row_num)]
    c2.value = row[1]
    c3 = ws["C" + str(row_num)]
    c3.value = row[2]
    c4 = ws["D" + str(row_num)]
    c4.value = row[3]
    c5 = ws["E" + str(row_num)]
    c5.value = row[4]
    c6 = ws["F" + str(row_num)]
    # 数式の組み立て
    c6.value = "=D" + str(row_num) + "*E" + str(row_num)
    # １行進
    row_num = row_num + 1

wb.save("売上データ_6月入力.xlsx")
実行結果

≫ 2-5　シートの操作方法

◎ シートの挿入

import openpyxl

wb = openpyxl.load_workbook("売上データ.xlsx")

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

# シートの名前を表示
print(ws_new1.title)
print(ws_new2.title)

wb.save("売上データ_シート挿入.xlsx")
実行結果 
Sheet
Sheet1

◎ シートの削除

import openpyxl

wb = openpyxl.load_workbook("売上データ_シート挿入.xlsx")
ws = wb.worksheets[0]

wb.remove(ws)
wb.save("売上データ_シート挿入.xlsx")
実行結果

◎ シートの名前を変更

import openpyxl

wb = openpyxl.load_workbook("売上データ_シート挿入.xlsx")
ws = wb["Sheet1"]
ws.title = "第１四半期"

wb.save("売上データ_第１四半期.xlsx")
実行結果

◎ 同じブック内でシートの移動

◯ ブック間でのシート移動は、

できない

◯ ws = wb.worksheets[-1]

 末尾のシートは番号に「-1」を指定する

◯ wb.move_sheet(ws, offset=-3)

マイナスは左に、プラスは右に移動する

◯ 末尾のシートを先頭に移動する場合のoffset値の算出

to_top = 1 - len(wb.worksheets)

wb.move_sheet(ws, offset=to_top )

  

import openpyxl

wb = openpyxl.load_workbook("売上データ_第１四半期.xlsx")
ws = wb.worksheets[-1]

wb.move_sheet(ws, offset=-3)

wb.save("売上データ_第１四半期.xlsx")
実行結果

◎ 同じブック内でシートのコピー

◯ ws_copy = wb.copy_worksheet(ws)

シートのコピーを作成する（末尾に追加される） 

import openpyxl

wb = openpyxl.load_workbook("売上データ.xlsx")
ws = wb["4月売上"]

ws_copy = wb.copy_worksheet(ws)
ws_copy.title = "4月作業用"

wb.save("売上データ_作業用.xlsx")
実行結果

◎ 新しいブックにシートをコピー

◯ ブック間でのシートのコピーは、

できないので、ブックをコピーして、不要シートを削除して、別名で保存する

 

  

import openpyxl

wb = openpyxl.load_workbook("売上データ.xlsx")
#ws = wb["4月売上"]

for ws in wb.worksheets:
    if ws.title != "4月売上":
        wb.remove(ws)

wb.save("売上データ_4月のみ.xlsx")
実行結果

≫ 2-5　複数のシートをまとめる

◎ 複数のシートを１つにする

◯ row[0].row

row[0]で処理対象の行情報を取得できる

row[0].rowで処理対象の行番号を取得できる

◯ ws_new.append(row)

新しいシートの最終行に行追加される（最初は１行目から）

◯ ws_new.cell(row_num, 1).number_format

セルの位置ををR１C１形式で指定して表示形式を設定

  

import openpyxl

wb = openpyxl.load_workbook("売上データ_6月入力.xlsx")

# 1つ目のシートかどうか
is_first_sheet = True

# このリストに全シートから読み取ったデータをまとめる
row_list = []

# ブックの中のすべてのシートを処理
for ws in wb.worksheets:

    # １つ目と、２つ目以降のシートの読み込み位置を変更
    if is_first_sheet:
        start_row = 1
    else:
        start_row = 4

    # シートを指定開始行から1行づつ読み込む
    for row in ws.iter_rows(min_row=start_row):
        # ヘッダーより下で空行になったら読み込み終了
        if row[0].row > 3 and row[0].value is None:
            break

        # 1行分のデータをリストに格納
        value_list = []
        for c in row:
            value_list.append(c.value)

        # リストに1行分のデータを追加
        row_list.append(value_list)

    # １つ目のシートは読み込んだのでFalseを代入
    is_first_sheet = False

# データを転記する新しいシート
ws_new = wb.create_sheet(title="第1四半期売上")

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

# 新しいシートに１行づつデータを書き込む
for row in row_list:
    # 1行分のデータを書き込む
    ws_new.append(row)

    # データ部分のA列に日付の表示形式を設定
    if row_num > 3:
        ws_new.cell(row_num, 1).number_format = "yyyy/mm/dd"

    row_num = row_num + 1

# 別名でブック保存
wb.save("売上データ_第1四半期売上.xlsx")

◎ 複数のブックをシート名ごとにまとめる

◯ for file in Path("売上全支店").glob("*.xlsx"):

売上全支店フォルダー内の検索パターンに一致するファイルを１つずつループする

◯ wb_list.append(wb)

リストにworkbookを格納する

◯ wb_new = openpyxl.Workbook()

新しいブックを生成。ブック生成時に１シートが自動で生成される

 ◯ wb_list[0].sheetnames

リストに格納された１つ目のworkbookのシート名一覧 

import openpyxl
from pathlib import Path

# 各支店ブックの読み込み
wb_list = []
for file in Path("売上全支店").glob("*.xlsx"):
    wb = openpyxl.load_workbook(file)
    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 = 4

        # シートを指定開始行から1行づつ読み込む
        for row in ws.iter_rows(min_row=start_row):
            # ヘッダーより下で空行になったら読み込み終了
            if row[0].row > 3 and row[0].value is None:
                break

            # 1行分のデータをリストに格納
            value_list = []
            for c in row:
                value_list.append(c.value)

            # リストに1行分のデータを追加
            row_list.append(value_list)

        # １つ目のシートは読み込んだのでFalseを代入
        is_first_book = False

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

    # シートに１行づつデータを書き込む
    for row in row_list:
        # 1行分のデータを書き込む
        ws_new.append(row)

        # データ部分のA列に日付の表示形式を設定
        if row_num > 3:
            ws_new.cell(row_num, 1).number_format = "yyyy/mm/dd"

        row_num = row_num + 1

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

# 保存した新しいブックを保存
wb_new.save("売上データ_全国.xlsx")

≫ 4-3　Excelのマスタからデータを検索

◎ 顧客データをIDで検索

VLOOKUPと同じ作業をPythonでプログラミング

◯ 条件に合う複数の顧客を検査する場合

 if customer[0].startswith("K"):

文字列の先頭文字が「K」であればTrueを返す 

import openpyxl

wb = openpyxl.load_workbook("顧客マスタ.xlsx")
ws = wb["Sheet1"]

# 顧客マスタの全リストデータ
customer_list = []

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

# 検索する顧客ID
customer_id = "K0004"

# 顧客の検索
for customer in customer_list:
    # 検索条件（顧客IDの一致）
    if customer[0] == customer_id:
        target = customer
        # 確認
        print(target)
        print(target[1])
        break

≫ 4-4　表のデータを別のブックに転記

◎ 顧客マスターからデータを転記

売上データ_202007.xlsxのH列に顧客マスタ.xlsxの当社営業担当を転記 
import openpyxl

# 顧客マスタのブック、シート
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]

# 売上データのブック、シート
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx", data_only=True)
ws_data = wb_data["Sheet1"]

# 顧客マスタの全データリスト
customer_list = []

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

# データ行番号
row_num = 3
# 列名追加
ws_data["H" + str(row_num)].value = "当社営業担当"

# 売上データを１行づつ処理
for row in ws_data.iter_rows(min_row=4):
    row_num = row_num + 1

    # 顧客ID：売上データの[B列]
    customer_id = row[1].value

    # 顧客の検索
    for customer in customer_list:
        # 検索条件（顧客IDの一致）
        if customer[0] == customer_id:
            ws_data["H" + str(row_num)].value = customer[5]
            break

# 別名で保存
wb_data.save("売上データ_202007_営業担当あり.xlsx")