查询数据库 生成excel

不想安装PL SQL，就写了下面的代码。

查询数据库并写文件到txt：

#!/usr/bin/perl  
#perl script used to connect to Oracle  
use strict;  
use DBI;  
use txt_to_excel;
#声明数据连接所需的参数
my $ip="16.157.128.xx";   #oracle所在机器的ip
my $sid = "openview";  #数据库实例  
my $username = "test"; #连接数据库的用户名
my $password = "test"; #连接数据库的密码 

#创建连接
my $dbh=DBI->connect("dbi:Oracle:host=$ip;sid=$sid", $username, $password) or die "Cannot conenct oracle11g: $DBI::errstr\n"; 
my $template_name;
my $description;
my $cma_name;
my $cma_value_rule;
my $info = "TEMPLATE_NAME | CONDITION | CMA_NAME | CMA_VALUE_RULE\n";
my @info;
my $count = 0;
#查询数据  ,c.description "CONDITION",cma.cma_name,cma.cma_value_rule
my $sql = qq{
SELECT t.template_name,c.description,cma.cma_name,cma.cma_value_rule
FROM opc_cond c LEFT OUTER JOIN OPC_COND_CUST_ATTRIB cma ON c.condition_id=cma.condition_id,opc_source_templ t,opc_msg_cond mc
WHERE c.condition_id=mc.condition_id 
AND c.template_id = t.template_id
AND t.source_type_id<>256
AND (cma.cma_name IS NULL 
     OR (cma.cma_name='EventSource' 
     AND cma.cma_value_rule NOT IN ('MS_OVO','MS_OVO_PROXY')))
AND t.template_id IN
    (SELECT gt.template_id
     FROM   opc_templ_groups g,opc_templ_in_tgrp gt
     WHERE  g.templ_group_id=gt.templ_group_id
     AND    g.templ_group_name LIKE '%UXMON%')
ORDER BY t.template_name
};  
my $sth = $dbh->prepare($sql);  
$sth->execute();    
 
$sth->bind_columns(undef, \$template_name, \$description,\$cma_name,\$cma_value_rule);  
print "The results are:\n\n"; 
print "$info"; 
while ( $sth->fetch() ) { #fetch rows from DataBase  
        print "$template_name $description $cma_name $cma_value_rule\n";
        $info = $info."$template_name | $description | $cma_name | $cma_value_rule\n";
        @info = $info;
        if($info ne "TEMPLATE_NAME | CONDITION | CMA_NAME | CMA_VALUE_RULE\n"){
        	$count++;
        }
}  
if($count>0){
	open(FH,"> 数据1.txt");
	print FH @info;
	close FH;
	Convert();
}
else{
	print "no data";
}

$sth->finish(); 

txt转化为excel文件：

#!/usr/bin/perl -w

    use Encode(qw(decode));
    use strict;
    use Spreadsheet::WriteExcel; #安装这个模块
    sub Convert{
	    open (CVS, '数据1.txt') or die "数据1.txt: $!";  #同目录下txt文件
	    my $workbook  = Spreadsheet::WriteExcel->new('tab.xls'); #转换后的文件名为tab.xls
	    my $worksheet = $workbook->add_worksheet();
	    # Row and column are zero indexed
	    my $row = 0;
	    while (<CVS>) {
	        chomp;
	        # Split on |
	        my @Fld = split('\|', $_); #用 | 来分隔列
	        my $col = 0;
	        foreach my $token (@Fld) {
	            $worksheet->write_string($row, $col, decode('gb2312',$token));
	            $col++;
	        }
	        $row++;
	    }
    }
    
    return 1;

注意：转载本文需要注明作者和出处，本文作者 No. Liu