ExcelColConv.pl

#!/usr/bin/env perl
use strict;
use warnings;
use Test::More;
use Data::Dumper;

my @alphabets = 'A' .. 'Z';
my $offset = 64; # A == 65 なので A == 1 にするために 64 引く

my %lookup_table = map { $_ => ord($_) - $offset } @alphabets;
my %reverse_lookup_table = reverse %lookup_table;


sub number_to_alphabet {
    my ($num) = @_;
    my @rec = ();
    _number_to_alphabet_rec($num, \@rec);
    my $alpha = join '', @rec;
    return $alpha;
}

sub _number_to_alphabet_rec {
    my ($num, $rec) = @_;
    my $div = int($num / 26);
    my $mod = $num % 26;

    if ($div > 0) {
        unshift @$rec, $reverse_lookup_table{$mod};
        _number_to_alphabet_rec($div, $rec);
    } else {
        unshift @$rec, $reverse_lookup_table{$mod};
    }
}

sub alphabet_to_number {
    my ($alpha) = @_;
    my @rec = ();
    for my $alphabet (split //, $alpha) {
        _alphabet_to_number_rec($alphabet, \@rec);
    }

    my $total;
    for (my $i=0; $i<=$#rec; $i++) {
        my $figure = $#rec - $i; # 何桁目か
        $total += $rec[$i] * (26 ** $figure);
    }
    return $total;
}

sub _alphabet_to_number_rec {
    my ($alpha, $rec) = @_;
    my $num = $lookup_table{$alpha};
    push @$rec, $num;
}


# 問題1: Excel列名変換問題
is( alphabet_to_number('A'),   1,     'A   -> 1'     );
is( alphabet_to_number('AA'),  27,    'AA  -> 27'    );
is( alphabet_to_number('XFD'), 16384, 'XFD -> 16384' );

# 問題2: Excel列名変換問題(逆変換)
is( number_to_alphabet(1),     'A',   '1 -> A'       );
is( number_to_alphabet(27),    'AA',  '27 -> AA'     );
is( number_to_alphabet(16384), 'XFD', '16384 -> XFD' );

done_testing;