用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> <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