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;

how do you write xls files with UTL_FILE? Can you share with us? — Florin Ghita, Dec 07 '11 at 12:58
OK, that is a csv file, not xls. It is a flat text file, that MS Excel knows to read. in csv, obvious, you can't have sheets. xls is more complicated. — Florin Ghita, Dec 07 '11 at 13:46

score 6 · Accepted Answer · edited Dec 16 '11 at 13:02

6

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.

edited Dec 16 '11 at 13:02

Sathyajith Bhat

21,321
22
95
134

answered Dec 07 '11 at 15:23

HAL 9000

3,877
1
23
29

The link you posted no longer works. This [link](https://msdn.microsoft.com/en-us/library/dd979954(v=office.12).aspx) to MSDN should be the same thing though. – Corwin01 Apr 01 '15 at 15:59

Jon Heller · Answer 2 · 2015-09-25T15:18:46.373

3

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

edited Sep 25 '15 at 15:18

answered Jan 16 '12 at 07:41

Jon Heller

34,999
6
74
132

score 0 · Answer 3 · edited May 23 '17 at 12:33

0

I was able to find a solution to this problem recently posted here on SO (Create an Excel File (.xlsx) using PL/SQL).

i used a package called as_xlsx created by Anton Scheffer, Create an Excel-file with PL/SQL and did a few modifications to the package so it creates multiple sheets inside a single Excel Workbook by putting it through a Loop.

Hope this helps!

edited May 23 '17 at 12:33

Community

1
1

answered Dec 28 '16 at 05:50

Migs Isip

1,450
3
23
50

Writing in ExcelSheet using UTL_FILE package in Oracle

3 Answers3

Linked

Related