メールサーバーとの通信方法
メールサーバーに接続する
SMTPサーバーからメールを送信するには、通常、暗号化通信を開始した状態で認証を行う必要がある。
具体的には、メール送信するまでに次の5つのタスクを実行。
① 「SMTPサーバーのアドレス」と「ポート番号」を指定する
② 暗号化通信（STARTTLS）を開始する
③ SMTPサーバーにログインする
④ メッセージを送信する
⑤ SMTPサーバーとの接続を閉じる
import smtplib # ①SMTPサーバーの指定 server = smtplib.SMTP(SMTPサーバーのアドレス、ポート番号) # ②暗号化通信の開始 server.starttls() # ③SMTPサーバーにログイン server.login(アカウント、パスワード) # ④メッセージ送信（メッセージmsgの作成方法は、次節で説明） server.send_message(msg) # ⑤SMTPサーバーとの接続を閉じる server.quit()
・メッセージを送信するには、ログインまでが無事に完了している必要がある
SMTPサーバーにPythonでログインできるかを次のコードでテスト
・利用者の多いGmailでSMTPサーバーにログインする
・SMTPサーバーのアドレスには「smtp.gmail.com」、ポート番頭には「587」を指定
・ログインのアカウントには「自分のGmailアドレス（XXXX@mail.com）」、パスワードには「自分のGmailパスワード」を使用
・15行目のnoop()はSMTPサーバーが応答しているか確認するための命令
import smtplib # SMTPサーバー(今回はGmailで送信) smtp_server = "smtp.gmail.com" port_number = 587 # ログイン情報（今回はGmailのアカウントを入力する） account = "自分のメールのアカウント" password = "自分のメールのパスワード" # ①SMTPサーバーの指定 server = smtplib.SMTP(smtp_server, port_number) # SMTPサーバーの応答確認 res_server = server.noop() print(res_server) # ②暗号化通信の開始 res_starttls = server.starttls() print(res_starttls) # ③SMTPサーバーにログイン res_login = server.login(account, password) print(res_login) # ⑤SMTPサーバーとの接続を閉じる server.quit()
結果
エラー・・・Why!?😱
でも、Gmailのアドレスに「不審なアクセスありました」という通知が来ていたから、プロセスは間違ってなかったっぽい！
設定上の問題だろう・・・
⇨エラーメッセージに書いてあるリンク先に飛んで、Gmailヘルプの「安全性の低いアプリがアカウントにアクセスすることを許可（怖いけど）」をしたところ、ちゃんと受け入れが完了しました🎶
ちなみに、２段階認証設定にして、アプリ パスワードを使ってログインをする方法もあるらしい。こっちの方が安全っぽいっすね
次はメールで送るメッセージを作成してみます！
参考文献
【Mac】VSCodeでVBAを扱いたい※失敗しました
Try１ 拡張機能を使う
こちらの記事を参照し、トライしてみました。
ボタンは表示されたんだけど、ボタン押しても、何も起こらず・・・
結局断念・・・
Try２ ファイルをインポートする
次にこちらも挑戦しました
macdemacro.com
こちらはアクセス権を付与しようとしても、読み込みがされず、エラー
エラーメッセージ「libdef.txtが存在しません。」
結局、実現できず、3時間近く粘ったのに！
今回は結局できなかったけど、何かいい方法を見つけたい😭
顧客ごとの売上データの絞り込み
3つのExcelブックを読み込んで絞り込む
（ポイント）
・最初に顧客マスタの全データを空のリストのcustomer_listに読み込んでおく
・for文でcustomer_listから顧客データを1件ずつ取り出して、繰り返し処理を行う
・繰り返し処理（for文の中）では、取り出した顧客データと売上データの顧客IDを比較し、一致するデータだけをdata_listに追加する
⇨格納したデータを請求書に書き込む
・プログラムの最後では、data_listの先頭[0]と末尾[-1]をprint()で表示して、期待通り売上データが顧客ごとに絞り込まれているかを確認する
import openpyxl
import datetime
#顧客マスタのブック
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"]
#請求書雛形のブック、シート
wb_inv = openpyxl.load_workbook("請求書ひな形.xlsx")
ws_inv = wb_inv["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)
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売上データの検索
data_list = []
for row in ws_data.iter_rows(min_row=4):
value_list = []
for c in row:
value_list.append(c.value)
#検索条件（売上データの顧客IDが一致したデータだけ追加）
if value_list[1] == customer_id:
data_list.append(value_list)
if len(data_list) > 0:
#確認（売上データの最初と最後を表示）
print(data_list[0])
print(data_list[-1])
#ここから請求書作成の処理を行う（後半へ続く）
実行結果
顧客ごとの請求書を作成する
（ポイント）
・ひな型を準備しておく（体裁を整えておく）
・日付や金額のセルには適切な書式を設定しておく
・オレンジの枠の中にアテナや明細を入力する
・数式は指定しておく
※E列の「区分」の欄に「※」が入力されている場合は税率8％対象、空欄の場合は税率10％対象になるようにSUMIFSで設定 ★H29セルの数式（税率8％対象の小計を求める） ⇨=SUMIFS(H12:H28, E12:E28, "※") ★H30セルの数式（税率10％対象の小計を求める） ⇨=SUMIFS(H12:H28, E12:E28, ””) ★H31セルの数式（税率8％対象の小計を求める） ⇨=ROUNDDOWN(H29*8%, 0) ★H23セルの数式（税率10％対象の小計を求める） ⇨=ROUNDDOWN(H30*10%, 0) ★H33セルの数式（合計セル） ⇨=SUM(H29:H32) ★C9セルの数式（合計セル） ⇨=H33
請求書のシートを作成する
（ポイント）
・シートのtitleに、顧客IDを収めたcustomer_idを代入して、シート名を「顧客ID」に変更する
・各セルへは、ws_new.cell(行番号、列番号).valueに値を代入して入力
※ここで、「請求日」のセルには、datetime型で日付を入力する
・税率区分は、8％ハウりい上げデータのシートでは「*」でマークされているので、id not Noneで空欄でない場合に「※」を請求書に入力する
・最後に、コピーに使用したひな形のシートを削除して、「請求書_202007.xlsx」という名前でブックを別名保存
import openpyxl
import datetime
#顧客マスタのブック
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"]
#請求書雛形のブック、シート
wb_inv = openpyxl.load_workbook("請求書ひな形.xlsx")
ws_inv = wb_inv["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)
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売上データの検索
data_list = []
for row in ws_data.iter_rows(min_row=4):
value_list = []
for c in row:
value_list.append(c.value)
#検索条件（売上データの顧客IDが一致したデータだけ追加）
if value_list[1] == customer_id:
data_list.append(value_list)
if len(data_list) > 0:
#確認（売上データの最初と最後を表示）
# print(data_list[0])
# print(data_list[-1])
# 請求書のひな型のシートをコピー
ws_new = wb_inv.copy_worksheet(ws_inv)
#顧客IDをシート名にする
ws_new.title = customer_id
#宛名書込み
ws_new.cell(3,1).value = customer_name
#請求日書込み
ws_new.cell(2,8).value = datetime.datetime(2020,8,10)
#表部分へのデータ書込み（A,B ,E ,F ,G ,H列に書き込む）
row_num = 12
for data in data_list:
ws_new.cell(row_num,1).value = data[0]
ws_new.cell(row_num,2).value = data[3]
#税率区分
if data[7] is not None:
ws_new.cell(row_num,5).value = "※"
ws_new.cell(row_num,6).value = data[4]
ws_new.cell(row_num,7).value = data[5]
ws_new.cell(row_num,8).value = data[6]
row_num = row_num + 1
#請求書ひな型のシートは削除
wb_inv.remove(ws_inv)
#別ブックで保存
wb_inv.save("請求書_202007.xlsx")
毎月の処理をもっと簡単にする
今回のプログラムを毎月使用する場合は、売上データのブック名や請求日を画面から入力できるようにすると便利になる。
画面で入力した文字をプログラムで受け取るには、input()を利用する。カッコの中に文字列を指定すると、ユーザーに何を入力してもらいたいかメッセージを表示することができる。
表示されたメッセージに何らかのテキストを入力してEnterキーを押すと、input()は入力されたテキストを文字列として返す。
参考図書
VSCodeでjupyterが使えなくなった！！
VSCodeのアップグレードをしたら、使えなくなりました。
トラブルシューティングで1時間半を溶かしてしまった😭😭
やった事
・新しいファイルを作る⇨❌
・VS Codeを立ち上げ直す⇨❌
・jupyterの拡張機能をアンインストールしてインストールし直す⇨❌
・仮想環境に入り直して、仮想環境にjupyterパッケージをインストールし直す⇨⭕️
なんとか復活。アップグレード怖い！！
関連記事
jupyterの導入についてはこちらに yoji.hateblo.jp
条件を満たすデータや個数や合計を求める
顧客ごとの売上件数をカウントする
（ポイント）
・まず今までと同様に、最初に顧客マスタのデータを全てからのリストのcustomer_listに取り込んでおく
・customer_listの顧客を1件ずつループさせて、その顧客IDと一致したら、売上データが計上された場合だけ「顧客ID、顧客名称、売上件数（count_sales）」をリストにしてresult_listに追加する
・全ての顧客について集計したら、売上データのブックに集計結果用のシートを追加して、そこにresult_listの中身を書き込む
・最後にそのブックを別名保存
import openpyxl
#顧客マスタのブック、シート
import openpyxl
#顧客マスタのブック、シート
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]
#売上データのブック、シート
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx")
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)
#集計結果を入れるリスト
result_list = []
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売上件数のアカウント
count_sales = 0
#売上データの検索
for row in ws_data.iter_rows(min_row=4):
#検索条件（売上データの顧客IDが一致）_１を加えてカウントする
if row[1].value == customer_id:
count_sales = count_sales + 1
if count_sales > 0:
#顧客ID、顧客名称、売上件数の3項目を追加
result_list.append([customer_id, customer_name, count_sales])
#集計結果用シートを追加
ws_new = wb_data.create_sheet(title="顧客別売上件数")
#ヘッダー書き込み
ws_new.append(["顧客ID", "顧客名称", "売上件数"])
#集計結果書き込み
for result in result_list:
ws_new.append(result)
#別ブックで保存
wb_data.save("売上データ_202007_顧客別売上件数.xlsx")
実行結果
顧客マスタにない顧客について集計する
・顧客マスタにない顧客については集計されない。そこで次のコードを追加しておけば、顧客マスタにない顧客の見落としを防げる
・最終行のwb_data.save("売上データ202007顧客別売上件数.xlsx")の前に追記する
・・・
id_list = []
#集計結果書き込み
for result in result_list:
ws_new.append(result)
#集計した顧客IDのリストを作成
id_list.append(result[0])
#集計されていない顧客を検索
for row in ws_data.iter_rows(min_row=4):
#検索条件（集計した顧客IDのリストに含まれない）
if row[1].value not in id_list:
#集計されていない顧客IDと顧客名称を表示
print(row[1].value, row[2].value)
#別ブックで保存
wb_data.save("売上データ_202007_顧客別売上件数.xlsx")
顧客ごとの売上額を集計する
（ポイント）
・このプログラムは、上記の「売上件数をカウントする場合」とほとんど同じ
・売上件数のカウントでは、顧客IDが一致するデータがあるたびに、count_salesに「１」を加えた。売り上げの合計を集計するためには、sum_salesに「売上額」を足していく
・売り上げデータの「計」のセルには数式が入力されているので、値を取得できるように、ブックの読み込み時に「data_only=True」を指定するのを忘れないようにすること！
import openpyxl
#顧客マスタのブック、シート
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]
#売上データのブック、シート。data_only=Trueで数式の「計」セルから値を取得できるようにする
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)
#集計結果を入れるリスト
result_list = []
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売り上げの集計
sum_sales = 0
#売上データの検索
for row in ws_data.iter_rows(min_row=4):
#検索条件（売上データの顧客IDが一致）
if row[1].value == customer_id:
#売上の額は「G列（インデックス＝６）」
sum_sales = sum_sales + row[6].value
if sum_sales > 0:
#顧客ID、顧客名称、売上合計の3項目を追加
result_list.append([customer_id, customer_name, sum_sales])
#集計結果用シートを追加
ws_new = wb_data.create_sheet(title="顧客別売上")
#ヘッダー書き込み
ws_new.append(["顧客ID","顧客名称","売上計"])
#集計結果書き込み
for result in result_list:
ws_new.append(result)
#別ブックで保存
wb_data.save("売上データ_202007_顧客別売上合計.xlsx")
結果
※ここでも顧客マスタから漏れてしまっている顧客は集計されない。善行と同じコードを追加することで、顧客マスタにない顧客の見落としを防止できる
表のデータを別のブックに転記する
やること
・既存のExcel表に、顧客マスタから検索したデータを転記
⇨今回は「売上データ202007.xlsx」の右端の「H列」に、顧客の「当該営業担当」を顧客マスタから転記
（ポイント）
・「顧客マスタ.xlsx」と「売上データ202007.xlsx」の2つのブックをload_workbook()で読み込んでおく
・「顧客マスタ.xlsx」は最初に全データを空のリストのcustomer_listに取り込んでおく。売上データは1行ずつ読み込みながら「B列」の顧客IDと一致する顧客データをcustomer_listから検索する
⇨該当する顧客データが見つかったら、顧客マスタの６列目にある「当社営業担当」（customer[5]）を「H列」のセルにかきこむ
・売上データの全行について処理を完了したら、最後に別名でブックを保存
import openpyxl
#顧客マスタのブック、シート
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]
#売上データのブック、シート
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx")
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 = "当社営業担当"
#売上データを1行ずつ処理
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:
#当社営業担当を「H列」に追加
ws_data["H" + str(row_num)].value = customer[5]
break
#別名で保存
wb_data.save("売上データ_202007_営業担当あり.xlsx")
売上データを顧客ごとのシートに転記する
・今回は顧客ごとのシートに振り分けて転記する
・シートは、売上データのブックにプログラムで追加し、サイトにそのブックを別名で保存
（ポイント）
・顧客マスタの全データをからリストのcustomer_listに読み込んでおく
・customer_listの顧客を1件ずつループし、その顧客IDと一致するデータを売上データから検索し、data_listに追加していく
⇨「同じ子役の売上データ」がdata_listに格納される
・該当する顧客の売り上げデータが1件でも存在したら、その顧客名称でシートを新規に追加して、data_listの中身をそのシートに書き込む。この処理を全ての顧客分繰り返すと完了
・「売上日」のセルは、日付で表示するために、number_formatに年月日の書式を代入しておく。また、「計」のセルには数式が入力されているため、そのまま書き写すと元のセルの位置を参照して、誤った計算をしてしまうため、売上データのブックを読み込むときに「data_ouly=True」を指定し、値だけを書き写すようにする
import openpyxl
#顧客マスタのブック、シート
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)
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売上データの検索
data_list = []
for row in ws_data.iter_rows(min_row=4):
value_list = []
for c in row:
value_list.append(c.value)
#検索条件（売上データの顧客IDが一致）
if value_list[1] == customer_id:
data_list.append(value_list)
if len(data_list) > 0:
#顧客名称をシート名にしてシート追加
ws_new = wb_data.create_sheet(title=customer_name)
#ヘッダー書き込み
ws_new.append(["売上日","顧客ID","顧客名称","商品名","単価","数量","計"])
#データ書き込み
row_num = 2
for data in data_list:
ws_new.append(data)
ws_new.cell(row_num,1).number_format = "yyyy/m/d"
row_num = row_num + 1
#別ブックで保存
wb_data.save("売上データ_202007_顧客別シート.xlsx")
