Edited at

PerlでExcelファイルを読み込む

どうも みなさん

Perl書いてますか?

今回は「ExcelファイルをPerlで読み込む方法」をご紹介します。


Spreadsheet::ParseExcel

今回Excelファイルの読み込みで使うモジュールは、

Spreadsheet::ParseExcel

https://metacpan.org/pod/Spreadsheet::ParseExcel

です。

モジュールが入っていない場合は、

cpanm -v Spreadsheet::ParseExcelでインスコしましょう。

それではまず上記ページに書かれているSYNOPSISのコードを実行してみましょう。

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Book1.xls');

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;

print "Row, Col = ($row, $col)\n";
print "Value = ", $cell->value(), "\n";
print "Unformatted = ", $cell->unformatted(), "\n";
print "\n";
}
}
}

上記のコードは、「Book1.xls」というExcelファイルを読み込んで

ファイル内にある値の「座標」と「値」を表示してくれます。

ということでまずは、「Book1.xls」を用意します


  1. Excelファイルを新規作成して、適当なシートのセルに文字を入れてファイルを保存します


    • Book1.xlsで保存します



  2. コードを実行します


    • 値が表示されます



ん?拡張子が「xls」?と思われた方がいらっしゃるかと思います・・・。

そうなんです、このモジュールExcelファイルの2003までの形式しか対応していません。。。

ページにもそう書かれている。

https://metacpan.org/pod/Spreadsheet::ParseExcel#DESCRIPTION


DESCRIPTION

The Spreadsheet::ParseExcel module can be used to read information from Excel 95-2003 binary files.

The module cannot read files in the Excel 2007 Open XML XLSX format. See the Spreadsheet::XLSX module instead.


Excelファイルの2007形式使うなら

Spreadsheet::XLSXを入れろって書いてあるのですが

今回は少し違った方法でご紹介します。


SYNOPSISのコードを解説

先ほど実行したコードを解説していきます。


parse部

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Book1.xls');

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}



  1. Speadsheet::ParseExcel->newでオブジェクトを作成

  2. parse関数にファイルパスを与える

parseに成功すると$workbookになにかしら入ってきます。

失敗すると$workbookにはundefが入っており、

if ( !defined $workbook )内でエラーを出力しつつdieします。


read部

parseが成功したら、早速解析した結果を読み込んでいきます。

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;

print "Row, Col = ($row, $col)\n";
print "Value = ", $cell->value(), "\n";
print "Unformatted = ", $cell->unformatted(), "\n";
print "\n";
}
}
}


worksheets

$workbook->worksheets()ですべてのワークシートが配列で返ってきます。


row_range col_range

ワークシートの最小・最大セルの番地を取得できます。

my ( $row_min, $row_max ) = $worksheet->row_range();

my ( $col_min, $col_max ) = $worksheet->col_range();

横(行)の場合は、row_range

縦(列)の場合は、col_range

を使用します。

ちなみに返ってくる値は、0オリジンです。

1行目: 0

A列 : 0

となります。


get_cell

番地を指定して、セルの情報を取得します。

my $cell = $worksheet->get_cell( $row, $col );

next unless $cell;

空セルの場合は、情報がないためundefとなります。

なので次の行でnextしてるんですね。


value

セルの情報が取れたら、valueを実行して値を取得できます。

print "Value       = ", $cell->value(),       "\n";

get_cellでセルの情報がないもの(undef)に対して、valueは実行できません(死にます)


その他の関数

SYNOPSISで紹介されてないよく使う関数


worksheet

シート名がわかってる・読むシートは固定とかそういうときに使います。

# シート名を指定する

$worksheet = $workbook->worksheet(`Sheet1`);

# 1番目(Sheet1)
$worksheet = $workbook->worksheet(0);

これも0オリジンなので、1枚目のシートが「0」となります。

シート名でも数字でも指定できます。


Spreadsheet::ParseXLSX

Excelファイルの2007形式(xlsx)を扱うときは、下記のモジュールを使用します。

Spreadsheet::ParseXLSX

https://metacpan.org/pod/Spreadsheet::ParseXLSX

モジュールが入っていない場合は、

cpanm -v Spreadsheet::ParseXLSXでインスコしましょう。

使い方は、簡単です。

先程のSYNOPSISのコードを3行変えるだけ・・・

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseXLSX;

my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse('Book1.xlsx');

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;

print "Row, Col = ($row, $col)\n";
print "Value = ", $cell->value(), "\n";
print "Unformatted = ", $cell->unformatted(), "\n";
print "\n";
}
}
}

さてどこが変わったでしょう・・・?w

というほどほとんど変わってないかと思います。

変えたのは以下の3行です。

use Spreadsheet::ParseXLSX;

my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse('Book1.xlsx');

上記を変えるだけなので、

中身のコードは、Spreadsheet::ParseExcelと同じでいけちゃいます。

動作確認したい人は、「Book1.xlsx」を作成して実行してみてください。


Tips


Excelファイルのどちらの形式にも対応する

evalを使ってtry~catchな感じで対応すればいけます。


use strict;
use warnings;
use utf8;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseXLSX;

my $input = $ARGV[0] // die;

my $parser = undef;
my $workbook = undef;

eval {
# Excel 95-2003
$parser = Spreadsheet::ParseExcel->new;
$workbook = $parser->parse($input);
};
if ($@) {
# Excel 2007
$parser = Spreadsheet::ParseXLSX->new;
$workbook = $parser->parse($input);
}

上記は、エラー処理(die $parser->errorなど)を書いてないので適宜いれてください。

Spreadsheet::ParseExcelでparseしてみて失敗したら

Spreadsheet::ParseXLSXでもう一度parseするという方法です。

parse時にファイルの読み込みができなければ、

モジュール内でdieするのでeval内で実行しています。

Excelファイルの2007形式の方が使用されるケースが多い場合は、

「eval内の処理」と「if($@)内の処理」を逆にしてくださいね~。


注意


ファイルパスのエンコード(windows)

ファイルパスは、cp932で渡してください。

use strict;

use warnings;
use utf8;
use Encode qw(encode);
use Spreadsheet::ParseExcel;

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse(encode('cp932', 'ほげ.xls'));

ファイルパスに日本語などのファイル名が含まれているとき、

ファイルが読み込めない・存在しない!と怒られるかもしれません。

そんなときはエンコードを疑ってみてください。


フリガナ付いちゃう問題

Excelファイル(xlsx)を読み込んだら、漢字の後ろにフリガナ入ってくるんだけど?

っていう人いっぱい出てくるかと思います。

こういうときは、『消してええええぇぇリライトしてえええええぇぇ』で対応してください。

対応方法はいろいろあるんですけど、

根本的に治すなら直接モジュールをいじる・・・なんですけど

バージョンアップするたびにいじる(コンフリクトする)の面倒だし。。。

ということで、自前パッケージを作ってオーバーライドして使うって感じがいいと思います。

use strict;

use warnings;
use utf8;

binmode STDOUT, ':encoding(cp932)';

my $parser = ParseXLSX->new;
my $wb = $parser->parse('hoge.xlsx');

if (not defined $wb) {
die $parser->error, ".\n";
}

for my $worksheet ( $wb->worksheets ) {

my ($row_min, $row_max) = $worksheet->row_range;
my ($col_min, $col_max) = $worksheet->col_range;

for my $row ($row_min .. $row_max) {
for my $col ($col_min .. $col_max) {

my $cell = $worksheet->get_cell($row, $col);
next unless $cell;

print "Row, Col = ($row, $col)\n";
print "Value = ", $cell->value, "\n";
print "Unformatted = ", $cell->unformatted, "\n";
print "\n";
}
}
}

package ParseXLSX;
use base qw(Spreadsheet::ParseXLSX);

sub _get_text_and_rich_font_by_cell {
my $self = shift;
my ($si, $theme_colors) = @_;

# XXX
my %default_font_opts = (
Height => 12,
Color => '#000000',
Name => '',
Bold => 0,
Italic => 0,
Underline => 0,
UnderlineStyle => 0,
Strikeout => 0,
Super => 0,
);

my $string_text = '';
my @rich_font_by_cell;
my @nodes_r = $si->find_nodes('.//s:r');
if (@nodes_r > 0) {
for my $chunk (map { $_->children } @nodes_r) {
my $string_length = length($string_text);
if ($chunk->name eq 's:t') {
if (!@rich_font_by_cell) {
push @rich_font_by_cell, [
$string_length,
Spreadsheet::ParseExcel::Font->new(%default_font_opts)
];
}
# $string_text .= $chunk->text; # コメントアウト
$string_text .= &remove_rph_text($chunk); # 追加
}
elsif ($chunk->name eq 's:rPr') {
my %format_text = %default_font_opts;
for my $node_format ($chunk->children) {
if ($node_format->name eq 's:sz') {
$format_text{Height} = $node_format->att('val');
}
elsif ($node_format->name eq 's:color') {
$format_text{Color} = $self->_color(
$theme_colors,
$node_format
);
}
elsif ($node_format->name eq 's:rFont') {
$format_text{Name} = $node_format->att('val');
}
elsif ($node_format->name eq 's:b') {
$format_text{Bold} = 1;
}
elsif ($node_format->name eq 's:i') {
$format_text{Italic} = 1;
}
elsif ($node_format->name eq 's:u') {
$format_text{Underline} = 1;
if (defined $node_format->att('val')) {
$format_text{UnderlineStyle} = 2;
} else {
$format_text{UnderlineStyle} = 1;
}
}
elsif ($node_format->name eq 's:strike') {
$format_text{Strikeout} = 1;
}
elsif ($node_format->name eq 's:vertAlign') {
if ($node_format->att('val') eq 'superscript') {
$format_text{Super} = 1;
}
elsif ($node_format->att('val') eq 'subscript') {
$format_text{Super} = 2;
}
}
}
push @rich_font_by_cell, [
$string_length,
Spreadsheet::ParseExcel::Font->new(%format_text)
];
}
}
}
else {
# $string_text = join '', map { $_->text } $si->find_nodes('.//s:t'); # コメントアウト
$string_text = &remove_rph_text($si); # 追加
}

return (
String => $string_text,
Rich => \@rich_font_by_cell,
);
}

#このメソッドを追加
sub remove_rph_text {
my $node = shift;

my @t_tag = $node->find_nodes('.//s:t'); # 変更
my @rph_tag = $node->find_nodes('.//s:rPh'); # 変更

my $return_text = "";

foreach (@t_tag){
my $t_text = $_->text;

my $flag = "1";

LOOP:
foreach (@rph_tag){
my $rph_text = $_->text;
if($rph_text eq $t_text){
$flag = "0";
last LOOP;
}
}

$return_text = $return_text.$t_text if($flag);
}

return $return_text;
}

1;

※Spreadsheet::ParseXLSX 0.27をベースにいじって動作確認しました

元ソースは、perl\site\lib\Spreadsheet\ParseXLSX.pmにあります。


変更前

Row, Col    = (0, 0)

Value = 忍者ニンジャ
Unformatted = 忍者ニンジャ

Row, Col = (1, 0)
Value = 隼ハヤブサ
Unformatted = 隼ハヤブサ



変更後

Row, Col    = (0, 0)

Value = 忍者
Unformatted = 忍者

Row, Col = (1, 0)
Value = 隼
Unformatted = 隼


Spreadsheet::ParseXLSX 0.20とか古めな方は、下記な感じで対応できる?

package ParseXLSX;

use base qw(Spreadsheet::ParseXLSX);

sub _parse_shared_strings {
my $self = shift;
my ($strings) = @_;

my $PkgStr = [];

if ($strings) {
my $xml = XML::Twig->new(
twig_handlers => {
'si' => sub {
my ( $twig, $si ) = @_;

# XXX this discards information about formatting within cells
# not sure how to represent that
push @$PkgStr,
# join( '', map { $_->text } $si->find_nodes('.//t') ); # オリジナル
join( '', &remove_rph_text($si) ); # 追加
$twig->purge;
},
}
);
$xml->parse( $strings );
}
return $PkgStr;
}

#このメソッドを追加
sub remove_rph_text {
my $node = shift;

my @t_tag = $node->find_nodes('.//t');
my @rph_tag = $node->find_nodes('.//rPh');

my $return_text = "";

foreach (@t_tag){
my $t_text = $_->text;

my $flag = "1";

LOOP:
foreach (@rph_tag){
my $rph_text = $_->text;
if($rph_text eq $t_text){
$flag = "0";
last LOOP;
}
}

$return_text = $return_text.$t_text if($flag);
}

return $return_text;
}

1;

下記のページから一部引用させていただきました。

perlでxlsxデータを - かんちゃんの徒然記

http://d.hatena.ne.jp/kanchan0318/20140117/1389970071

なんとか対応はできましたが、

ちょこちょこかわってるので、

バージョンアップしたら変更が必要になるかもしれないですねぇ。。。


まとめ

今回はExcelファイルの読みこみをご紹介しました。

読み込みさえできれば、あとはいろいろできそうですよね!

是非ご活用ください~。

他にもいい方法があったら是非教えてください~。