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

#!/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::errstr\n";
$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 "Done\n";

print "retrieve date column\n";

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

$datastmt = $dbh->prepare($sql) or die "Can't prepare SQL statement $DBI::errstr\n";
$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 invoice\n";

...
...

$datastmt->finish();

print "Done\n";

#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 "Done\n";