データエンジニアのものこと

データエンジニアです。技術系のことや買ったものの感想などをネタにします。

Powershell で Excel を編集するときにえって思ったこと～ Python の記述もあわせて～

はじめに

皆さんは大量のデータの整形をおこなうとき、どのようなツールを利用しているでしょうか？今の時代は生成 AI？データが社外秘の情報を含んているため手作業でやることがあるかもしれません。しかし、整形の作業に 2，3 時間かかるうえにその作業が週次であるなんてことないでしょうか。それはもはや Excel 奴隷といっても過言ではありません。古代エジプトにてピラミッドを建てるために石を運ぶ奴隷と何ら変わらないのではないかと私は考えてます（もちろん重要な任務であり大切なことであることもあります）。そんな現実に直面したときに私は Python または Powershell を使ってデータの整形を済ますことがあります。今回はそのツールによって動きについてちょっと困ったことが起きた時のお話です。

背景

Powershell でデータの整形をしていて「えっ」て思ったこと

Powershell で整形したデータをデータベースに放り込もうとしたときに必須の項目が NULL になっていますというエラーで怒られました。どうやら元ファイルの Excel にフィルターがかかっており、隠れているセルには必要な項目が入っていなかった模様です。

実験

実行環境・バージョン

実行内容

以下、実行コードです。

import pandas as pd
import openpyxl
from datetime import datetime

# Excelファイルの読み込み
file_path = 'Item.xlsx'  # 入力ファイルのパスを指定
df = openpyxl.load_workbook(file_path)

data = pd.read_excel(file_path, engine='openpyxl')

# 日付の列を追加（例: 現在の日付を追加）
data['日付'] = datetime.now().strftime('%Y-%m-%d')

# 更新したデータフレームを新しいExcelファイルに保存
data.to_excel(file_path, index=False)

pandas を使ったよくあるデータ整形だと思います。データフレームを利用したデータ整形はよくやるのですが、できることが多いし小回りも聞くので個人的には好きです。次は 今回の本題である Powershell です。

# Excelを操作する為の宣言
$excel = New-Object -ComObject Excel.Application

# 既存のExcelファイルを開く
$inputFilePath = "\Item.xlsx"
$book = $excel.Workbooks.Open($inputFilePath)

# ワークシートを番号で指定し、接続する
$sheet = $excel.Worksheets.Item(1)

# 使用している行数を取得する
$lastrow = $sheet.UsedRange.Rows.Count
# 使用している列数を取得する
$lastcol = $sheet.UsedRange.Columns.Count
# 日付を取得する
$date = (Get-Date).ToShortDateString()

# 新しい列にデータを追加
$sheet.Cells.Item(1, $lastcol + 1) = '日付'
$sheet.Range($sheet.Cells(2, $lastcol + 1), $sheet.Cells($lastrow, $lastcol + 1)) = $date

# 上書き保存
$book.Save()

# Excelを閉じる
$excel.Quit()

# プロセスを解放する
$excel = $null
[GC]::Collect()

いや、記述量の差よ、、、。 Excel を開いて行数、列数と現在の日付を取得、その後新しい日付という列を作成して範囲指定で値を貼り付けるといったところでしょうか。編集した Excel を保存してプロセルを開放して終了です。私の記述が未熟であるということもありますが、それでも Python と比べると記述量が多いという印象です。

なにが悪いのか

結論から言うと以下の 2 点が悪いところです。

  • Powershell で日付を入力するときに Range()で指定してしまっていること
  • データソース的な観点からデータソース（今回でいうと Excel）がフィルターをかけるなど編集できる状態にあること

Powershell は Excel を直接開いて編集するのでその Excel 自体にフィルターや編集中など誰かがいじっているをその影響を受けてしまいます。その結果、出力する結果が想定外にものになってしまったということです。それに付随してデータソースが誰でもいじれてしまう環境にあるというのもよくない点です。データソースは何も手が入っていない状態を維持しているのが理想的だと私は考えます。

どうするのか

Powershell を Python に変更しよう。それができれば楽ですが、そうはできない状況もあるかと思います。日付を入力する処理部を以下のように変更すれば少なくともフィルターの影響は受けないかと思います。

# 新しい列にデータを追加
for ($row = 2; $row -le $lastrow; $row++) {
    $sheet.Cells.Item($row, $lastcol + 1) = $date
}

しかし、for 文を使っているのでデータ量が多くなれば多いほど処理が重くなります。一長一短で難しいところです。処理が重くなるのが嫌な場合はデータソースをいじれないような環境を作りましょう。

まとめ

Python を利用することも、Powershell を利用することもどちらも得意不得意があります。どちらも使うことがありますが、Powershell は Excel を直接いじっているという点から元ファイルの状態の影響をもろに受けるのがたまにキズといったところでしょうか。それぞれ制約はありますが、環境にあったツールを利用していきたいと思います。個人的には学習リソースが多い Python を使ったデータ整形を学んでいきたいところです。

参考資料