oracle excel中的Oracle查询输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11796016/
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
Oracle query output in excel
提问by GrumP
I have an Oracle 10G database and I need to write a fairly straightforward query that joins two tables and selects some data. However, I'd like to export the result list to an excel, so end users can use this .xls document to see the results and filter by one of the fields (location)
我有一个 Oracle 10G 数据库，我需要编写一个相当简单的查询来连接两个表并选择一些数据。但是，我想将结果列表导出到 excel，因此最终用户可以使用此 .xls 文档查看结果并按字段（位置）之一进行过滤
When I write the query, is there an easy way I can generate/ create an excel document that would hold these results as described above? The SQL doesn't need to run from within excel, but I guess that would be a useful feature now that I think about it! Thanks.
当我编写查询时，是否有一种简单的方法可以生成/创建一个 excel 文档来保存上述这些结果？SQL 不需要在 excel 中运行，但我想现在我想这将是一个有用的功能！谢谢。
采纳答案by ora_excel
There is simple solution for your request.
有一个简单的解决方案可以满足您的要求。
By using ora_excel, small pl/sql package which generates Excel xlsx file, you can select data and export selected data to Excel and set filtering.
通过使用ora_excel，生成Excel xlsx文件的pl/sql小包，您可以选择数据并将选择的数据导出到Excel并设置过滤。
Please see following example:
请看下面的例子：
BEGIN
ORA_EXCEL.new_document;
ORA_EXCEL.add_sheet('My sheet');
ORA_EXCEL.query_to_sheet('select * from employees'); -- Select data from database
ORA_EXCEL.set_cells_filter('A1', 'K1'); -- Add cell filtering from column A1 to column K1
-- Save generated Excel to file with name example.xlsx to Oracle folder EXAMPLE_XLSX
ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');
END;
For more details please check here
欲了解更多详情，请查看这里
Cheers
干杯
回答by AnBisw
Since you cannot use OLE DB in your version of Excel. Use SPOOLto create a CSV file.
因为您不能在您的 Excel 版本中使用 OLE DB。使用SPOOL创建一个CSV文件。
SQL> SET echo off
SQL> SET verify off
SQL> SET colsep ,
SQL> SET pagesize 0
SQL> SET trimspool on
SQL> SET feedback off
SQL> SPOOL ON
SQL> SPOOL C:\data.csv
SQL> SELECT COLUMN1,COLUMN2,COLUMN3....
FROM TABLE;
SQL> SPOOL OFF
The .csvfile should open in Excel by default. Use proper column aliases so that users understand the column headers.
.csv默认情况下，该文件应在 Excel 中打开。使用正确的列别名，以便用户了解列标题。
回答by xQbert
Pretty easy to do in excel; and when done user can right click the data and say "Refresh" to get the latest updates.
在excel中很容易做到；完成后，用户可以右键单击数据并说“刷新”以获取最新更新。
but why reinvent the wheel lots of online articles already explain how to do this... Here's one
但是为什么要重新发明轮子很多在线文章已经解释了如何做到这一点......这是一个
http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/
http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/
After you've connected to a table, you can edit the properties on the connection and enter custom SQL (copy and paste from your developer tools)
连接到表后，您可以编辑连接上的属性并输入自定义 SQL（从开发人员工具复制和粘贴）
回答by hmmftg
Quick way:
快捷方式：
At first create a view which contains your Query(Best way because you might need to change this query later).
首先创建一个包含您的查询的视图（最好的方法，因为您以后可能需要更改此查询）。
Be sure to properly have installed oracle client.
确保正确安装了 oracle 客户端。
- In Excel(2007 and above) in Data tab go this way:
- 在数据选项卡中的 Excel（2007 及更高版本）中，请执行以下操作：
From Other sources -> From Data Connection Wizard -> Microsoft Data Access - OLE DB Provider for Oracle
从其他来源 -> 从数据连接向导 -> Microsoft 数据访问 - OLE DB Provider for Oracle
Now Enter your DataSource Name(Stored in tnsnames.ora) and user password
Find you view and Then You'll have what you need.
现在输入您的数据源名称（存储在 tnsnames.ora 中）和用户密码
找到您的视图，然后您将拥有所需的东西。
You can save password and set option to refresh automatically in connection properties.
您可以在连接属性中保存密码并设置选项以自动刷新。
回答by mrcardoor
You are able to query an oracle database directly from Excel 2003 however, your sql statements are interpreted by MS Query and because of this it can often be frustrating. I will assume the machine in question already has the ability to query your database and has properly configured the database naming.
您可以直接从 Excel 2003 查询 oracle 数据库，但是，您的 sql 语句由 MS Query 解释，因此通常会令人沮丧。我将假设有问题的机器已经能够查询您的数据库并正确配置了数据库命名。
To query your database from excel 2003 you must:
要从 excel 2003 查询您的数据库，您必须：
Install and configure oracle's ODBC Driver (You must have the 32bit drivers installed since excel03 is a 32bit application). ODBC can be configured under start > administrative tools > ODBC Data Source Administrator
Open excel 2003 and goto data > import external data > new database query.
安装和配置 oracle 的 ODBC 驱动程序（您必须安装 32 位驱动程序，因为 excel03 是 32 位应用程序）。可以在开始 > 管理工具 > ODBC 数据源管理器下配置 ODBC
打开excel 2003并转到数据>导入外部数据>新数据库查询。
This should bring up MS Query which is an Access-like interface.
这应该会打开 MS Query，它是一个类似 Access 的界面。
Obviously this is a very brief starter to get you stepping in the right direction. If you have any specific questions, please comment and I will try and help you.
显然，这是一个非常简短的入门指南，可让您朝着正确的方向迈进。如果您有任何具体问题，请发表评论，我会尽力帮助您。
回答by Vivek Kumar
Step 1 Run Your Query Right Click on Resultenter image description here
步骤 1 运行您的查询右键单击结果在此处输入图像描述
Step 2 Click on Export enter image description here
步骤 2 单击导出 在此处输入图像描述
Step 3 Select Format To Excel Enter datasheet name and location
步骤 3 选择 Format To Excel 输入数据表名称和位置
Step 4 Click on Next and then finish enter image description here
第 4 步点击下一步，然后完成 在这里输入图像描述
回答by Birupakhya Dash
You can do one thing.
你可以做一件事。
- First generate the output in a form that includes column separators using symbols (like , or #).
- Import the data to the excel and then define the placeholders as the column separators.
- 首先以包含使用符号（如，或#）的列分隔符的形式生成输出。
- 将数据导入excel，然后将占位符定义为列分隔符。
