Perl で、Excel のデータ (XML) をアップロードして処理する方法
- まず、Excel で 以下のように category1、category2、name、address、phone、link、comment1、comment2 というカラム構成のワークシートを作成し、適当にデータを入力、XML 形式で保存しておく。
注: 住所は米国の住所でないと正しく処理されないことに注意 - 次に、以下にあるように gmap_excel.html と gmap_excel.cgi の 2つのファイルを用意する (.gmap_excel.cgi 中のパスの設定などは適宜変更のこと)。
- gmap_excel.html から上の XML ファイルをサーバにアップロードする。
- サーバ側では Excel の XML データを処理して、gmap_excel.xml というファイルが出力される。
- このサーバ側で出力された gmap_excel.xml ファイルは Google Maps API を使った AJAX クライアントから利用できる。
gmap_excel.html のファイルの内容
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="styles-site.css" type="text/css" />
</head>
<body>
<form enctype="multipart/form-data" action="gmap_excel.cgi" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="2000000" />
<table width="600">
<tr><td>Names file:</td><td><input type="file" name="file" />
<input type="submit" value="Upload" /></td></tr>
</table>
</form>
</body>
gmap_excel.cgi のファイルの内容
#!/usr/bin/perl
use lib "path_to_your_perl_lib/lib/perl5/site_perl";
use strict;
use Geo::Google;
use CGI;
our $XML_HEADER = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; # XML ヘッダ
our $HOME_DIR = 'your_home_dir';
our $LOCK_DIR = 'path_to_lock_dir/lock'; # do chmod 777 path_to_lock_dir/lock
our $LOCK_FILE = 'lock';
our $XML_FILE = 'path_to_your_output_xml/gmap_excel.xml'; # 出力用 XML ファイル名
our $col_index = 0; # Excel シートの現在の列（カラム）インデックス
our $row_index = 0; # Excel シートの現在の行インデックス
our @data = (); # 出力用データ
our @current_data = (); # 現在のデータ
# オブジェクト初期化
our $CGI = new CGI;
our $GEO = new Geo::Google;
# HTML 出力
print qq|Content-type: text/html\n\n|;
print qq||;
print qq||;
print qq||;
init();
print qq|
|;
# 個々のデータのハンドラ
sub dataHandler {
my ($expat, $text) = @_;
# $row_index = 0 と 1 は、Excel で データのヘッダのため必要なし
if($text !~ /^\s+$/ && $row_index > 1) { # 空白行でなければ
$current_data[$col_index] = $text;
}
}
# 要素の開始
sub startElement {
my ($expat, $tag, %attr) = @_;
if($tag eq 'Row') { # <Row>で始まるとき
$col_index = 0; # カラムを初期化
$row_index++; # 行を進める
@current_data = (); # 現在の行データを初期化
}
if($tag eq 'Data') { # <Data>で始まるとき
$col_index++; # 列を進める
}
}
# 要素の終了
sub endElement {
my ($expat, $tag) = @_;
my ($xml, $html);
my ($lng, $lat, $name, $link, $category1, $category2);
my ($street, $city, $state, $phone, $comment1, $comment2);
# </Row> のとき
if($tag eq 'Row') {
push(@data, { # データに現在の行データを代入
'category1' => $current_data[1],
'category2' => $current_data[2],
'name' => $current_data[3],
'address' => $current_data[4],
'phone' => $current_data[5],
'link' => $current_data[6],
'comment1' => $current_data[7],
'comment2' => $current_data[8]
});
}
# </Workbook> のとき
if($tag eq 'Workbook') {
$row_index = -1; # 行数を初期化 ($row_index = 0 はヘッダのため -1 からスタート)
foreach my $row (@data) {
$row_index++;
if($row->{address}) {
$lng = ''; $lat = '';
my ($degree) = getDegrees($row->{address});
if($degree) {
$lng = $degree->longitude;
$lat = $degree->latitude;
}
}
$name = $row->{name};
$link = $row->{link}
=~ /http:\/\/[!#-9A-~]+\.+[a-z0-9]/ ? $row->{link} : '';
$category1 = $row->{category1};
$category2 = $row->{category2};
($street, $city, $state) = split(',', $row->{address});
$street = $street;
$city = $city;
$state = $state;
$phone = $row->{phone};
$comment1 = $row->{comment1} eq '-' ? '' : $row->{comment1};
$comment2 = $row->{comment2};
# lng と lat が検索できたら (データが入っているということだから)
if($lng && $lat) {
$xml .= "\t<marker\n"
. "\t\tlng = \"$lng\" lat = \"$lat\"\n"
. "\t\tname = \"" . $name . "\"\n"
. "\t\tlink = \"" . $link . "\"\n"
. "\t\tcategory1 = \"" . $category1 . "\"\n"
. "\t\tcategory2 = \"" . $category2 . "\"\n"
. "\t\taddress1 = \"$street\"\n"
. "\t\taddress2 = \"$city, $state\"\n"
. "\t\tphone = \"$phone\"\n"
. "\t\tcomment1 = \"$comment1\"\n"
. "\t\tcomment2 = \"$comment2\"\n"
. "\t/>\n\n";
} elsif($row_index) {
$html .= '<tr><td>'
. "$row_index / $name / $street, $city, $state"
. '</td></tr>';
}
}
$xml = $XML_HEADER
. "<markers>\n"
. $xml
. "</markers>\n";
if($html) { # エラーがあれば
$html = '<table>'
. '<tr><td>＊＊＊＊＊ 以下、緯度経度が検索不能でした。</td></tr>'
. $html
. '</table>';
} else { # エラーがなければ
print '<br />done.';
}
# ファイルのオープン
&file_lock; # ファイルロック
open(XML, ">$HOME_DIR$XML_FILE")
or die('Cannot open an XML file: ' . "$HOME_DIR$XML_FILE");
print XML $xml; # 書き込み
close(XML); # ファイルを閉じる
&file_unlock; # ロック解除
print $html;
}
}
sub getDegrees { # 住所から緯度経度を検索
return $GEO->location( address => shift); # shift = address / リファレンスを返す
}
sub init() {
# XMLパーサー作成 ('UTF-8')
my $xml_parser = new XML::Parser( ProtocolEncoding => 'UTF-8',
Handlers=>{ Start=>\&startElement,
End =>\&endElement,
Char =>\&dataHandler });
# ファイルのオープンはいらない。input タグで指定した name 属性がそのままファイルハンドルになる
my $xml = $CGI->param('file');
# XMLパース処理
$xml_parser->parse($xml) or die "XML error: $xml_parser";
# ファイルクローズ
close($xml);
}
sub file_lock {
my $wait = 5;
while (!symlink('.',"$HOME_DIR$LOCK_DIR/.$LOCK_FILE")) {
if (--$wait <= 0) {
# &error( '現在他の方が使用中です。',
# 'しばらくしてから再度ご利用ください。');
}
sleep (1);
}
}
sub file_unlock {
unlink ("$HOME_DIR$LOCK_DIR/.$LOCK_FILE");
}
1;
gmap_excel.xml 出力例
<?xml version="1.0" encoding="UTF-8"?>
<marker
lng = "-122.125690" lat = "37.413570"
name = "Tofu House"
link = ""
category1 = "Restaurant"
category2 = "Korean"
address1 = "4127 El Camino Real"
address2 = " Palo Alto, CA 94306"
phone = "650-424-8805"
comment1 = ""
comment2 = "Santa Clara の Tofu House と同じ店。"
/>
...
参考リンク
- PHPでExcelデータを読み書きする
→ 試したのだが、PHP はおそらく最新のバージョンが必要? サーバにインストールされているライブラリによってはうまく動かなかった。
→2006/12/14 追記：このページは削除されていました。