oracle php 导出excel,用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;

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