用PERL 程序编写一个从ORACLE数据库中取数然后规范格式插入到EXCEL中的程序
以下为一个实例
安装WINDOWS下支持PERL并且能连接的数据库的环境 见 文档在Win2000中安装perl并访问Oracle
#! c:perlinperl ################################################################### 大客户资料 提供给xxx ## 今年本月 各个供应商的进货额 销售 毛利 毛利额 快讯海报销 ## 本月快讯单品的快讯期间进货额 销售额 ## 去年这个月的进货额 销售 毛利 毛利额 # ##################################################################use DBI;use Win32::OLE;if (@ARGV<5) { #主程序参数print "Usage: dkh.pl <store_no> <start date> <end date> <mail1> <mail2> 例如: dkh.pl 12 20060101 20060131 2601 2602 ";exit(0);}$startdate = $ARGV[1];$enddate = $ARGV[2];$startdate2=$ARGV[1]-10000; #获得去年的日期$enddate2=$ARGV[2]-10000;$storeno = $ARGV[0];$mail1 = $ARGV[3];$mail2 = $ARGV[4];if ($storeno == 12) { $oracleid = "report/system"; #数据库用户名密码 $oracleid1 = "jxc/xjyjxc"; }$dbh = DBI->connect("dbi:Oracle:host=148.20.40.4;sid=ora7",$oracleid1,'',{AutoCommit=>0})|| die "Don't connect database ! "; #连接数据库$dbh_st = DBI->connect("dbi:Oracle:host=148.20.40.2;sid=ora7",$oracleid,'',{AutoCommit=>0})|| die "Don't connect database ! ";#连接数据库# get already active Excel application or open new 打开一个新EXCEL实例 $Excel = Win32::OLE->new('Excel.Application', 'Quit')|| Win32::OLE->GetActiveObject('Excel.Application'); $Book = $Excel->Workbooks->Open("e:/tmp/tmp/DKH.XLS"); #打开一个EXCEL表格$sheet=1; #指定第一张表 $Sheet = $Book->Worksheets($sheet); ############################################################################################ 在第一张sheet1表中插入本年的各项数据 ########################################################################################### print "### 插入各项数据 大于29000 小与29999 供应商$startdate -- $enddate 期间销售 ### ";$sql="select j.buyer_uid, j.suppl_no, s.NAME, sum(j.GOR_AMOUNT), sum(j.SALE_AMOUNT), sum(j.SALE_AMOUNT) - sum(j.SALE_NN_AMOUNT), sum(j.SALE_MM_AMOUNT), decode(sum(j.SALE_MM_AMOUNT), 0, 0, sum(j.SALE_MM_AMOUNT) / sum(j.SALE_AMOUNT)) from jxc_sup j, supplier s where j.suppl_no = s.suppl_no and j.run_date between to_date($startdate, 'yyyymmdd') and to_date($enddate, 'yyyymmdd') and j.buyer_uid!='DZH' and j.suppl_no between 29000 and 29999 and j.store_no=12 group by j.buyer_uid, j.suppl_no,s.NAME order by j.buyer_uid,j.suppl_no";#嵌入SQL语句$sth=$dbh->prepare($sql);$sth->execute();$Sheet->Cells(1,1)->{Value}="大于29000 小与29999 供应商的$startdate~$enddate销售数据";for($row=3;@result=$sth->fetchrow_array;$row++) { for($line=2;$line<=9;$line++) { $Sheet->Cells($row,$line)->{Value}=$result[$line-2]; }};$sth->finish; $sheet=2; $Sheet = $Book->Worksheets($sheet); ############################################################################################ 在第二张sheet2表中插入去年的各项数据 ########################################################################################### print "### 插入各项数据 29001-29999 供应商$startdate2 - $enddate2 期间销售 ### ";$sql="select j.buyer_uid, j.suppl_no, s.NAME, sum(j.GOR_AMOUNT), sum(j.SALE_AMOUNT), sum(j.SALE_AMOUNT) - sum(j.SALE_NN_AMOUNT), sum(j.SALE_MM_AMOUNT), decode(sum(j.SALE_MM_AMOUNT), 0, 0, sum(j.SALE_MM_AMOUNT) / sum(j.SALE_AMOUNT)) from jxc_sup j, supplier s where j.suppl_no = s.suppl_no and j.run_date between to_date($startdate2, 'yyyymmdd') and to_date($enddate2, 'yyyymmdd') and j.suppl_no between 29000 and 29999 and j.buyer_uid!='DZH' and j.store_no = 12 group by j.buyer_uid, j.suppl_no,s.NAME order by 1,2";$sth=$dbh->prepare($sql);$sth->execute();$Sheet->Cells(1,1)->{Value}="大于29000 小与29999 供应商的$startdate2 -- $enddate2销售数据";for($row=3;@result=$sth->fetchrow_array;$row++) { for($line=2;$line<=9;$line++) { $Sheet->Cells($row,$line)->{Value}=$result[$line-2]; }}$sth->finish;$dbh->disconnect;############################################################################################## 下面开始运算快讯的信息 插入到SHEET3表格中 ##############################################################################################$sheet=3;$Sheet = $Book->Worksheets($sheet);print "### 插入各项数据$mail1 ### ";$sql=" select '$mail1' , t1.suppl_no, t2.name, sum((t1.gor_qty+t1.del_corr+t1.return_qty)*t1.old_nn_buy_price*(t1.vat_perc/100+1)), sum(t1.sale_amount) from tb_fin_art_stock t1,supplier t2 where t1.suppl_no=t2.suppl_no and t1.suppl_no between 29001 and 29099 and t1.art_no in (select art_no from mm_article where mmail_no in ($mail1)) and t1.run_date>=(select START_DATE from mm_calendar where mmail_no =$mail1) and t1.run_date<=(select end_date from mm_calendar where mmail_no=$mail1) group by t1.suppl_no,t2.name";$sth=$dbh_st->prepare($sql);$sth->execute();$Sheet->Cells(1,1)->{Value}="快讯期数据$mail1"; #插入EXCEL中第一行第一列for($row=3;@result=$sth->fetchrow_array;$row++) { for($line=1;$line<=5;$line++) { $Sheet->Cells($row,$line)->{Value}=$result[$line-1]; }};$sth->finish;print "### 插入各项数据$mail2 ### ";$sql=" select '$mail2' , t1.suppl_no, t2.name, sum((t1.gor_qty+t1.del_corr+t1.return_qty)*t1.old_nn_buy_price*(t1.vat_perc/100+1)), sum(t1.sale_amount) from tb_fin_art_stock t1,supplier t2 where t1.suppl_no=t2.suppl_no and t1.suppl_no between 29001 and 29099 and t1.art_no in (select art_no from mm_article where mmail_no in ($mail2)) and t1.run_date>=(select START_DATE from mm_calendar where mmail_no =$mail2) and t1.run_date<=(select end_date from mm_calendar where mmail_no=$mail2) group by t1.suppl_no,t2.name";$sth=$dbh_st->prepare($sql);$sth->execute();$Sheet->Cells(1,8)->{Value}="快讯期数据$mail2";for($row=3;@result=$sth->fetchrow_array;$row++) { for($line=8;$line<=12;$line++) { $Sheet->Cells($row,$line)->{Value}=$result[$line-8]; }};$sth->finish;$dbh_st->disconnect;$Book->Close;
学习基本PERL 语法，使用PERL 中的DBI库连接数据库，利用WIN32::OLE来写入EXCEL