Html Oracle 到 Excel - PL/SQL 导出过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/298648/
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 to Excel - PL/SQL export procedure
提问by
I'm writing pl/sql procedure that exports data from Oracle to Excel. I need data formatting so I can't use CSV. I've already tried with XML but it generates too large files when I want to export e.g. 70000 rows with 50 columns (almost 300 MB!!!).
我正在编写将数据从 Oracle 导出到 Excel 的 pl/sql 过程。我需要数据格式，所以我不能使用 CSV。我已经尝试过使用 XML，但是当我想导出例如 70000 行 50 列（几乎 300 MB ！！！）时，它会生成太大的文件。
That's why I decided to use HTML tags to generate XLS file – it is smaller than XML and I must directly define format of only some special columns (strings, numbers and dates are formatted automatically by Excel). It's very simple and convenient but I can't define more than one worksheet.
这就是为什么我决定使用 HTML 标签来生成 XLS 文件的原因——它比 XML 小，我必须直接定义一些特殊列的格式（字符串、数字和日期由 Excel 自动格式化）。它非常简单方便，但我不能定义多个工作表。
Do you know how to add/define more than one worksheet in excel file written using HTML? I've tried to use VBScript formula like <% ActiveWorkbook.Worksheet.Add %>, but it doesn't work.
您知道如何在使用 HTML 编写的 Excel 文件中添加/定义多个工作表吗？我试过使用像 <% ActiveWorkbook.Worksheet.Add %> 这样的 VBScript 公式，但它不起作用。
回答by user38123
Instead of creating Excel or HTML files on Oracle server, you can fetch Oracle data to existing Excel document via ODBC or OLEDB. The shortcoming is, that you should be careful with user permissions.
您可以通过 ODBC 或 OLEDB 将 Oracle 数据提取到现有 Excel 文档中，而不是在 Oracle 服务器上创建 Excel 或 HTML 文件。缺点是，您应该小心用户权限。
回答by moleboy
I've had similar issues and eventually made a spreadsheet with some VBA code that queried and populated the spreadsheet for me. My task was to export a series of tables, each one on a different sheet, but any flag could be used to switch to a new sheet. Anyhow, let me know if you would like to see the code. Here is a chunk that might help you out. Just change the TableSQL string to whatever your select should be. Each record returned will be inserted as a row in the sheet. Then, based on whatever flag you decide, you can create and move to the next sheet. Please let me know if you need more information (as this particular example isn't EXACTLY what you are doing)
我遇到了类似的问题，最终用一些 VBA 代码制作了一个电子表格，为我查询和填充电子表格。我的任务是导出一系列表格，每个表格都在不同的工作表上，但可以使用任何标志来切换到新工作表。无论如何，如果您想查看代码，请告诉我。这是一个可以帮助你的大块。只需将 TableSQL 字符串更改为您选择的任何内容即可。返回的每条记录都将作为一行插入到工作表中。然后，根据您决定的任何标志，您可以创建并移动到下一个工作表。如果您需要更多信息，请告诉我（因为这个特定示例并不完全是您在做什么）
Private Sub getMyRows(inSchema As String, InTable As String)
Dim RS As Object
Dim TableSQL As String
Dim DataType As String
Dim DataLength As String
Dim DataPrecision As String
Dim DataScale As String
Dim ColCount As Integer
Dim WS As Worksheet
' create a sheet with the current table as name
Worksheets.Add().Name = InTable
Set RS = CreateObject("ADODB.recordset")
TableSQL = "Select * from " & inSchema & "." & InTable
' grab the data
RS.Open TableSQL, conn, adOpenStatic
For ColCount = 0 To RS.Fields.Count - 1
' set column headings to match table
ActiveSheet.Cells(1, ColCount + 1).Value = RS.Fields(ColCount).Name
Next
' copy table data to sheet
With Worksheets(InTable).Range("A2")
.CopyFromRecordset RS
End With
RS.Close
End Sub
回答by moleboy
The ExcelDocumentType is a great solution. It allows you to generate fully functional multi-sheet Excel documents with PL/SQL. You can find it here:
ExcelDocumentType 是一个很好的解决方案。它允许您使用 PL/SQL 生成功能齐全的多表 Excel 文档。你可以在这里找到它：
http://radio.weblogs.com/0137094/2006/10/26.html
http://radio.weblogs.com/0137094/2006/10/26.html
http://radio.weblogs.com/0137094/2009/01/02.html
http://radio.weblogs.com/0137094/2009/01/02.html
(Jason Bennett's Developer Corner)
（Jason Bennett 的开发者角）
回答by Andrew not the Saint
回答by gWay
I found this solution on the web (not invented by me), using SQL plus:
我在网上找到了这个解决方案（不是我发明的），使用 SQL plus：
set feed off markup html on spool on
spool c:\table1.xls
select * from table1;
spool off
set markup html off spool off
回答by ora_excel
There is another project ORA_EXCEL (www.oraexcel.com) that can produce Excel documents from Oracle database using only PL/SQL.
还有另一个项目 ORA_EXCEL ( www.oraexcel.com) 可以仅使用 PL/SQL 从 Oracle 数据库生成 Excel 文档。
回答by vbence
From: MSDN. If you want your worksheet to be portable and not to rely on System DSNs you can connect using a connection string like:
来自：MSDN。如果您希望您的工作表可移植并且不依赖于系统 DSN，您可以使用如下连接字符串进行连接：
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=<PASSWORD>;Persist Security Info=True;User ID=<USER ID>;Data Source=<DATABASE NAME>"
cnn.Open
... then use Moleboy's solution.
...然后使用Moleboy的解决方案。
回答by Migs Isip
Your problem seems similar to a thread here in SO (Writing in ExcelSheet using UTL_FILE package in Oracle.) and I was able to find a simple solution to this problem recently.
i used a package called as_xlsxcreated by Anton Scheffer, (Create an Excel-file with PL/SQL) and did a few modifications so it creates multiple sheets inside a single Excel Workbook by putting it through a Loop.
It also supports some basic formatting such as Cell Color, Font Style, Font Size, Borders, etc. Its pretty nifty.
(Create an Excel File (.xlsx) using PL/SQL).
您的问题似乎类似于 SO 中的一个线程（使用 Oracle 中的 UTL_FILE 包在 ExcelSheet 中编写。），我最近找到了解决此问题的简单方法。我使用了一个as_xlsx由 Anton Scheffer 创建的包（使用 PL/SQL 创建 Excel 文件）并进行了一些修改，因此它通过循环在单个 Excel 工作簿中创建多个工作表。它还支持一些基本格式，如单元格颜色、字体样式、字体大小、边框等。它非常漂亮。（使用 PL/SQL 创建 Excel 文件 (.xlsx)）。
Hope this helps!
希望这可以帮助！
回答by Migs Isip
There is a product called SQL*XL which allows you to run sql queries from within Excel, and the results appear within the worksheet (it can also update).
有一个名为 SQL*XL 的产品，它允许您从 Excel 中运行 sql 查询，结果显示在工作表中（它也可以更新）。
It is commercial, not free, but is only about 50, so not expensive. I use it quite a lot
它是商业的，不是免费的，但只有 50 左右，所以不贵。我经常使用它
回答by Matthew Farwell
You could save a (small) Excel sheet as html in Excel, and then reproduce that format.
您可以在 Excel 中将（小）Excel 工作表另存为 html，然后重现该格式。
