从Oracle数据库创建Excel电子表格 [英] Create an Excel Spreadsheet from a Oracle Database

问题描述

我在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.

任何帮助将不胜感激.

谢谢.

问候 Dibs

推荐答案

我想问题是，您的复杂结构"到底有多复杂?

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而不会产生任何抢劫. 生成HTML报告>查找更多

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:\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;

如果要专门导出到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编写了一个PL/SQL对象，该对象生成Excel XML文档. 了解详情.

Jason Bennett has written a PL/SQL object which generates an Excel XML document. Find out more.

这篇关于从Oracle数据库创建Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！