本脚本功能：
从数据库获取数据并且写入excel文件；(据官网显示已经废弃此模块)
改用 Spreadsheet::ParseExcel及Spreadsheet::WriteExcel最好。前者是读Excel文件用的,后者用于写Excel文件。Spreadsheet::ParseExcel只能读95-2003格式的Excel文档，对于office 2007 Excel则要安装Spreadsheet::XLSX。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
#!/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
,
$min
,
$hour
,
$mday
,
$mon
,
$year
) = (
sprintf
(
"%02d"
,
$sec
),
sprintf
(
"%02d"
,
$min
),
sprintf
(
"%02d"
,
$hour
),
sprintf
(
"%02d"
,
$mday
),
sprintf
(
"%02d"
,
$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"
;
|
本文转自 西索oO 51CTO博客，原文链接:http://blog.51cto.com/lansgg/1648322
