Forum Stats

  • 3,852,414 Users
  • 2,264,102 Discussions
  • 7,905,061 Comments

Discussions

Export to Excel – XLS file formatting

Przemysław Staniszewski
Przemysław Staniszewski Member Posts: 170
edited Nov 14, 2008 11:32AM in APEX Discussions
Hi,

I have created process that exports report to XLS file. I write XLS file using XML tags, supported by Excel, which allow me to format each cell (especially look and data type). Unfortunately I can’t force Excel to see cell as date-cell.
I’m getting from database date in YYYY-MM-DD format and I want the same in Excel. I can export it as string value, but I want Excel to see it as date – not string.

This code works fine:

<ss:Cell><ss:Data ss:Type="String">'||x.begin_date||'</ss:Data></ss:Cell>

but Excel see the cell as a string.
I want something like that:

<ss:Cell><ss:Data ss:Type="Date">'||x.prnm_short_name||'</ss:Data></ss:Cell>

but this one doesn’t work, as well as “DateTime” type.

Do you know how to solve this problem?

Regards,
Przemek

Edited by: Przemek on 2008-11-14 13:11
Tagged:

Answers

  • marife
    marife Member Posts: 1,312
    1) Create an Excel spreadsheet with the correct formatting you wish.
    2) Save the spreadsheet as an XML file.
    3) Look at the resulting XML file for the correct XML tags.
    4) Try duplicating those XML tags in your process.

    Mike
    marife
  • 404194
    404194 Member Posts: 46
    Here is a sample of how you can create a procedure from an EXCEL file.
    1. create, format and save Excel file as an XML file
    2. edit file to add" htp.p(' " tags to begining of each line and "')';" tags to the end of the lines
    3. add standard header tags at start of file (see code below)
    4. add standard end tags at end of file (see code below)
    5. grant excute privileges as required
    6. test procedure: from browser (http://.../apex/owner.excel_rpt in this case; you do not need to use apex to do this
    - you might want to secure the procedure once you got it running by limiting it to run only from your app



    CREATE OR REPLACE PROCEDURE excel_rpt (fname VARCHAR2 DEFAULT 'excel_rpt.xls')
    IS
    -- query to get data; can be used in CURSOR LOOOP to fill in report

    CURSOR rpt_cur IS
    SELECT PROGNAME, user_name, priv, dept
    FROM test_data;


    BEGIN
    -- standard header to open file in EXCEL application -- REQUIRED

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

    htp.p('Content-Disposition: attachment; filename="' || fname || '"');

    owa_util.http_header_close;

    -- to get this, save your Excel file as XML, open in editor (I used Notepad++),
    -- prepend "htp.p(' to all the line", postpend "');" to all lines
    --

    htp.p('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');

    htp.p('<?mso-application progid="Word.Document"?> ');

    htp.p('<w:wordDocument
    xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
    xmlns:v="urn:schemas-microsoft-com:vml"
    xmlns:w10="urn:schemas-microsoft-com:office:word"
    xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
    xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
    xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
    xmlns:st1="urn:schemas-microsoft-com:office:smarttags"
    w:macrosPresent="no"
    w:embeddedObjPresent="no"
    w:ocxPresent="no"
    xml:space="preserve">');
    htp.p('<w:ignoreElements w:val="http://schemas.microsoft.com/office/word/2003/wordml/sp2"/>');

    htp.p('<o:SmartTagType o:namespaceuri="urn:schemas-microsoft-com:office:smarttags" o:name="PlaceType"/>');
    -- :
    -- : deleted FOR brevity
    -- :
    -- :
    htp.p('</wx:sect>');
    htp.p('</w:body>');
    htp.p('</w:wordDocument>');

    -- standard closing tags -- REQUIRED

    HTMLDB_APPLICATION.g_unrecoverable_error := TRUE;

    END excel_rpt;
    /

    GRANT EXECUTE ON excel_rpt TO PUBLIC;
    404194
This discussion has been closed.