perl写入excel

本脚本功能：

从数据库获取数据并且写入excel文件；(据官网显示已经废弃此模块)

改用 Spreadsheet::ParseExcel及Spreadsheet::WriteExcel最好。前者是读Excel文件用的,后者用于写Excel文件。Spreadsheet::ParseExcel只能读95-2003格式的Excel文档，对于office 2007 Excel则要安装Spreadsheet::XLSX。

#!/usr/bin/env?perl
use?strict;
use?DBI;
use?Encode;
use?OLE::Storage_Lite;
use?Spreadsheet::WriteExcel::Big;

#数据库信息
my?$host?=?{
????user?=>?'root',????pass?=>?'123456',????host?=>?'127.0.0.1',????database?=>?'aries_host_info',????port?=>?3306,????table=>'host',};

#输出文件格式
my?($sec,$min,$hour,$mday,$mon,$year)?=?(localtime)[0..5];
($sec,$year)?=?(
sprintf("%02d",?$sec),sprintf("%02d",?$min),?$hour),?$mday),?$mon?+?1),$year?+?1900
);
my?$date="$year$mon$mday$hour$min";

#输出excel名字??邮件实体附件
my?$excel_file="${date}.xls";


my?@cols=('A:A','B:B','C:C','D:D','E:E','F:F','G:G','H:H','I:I','J:J','K:K','L:L','M:M','N:N','O:O','P:P','Q:Q','R:R','S:S','T:T','U:U','V:V','W:W','X:X','Y:Y','Z:Z','AA:A','BB:B','CC:C','DD:D','EE:E','FF:F','GG:G','HH:H','II:I','JJ:J','KK:K','LL:L','MM:M','NN:N','OO:O','PP:P','QQ:Q','RR:R','SS:S','TT:T','UU:U','VV:V','WW:W','XX:X','YY:Y','ZZ:Z');

#连接数据库
my?$dbh=DBI->connect("DBI:mysql:$host->{database};host=$host->{host};port=$host->{port}",$host->{user},$host->{pass},{RaiseError=>1});

#设置字符集
$dbh->do?("set?character_set_client?=?'utf8'");
$dbh->do?("set?character_set_connection?=?'utf8'");
$dbh->do?("set?character_set_results?=?'utf8'");

#sql?语句
my?$sql=qq(select?*?from?$host->{table};);
my?$sth=$dbh->prepare($sql)?or?die?'Unable?to?perpare?our?query:'.$dbh->errstr."n";
my?$results=$sth->execute()?or?die?'Unable?to?execute?our?query:'.$dbh->errstr."n";

#打印出sql的select行数到桌面、
if?($results?==?0){
????print?"查询结果?:?N/A";
}else{
#print?"$sql?cmd?find?$results?rows.n";
????print?"查询结果：$results?行.n";
????}

#从数据库查询结果的列名
my?@cols_name?=?@{$sth->{'NAME'}};

if?($#cols_name?>?$#cols)
{
print?"result?table?fields?overflow!(max?num.?>?".($#cols+1).")n";
exit;
}
print?"正在写入excel...n";

#创建excel文件
my?$excel?=?Spreadsheet::WriteExcel::Big->new($excel_file)?||?die?"excel?文件创建失败:?$!";

#创建?excel?sheet
my?$sheet?=?$excel->add_worksheet('anbound');

#excel文件格式
my?$title_style?=?$excel->add_format();
$title_style->set_size(11);
$title_style->set_bold();
$title_style->set_align('center');
my?$sheet_col?=?0;???#列信息


#将结果输出到excel?文件
for?(my?$i=0;?$i<scalar?@cols_name?;$i++)???#列信息
{
$sheet->set_column($cols[$i],?length($cols_name[$i])+20);
$sheet->write($sheet_col,$i,$cols_name[$i],$title_style);
}

#冻结表首行
$sheet->freeze_panes(1,?0);

while?(my?@row?=?$sth->fetchrow_array)
{
????????$sheet_col++;
????????for?(my?$i=0;?$i<?scalar?@cols_name?;$i++)
????????{
????????????????next?if?($row[$i]?eq?'');?????????????#无信息，就不写入
????????????????Encode::_utf8_on($row[$i]);?????????#把$row[i]当作utf8来处理
????????????????$sheet->write($sheet_col,?$i,$row[$i]);?
????????}
}
print?"excel写入完成!n";