Perl
Excel

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

どうも みなさん
Perl書いてますか?

私は最近Golangに浮気中です・・・

今回は「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ファイルの読みこみをご紹介しました。
読み込みさえできれば、あとはいろいろできそうですよね!

是非ご活用ください~。

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