2020年6月 :: ひまつぶし雑記帖

perlでエクセルを読む

2020/6/23 [09:00:24] (火)

WINDOWSのActive perlでSpreadsheet、ParseExcel、ParseXLSXを使ってエクセルを読んで作業したのでメモ。

複数のフォルダに入っている、複数のエクセルファイル。
フォルダ名は五十音のひらがな。あかさたな～ってやつ。
ファイル名は日本語だったり乱数だったり、中身とはほぼ関係もなく意味不明なファイル名となっている。

エクセルファイルの特定箇所を確認して、別途登録されているものと違っていたら修正。
エクセルのファイル名を登録されているもの（日本語）にリネーム。

ざっくり、てな作業。
ひとつずつエクセルを開いて、ひとつずつ確認する、なんて手作業はやってられない。作業前から、見逃す・リネームを間違えるのが目に浮かぶようだ。

一覧表を作って確認して、そこから修正すべきものだけコピペする方が早くて確実。

エクセルファイルは「.xls」なので、使うのはSpreadsheet::ParseExcel
エクセルファイルを読み込んで、エクセルのファイル名と該当セルに記載されている名前をタブ区切りで出力するだけのスクリプトになる。

use strict; use utf8; use Encode; use Spreadsheet::ParseExcel; my $f = shift(@ARGV); my $fname = Encode::decode('cp932', $f); $fname =~ s!\\!\t!g; $fname =~ s!^.+\t([^\t])\t([^\t]+)$!$1\t$2!; print Encode::encode('cp932',$fname) . "\t"; my $p = Spreadsheet::ParseExcel->new(); my $e = $p->parse($f); if (! defined $e ){ print "!!!ERROR!!!\n"; exit;} my $cnt; for my $sheet($e->worksheets()){ last if ++$cnt > 1; my $cell = $sheet->get_cell(2,2); my $c = $cell->value(); $c =~ s!^[\t 　]+!!; print Encode::encode('cp932',$c); print "\n" }

確認が必要なのは1枚目のシートにある3行目C列のセル内容
get_cell(2,2)で該当セル情報を取得（perlは0からなので3番目は2）
セル情報がHASHで入っている。必要なのは値なのでvalueで取得する。

取得する値はutfフラグ付きの日本語になるのでcp932(WINDOWSの文字コード)にして出力。

ここで気づかれると思うけど、このスクリプトは、複数のファイルを一度に処理するようにはできてない。引数に渡されたひとつのエクセルファイルを処理するだけ。

perlにはopendirがあるし、ファイル一覧を取得してループでSpreadsheet::ParseExcelに読み込ませるのが綺麗で正しいやり方。だけど、日本語フォルダ名、日本語ファイル名がうまく渡せない、読み込めなくてハマった。
たぶん文字コードの問題。utfフラグをつけてみたり何もしないまま渡してみたりしたんだけど、どうもうまくいかない。時間もあまりない。

そこでわたしの得意な現物合わせのやっつけ仕事の出番。
よくわかってない人間が間に入るからダメなわけで、だったら機械同士、ソフトウエア同士で直接やりとりしてもらおうとバッチファイルにした。

@echo off for /r %%f in (*.xls) do ( echo %%f perl parse-excel.pl "%%f" >> _list.tsv ) pause:

バッチファイルの for 文で /r をつけると再帰でサブディレクトリも拾ってくれるなんて、今回初めて知った。

このバッチファイルでperlにファイル名を渡すとエラーもなく意図通り読み込んでくれるようになった。いや、上記したように、本当だったらperlだけで済むはずなので、綺麗な解決方法じゃないけど、結果オーライ、だ。

出力されたtsv(タブ区切りファイル)を新しいエクセルに貼りつけ、別途正しい登録名を貼りつけ一覧表を作成する。
そうしたら、エクセルお得意のvlookupでエクセルの該当箇所と正しい登録名の相違を確認。登録名と違っているファイルだけ開いて該当セルを修正する。

また、修正作業には登録名をコピるのでついでに一覧表も修正。
エクセルのファイル名と登録名がこの一覧表の「.xlsx」ファイルに記載されることになるので、Spreadseet::ParseXLSXを使って読み込み、一括でリネームするようにした。

use strict; use utf8; use Encode; use Spreadsheet::ParseXLSX; my $f = '_work2.xlsx'; my $fixed = '_fixed'; opendir(DIR,'.') || die; my @dirs = grep(!/^\./ && -d $_, readdir(DIR)); closedir(DIR); if(! -d $fixed ){ mkdir $fixed; foreach my $d (@dirs){ mkdir $fixed .'/'. $d; } } my $p = Spreadsheet::ParseXLSX->new(); my $e = $p->parse($f); if (! defined $e ){ print "!!!ERROR!!!\n"; exit;} my $cnt; for my $sheet($e->worksheets()){ last if ++$cnt > 1; my $row; my $col; ($row->{min}, $row->{max}) = $sheet->row_range(); ($col->{min}, $col->{max}) = $sheet->col_range(); for my $i (1..$row->{max}){ my $fname0 = $sheet->get_cell($i,4); my $fname1 = $sheet->get_cell($i,1); my $dname = $sheet->get_cell($i,3); last if (! defined($fname0)); if($fname1->value() =~ m!N/A!){ $fname1 = $sheet->get_cell($i,2); } if(! defined($fname1)){ print $fname0->value() . "\n"; } else{ my $cmd = sprintf qq{copy %s\\"%s" %s\\%s\\%s.xls\n}, $dname->value(), $fname0->value(), $fixed, $dname->value(), $fname1->value(); print Encode::encode('cp932', $cmd); system(Encode::encode('cp932', $cmd)); } } }

row_range()とcol_range()でシートに記載されている行数とカラム数を取得してループさせて全部読み込ませる。また、vlookupで N/A になっているところは正しい登録名を別セルに記載したので、そちらを読むようにさせた。

ここでもperlだけでリネームはできるんだけど、なんせ日本語のフォルダ名とファイル名。またうまくいかなかったら面倒くさいんで、perlでリネームするのではなく、system関数を使ってWINDOWSに仕事をさせることにした。

といいつつ、リネームは怖いので、別フォルダに同じディレクトリ構成で登録名ファイルをコピーすることにした小心者だ。

日本語のファイル名は見た目分かりやすいけど、スクリプトで扱うのはただただ面倒くさい。
それに、そもそもなことを言っちゃうと。
ファイル名や入力項目などなど、表記の揺れレベルじゃない間違いは、依頼する時点で入力するひとのことを考えて何か仕組みを作らないといかんよなあ。

あ。もうひとつ。
目grep手merge撲滅！ひとのやる手作業を信用しちゃいけない。