如何将oracle中的表导入到excel中? 有很多方法.1. 先用spool 表数据导出. 然后打开excel,数据/导入外部数据即可.2. 直接打开excel,数据/导入外部数据,选择你的数据源,然后选择你要的表就可以了. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 plsql developer,右键--export results--csv.file 使用Excel里面的数据导入,直接通过 ODBC ,选择所需的表,即可直接导入。 方法多种多样,可以利用工具,也可以写程序俺写一Perl程序,但最多只能select 255列,因为Excel一个sheet最多只有255列实施步骤1、先到www.activeperl.com去下载一个activeperl,免费的2、安装perl3、到\Perl\bin下面执行 ppm4、出现PPM提示符如下 D:\Perl\bin>PPM PPM interactive shell (2.1.6) - type 'help' for available commands. PPM>5、在PPM提示符下执行如下命令,安装数据库连接模块DBI,DBD PPM>install dbi PPM>install dbd-oracle6、执行下面的perl程序即可,其中用户名和密码,以及连接字符串自己改改 再命令窗口直接键入如下命令格式,该程序无需编译,祝你好运 table2xls.pl 7、该程序不支持RAW,LONG和LOB类型的字段table2xls.pl#!/usr/bin/perl -w#function: select records from table and print to Excel file#author: ATGC#version: 1.0#date of compilation September 6,2004#you can change the following parametersmy $conn_str = '连接名';my $user = 'user';my $pass = 'password';my $excel_file = 'e:\output.xls';my $sql = "select * from table";use strict;use DBI;use Win32::OLE;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");$dbh = DBI->connect("DBI:Oracle:$conn_str",$user,$pass);$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++; $Sheet->Range("A$row:$cell_end$row")->{Value} = [@field]; } $sth->finish(); $dbh->disconnect(); $Book->SaveAs($excel_file);}undef($Sheet);undef($Book);undef($Excel); 存储过程执行太慢,求高手解答 oracle中二进制常量的表示方式 请问一下ORACLE的稳定性和MYSQL相比如何? 关于TRIGGER 急!如何验证大量数据? 计算非重复时间 在线等,关于ORACLE批量插入数据,请各路高手发言?? 修改用户名和密码? 请问 在 Oracle 8i 下误装了9i 的客户端后,8i的服务启动不了,怎么可以恢复,并且获得原来8 i下的数据? 急问:MYSQL 中有何种锁和并发控制?谢谢!! 类型定义问题,请各位帮忙! 问高手一个问题????关于查询!
俺写一Perl程序,但最多只能select 255列,因为Excel一个sheet最多只有255列
实施步骤
1、先到www.activeperl.com去下载一个activeperl,免费的
2、安装perl
3、到\Perl\bin下面执行 ppm
4、出现PPM提示符如下
D:\Perl\bin>PPM
PPM interactive shell (2.1.6) - type 'help' for available commands.
PPM>
5、在PPM提示符下执行如下命令,安装数据库连接模块DBI,DBD
PPM>install dbi
PPM>install dbd-oracle
6、执行下面的perl程序即可,其中用户名和密码,以及连接字符串自己改改
再命令窗口直接键入如下命令格式,该程序无需编译,祝你好运
table2xls.pl
7、该程序不支持RAW,LONG和LOB类型的字段
table2xls.pl#!/usr/bin/perl -w
#function: select records from table and print to Excel file
#author: ATGC
#version: 1.0
#date of compilation September 6,2004#you can change the following parameters
my $conn_str = '连接名';
my $user = 'user';
my $pass = 'password';
my $excel_file = 'e:\output.xls';
my $sql = "select * from table";use strict;
use DBI;
use Win32::OLE;
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");
$dbh = DBI->connect("DBI:Oracle:$conn_str",$user,$pass);
$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++;
$Sheet->Range("A$row:$cell_end$row")->{Value} = [@field];
}
$sth->finish();
$dbh->disconnect();
$Book->SaveAs($excel_file);
}
undef($Sheet);
undef($Book);
undef($Excel);