Akdora’s Blog

Programming, Oracle, Life, Fun

How to write,create Excel files via PL/SQL and save the file to a directory? February 6, 2009

In this post, I will write a PL/SQL package that helps us to write a EXCEL file in XML format. Then, we will save it some directory. This is a basic way to create an Excel file. There is no formatting type in the cells. We will just write something in the cells. I will try to develop this package with the formatting commands. Anyway, before we write our package, we shoul know about something oracle’s directory policy. We should tell to ORACLE, I have a directory path and I will name it. I mean, we will use create directory command of oracle.

 CREATE OR REPLACE DIRECTORY MY_ORACLE_DIR AS 'C:\oracle_test\';

To run this command, we have the following privileges;

 GRANT CREATE ANY DIRECTORY TO HR; >>>>>HR is the schema name!

On the other hand, you created an directory in another schema. You should grant  the directory to your shema.

GRANT read, write on DIRECTORY MY_ORACLE_DIR to HR;

After defining the directory to oracle, we can write our package. I passed a variable referencing the XML body of the file as CLOB in the procedures.

CREATE OR REPLACE PACKAGE pkg_excel_export IS
/**
 *  @author  : Özay AKDORA
 *  @version : 1.0
 *
 *  Name of the Application         :  pkg_excel_export.sql
 *  Creation/Modification History   :  5-Jan-2009
 *
 *  Overview of  Package/Sample     :Create Excel files via PL/SQL
 *           write the file to a directory
 * 
 **/
     PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE worksheet_open
     (
          l_xml_body      IN OUT NOCOPY CLOB,
          p_worksheetname IN VARCHAR2
     );
     PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE cell_write
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_content  IN VARCHAR2
     );
     PROCEDURE excel_get
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_filename IN VARCHAR2
     );
     PROCEDURE prc_write_file
     (
          p_filename IN VARCHAR2,
          p_dir      IN VARCHAR2,
          p_clob     IN CLOB
     );
END pkg_excel_export;
/

CREATE OR REPLACE PACKAGE BODY pkg_excel_export IS

  /**
  *  Opens the excel file
  * 
  **/
  PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB) IS
     BEGIN

          l_xml_body := '<?xml version="1.0" encoding="ISO-8859-9"?>' || chr(10) ||
                        '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"' ||
                        chr(10) ||
                        'xmlns:o="urn:schemas-microsoft-com:office:office"' ||
                        chr(10) ||
                        'xmlns:x="urn:schemas-microsoft-com:office:excel"' ||
                        chr(10) ||
                        'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' ||
                        chr(10) ||
                        'xmlns:html="http://www.w3.org/TR/REC-html40">' ||
                        chr(10) ||
                        '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">' ||
                        chr(10) || '<WindowHeight>8580</WindowHeight>' ||
                        chr(10) || '<WindowWidth>15180</WindowWidth>' || chr(10) ||
                        '<WindowTopX>120</WindowTopX>' || chr(10) ||
                        '<WindowTopY>45</WindowTopY>' || chr(10) ||
                        '<ProtectStructure>False</ProtectStructure>' || chr(10) ||
                        '<ProtectWindows>False</ProtectWindows>' || chr(10) ||
                        '</ExcelWorkbook>' || chr(10) || '<Styles>' || chr(10) ||
                        '<Style ss:ID="Default" ss:Name="Normal">' || chr(10) ||
                        '<Alignment ss:Vertical="Bottom"/>' || chr(10) ||
                        '<Borders/>' || chr(10) || '<Font/>' || chr(10) ||
                        '<Interior/>' || chr(10) || '<NumberFormat/>' || chr(10) ||
                        '<Protection/>' || chr(10) || '</Style>' || chr(10) ||
                        '<Style ss:ID="s22">' || chr(10) ||
                        '<Font x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/>' ||
                        chr(10) || '</Style>' || chr(10) || '</Styles>';
     END excel_open;
  /**
  *  Closes the excel file
  * 
  **/
     PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB) IS
     BEGIN
          l_xml_body := l_xml_body || '</Workbook>';
     END excel_close;
  /**
  *  Opens a worksheet in the Excel file.
  *  You may open multiple worksheets.
  **/
     PROCEDURE worksheet_open
     (
          l_xml_body      IN OUT NOCOPY CLOB,
          p_worksheetname IN VARCHAR2
     ) IS
     BEGIN
          --
          -- Create the  worksheet
          --
          l_xml_body := l_xml_body || '<Worksheet ss:Name="' || p_worksheetname ||
                        '"><Table>';
     END worksheet_open;
  /**
  *  Closes the worksheet in the Excel file.
  * 
  **/
     PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB) IS
     BEGIN
          l_xml_body := l_xml_body || '</Table></Worksheet>';
     END worksheet_close;
  /**
  *  Opens the row tag
  * 
  **/
     PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB) IS
     BEGIN
          l_xml_body := l_xml_body || '<Row>';
     END row_open;
  /**
  *  Closes the row tag
  * 
  **/
     PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB) IS
     BEGIN
          l_xml_body := l_xml_body || '</Row>' || chr(10);
     END row_close;
  /**
  *  After opening the row, we can write something the first cell
  *  If you want it blank, write ''
  **/
     PROCEDURE cell_write
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_content  IN VARCHAR2
     ) IS
     BEGIN
          l_xml_body := l_xml_body || '<Cell><Data ss:Type="String"> ' ||
                        p_content || ' </Data></Cell>';
     END cell_write;
  /**
  *  If you are using this package from APEX, you get download the excel file.
  * 
  **/
     PROCEDURE excel_get
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_filename IN VARCHAR2
     ) IS
          xx BLOB;
          do NUMBER;
          so NUMBER;
          bc NUMBER;
          lc NUMBER;
          w  NUMBER;
     BEGIN

          dbms_lob.createtemporary(xx, TRUE);
          do := 1;
          so := 1;
          bc := dbms_lob.default_csid;
          lc := dbms_lob.default_lang_ctx;
          w  := dbms_lob.no_warning;

          dbms_lob.converttoblob(xx,
                                 l_xml_body,
                                 dbms_lob.lobmaxsize,
                                 do,
                                 so,
                                 bc,
                                 lc,
                                 w);

          owa_util.mime_header('application/octet', FALSE);

          -- set the size so the browser knows how much to download
          htp.p('Content-length: ' || dbms_lob.getlength(xx));
          -- the filename will be used by the browser if the users does a save as
          htp.p('Content-Disposition:  attachment; filename="' || p_filename ||
                '.xml' || '"');
          -- close the headers
          owa_util.http_header_close;
          -- download the BLOB
          wpg_docload.download_file(xx);
     END excel_get;
  /**
  *  Writes the Excel file to some directory with a name.
  *  This procedure writes the CLOB data to file
  * 
  **/
     PROCEDURE prc_write_file
     (
          p_filename IN VARCHAR2,
          p_dir      IN VARCHAR2,
          p_clob     IN CLOB
     ) IS

          c_amount CONSTANT BINARY_INTEGER := 32767;
          l_buffer   VARCHAR2(32767);
          l_chr10    PLS_INTEGER;
          l_cloblen  PLS_INTEGER;
          l_fhandler utl_file.file_type;
          l_pos      PLS_INTEGER := 1;

     BEGIN

          l_cloblen  := dbms_lob.getlength(p_clob);
          l_fhandler := utl_file.fopen(p_dir, p_filename, 'W', c_amount);

          WHILE l_pos < l_cloblen
          LOOP
               l_buffer := dbms_lob.substr(p_clob, c_amount, l_pos);
               EXIT WHEN l_buffer IS NULL;
               l_chr10 := instr(l_buffer, chr(10), -1);
               IF l_chr10 != 0
               THEN
                    l_buffer := substr(l_buffer, 1, l_chr10 - 1);
               END IF;
               utl_file.put_line(l_fhandler, l_buffer, TRUE);
               l_pos := l_pos + least(length(l_buffer) + 1, c_amount);
          END LOOP;

          utl_file.fclose(l_fhandler);

     EXCEPTION
          --WE SHOULD HANDLE THE FILE EXCEPTIONS HERE!!!!!
          WHEN OTHERS THEN
               IF utl_file.is_open(l_fhandler)
               THEN
                    utl_file.fclose(l_fhandler);
               END IF;
               RAISE;   
     END;
END pkg_excel_export;
/

This is an example to use the the package;

 
DECLARE
     myexcelcontent CLOB;
BEGIN
     -- Test statements here

     --open the file
     pkg_excel_export.excel_open(myexcelcontent);
     --open a worksheet
     pkg_excel_export.worksheet_open(myexcelcontent, 'test');
     --open the row
     pkg_excel_export.row_open(myexcelcontent);
     pkg_excel_export.cell_write(myexcelcontent, 'My First Cell');
     pkg_excel_export.cell_write(myexcelcontent, 'My Second Cell');
     pkg_excel_export.row_close(myexcelcontent);
     --open the row
     pkg_excel_export.row_open(myexcelcontent);
     pkg_excel_export.cell_write(myexcelcontent,
                                 'My First Cell in the Second Row');
     pkg_excel_export.row_close(myexcelcontent);
     --close the worksheet
     pkg_excel_export.worksheet_close(myexcelcontent);
     --close the file
     pkg_excel_export.excel_close(myexcelcontent);
     --write the file somewhere
     pkg_excel_export.prc_write_file(p_filename => 'my_first_excel',
                                     p_dir      => 'MY_ORACLE_DIR',
                                     p_clob     => myexcelcontent);
     dbms_output.put_line(substr(myexcelcontent, 1, 10000));
END;
/

I hope it will useful for you.

References

[1] http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11070
[2] http://eiunkar.spaces.live.com/
 
 

For Monica;

you cannot use html tags for this example. To make data bold, you have to define Excel ss:Styles into the beginning of your xml. Then you can call the styles from the cells.

Define the Style s62 in prosedure excel_open to the l_xml_body variable:

 <Style ss:ID="s62">
 <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
 <Borders>
 <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
 <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
 <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
 <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
 </Borders>
 <Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#FFFFFF" ss:Bold="1"/>
 <Interior ss:Color="#0D0D0D" ss:Pattern="Solid"/>
 </Style>

Then call it from the cell :

'<Cell ss:StyleID="s62"><Data ss:Type="' || P_TYPE || '">' || V_CONTENT || '</Data></Cell>';
You may check the excel xml formatting :
http://msdn.microsoft.com/en-us/library/office/aa140062(v=office.10).aspx

About these ads
 

25 Responses to “How to write,create Excel files via PL/SQL and save the file to a directory?”

  1. Rafal Says:

    Özay,

    you’ve opened my eyeas with this solution. That’s what I was looking for. Thanks very much!

    Cheers!
    Rafal

  2. moleboy Says:

    This rocks! I have a VBA script that I use to load entire schemas into a workbook, one table per worksheet.
    I can probably use this as the basis for moving that process to the database. Just need to figure out how to add sheets to the workbook, and then switch to that sheet.
    Thanks!

  3. Sandeep Says:

    Solution is great, thanks for that. But I am getting ORA-29285 when trying to write more than 65 rows in excel file, can you please help me on that.

    Thanks in advance.

  4. I actually designed and published (for free) a user defined type called ExcelDocumentType in 2006 that will let you generate fully functional Excel XML documents from PL/SQL. You can create mult-sheet documents, styles, summaries, etc …

    I also created a utility API for creating ‘quick’ reports.

    Here is the links:

    http://radio.weblogs.com/0137094/2006/10/26.html

    http://radio.weblogs.com/0137094/2009/01/02.html

    • Ildiko Says:

      Hello,
      I liked your solution so I’ve tried it out, and the excel file generating version works fine, but running your other demo procedure (employeeReport) produced an error:

      ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at “SYS.OWA_UTIL”, line 354
      ORA-06512: at “SYS.HTP”, line 1367
      ORA-06512: at “SYS.HTP”, line 1442
      ORA-06512: at “SYS.OWA_UTIL”, line 410
      ORA-06512: at “HR.EXCELDOCUMENTTYPE”, line 865
      ORA-06512: at “HR.EMPLOYEEREPORT”, line 50

      the problem is with this call
      cversion := owa_util.get_cgi_env (‘GATEWAY_IVERSION’);

      (for i in 1..owa.num_cgi_vars)

      Do you have any workaround or suggestions or any idea about this one?
      Did it happen to others, perhaps?

      Thank you in advance for yr answer
      Best regards, Ildiko.

  5. muthukumar Says:

    Many thanks!

    How we have password protected excel file, is there any other tags?

    Help me please.

    Thanks and regards,
    Muthu

  6. pradeep Says:

    Hi..
    first of all thx for the work.
    but its not wrkn for oracle 9i as there is no dbms_lob.converttoblob.

    Can you pls help me??

    Thanks,

  7. [...] PL/SQL recently, I also decided to share my Excel API code in PL/SQL. I wrote a similar article before .  This API has following [...]

  8. GCR Says:

    Thanks for the simple but elegant solution to the problem of creating Excel files via PL/SQL.
    I have a question. How could I write the contents of any particular cell in bold font. Thanks.

    • Akdora Says:

      Hi,
      You have to set your Stlyle tag as following;

      Style ss:ID=”s23″
      Font ss:Size=”12″ ss:Bold=”1″
      /Style

      • GCR Says:

        Worked perfectly. Thanks again.
        One more question. Numeric values from the database are showing up as text when the file is opened in Excel.
        What needs to be done so that they show up as numbers?
        Thanks for your time.

      • Akdora Says:

        Sorry for the late response;
        It’is the same logic. For numbers, you have to specify format :

        Style ss:ID=”s65″
        NumberFormat ss:Format=”0.000000″/
        /Style

        Style ss:ID=”s66″
        NumberFormat ss:Format=”0″/
        /Style

  9. ganesh Says:

    hi,
    This Procedure is not working in oracle 9i, converttoblob must be declared, can u pls tell me how to use the same in oracle 9i

  10. ganesh Says:

    same function is not working in oracle 9i Pls help

  11. ganesh Says:

    liitle bit urgent pls help

  12. metal Says:

    Great package! I have been playing with same problems and result is ORA_EXCEL package which produces Excel xlsx documents using only PL/SQL. If someone is interested or have comments you are welcome to try my package.

  13. Sanjay Says:

    What is Open Excel must be declared in Oracle

  14. Andres Says:

    Özay

    Excellent code … thanks a lot !

  15. Karthik Says:

    Hi thanks for the code its works fine.

    But i have issue: When I try to write more than 10000 lines, am not able to open the excel file. am getting the below error:

    XML PARSE ERROR: Malformed or illegal tag name
    Error occurs at or below this element stack:

    Please help me on this…

  16. Monika Says:

    Hi, This solution is great for creating excel sheets. But I am facing the following issue:
    One of the excel columns have html tags like etc. and I want to render those html tags in the data. I mean should show the column data in bold. Is it possible to add html data in the excel column? Please let me know if you have any solution for this. This is a critical issue for 1 of our applications.

    Please reply.
    Thanks in advance.

    Regards,
    Monika

  17. AJ Says:

    Will the solution provided here to create an Excel file create a .xml file or .xls file in the directory? If it creates an .xlm file, how can one create or convert it into an .xls file in the directory?

    • Akdora Says:

      I do not save the file content as “.xml” file. I save it as “.xls” file. However the file content is in xml format. You can open excel files like this.
      Moreover, after openning an excel file, you may save it as xml format. You can try it.

  18. Tony Says:

    I have used this with a small modification for performance, all concatanations || replaced with dbms_lob.writeappend improves performance 200x, also added nocopy to last procedure


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 27 other followers