从 Oracle 数据库创建 Excel 电子表格
我在 Oracle 数据库中有一个表.我必须从 Oracle 表创建一个复杂的电子表格结构.我正在寻找实现这一目标的最佳方法.我可以使用 SSIS 或使用一些 Oracle 实用程序来创建电子表格吗.
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.
任何帮助将不胜感激.
提前致谢.
问候迪布斯
推荐答案
我想问题是，你的复杂结构"到底有多复杂?
I suppose the problem is, just how complex is your "complex structure"?
Oracle SQL Developer 或 Quest TOAD 等编程 IDE 具有将数据表导出为 CSV 文件的向导.
Programming IDEs such as Oracle SQL Developer or Quest TOAD have wizards to export data table to CSV files.
如果要连接多个表中的数据，可以使用视图，甚至可以编写 SQL 语句
If you want to join data from multiple tables you could use a view, or even write a SQL statement
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.)
另一种快速的方法是使用 SQL*Plus 的 HTML 报告功能.许多电子表格工具可以轻松导入结构良好的 HTML 和 XML.了解更多.
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.
如果您想要扁平化层次结构或更复杂的结构，您可能需要转向 PL/SQL.手动方法将使用适合使用 UTL_FILE 的上述语句的变体:
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:
declare
csv_fh utl_file.filetype;
begin
csv_fh := utl_file.fopen('C: emp', '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;
如果您想专门导出到 Excel(即 .XLS 文件)，那么您需要超越 Oracle 内置程序.直接从 PL/SQL 导出到 Excel 的常用解决方案是 Tom Kyte 的 SYLK api 的 OWA_SYLK 包装器.了解更多信息.
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.
这适用于单个工作表.如果您想导出到多个工作表，有几种替代解决方案.
This works with single worksheets. If you want to export to multiple worksheets there are a couple of alternative solutions.
Sanjeev Sapre 有他的 get_xl_xml 包.顾名思义，它使用 XML 进行转换.了解更多.
Sanjeev Sapre has his get_xl_xml package. As the name suggests it uses XML to undertake the transformation. Find out more.
Jason Bennett 编写了一个生成 Excel XML 文档的 PL/SQL 对象.了解更多.
Jason Bennett has written a PL/SQL object which generates an Excel XML document. Find out more.
相关文章