Writing in ExcelSheet using UTL_FILE package in Oracle

Question

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

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.

I'm using 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.

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;

Answer 1

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.

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.

Answer 2

You can do this with the open source PL/SQL ExcelDocumentType. (Although I haven't tried it on 9i.)