ITPub博客

首页 > 数据库 > 数据库开发技术 > perl连接oralce取数据并生成excle文件

perl连接oralce取数据并生成excle文件

原创 数据库开发技术 作者:liang573728 时间:2018-12-26 19:06:04 0 删除 编辑
使用perl Spreadsheet::WriteExcel作报表,生成excle表.例子如下:[@more@]

#!/usr/bin/perl -w
#
# System : Macau DW
# Program ID : dw_***_srpt.pl
# Description : Generate **** report
#
# Create By : Bill ***
# Creation Date : 04-SEP-2008
#
#
use strict;
use DBI;
use Spreadsheet::WriteExcel;

#Input Date Parameter
my $input_date=$ARGV[0];

#Result File Name Prefix
my $result_file_prefix = "hurrytracking_frpt_";

my $dbh = DBI->connect("dbi:Oracle:", "", "") or die "$DBI::errstr/n";

$dbh->{ChopBlanks} = 1;

my $sql = " SELECT to_char($input_date, 'DD-MON-YYYY'), to_char($input_date, 'YYMMDD') from dual";
my $datastmt = $dbh->prepare($sql) or die "Can't prepare SQL statement $DBI::errstrn";
$datastmt->execute;

my ($file_date,$report_date);
while (my @fld = $datastmt->fetchrow_array) {
$report_date = $fld[0];
$file_date = $fld[1];
}


$datastmt->finish;

my $working_file = $result_file_prefix . $file_date . ".xls";
print "Define a EXCEL Workbook " . $working_file . "...";

#Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new($working_file);

my $format_title = $workbook->addformat();
$format_title->set_bold();
$format_title->set_italic();
$format_title->set_color("Blue");
$format_title->set_font("Times New Roman");
$format_title->set_size(12);

my $format_header = $workbook->addformat();
$format_header->set_color("Blue");
$format_header->set_bold();
$format_header->set_font("Times New Roman");
$format_header->set_size(10);

my $format_data = $workbook->addformat();
$format_data->set_color("Black");
$format_data->set_font("Times New Roman");
$format_data->set_size(10);

print "Donen";

print "retrieve date columnn";

$sql = "select to_char($input_date, 'DD') from dual";

$datastmt = $dbh->prepare($sql) or die "Can't prepare SQL statement $DBI::errstrn";
$datastmt->execute;

my @date_col;

while (my @fld = $datastmt->fetchrow_array) {
my $day_num = $fld[0];
for (my $i = 1;$i <= $day_num; $i++) {
if ($i < 10){
push(@date_col,"0$i");
}else{
push(@date_col,"$i");
}
}
}
push(@date_col,"MTD");

print "retrieve data for Card sales invoicen";

...
...

$datastmt->finish();

print "Donen";


#print to excel for local
print "Create EXCEL worksheets for local...n";

my $worksheet = $workbook->addworksheet("SMC Hurry Performance Tracking");

$worksheet->write_string("A1", "Smartone Hurry Preliminary Performance Tracking Report", $format_title);
$worksheet->write_string("A2", "Report Date: $report_date", $format_title);
$worksheet->write_string("A4", "Card & Voucher Sales", $format_header);

my $count = 4;
$worksheet->write_string($count,0, "Spot SIM Sales", $format_header);

$count ++;
$worksheet->write_string($count,0, "New Number", $format_header);

$count ++;
$worksheet->write_string($count,0, " - Shop Front", $format_header);

$count ++;
$worksheet->write_string($count,0, " - Road Show", $format_header);

$count ++;
$worksheet->write_string($count,0, " - Others", $format_header);

$count += 2;
$worksheet->write_string($count,0, "MNP", $format_header);

...
...

$count +=2;
$worksheet->write_string($count,0, "Activated Grouped by Activation Date", $format_header);
foreach my $plan (sort @actvGroupedName) {
$count ++;
$worksheet->write_string($count,0, " - $plan", $format_header);
}

$count += 2;
$worksheet->write_string($count,0, "Activation Subscriber (SnapShot)", $format_header);

...
...

my $row = 3;
my $mcol = 1;

print "Updating the Excel file ...n";
foreach my $dateVar (@date_col) {

$worksheet->write_string($row, $mcol, $dateVar, $format_header);
$row++;

#define Starter Kit
#define New Number Shop Front
if (! defined $kitNewShop{"$dateVar"}) {
$kitNewShop{"$dateVar"} = 0
}
my $data_kitNewShop = $kitNewShop{"$dateVar"};

#define New Number Roadshow
if (! defined $kitNewRoad{"$dateVar"}) {
$kitNewRoad{"$dateVar"} = 0
}
my $data_kitNewRoad = $kitNewRoad{"$dateVar"};

#define New Number Other
if (! defined $kitNewOther{"$dateVar"}) {
$kitNewOther{"$dateVar"} = 0
}
my $data_kitNewOther = $kitNewOther{"$dateVar"};

#define New Number Total
my $data_kitNewTotal = $data_kitNewShop + $data_kitNewRoad +$data_kitNewOther;

#print New Number
$row ++;
$worksheet->write_number($row, $mcol, $data_kitNewTotal, $format_data);
$row ++;
$worksheet->write_number($row, $mcol, $data_kitNewShop, $format_data);
$row ++;
$worksheet->write_number($row, $mcol, $data_kitNewRoad, $format_data);
$row ++;
$worksheet->write_number($row, $mcol, $data_kitNewOther, $format_data);

#define MNP Shop Front

...
...

#Min Bank Bal
#define total
$totbank{"$dateVar"} = 0 if !exists $totbank{"$dateVar"};
my $data_totbank = $totbank{"$dateVar"};

my $data_totbank_Subr;
if ($dateVar eq "MTD") {
$data_totbank_Subr = $ActvSubscriber{"MTD"};
}
else {
$data_totbank_Subr = $ActvSubscriber{"$dateVar"}->{"Total"};
}

my $data_totbankAvg;
if ($data_totbank_Subr == 0) {
$data_totbankAvg = 0;
}
else{
$data_totbankAvg = sprintf "%.2f", $data_totbank/$data_totbank_Subr;
}

$row += 2;
$worksheet->write_number($row, $mcol,$data_totbank, $format_data);

$row ++;
$worksheet->write_number($row, $mcol,$data_totbankAvg, $format_data);

#define Inter
$interbank{"Total"}->{"$dateVar"} = 0 if !exists $interbank{"Total"}->{"$dateVar"};
my $data_interbank = $interbank{"Total"}->{"$dateVar"};

my $data_interbankAvg;
if ($data_ActvSubscriber == 0) {
$data_interbankAvg = 0;
}
else{
$data_interbankAvg = sprintf "%.2f", $data_interbank/$data_totbankAvg;
}

$row += 3;
$worksheet->write_number($row, $mcol,$data_interbank, $format_data);

$row ++;
$worksheet->write_number($row, $mcol,$data_interbankAvg, $format_data);

$row += 2;
for my $plan (sort @bankPlanName){

$interbank{$plan}->{"$dateVar"} = 0 if !exists $interbank{$plan}->{"$dateVar"};
my $data_interbank_plan = $interbank{$plan}->{"$dateVar"};

my $data_interbank_Subr;
if ($dateVar eq "MTD") {
$data_interbank_Subr = $ActvSubscriber{$plan}->{"MTD"};
}
else {
$data_interbank_Subr = $ActvSubscriber{"$dateVar"}->{$plan};
}

my $data_interbank_planAvg;
if ( $data_interbank_Subr == 0) {
$data_interbank_planAvg = 0;
}
else{
$data_interbank_planAvg = sprintf "%.2f",$data_interbank_plan/$data_interbank_Subr;
}

$row += 2;
$worksheet->write_number($row, $mcol,$data_interbank_plan, $format_data);
$row ++;
$worksheet->write_number($row, $mcol,$data_interbank_planAvg, $format_data);
}

#define Intra
$intrabank{"Total"}->{"$dateVar"} = 0 if !exists $intrabank{"Total"}->{"$dateVar"};
my $data_intrabank = $intrabank{"Total"}->{"$dateVar"};

my $data_intrabankAvg;
if ($data_ActvSubscriber == 0) {
$data_intrabankAvg = 0;
}
else{
$data_intrabankAvg = sprintf "%.2f", $data_intrabank/$data_totbankAvg;
}

$row += 3;
$worksheet->write_number($row, $mcol,$data_intrabank, $format_data);

$row ++;
$worksheet->write_number($row, $mcol,$data_intrabankAvg, $format_data);

$row += 2;
for my $plan (sort @bankPlanName){

$intrabank{$plan}->{"$dateVar"} = 0 if !exists $intrabank{$plan}->{"$dateVar"};
my $data_intrabank_plan = $intrabank{$plan}->{"$dateVar"};

my $data_intrabank_Subr;
if ($dateVar eq "MTD") {
$data_intrabank_Subr = $ActvSubscriber{$plan}->{"MTD"};
}
else {
$data_intrabank_Subr = $ActvSubscriber{"$dateVar"}->{$plan};
}

my $data_intrabank_planAvg;
if ( $data_intrabank_Subr == 0) {
$data_intrabank_planAvg = 0;
}
else{
$data_intrabank_planAvg = sprintf "%.2f",$data_intrabank_plan/$data_intrabank_Subr;
}

$row += 2;
$worksheet->write_number($row, $mcol,$data_intrabank_plan, $format_data);
$row ++;
$worksheet->write_number($row, $mcol,$data_intrabank_planAvg, $format_data);
}

$row = 3;
$mcol ++;

}

$workbook->close();

print "Donen";

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/640706/viewspace-1043929/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 大数问题
请登录后发表评论 登录
全部评论

注册时间:2005-10-15

  • 博文量
    152
  • 访问量
    120704