用Perl從oracle匯出百萬級資料到excel

用Perl從oracle匯出百萬級資料到excel[@more@]轉自：
http://blog.chinaunix.net/u3/90327/showart.php?id=2027719
用Perl從oracle匯出百萬級資料到excel
用Perl從oracle匯出百萬級資料到excel excel 2007 可以存放1048576行、16384列的資料；excel 2003大概是65535行 我從oracle中匯出30萬行60列的資料到excel中花了約8分鐘。
如果你還沒有安裝perl，按下面步驟
步驟：
1、下載並安裝perl v5.8.8，可以使用perl －v檢視是否安裝成功，
2、輸入PPM命令開啟perl的包管理器。找到並更新DBI，
2、下載DBD-Oracle-1.17.zip解壓到c:perl 在命令列進入該目錄執行： ppm install dbd-oracle.ppd 安裝DBD－oracle；
3、安裝 Excel 模組，如果你能聯網，直接使用如下命令即可：
ppm install OLE::Storage_Lite
ppm install Spreadsheet::ParseExcel
ppm install Spreadsheet::WriteExcel4、將如下程式碼儲存到d:test.pl，更改裡面的oracle的使用者名稱和ip地址，以及埠號，以及sql語句
5、在命令列進入d盤下執行：perl test.pl
程式碼：
$ENV{NLS_LANG} = 'AMERICAN_AMERICA.ZHS16GBK';
use strict;
use DBI;
use Win32::OLE;
use POSIX qw(strftime);
my $now_string=strftime "%Y-%m-%d-%H-%M-%S", localtime;
my $ti_s =strftime "%S", localtime;
my $ti_m=strftime "%M", localtime;
my $ti_h=strftime "%H", localtime;
my $ti_old=$ti_s+($ti_m*60)+($ti_h*3600);
my $excel_file = "d:$now_string".".xlsx";
#在此修改你的sql
my $sql = "select * from table1";
my($dbh,$sth,$row,$col,@field,$ele,$c_times,$residual,$cols,$cell_end);
unlink $excel_file if (-e $excel_file);
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->add;
my $Sheet = $Book->Worksheets(1);
my @array_cols=("","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
#在此修改你的oracle的使用者名稱、密碼、ip、埠，
$dbh = DBI->connect('dbi:Oracle:',
= (PROTOCOL = TCP)(HOST = 10.XXX.XXX.XXX)(PORT = 1521)) (CONNECT_DATA=(SERVICE_NAME = test)))},
""
);
$sth = $dbh->prepare($sql);
$sth->execute();
$row=1;
$cols=0;
if (@field = $sth->fetchrow)
{
$cols=scalar(@field);
$c_times=int($cols/26);
$residual=$cols%26;
if ($cols<27)
{
$cell_end = $array_cols[$cols];
}
else
{
if ($residual == 0)
{
$cell_end = $array_cols[$c_times-1]."Z";
}
else
{
$cell_end = $array_cols[$c_times].$array_cols[$residual];
}
}
$Sheet->Range("A1:$cell_end$row")->{Value} = [@field];
while(@field = $sth->fetchrow)
{
$row++;
print ("正在匯出第 $row 條記錄！");
print ("n");
$Sheet->Range("A$row:$cell_end$row")->{Value} = [@field];
}
$sth->finish();
$dbh->disconnect();
$Book->SaveAs($excel_file);
}
undef($Sheet);
undef($Book);
undef($Excel);
my $now_string1=strftime "%Y-%m-%d-%H-%M-%S", localtime;
my $ti_s1 =strftime "%S", localtime;
my $ti_m1=strftime "%M", localtime;
my $ti_h1=strftime "%H", localtime;
my $ti_new=$ti_s1+($ti_m1*60)+($ti_h1*3600);print "************************************************************************nn";
print"匯出完成！開始時間：$now_string.t結束時間：$now_string1 n共耗時:t";
print $ti_new-$ti_old;
print ("秒nnn");
print "************************************************************************nn";
use Term::ReadKey;
$| = 1;
print "請按任意鍵退出...";
;
ReadMode 4; # Turn off controls keys
while (! defined ReadKey(-1)) {}
ReadMode 0; # Reset tty mode
$| = 0;