Spreadsheet::WriteExcel で Excel 関数を使う

みんな大好き Excel ファイルを perl で書くメモ。

perl モジュール Spreadsheet::WriteExcel で Excel 関数を使いたいと思った
ので調べました。

CPAN の ココ を読むとこんな感じで簡単にできるらしい。

$sheet->write( 5, 1, '=SUM(B2:I2)');

簡単！！

でも、列名をアルファベットで指定しなければならないので、このままだとちょ
っと微妙。

行と列に合わせて計算範囲も勝手にシフトして欲しい。でも、持ってる列情報
は数字だけ。

と、言うことで “数字” => “アルファベット” に変換する方法を考えて書いた
のがコレ。

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::WriteExcel;

    my $file = "dir/perl.xls";
    my $font_name = 'MS UI Gothic';
    my $font_size = '11';

    my $book  = Spreadsheet::WriteExcel->new("$file");
    my $sheet = $book->add_worksheet();

    my $format0 = $book->add_format(
                                     font   => $font_name,
                                     size   => $font_size,
                                     bold   => 1,
                                     border => 1,
                                     valign => 'vcenter',
                                     align  => 'center'
                                   );

    my $format1 = $book->add_format(
                                     font   => $font_name,
                                     size   => $font_size,
                                     bold   => 1,
                                     border => 1,
                                     valign => 'vcenter',
                                     align  => 'center'
                                   );

    my $x = '0';
    my $y = '0';

    $sheet->merge_range( $x, $y, $x, $y + 5, "write start", $format0 );
    $x++;

    for (my $i = '0'; $i <= '5'; $i++ ) {

        for (my $j = '1'; $j <= '20'; $j++ ) {

            $sheet->write( $x, $y + $i, int( rand(100) ), $format1 );
            $x++;

            if ( $j == '20' ) {

                my $abc = &column_abc( $y + $i );
                my $range = $abc . ($x - $j) . ":" . $abc . $x;
                my $excel_func = "=SUM($range)";

                $sheet->write( $x, $y + $i, $excel_func, $format1 );

            }
        }

        $x = '1';

    }

    $book->close();

sub column_abc {

    my $position = shift;

    my @abc = ( 'A'..'Z' );
    my ( $abc, $i, $j );

    if ( $position > '701' ) {

        $abc = "over";

    } elsif ( $position > '51' ) {

        $i = int( $position / 26 ) - 1;
        $j = $position - ( $i + 1 ) * 26;

        $abc = "$abc[$i]$abc[$j]";

    } elsif ( $position > '25' ) {

        $i = int( $position / 26 ) - 1;
        $j = $position - 26;

        $abc = "$abc[$i]$abc[$j]";

    } else {

        $i = $position;

        $abc = "$abc[$i]";

    }

    return( $abc );

}

exit;

これを実行するとこんなやつができます。

列名の変換は A ? ZZ までは対応。
意外と難しくて、結構やっつけな感じで書いてます。

AAA (703 列) 以上は省きましたが、702 列もあれば流石に足りるだろって事で。

でも、いざやってみると Spreadsheet::WriteExcel では 256 列までしか書け
ないようなので、この方法で OK と思うことにしました。