使用 Oracle 中的 UTL_FILE 包在 ExcelSheet 中写入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8414050/
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
Writing in ExcelSheet using UTL_FILE package in Oracle
提问by Avi
I have no problem in writing data in excel sheet which is stored in some predefined directory.
我在存储在某个预定义目录中的 Excel 表中写入数据没有问题。
Now I have 10 sets of data and for each set I have to create 10 seperate excel sheet. But what I want is to create Workbook conating sheet1,sheet2,. Sheet10. Which will have all 10 sets of record. If my question is not clear let me know.
现在我有 10 组数据，对于每组数据，我必须创建 10 个单独的 excel 表。但我想要的是创建工作簿 conating sheet1,sheet2,。表 10。其中将有所有10组记录。如果我的问题不清楚，请告诉我。
I'm using PL/SQL Oracle 9i
我正在使用 PL/SQL Oracle 9i
My Code which will write to excel for one set of data. What if I have more set of data and I don't want to have multiple excel sheet instead I want one workbook with diff sheet.
我的代码将为一组数据写入excel。如果我有更多数据集并且我不想拥有多个 Excel 表，而是想要一个带有差异表的工作簿，该怎么办。
CREATE OR REPLACE PROCEDURE SP_ORACLE_EXCEL(I_FILE_NAME IN VARCHAR2) AS
FILENAME UTL_FILE.FILE_TYPE;
FILENAME1 VARCHAR2(1000);
CURSOR C1 IS
SELECT * FROM MY_TABLE;
VARC1 C1%ROWTYPE;
BEGIN
FILENAME1 := 'TEST_' || I_FILE_NAME || '_' || SYSDATE || '.CSV';
FILENAME := UTL_FILE.FOPEN('TEMP_DIR', FILENAME1, 'W');
/* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
UTL_FILE.PUT_LINE(FILENAME,
'HEADER1' || ',' || 'HEADER2' || ',' || 'HEADER3' || ',' ||
'HEADER4' || ',' || 'HEADER5');
OPEN C1;
LOOP
FETCH C1
INTO VARC1;
EXIT WHEN C1%NOTFOUND;
/* THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
UTL_FILE.PUT_LINE(FILENAME,
'"' || VARC1.COL1 || '"' || ' ,' || '"' ||
VARC1.COL2 || '"' || ' ,' || '"' ||
VARC1.COL3 || '"' || ' ,' || '"' ||
VARC1.COL4 || '"' || ' ,' || '"' ||
VARC1.COL5|| '"');
END LOOP;
UTL_FILE.FCLOSE(FILENAME);
END SP_ORACLE_EXCEL;
回答by HAL 9000
There's no ready-to-use implementation that I'm aware of.
我知道没有现成的实现。
Excel files (.xslx) since '07 are actually zip archives containing separate xml files for each worksheet.
自 07 年以来的 Excel 文件 (.xslx) 实际上是包含每个工作表的单独 xml 文件的 zip 存档。
The XML schema they're using is pretty straight forward. You'd have to use Java to create the folders and do the zip compression in order to write such files.
他们使用的 XML 模式非常简单。您必须使用 Java 来创建文件夹并进行 zip 压缩才能写入此类文件。
回答by Jon Heller
You can do this with the open source PL/SQL ExcelDocumentType. (Although I haven't tried it on 9i.)
您可以使用开源PL/SQL ExcelDocumentType执行此操作。（虽然我没有在 9i 上试过。）
回答by Migs Isip
I was able to find a solution to this problem recently posted here on SO (Create an Excel File (.xlsx) using PL/SQL).
我能够找到最近在 SO 上发布的此问题的解决方案（使用 PL/SQL 创建 Excel 文件 (.xlsx)）。
i used a package called as_xlsxcreated by Anton Scheffer, Create an Excel-file with PL/SQLand did a few modifications to the package
so it creates multiple sheets inside a single Excel Workbook by putting it through a Loop.
我使用了一个名为as_xlsxAnton Scheffer创建的包，使用 PL/SQL 创建一个 Excel 文件并对包进行了一些修改，因此它通过循环在单个 Excel 工作簿中创建多个工作表。
Hope this helps!
希望这可以帮助！
