用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::WriteExcel
4、将如下代码保存到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:',q{scott/tiger@(DESCRIPTION=(ADDRESS = (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 ($colsRange("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 "************************************************************************\n\n";
print"导出完成!开始时间:$now_string。
\t结束时间:$now_string1 \n共耗时:\t";
print $ti_new-$ti_old;
print ("秒\n\n\n");
print "************************************************************************\n\n";
use Term::ReadKey;
$| = 1;
print "请按任意键退出。
。。";
;
ReadMode 4; # Turn off controls keys
while (! defined ReadKey(-1)) {}
ReadMode 0; # Reset tty mode
$| = 0;。
全部