从 Oracle 数据库创建 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6017863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create an Excel Spreadsheet from a Oracle Database
提问by user524230
I have a table in a Oracle database. I have to create a complex spreadsheet structure from the Oracle table. I am looking for best approach to achieve this. Can I use SSIS or use some Oracle utilities to create the spreadsheet.
我在 Oracle 数据库中有一个表。我必须从 Oracle 表创建一个复杂的电子表格结构。我正在寻找实现这一目标的最佳方法。我可以使用 SSIS 或使用某些 Oracle 实用程序来创建电子表格吗？
Any help would be extremely appreciated.
任何帮助将不胜感激。
Thanks in advance.
提前致谢。
Regards Dibs
问候迪布斯
回答by APC
I suppose the problem is, just how complex is your "complex structure"?
我想问题是，你的“复杂结构”有多复杂？
Programming IDEs such as Oracle SQL Developer or Quest TOAD have wizards to export data table to CSV files.
Oracle SQL Developer 或 Quest TOAD 等编程 IDE 具有将数据表导出为 CSV 文件的向导。
If you want to join data from multiple tables you could use a view, or even write a SQL statement
如果要连接多个表中的数据，可以使用视图，甚至可以编写 SQL 语句
select e.ename||','||d.dname
from emp e
join dept d on ( e.deptno = d.deptno )
/
(rembering that columns can often contain data which includes commas, so you may want to use a more unusual character - or set of characters - as your separator.)
（记住列通常可以包含包含逗号的数据，因此您可能需要使用更不寻常的字符 - 或字符集 - 作为分隔符。）
Another quick way of doing soemthing is to use SQL*Plus's HTML reporting function. Many spreadsheet tools can import well-structured HTML and XML without a snatch. Find out more.
另一种快速的方法是使用 SQL*Plus 的 HTML 报告功能。许多电子表格工具可以轻松导入结构良好的 HTML 和 XML。 了解更多。
If you want to flatten a hierarchical structure or something even more convoluted you'll probably need to move into PL/SQL. A hand-rolled approach would use a variant of the above statement fitted to use UTL_FILE:
如果您想要扁平化层次结构或更复杂的结构，您可能需要转向 PL/SQL。手动方法将使用适合使用 UTL_FILE 的上述语句的变体：
declare
csv_fh utl_file.filetype;
begin
csv_fh := utl_file.fopen('C:\temp', 'data_export.csv', 'W');
for r in ( select e.ename, d.dname
from emp e
join dept d on ( e.deptno = d.deptno )
) loop
utl_file.put_line(csv_fh, r.ename||'|'||r.dname;
end loop;
utl_file.fclose(csv_fh);
end;
If you want to export specifically to Excel (i.e. a .XLS file) then you'll need to go beyond Oracle built-ins. The usual solution for exporting straight from PL/SQL to Excel is Tom Kyte's OWA_SYLK wrapper for the SYLK api. Find out more.
如果您想专门导出到 Excel（即 .XLS 文件），那么您需要超越 Oracle 内置程序。直接从 PL/SQL 导出到 Excel 的常用解决方案是 Tom Kyte 的 SYLK api 的 OWA_SYLK 包装器。 了解更多。
This works with single worksheets. If you want to export to multiple worksheets there are a couple of alternative solutions.
这适用于单个工作表。如果您想导出到多个工作表，有几种替代解决方案。
Sanjeev Sapre has his get_xl_xml package. As the name suggests it uses XML to undertake the transformation. Find out more.
Sanjeev Sapre 有他的 get_xl_xml 包。顾名思义，它使用 XML 进行转换。 了解更多。
Jason Bennett has written a PL/SQL object which generates an Excel XML document. Find out more.
Jason Bennett 编写了一个生成 Excel XML 文档的 PL/SQL 对象。 了解更多。
回答by ora_excel
You can use ORA_EXCEL package to create Excel documents with complex structure. ORA_EXCEL have basic but powerful components which you may use to build complex Excel files.
您可以使用 ORA_EXCEL 包创建结构复杂的 Excel 文档。ORA_EXCEL 具有基本但功能强大的组件，可用于构建复杂的 Excel 文件。
Check functions list at: http://www.oraexcel.com/documentation
检查功能列表：http: //www.oraexcel.com/documentation
回答by Aditya Patel
set linesize 4000 pagesize 0 colsep ',' set heading off trimspool on trimout on
set linesize 4000 pagesize 0 colsep ',' 设置标题关闭修剪池
spool c:/file.xls
线轴 c:/file.xls
select * from tableName;
select * from tableName;
spool off;
脱线；
this will generate your file.xls in c drive. You can give any address and if you want column heading then remove "heading off" clause and set pagesize to some numeric value.
这将在 c 驱动器中生成您的 file.xls。您可以提供任何地址，如果您想要列标题，则删除“heading off”子句并将 pagesize 设置为某个数值。
回答by Hank Gay
The first thing I would try is just dumping the values I wanted to CSV. Excel should be able to import that just fine, and it's often easier to fiddle with Excel formulas, etc., manually than to try to automate it. If you're using Oracle SQLDeveloper, there's an option to export results to CSV. If you're using SQL*Plus, then you can use set colsep ,to make Oracle use commas to separate the columns. You'll probably want to do some more formatting tweaks if you're using SQL*Plus, though.
我会尝试的第一件事就是将我想要的值转储到 CSV。Excel 应该能够很好地导入它，并且手动处理 Excel 公式等通常比尝试自动化更容易。如果您使用 Oracle SQLDeveloper，则可以选择将结果导出到 CSV。如果您使用的是 SQL*Plus，那么您可以set colsep ,使 Oracle 使用逗号来分隔列。不过，如果您使用的是 SQL*Plus，您可能需要进行更多的格式调整。
回答by Cos Callis
There is a 'disconnected' version of SSRS report descriptions called .RDLC (Report Definition Language Client). This is a subset of the RDL that is part of SSRS. There are report viewer controls that have built in exporters for Excel and PDF formats. The viewer control is available for both windows forms and web forms. The datasource for these reports are generic DataSets or data objects that are agnostic to data sources (SQL, Oracle,...smoke signals..)
有一个名为 .RDLC（报告定义语言客户端）的“断开连接”版本的 SSRS 报告描述。这是作为 SSRS 一部分的 RDL 的子集。有内置的 Excel 和 PDF 格式导出器的报表查看器控件。查看器控件可用于 windows 窗体和 web 窗体。这些报告的数据源是与数据源（SQL、Oracle、...烟雾信号..）无关的通用数据集或数据对象
http://www.highoncoding.com/Articles/339_Creating_Charts_Using_Aspnet_ReportViewer_Control.aspx
http://www.highoncoding.com/Articles/339_Creating_Charts_Using_Aspnet_ReportViewer_Control.aspx
http://msdn.microsoft.com/en-us/library/ms251771%28VS.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms251771%28VS.80%29.aspx
