Created
November 3, 2011 08:20
-
-
Save mrmt/1336032 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/perl | |
# Excel列名変換問題で第2回社内プログラミングコンテストを開催してみた | |
# (前編) - ITは芸術だ | |
# http://d.hatena.ne.jp/JunichiIto/20111102/1320253815 | |
# | |
# from 15:23 | |
# step 1 15:51 | |
# step 2 16:35 | |
use strict; | |
use warnings; | |
use feature 'say'; | |
use constant ALPHABET_CHARS => 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; | |
use constant ALPHABET_RADIX => length(ALPHABET_CHARS); | |
if(defined($ARGV[0]) && defined($ARGV[1])){ | |
if($ARGV[0] eq '0'){ | |
if($ARGV[1] =~ /^\d+$/){ | |
say num_to_alpha($ARGV[1]); | |
exit; | |
} | |
} | |
if($ARGV[0] eq '1'){ | |
if($ARGV[1] =~ /^[A-Z]+$/){ | |
say alpha_to_num($ARGV[1]); | |
exit; | |
} | |
} | |
} | |
die <<"EOM"; | |
Usage: | |
$0 0 number_of_column | |
$0 1 alphabetical_column_label | |
EOM | |
################################################################ | |
sub alpha_to_num{ | |
my $arg = shift; | |
my $n = 0; | |
for my $c (split(//, $arg)){ | |
$n *= ALPHABET_RADIX; | |
$n += (index(ALPHABET_CHARS, $c) + 1); | |
} | |
return $n; | |
} | |
################################################################ | |
sub num_to_alpha{ | |
my $arg = shift; | |
my $str = ''; | |
for(;;){ | |
my $modulo = $arg % ALPHABET_RADIX; | |
$str = alpha($modulo) . $str; | |
$arg -= $modulo; | |
# 桁上がり直後の第一桁ゼロにあたるものがずれるので | |
if($modulo == 0){ | |
$arg -= ALPHABET_RADIX; | |
} | |
if($arg == 0){ | |
return $str; | |
} | |
$arg = int($arg / ALPHABET_RADIX); | |
} | |
} | |
################################################################ | |
sub alpha{ | |
substr(ALPHABET_CHARS, (shift)-1, 1); | |
} | |
__END__ | |
問題1: Excel列名変換問題 | |
* 仕様 | |
- 入力されたアルファベットを数字に変換する。 | |
- 変換ルールはExcelの列名と同等。 | |
- 例) A=1、B=2、Z=26、AA=27、XFD=16384 | |
* 起動時引数 | |
- [0] アルファベット (A~ZZZZ...[上限なし]) | |
実行例 | |
- ExcelColConv.pl A → 1 | |
- ExcelColConv.pl AA → 27 | |
問題2: Excel列名変換問題(逆変換) | |
* 仕様 | |
- 入力された数字をアルファベットに変換する。 | |
- ただし、問題1で作ったプログラムを拡張すること。 | |
* 起動時引数 | |
- [0] 0=数字へ変換、1=アルファベットへ変換 | |
- [1] 変換する数字またはアルファベット(どちらも上限なし) | |
* 実行例 | |
- ExcelColConv.pl 0 AA → 27 | |
- ExcelColConv.pl 1 27 → AA | |
sub test_me{ | |
use Test::More 'no_plan'; | |
my $v; | |
$v = alpha_to_num('A'); is($v, 1); | |
$v = alpha_to_num('B'); is($v, 2); | |
$v = alpha_to_num('Z'); is($v, 26); | |
$v = alpha_to_num('AA'); is($v, 27); | |
$v = alpha_to_num('XFD'); is($v, 16384); | |
$v = num_to_alpha(1); is($v, 'A'); | |
$v = num_to_alpha(2); is($v, 'B'); | |
$v = num_to_alpha(25); is($v, 'Y'); | |
$v = num_to_alpha(26); is($v, 'Z'); | |
$v = num_to_alpha(27); is($v, 'AA'); | |
$v = num_to_alpha(51); is($v, 'AY'); | |
$v = num_to_alpha(52); is($v, 'AZ'); | |
$v = num_to_alpha(53); is($v, 'BA'); | |
$v = num_to_alpha(16384); is($v, 'XFD'); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment