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

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

Advertisement