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
– Florin Ghita, Commented Dec 7, 2011 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
– Florin Ghita, Commented Dec 7, 2011 at 13:46

Sathyajith Bhat · Accepted Answer · 2011-12-16 13:02:59Z

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, 2011 at 13:02

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

answered Dec 7, 2011 at 15:23

HAL 9000

3,9951 gold badge25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Corwin01 Over a year ago

The link you posted no longer works. This link to MSDN should be the same thing though.

Jon Heller · Accepted Answer · 2015-09-25 15:18:46Z

3

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

edited Sep 25, 2015 at 15:18

answered Jan 16, 2012 at 7:41

Jon Heller

36.9k6 gold badges84 silver badges150 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:33:54Z

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, 2017 at 12:33

CommunityBot

11 silver badge

answered Dec 28, 2016 at 5:50

Migs

1,5006 gold badges29 silver badges58 bronze badges

Collectives™ on Stack Overflow

Writing in ExcelSheet using UTL_FILE package in Oracle

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related