Question: I have a script which retrieves data
from different table and my requirement is to use the SQL*Plus spool
command to write the table data into a flat file. Then, I need the
data from each table to be spooled into different sheets in the same
excel sheet.
spool d:\spool\spool1.xls
select * from tab1;
select * from tab2;
select * from tab3;
spool off
In the above code, data from tab1 should be spooled into Excel
sheet1 and data from Tab2 should be spooled into Excel sheet2 and so
on, all within the same excel spreadsheet.
Answer:
One option is to use Excel-DB
with a direct API from Excel to Oracle.
Another way to write
Oracle data into multiple Excel sub-sheets is to use a Perl script,
like this one:
use Win32::OLE::Const
'Microsoft Excel';
system
"sqlplus","develop\@wwcdb\/uters","\@D:\\ulab\\DB\\rec\\country"
,"D:\\ulab\\DB\\rec\\COUNTRY" ;
open CNTRY
,"D:\\ulab\\DB\\rec\\COUNTRY_DATA.txt" or die "Can't open : $!";
@country = ;
close CNTRY;
my $workbook =
Spreadsheet::WriteExcel->new('D:\\ulab\\DB\\rec\\COUNTRY.xls');
foreach $cnt (@country)
{
$cntry =
(substr($cnt,0,2) );
$worksheet1 =
$workbook->add_worksheet($cntry);
#system
"sqlplus","develop\@wwcdb\/uters","\@D:\\ulab\\DB\\rec\\test","$cntry","D:\\ulab\\DB\\rec\\$cntry"
;
$Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application');
$Excel->{'Visible'} =
0;
$Excel->{DisplayAlerts}=0; $ctry =
$cntry.'_DATA';
print $ctry;
$Book = $Excel->Workbooks->Open
("D:\\ulab\\DB\\rec\\$ctry.csv");
$Sheet = $Book->Worksheets(1);
#print "balaji";
$LastRow =
$Sheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};
#print $LastRow;
foreach $row (1..$LastRow)
{
foreach $col (1..22)
{
$worksheet1 ->write($row-1, $col-1,
$Sheet->Cells($row,$col)->{'Value'});
}
}
