LoginSignup
7
7

More than 5 years have passed since last update.

@hiro_nico

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

Last updated at Posted at 2018-08-18

どうも みなさん
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";
        }
    }
}

さてどこが変わったでしょう・・・？ｗ

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

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

是非ご活用ください～。

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

7
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
7