在Oracle中使用UTL_FILE包在ExcelSheet中编写 [英] Writing in ExcelSheet using UTL_FILE package in Oracle

问题描述

我将数据写入存储在某个预定义目录中的excel工作表中没有问题.

I have no problem in writing data in excel sheet which is stored in some predefined directory.

现在我有10组数据，每组数据必须创建10个单独的Excel工作表. 但是我要创建的工作簿连接sheet1，sheet2.工作表10.其中将拥有所有10套记录.如果我的问题不清楚，请告诉我.

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.

我正在使用PL/SQL Oracle 9i

I'm using PL/SQL Oracle 9i

我的代码，它将为一组数据写入excel.如果我有更多数据集并且不想拥有多个Excel工作表，而我想要一本带有差异工作表的工作簿，该怎么办?

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.

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;

推荐答案

我不知道可以使用的现成实现.

There's no ready-to-use implementation that I'm aware of.

自07年以来的Excel文件(.xslx)实际上是zip存档，其中包含每个工作表的单独xml文件.

Excel files (.xslx) since '07 are actually zip archives containing separate xml files for each worksheet.

他们使用的XML模式是非常简单.您必须使用Java创建文件夹并进行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.

这篇关于在Oracle中使用UTL_FILE包在ExcelSheet中编写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！