【Python】エクセルファイルからある列は入力されているが別の列が空欄な行を抜き出して、テキストファイルに保存する方法

2021年6月12日

プログラミングなんてやったことのない非エンジニアが、Pythonを使ってエクセル作業を自動化してみる記事です。

プログラミングによる自動化に興味を持ってインターネットで「Python エクセル 自動化」などと検索してみても、「セルの値の取得の仕方」「セルへの書き込み方」「シートの作成方法」といった基礎的な内容ばかりで、「それで結局何ができるの？」といまいち興味を持てないものが多いです。

なので「まずとりあえず動くものを作ってみてから、その中身がどうなってるかを調べていく」という方向でやりたい人向けに、コピペで動くコードと動かす環境の作り方を書いた記事になります。

※基礎は大事なので、とりあえず動かしてみたら他のサイトと合わせて中身の仕組みについて学んでいくことをお勧めします。
　気が向いたらそういう解説記事も書いていきたい。

広告

今回は具体例として、エクセルで作った下の画像のような発注管理表ファイルの中から、納品されているけどまだ請求が来ていない案件を調べてテキストファイルに保存するスクリプトを作ります。

※同じ内容で最後の出力方法がメッセージボックスの記事がこちらになります。

もう少し詳しく言うと、G列が記入されているけどH列が空欄の場合に、同じ行のB～E、G列の内容を読み取って同じフォルダにテキストファイルとして保存するスクリプトになります。

アプリケーションファイルをダブルクリックして動かすと、抜き出した案件の一覧がテキストファイルとして保存されます。

上の例だと「商品D」「商品E」「商品G」の3件が抜き出されます。

記事の前半はとりあえず動かしてみる方法の説明。

後半が内容の説明となっています。

コピペで簡単！とりあえず動かしてみる手順

  1. Pythonを動かす環境を構築
  2. 今回のスクリプトに必要なファイルを準備
  3. 今回のスクリプトを動かすのに必要なモジュールをインストール
  4. コピペでスクリプトを作成
  5. 実行ファイルに変換
  6. 実行

※この記事はWindows環境を想定しています。

1.Pythonを動かす環境を構築

Windows環境では、Pythonのスクリプトを作成してもそのままでは動きません。 まずはPythonスクリプトが動く環境を構築する必要があります。（一度構築したら再度構築し直す必要はありません。）

始めにPython.orgに行き、自分の環境にあったPythonをダウンロードしてインストールします。

Downloadsにカーソルを合わせて、Windowsを選択。

複数のバージョンがダウンロードできるようになっていますが、最新版で問題ないので最新版のweb-based installerをダウンロード。
※使っているのが32bitOSの場合はx86、64bitOSの場合はx86-64をダウンロードしてください。

ダウンロードが終わったらインストーラーを起動。

そのままインストールを進めていきますが、途中で下部にAdd Python 3.8 to PATHというチェックボックスが出てきたら、そこにチェックを入れて進めてください。

その後は手順に従って進めていけばインストールが完了します。

2.今回のスクリプトに必要なファイルを準備

デスクトップ上に「Python」というフォルダーを作り、その中にエクセルファイルとスクリプトを入れていきます。

下のリンクからエクセルファイルをダウンロードしてください。

アイコン

orderTable

1 ファイル 10.27 KB
ダウンロード

※ファイル名やシート名、シートの構造は変更しないでください。

3.今回のスクリプトを動かすのに必要なモジュールをインストール

まずはパソコンにエクセルを扱うためのopenpyxlモジュールをインストールします。

コマンドプロンプトかPowerShellを起動し、以下のコードを打ち込んでエンターキーを押してください。

pip install openpyxl

※コマンドプロンプトはWindowsキーを押して「cmd」、PowerShellはWindowsキーを押して「powershell」と入力してエンターキーを押すと起動できます。

続いて実行ファイルの変更に必要なPyinstallerをインストールします。

以下のコマンドを入力します。

pip install pyinstaller

4.コピペでスクリプトを作成

まずはWindowsにデフォルトで入っている「メモ帳」を開きます。

下のコードをコピーしてメモ帳にペースト。

#  エクセルファイルを扱うためのopenpyxlモジュールを読み込み
import openpyxl
from openpyxl import load_workbook, utils

# 発注管理表エクセルファイルへのパスを指定：今回は相対パスで同じフォルダ
excel_path = './発注管理表.xlsx'
# エクセルファイルを読み込み専用で読み込み
wb_order = load_workbook(filename = excel_path, read_only = True)
# エクセルファイルのシートを変数sheet_orderに代入
sheet_order = wb_order['Sheet1']

# 結果を格納する変数を宣言
result = '発注表のチェックを行いました。\n以下の案件の請求がまだ来ていません。\n'

# エクセルファイルを一行ずつ見ていく繰り返し処理（3行目から）
for row in sheet_order.iter_rows(min_row = 3):
    # G列とH列の値を変数に代入
    delivery = row[6].value
    invoice = row[7].value
    if delivery is not None and invoice is None:
        # G列に記入されていて、H列が空欄の場合、以下の処理を実行
        result += '\n\n 案件名：' + str(row[1].value)
        result += '\n 発注先：' + str(row[2].value)
        result += '\n 担当者：' + str(row[3].value)
        orderDate = row[4].value
        if row[4].number_format == 'General':
            orderDate = utils.datetime.from_excel(orderDate)
        orderDate = orderDate.date()
        result += '\n 発注日：' + str(orderDate)
        deliveryDate = row[6].value
        if row[6].number_format == 'General':
            deliveryDate = utils.datetime.from_excel(deliveryDate)
        deliveryDate = deliveryDate.date()
        result += '\n 納品日：' + str(deliveryDate) 

# テキストファイルに結果を書き込み
text_path = './発注管理表チェック結果.txt'
text_file = open(text_path, mode = 'w')
text_file.write(result)
text_file.close

wb_order.close()

名前を付けて保存で「invoiceCheckText.py」というファイル名で、デスクトップに作った「Python」フォルダ内に保存します。

5.実行ファイルに変換

コマンドプロンプトかPowerShellを起動して、以下のコードを順に実行していきます。

cd desktop\Python
pyinstaller invoiceCheckText.py --onefile

※大文字小文字に気を付けてください。

実行ファイルへの変換が成功していれば、Pythonフォルダ内に「__pycache__」「build」「dist」というフォルダと「invoiceCheckText.spec」というファイルができています。

その中で「dist」というフォルダを開くと、その中に「invoiceCheckText.exe」というアプリケーションファイルが入っています。このアプリケーションファイルが目的の実行ファイルになります。

このアプリケーションファイルをPythonフォルダに移動します。

「__pycache__」「build」「dist」フォルダと「invoiceCheckText.spec」ファイルは不要なので、削除しても構いません。

Pythonスクリプトの実行方法について

6.実行

アプリケーションファイル「invoiceCheckText.exe」をダブルクリックするとアプリケーションが実行され、テキストファイルが生成されます。

テキストファイルの中身が以下のようになっていたら成功です。

注意点として、アプリケーションファイルと対象のエクセルファイルは必ず同じフォルダ内に置いてください。

エクセルファイルの中身を変更すると、それに伴って生成されるテキストファイルの中身も変わります。

※納品されているけど未請求の案件の「案件名」「発注先」「担当者」「発注日」セルに抜けがあるとエラーになります。

広告

コード解説

ここからはコードの中身の解説です。

ここから先を理解すると、自分がやりたい目的に合わせてスクリプトの中身を書き換えることができるようになります。

今回の例ではエクセルファイルやコードの中身を書き換えることで、

  • 発注管理表からまだ納品されていない案件を抜き出す
  • 進捗管理表から未完了の項目を抜き出す

といったスクリプトを作ることができます。

コードを書くにはテキストエディタと呼ばれるソフトを使用します。

一番シンプルなのがWindowsにデフォルトで入っている「メモ帳」ですが、「書く」機能しかありません。

「VScode」や「Atom」「TeraPad」「秀丸エディタ」「サクラエディタ」など、プログラミング用テキストエディタを使用すると、言語に合わせてコード中のキーワードを色分けしてくれたり、コードの間違いを指摘してくれたり、入力候補を表示してくれたり、変数を選択すると同じ変数を使っている箇所をハイライトしてくれたりと、便利な機能がたくさんあります。

そのためプログラミング用テキストエディタを使用することを強くお勧めします。

ちなみに私は「VScode」を使用しています。

#  エクセルファイルを扱うためのopenpyxlモジュールを読み込み
import openpyxl
from openpyxl import load_workbook, utils

# 発注管理表エクセルファイルへのパスを指定：今回は相対パスで同じフォルダ
excel_path = './発注管理表.xlsx'
# エクセルファイルを読み込み専用で読み込み
wb_order = load_workbook(filename = excel_path, read_only = True)
# エクセルファイルのシートを変数sheet_orderに代入
sheet_order = wb_order['Sheet1']

# 結果を格納する変数を宣言
result = '発注表のチェックを行いました。\n以下の案件の請求がまだ来ていません。\n'

# エクセルファイルを一行ずつ見ていく繰り返し処理（3行目から）
for row in sheet_order.iter_rows(min_row = 3):
    # G列とH列の値を変数に代入
    delivery = row[6].value
    invoice = row[7].value
    if delivery is not None and invoice is None:
        # G列に記入されていて、H列が空欄の場合、以下の処理を実行
        result += '\n\n 案件名：' + str(row[1].value)
        result += '\n 発注先：' + str(row[2].value)
        result += '\n 担当者：' + str(row[3].value)
        orderDate = row[4].value
        if row[4].number_format == 'General':
            orderDate = utils.datetime.from_excel(orderDate)
        orderDate = orderDate.date()
        result += '\n 発注日：' + str(orderDate)
        deliveryDate = row[6].value
        if row[6].number_format == 'General':
            deliveryDate = utils.datetime.from_excel(deliveryDate)
        deliveryDate = deliveryDate.date()
        result += '\n 納品日：' + str(deliveryDate) 

# テキストファイルに結果を書き込み
text_path = './発注管理表チェック結果.txt'
text_file = open(text_path, mode = 'w')
text_file.write(result)
text_file.close

wb_order.close()

※上記のスクリプト中の#から始まる行はコメント行です。

ではコードの中身を見ていきます。

import openpyxl
from openpyxl import load_workbook, utils

まずエクセルファイルを扱うためのopenpyxlライブラリのload_workbookモジュール、utilsモジュールをimportしています。

excel_path = './発注管理表.xlsx'
wb_order = load_workbook(filename = excel_path, read_only = True)
sheet_order = wb_order['Sheet1']

エクセルのファイルを指定する部分になります。

まず1行目でエクセルファイルへのパスを指定。今回は./で相対パスで同じフォルダを指定し、次にエクセルファイルの名前を指定しています。

違う名前のエクセルファイルを読み込みたい時や、違うフォルダに置いてあるエクセルファイルを読み込みたい時は、ここを変更します。

load_workbook()でエクセルファイルをwb_orderという変数に代入。

※第2引数をread_only = Trueとすることで読み込み専用で読み込んでいるので、エクセルファイルを壊す心配がありません。

エクセルファイル中のSheet1というシートをsheet_orderという変数に代入。これでここ以降sheet_ordeerを呼び出すことでこのエクセルのシートを呼び出せます。

result = '発注表のチェックを行いました。\n以下の案件の請求がまだ来ていません。\n'

結果を格納するresultという変数を宣言。

このresultという変数に代入していった内容が最終的にテキストファイルに書き込まれます。

Pythonは空の変数を宣言することができないので、宣言と同時に一行目の内容を入れておきます。

※文字列の時に度々出てくる「\n」は改行です。

for row in sheet_order.iter_rows(min_row = 3):
    delivery = row[6].value
    invoice = row[7].value
    if delivery is not None and invoice is None:
        result += '\n\n 案件名：' + str(row[1].value)
        result += '\n 発注先：' + str(row[2].value)
        result += '\n 担当者：' + str(row[3].value)
        orderDate = row[4].value
        if row[4].number_format == 'General':
            orderDate = utils.datetime.from_excel(orderDate)
        orderDate = orderDate.date()
        result += '\n 発注日：' + str(orderDate)
        deliveryDate = row[6].value
        if row[6].number_format == 'General':
            deliveryDate = utils.datetime.from_excel(deliveryDate)
        deliveryDate = deliveryDate.date()
        result += '\n 納品日：' + str(deliveryDate)

このfor文が中身をチェックして結果を収集する部分になります。

openpyxlライブラリのiter_rowsを使用すると、エクセルファイルを1行ずつ見ていくことができます。

引数にmin_row = 〇〇と書くことで何行目から開始するかを指定することができます。続いてmax_row = 〇〇でどこで終わるかを指定することもできます。今回は最大行を指定していませんが、その場合はそのシートの中でデータの入っている一番最後の行までで終わってくれます。

このfor文では、変数rowが3からデータが入っている最後の行数まで1ずつ増えていく、という内容になります。

delivery = row[6].value
invoice = row[7].value

変数deliveryにセル番地row[6]のセルの中身の値、invoiceにrow[7]セルの中身の値を代入しています。

row[6]はセル番地を表します。rowが行数（最初が3で1ずつ増えていく）で、[6]が列数（エクセルのA列が0、B列が1、C列が2…で6はG列）。

セル番地に.valueをつけることでセルの中身を意味します。

[]の中の数字を変えることで、何列目を対象にするかを変えることができます。

if delivery is not None and invoice is None:

if文は見たら分かると思いますが、deliveryの中身がnot Noneつまり何かが記入されていて、invoiceの中身がNoneつまり空の場合に、この後の処理を実行します。

result += '\n\n 案件名：' + str(row[1].value)
result += '\n 発注先：' + str(row[2].value)
result += '\n 担当者：' + str(row[3].value)
orderDate = row[4].value
if row[4].number_format == 'General':
    orderDate = utils.datetime.from_excel(orderDate)
orderDate = orderDate.date()
result += '\n 発注日：' + str(orderDate)
deliveryDate = row[6].value
if row[6].number_format == 'General':
    deliveryDate = utils.datetime.from_excel(deliveryDate)
deliveryDate = deliveryDate.date()
result += '\n 納品日：' + str(deliveryDate)

resultという変数に「案件名：」という文字列とセルrow[1]の中身をくっ付けて追加しています。

※「=」で代入すると、それまで入っていた値に上書きされます。
　「+=」で追記すると、それまで入っていた値はそのままでその後ろに新しい値が追加されます。

resultという変数は文字列が入っているので、整数などを追加しようとすると型（セルの書式みたいなものだと思ってください）のエラーになります。

なのでstr()を使ってrow[1].valueを文字列型に変換しています。

発注日と納品日はちょっと特殊で、Pythonにもdateという日付を扱う型がありエクセルファイルのセルの書式設定が「日付」になっているとそのまま読み込めるのですが、書式設定が「標準」になっているとシリアル値で読み込んでしまいます。（エクセルで日付を扱っていると、たまによくわからない数字に変換されてしまったという経験はあるかと思います）

そこで書式設定が「標準（General）」の時はutilsモジュールを使って日付型に変換しています。

ここまでの処理を変数rowがデータの入っている最終行の数字になるまで繰り返したら、forループが終わります。

text_path = './発注管理表チェック結果.txt'

エクセルファイルと同様にテキストファイルのパスを指定します。

text_file = open(text_path, mode = 'w')

テキストファイルを開きます。

該当する名前のファイルがない場合は新規作成されます。

第2引数をmode = 'w’とすることで書き込みモードで開くので、もし前回の結果が置きっぱなしになっていた場合は結果が上書きされます。

mode = 'a’とすると追記モードで開き、前回の結果の下に今回の結果が追記されるようになります。

text_file.write(result)
text_file.close

テキストファイルに変数resultを書き込み、file.closeでファイルを閉じます。

wb_order.close()

最後にwb_order.close()で開いたエクセルファイルを閉じます。

PythonPython,Excel

Posted by texa