使用perl语言读数据库并将数据写入Excel

#需要使用的模板

use strict;

use Spreadsheet::ParseExcel;

use Spreadsheet::WriteExcel;

use Spreadsheet::ParseExcel::FmtUnicode;

use Unicode::Map();#该引用是为了实现中文的写入

use DBI;

use Encode;

?

#需要定义的全局变量

my $MYEXCEL;

my $dest_book;

my $sheet;

my $dest_sheet1;

my $Text_Date;

my $Num=3;

?

?

##################################开始生成Excel文件############################

? ??? #生成EXCEL文件名

$MYEXCEL=$OUT_DIR.'//免收//'.$bankname.'免收.xls';

#在对应的目录下生成一个EXCEL文件

$dest_book = Spreadsheet::WriteExcel->new($MYEXCEL) or die "Could not create a new Excel file in $MYEXCEL: $!";

?

#定义想要的输出格式

? ?? $fmt = {HEADER => $dest_book->add_format(bold=>1,bottom=>2,bg_color=>'yellow',left=>1),

??????????????? ENAME => $dest_book->add_format(bold=>1,bg_color=>'silver'),

??????? ????????MONEY => $dest_book->add_format(align=>'right',bg_color=>'red'),

??????????????? TOTAL => $dest_book->add_format(align=>'right')};

????????????? $fmt2= $dest_book->add_format(border =>1,bold=>1,color=>'black',size=>11,align=>'top',bg_color=>'silver');

????????????? $fmt3= $dest_book->add_format(border =>1,align=>'center');

?

?

?

?

?

?

###########################写第一个sheet#################################

$sheet="1";

? ?? $dest_sheet1 = $dest_book->addworksheet($sheet);

????????????? #对每一个SHEET写入表头

????????????? $dest_sheet1->merge_range(0,4,decode('CP936',"机构号".$bankname."免收数据信息"),$fmt3);

##设置某列的宽度

????????????? $dest_sheet1->set_row(1,140);??

##合并第2行的0-4列，内容为免收费类型，格式为$fmt2

#此处注意1合并的参数：起始行号、起始列号、结束行数、结束列数

#此处注意2输出的编码方式，如果是中文，需要进行转换

#此处注意3输出的格式，需要重新定义，否则报错

????????????? $dest_sheet1->merge_range(1,1,"免收费类型"),$fmt2);

?

????????????? #依次列出各个列的标题

????????????? $dest_sheet1->write_unicode(2,$Map->to_unicode("机构号"),$fmt->{HEADER});

????????????? $dest_sheet1->write_unicode(2,$Map->to_unicode("账号"),2,$Map->to_unicode("卡号"),3,$Map->to_unicode("户名"),$Map->to_unicode("免收类型"),$fmt->{HEADER});

?

#####其他sheet与上面类似###

?

#########################读数据库数据（略），写入Excel####################

##依次写入第$Num行，@recs[1]列

while(my @recs=$qu->fetchrow_array)

??? {

??? ?????? if($Num<60003)

??? ?????? {

??? ?????? ????? $dest_sheet1->write_unicode($Num,$Map->to_unicode("@recs[0]"));

?????????????????????????? $dest_sheet1->write_unicode($Num,$Map->to_unicode("@recs[1]"));

?????????????????????????? $dest_sheet1->write_unicode($Num,$Map->to_unicode("@recs[2]"));

?????????????????????????? $dest_sheet1->write_unicode($Num,$Map->to_unicode("@recs[3]"));

?????????????????????????? $dest_sheet1->write_unicode($Num,$Map->to_unicode("@recs[4]"));

?????

??????? $Num++;

????? }

?

写完后关闭

$dest_book->close();