用PERL 程序编写一个从ORACLE数据库中取数然后规范格式插入到EXCEL中的程序

用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><startdate><enddate><mail1><mail2>例如:dkh.pl12200601012006013126012602 " ;
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'tconnectdatabase! " ; # 连接数据库
$dbh_st = DBI -> connect ( " dbi:Oracle:host=148.20.40.2;sid=ora7 " , $oracleid , '' , {AutoCommit => 0 }) ||
die " Don'tconnectdatabase! " ; # 连接数据库

#getalreadyactiveExcelapplicationoropennew打开一个新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 = " selectj.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))
fromjxc_supj,suppliers
wherej.suppl_no=s.suppl_no
andj.run_datebetweento_date($startdate,'yyyymmdd')and
to_date($enddate,'yyyymmdd')
andj.buyer_uid!='DZH'
andj.suppl_nobetween29000and29999
andj.store_no=12
groupbyj.buyer_uid,j.suppl_no,s.NAME
orderbyj.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 = " selectj.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))
fromjxc_supj,suppliers
where
j.suppl_no=s.suppl_no
andj.run_datebetweento_date($startdate2,'yyyymmdd')and
to_date($enddate2,'yyyymmdd')
andj.suppl_nobetween29000and29999
andj.buyer_uid!='DZH'
andj.store_no=12
groupbyj.buyer_uid,j.suppl_no,s.NAME
orderby1,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)
fromtb_fin_art_stockt1,suppliert2
wheret1.suppl_no=t2.suppl_no
andt1.suppl_nobetween29001and29099
andt1.art_noin(selectart_nofrommm_articlewheremmail_noin($mail1))
andt1.run_date>=(selectSTART_DATEfrommm_calendarwheremmail_no=$mail1)
andt1.run_date<=(selectend_datefrommm_calendarwheremmail_no=$mail1)
groupbyt1.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)
fromtb_fin_art_stockt1,suppliert2
wheret1.suppl_no=t2.suppl_no
andt1.suppl_nobetween29001and29099
andt1.art_noin(selectart_nofrommm_articlewheremmail_noin($mail2))
andt1.run_date>=(selectSTART_DATEfrommm_calendarwheremmail_no=$mail2)
andt1.run_date<=(selectend_datefrommm_calendarwheremmail_no=$mail2)
groupbyt1.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