ABAP Development
Page tree
Skip to end of metadata
  • Created by Unknown User (1021n2l3v), last modified by Former Member on Oct 10, 2013
Go to start of metadata

You can also use the abap2xlsx tool described in SDN blog abap2xlsx - Generate your professional Excel spreadsheet from ABAP, by Ivan Femia, which generates an XLSX file (available from Excel 2007). It is much more simple to use.

Exporting data to Excel - XML to the rescue

There is often a requierment to export report data to a Microsoft Excel spreadsheet.  If the report is a simple ALV grid then the ALV control can do it automatically, but otherwise ABAP coding is required.

The simplest solution is to download a tab separated file and then open this in Excel.  This is fine for simple data, but has lots of problems:

  • no formatting (headings, colours, column widths etc)
  • dates are YYYYMMDD, so are not shown as a date in Excel
  • Only one worksheet can be exported
  • leading zeros are lost
  • numeric fields of more than about 15 digits are truncated

There have traditionally been a few ways around this problem

  • Use SAP function modules to create an XLS file (no customisation possible)
  • Use OLE to start Excel and manipulate the data cell by cell (difficult, not much help, presentation server only)

However, Excel can now read and write xml files.  An ABAP program can create an xml file and it can be opened directly into Excel.  The advantages and possibilities are endless:

  • An xml file is plain text so can be created on the presentation server or application server;
  • alternatively, it can be sent as an attachment to an email, or can be provided as a parameter to a web service;
  • colours, fonts, number formats and so on can be specified;
  • formatting can be applied to rows, columns or single cells;
  • column heights and widths can be specified, or auto-formatted;
  • multiple worksheets can be created;
  • formulas can be entered in cells;

How to proceed

View and analyse xml file created by Excel

Create a new Excel workbook and enter the data you would like to see.  Add formatting, headings, sheet names etc, and some data. 

Save as an xml file.

Open the xml file in a text editor (eg Notepad).

It should look something like this:

Manual coding

Possibly the simplest way to create the XML is to create an internal table and fill it with the xml tags and data.

This code is from the ABAP forum, by Rudolf Lindenmann

Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
REPORT Z_EXCEL_BATCH.
\* Definitions
TABLES USR02.
TYPES XMLLINE(1024) TYPE C.
DATA IT_XML TYPE XMLLINE OCCURS 0.
DATA WA_XML TYPE XMLLINE.
DATA FNAME(60) TYPE C VALUE '/ft/D02/001/test.xls'.
\* Build-up of xml in internal table
\*\* Opening tags
APPEND '<?xml version="1.0"?>' TO IT_XML.
APPEND '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'
TO IT_XML.
APPEND 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
TO IT_XML.
APPEND ' <Worksheet ss:Name="Tabelle1">' TO IT_XML.
APPEND ' <Table>' TO IT_XML.
\*\* The actual data from table USR02
SELECT * FROM USR02.
APPEND ' <Row>' TO IT_XML.
CONCATENATE ' <Cell><Data ss:Type="String">'
USR02-BNAME '</Data></Cell>' INTO WA_XML.
APPEND WA_XML TO IT_XML.
CONCATENATE ' <Cell><Data ss:Type="String">'
USR02-TRDAT '</Data></Cell>' INTO WA_XML.
APPEND WA_XML TO IT_XML.
APPEND ' </Row>' TO IT_XML.
ENDSELECT.
\**Closing tags
APPEND ' </Table>' TO IT_XML.
APPEND ' </Worksheet>' TO IT_XML.
APPEND '</Workbook>' TO IT_XML.
\* Display of internal table on screen
LOOP AT IT_XML INTO WA_XML.
WRITE / WA_XML.
ENDLOOP.

Coding using the xml DOM concept

SAP provides an interface and classes for building up an xml document. This ensures that the xml document is well formed, ie does not contain any syntax errors. There is some overhead in the coding, so it is probably overkill for a really simple export, but it is a much better alternative for anything complex.
There are a number of blogs on creating an xml document using DOM.

Sample code for a report to download any database table to a file on the presentation server.
Column headings are provided from the data dictionary, formatted bold with yellow background, wordwrap.
Numeric fields are formatted with the number of decimal places specified in the data dictionary.
Text fields are formatted as Text rather than General

Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
REPORT  z_excel_xml_dom_create.TYPE-POOLS: ixml.TYPES: BEGIN OF xml_line,
        data(256) TYPE x,
       END OF xml_line.DATA: l_ixml            TYPE REF TO if_ixml,
      l_streamfactory   TYPE REF TO if_ixml_stream_factory,
      l_ostream         TYPE REF TO if_ixml_ostream,
      l_renderer        TYPE REF TO if_ixml_renderer,
      l_document        TYPE REF TO if_ixml_document.DATA:
      l_element_root    TYPE REF TO if_ixml_element,
      ns_attribute      type ref to if_ixml_attribute,
      r_element_properties  TYPE REF TO if_ixml_element,
      r_element         TYPE REF TO if_ixml_element,
      r_worksheet       TYPE REF TO if_ixml_element,
      r_table           TYPE REF TO if_ixml_element,
      r_column          TYPE REF TO if_ixml_element,
      r_row             TYPE REF TO if_ixml_element,
      r_cell            TYPE REF TO if_ixml_element,
      r_data            TYPE REF TO if_ixml_element,
      l_value           TYPE string,
      l_type            type string,
      l_text(100)       type c,
      r_styles          TYPE REF TO if_ixml_element,
      r_style           TYPE REF TO if_ixml_element,
      r_format          TYPE REF TO if_ixml_element,
      num_rows          type i.DATA: l_xml_table       TYPE TABLE OF xml_line,
      l_xml_size        TYPE i,
      l_rc              TYPE i.DATA: lt_spfli          TYPE TABLE OF spfli.
DATA: l_spfli           TYPE spfli.
PARAMETERS:
  p_tab     LIKE dd02l-tabname     DEFAULT 'sflight'.data:
    data_tab        TYPE REF TO data,
    gotstate        TYPE  ddgotstate,
    dd03p_tab       TYPE TABLE OF dd03p,
    dd03p           TYPE dd03p.field-symbols:
  <data_tab>       TYPE STANDARD TABLE,
  <data_line>      TYPE ANY,
  <field>          TYPE ANY.START-OF-SELECTION.
  CALL FUNCTION 'DDIF_TABL_GET'
    EXPORTING
      name                = p_tab
*     STATE               = 'A'
      langu               = sy-langu
    IMPORTING
      gotstate            = gotstate
    TABLES
      dd03p_tab           = dd03p_tab
    EXCEPTIONS
      illegal_input       = 1
      OTHERS              = 2
            .
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.  ELSEIF gotstate IS INITIAL.
    WRITE: / 'Table', p_tab, 'does not exist'.
    EXIT.
  ENDIF.  CREATE DATA data_tab  TYPE STANDARD TABLE OF (p_tab).
  ASSIGN data_tab->* TO <data_tab>.  SELECT * FROM (p_tab) INTO TABLE <data_tab> up to 10 rows.
* Creating a ixml factory
  l_ixml = cl_ixml=>create( ).
* Creating the dom object model
  l_document = l_ixml->create_document( ).
* Create root node 'Workbook'
  l_element_root  = l_document->create_simple_element(
              name = 'Workbook'
*              uri  = 'urn:schemas-microsoft-com:office:spreadsheet'
              parent = l_document ).  l_element_root->set_attribute(
                    name = 'xmlns'
                    value = 'urn:schemas-microsoft-com:office:spreadsheet' ).
  ns_attribute = l_document->create_namespace_decl(
              name = 'ss'
              prefix = 'xmlns'
              uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
  l_element_root->set_attribute_node( ns_attribute ).  ns_attribute = l_document->create_namespace_decl(
              name = 'x'
              prefix = 'xmlns'
              uri = 'urn:schemas-microsoft-com:office:excel' ).
  l_element_root->set_attribute_node( ns_attribute ).
* Create node for document properties.
  r_element_properties = l_document->create_simple_element(
              name = 'DocumentProperties'
              parent = l_element_root ).  l_value = sy-uname.
  l_document->create_simple_element(
                    name = 'Author'
                    value = l_value
                    parent = r_element_properties  ).
*  Styles
* <Styles>
*  <Style ss:ID="Default" ss:Name="Normal">
*   <Alignment ss:Vertical="Bottom"/>
*   <Borders/>
*   <Font/>
*   <Interior/>
*   <NumberFormat/>
*   <Protection/>
*  </Style>
*  <Style ss:ID="s21">
*   <NumberFormat ss:Format="@"/>
*  </Style>
*  <Style ss:ID="s22">
*    <NumberFormat ss:Format="Fixed"/>
*  <Style ss:ID="s27">
*   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
*   <Font x:Family="Swiss" ss:Bold="1"/>
*   <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
*   <NumberFormat ss:Format="@"/>
*  </Style>  r_styles = l_document->create_simple_element(
                    name = 'Styles'
                    parent = l_element_root  ).* Header row - Yellow, Bold
  r_style  = l_document->create_simple_element(
                    name = 'Style'
                    parent = r_styles  ).
  r_style->set_attribute_ns(
                    name = 'ID'
                    PREFIX = 'ss'
                    value = 'Header' ).
  r_format  = l_document->create_simple_element(
                    name = 'Font'
                    parent = r_style  ).
  r_format->set_attribute_ns(
                    name = 'Bold'
                    PREFIX = 'ss'
                    value = '1' ).
  r_format  = l_document->create_simple_element(
                    name = 'Interior'
                    parent = r_style  ).
  r_format->set_attribute_ns(
                    name = 'Color'
                    PREFIX = 'ss'
                    value = '#FFFF00' ).
  r_format->set_attribute_ns(
                    name = 'Pattern'
                    PREFIX = 'ss'
                    value = 'Solid' ).
  r_format  = l_document->create_simple_element(
                    name = 'Alignment'
                    parent = r_style  ).
  r_format->set_attribute_ns(
                    name = 'Vertical'
                    PREFIX = 'ss'
                    value = 'Bottom' ).
  r_format->set_attribute_ns(
                    name = 'WrapText'
                    PREFIX = 'ss'
                    value = '1' ).  LOOP AT dd03p_tab INTO dd03p WHERE fieldname <> 'MANDT'.
    case dd03p-inttype.
      when 'I' or 'N'.
*       General format
      when 'P' or 'F'.
*       Numeric with specific number of decimals
        r_style  = l_document->create_simple_element(
                          name = 'Style'
                          parent = r_styles  ).
        l_value = dd03p-fieldname.
        r_style->set_attribute_ns(
                          name = 'ID'
                          PREFIX = 'ss'
                          value = l_value ).
        r_format  = l_document->create_simple_element(
                          name = 'NumberFormat'
                          parent = r_style  ).        if dd03p-DECIMALS > 0.
          l_value = '0.'.
          do dd03p-DECIMALS times.
            concatenate l_value '0' into l_value.
          enddo.
        else.
          l_value = ''.
        endif.
        r_format->set_attribute_ns(
                          name = 'Format'
                          PREFIX = 'ss'
                          value = l_value ).
      when others.
*       Fixed text
        r_style  = l_document->create_simple_element(
                          name = 'Style'
                          parent = r_styles  ).
        l_value = dd03p-fieldname.
        r_style->set_attribute_ns(
                          name = 'ID'
                          PREFIX = 'ss'
                          value = l_value ).
        r_format  = l_document->create_simple_element(
                          name = 'NumberFormat'
                          parent = r_style  ).
        l_value = '@'.
        r_format->set_attribute_ns(
                          name = 'Format'
                          PREFIX = 'ss'
                          value = l_value ).
    endcase.
  endloop.
*  Worksheet
*  <Worksheet ss:Name="Sheet1">
  r_worksheet = l_document->create_simple_element(
              name = 'Worksheet '
              parent = l_element_root ).  r_worksheet->set_attribute_ns(
                    name = 'Name'
                    PREFIX = 'ss'
                    value = 'Sheet1' ).* Table
* <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
  r_table = l_document->create_simple_element(
              name = 'Table'
              parent = r_worksheet ).  describe table dd03p_tab lines num_rows.
*  l_value = num_rows - 1.
*  r_table->set_attribute_ns(
*                    name = 'ExpandedColumnCount'
*                    PREFIX = 'ss'
*                    value = l_value ).
*
*  describe table <data_tab> lines num_rows.
*  l_value = num_rows." + 1.
*  r_table->set_attribute_ns(
*                    name = 'ExpandedRowCount'
*                    PREFIX = 'ss'
*                    value = l_value ).  r_table->set_attribute_ns(
                    name = 'FullColumns'
                    PREFIX = 'x'
                    value = '1' ).
  r_table->set_attribute_ns(
                    name = 'FullRows'
                    PREFIX = 'x'
                    value = '1' ).
** Column formatting
  LOOP AT dd03p_tab INTO dd03p WHERE fieldname <> 'MANDT'.*   Columnn
*   <Column>
    r_column = l_document->create_simple_element(
              name = 'Column'
              parent = r_table ).
    case dd03p-inttype.
      when 'I' or 'N'.
*       General format
      when 'P' or 'F'.
        l_value = dd03p-fieldname.
        r_column->set_attribute_ns(
                          name = 'StyleID'
                          PREFIX = 'ss'
                          value = l_value ).
    endcase.
    l_value = ( dd03p-outputlen + 5 ) * 5.
    condense l_value no-gaps.
    r_column->set_attribute_ns(
                      name = 'Width'
                      PREFIX = 'ss'
                      value = l_value ).
*    r_column->set_attribute_ns(
*                      name = 'AutoFitWidth'
*                      PREFIX = 'ss'
*                      value = '1' ).
*
  endloop.* Column headers
*   Row
*   <Row>
  r_row = l_document->create_simple_element(
            name = 'Row'
            parent = r_table ).
  r_row->set_attribute_ns(
                    name = 'StyleID'
                    PREFIX = 'ss'
                    value = 'Header' ).
  r_row->set_attribute_ns(
                    name = 'AutoFitHeight'
                    PREFIX = 'ss'
                    value = '1' ).
*  r_row->set_attribute_ns(
*                    name = 'Height'
*                    PREFIX = 'ss'
*                    value = '40' ).  LOOP AT dd03p_tab INTO dd03p WHERE fieldname <> 'MANDT'.
*     <Data>
    r_Cell = l_document->create_simple_element(
              name = 'Cell'
              parent = r_row ).
    l_value = dd03p-ddtext.    "fieldname, scrtext_m etc.
    r_Data = l_document->create_simple_element(
              name = 'Data'
              value = l_value
              parent = r_cell ).
    r_data->set_attribute_ns(
                      name = 'Type'
                      PREFIX = 'ss'
                      value = 'String' ).  endloop.* Finally loop at the data table and output the fields
  LOOP AT <data_tab> ASSIGNING <data_line>.*   Row
*   <Row>
    r_row = l_document->create_simple_element(
              name = 'Row'
              parent = r_table ).    LOOP AT dd03p_tab INTO dd03p WHERE fieldname <> 'MANDT'.
      ASSIGN COMPONENT dd03p-fieldname OF STRUCTURE <data_line> TO <field>.
      CHECK sy-subrc IS INITIAL.
*     <Cell>
      r_Cell = l_document->create_simple_element(
                name = 'Cell'
                parent = r_row ).      case dd03p-inttype.
        when 'I' or 'P' or 'F' or 'N'.
          l_type = 'Number'.
          l_value = <field>.
          condense l_value no-gaps.
        when 'D' or 'T'.
          l_type = 'String'.
          write <field> to l_text.
          l_value = l_text.
        when others.
*          l_value = <field>.    "Without conversion exit
          write <field> to l_text.
          shift l_text left deleting leading space.
          l_value = l_text.
          l_type = 'String'.
      endcase.*     <Data>      r_Data = l_document->create_simple_element(
                name = 'Data'
                value = l_value
                parent = r_cell ).
*     Cell format
      r_data->set_attribute_ns(
                        name = 'Type'
                        PREFIX = 'ss'
                        value = l_type ).    ENDLOOP.
  endloop.*   Creating a stream factory
    l_streamfactory = l_ixml->create_stream_factory( ).
*   Connect internal XML table to stream factory
    l_ostream = l_streamfactory->create_ostream_itable( table = l_xml_table ).*   Rendering the document
    l_renderer = l_ixml->create_renderer( ostream  = l_ostream
                                          document = l_document ).
    l_rc = l_renderer->render( ).*   Saving the XML document
    l_xml_size = l_ostream->get_num_written_raw( ).    CALL METHOD cl_gui_frontend_services=>gui_download
      EXPORTING
        bin_filesize = l_xml_size
        filename     = 'h:\xml\flights.xml'
        filetype     = 'BIN'
      CHANGING
        data_tab     = l_xml_table
      EXCEPTIONS
        OTHERS       = 24.
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

back to top

Coding using transformations

With ECC5 and later, xml can be created using Transformations. The transformation can be either coded with an ABAP-like syntax, or an XSLT style sheet.
For more information, look the F1 help on CALL TRANSFORMATION

Simple transformations

Export the file

Last but not least, the file needs to be exported. Options include:
Download to presentation server using GUI_DOWNLOAD
Download to application server using OPEN DATASET etc
Send as an attachment to an email
Have the xml table/string as a parameter on a RFC or web service.

back to top

10 Comments

  1. Unknown User (opqkq6t)

    Thank you, your article is a very helpful base to solve my problem.

    Can you tell me how to prevent the renderer from transforming :  & to <&>    ( to <(>     ) to <)>.

    I tried to call

      SPAN

    Unknown macro: { font-family}

    .L1S33

    Unknown macro: { color}

    .L1S52

    Unknown macro: { color}

      CALL METHOD l_renderer->SET_NO_ESCAPING
        EXPORTING NO_ESCAPING = '&'.

       SPAN

    Unknown macro: { font-family}

    .L1S33

    Unknown macro: { color}

    .L1S52

    Unknown macro: { color}

    CALL METHOD l_renderer->SET_NO_ESCAPING
        EXPORTING NO_ESCAPING = '('.

       SPAN

    Unknown macro: { font-family}

    .L1S33

    Unknown macro: { color}

    .L1S52

    Unknown macro: { color}

    CALL METHOD l_renderer->SET_NO_ESCAPING
        EXPORTING NO_ESCAPING = ')'.

    which did not work.

    The call with the three characters combined

       SPAN

    Unknown macro: { font-family}

    .L1S33

    Unknown macro: { color}

    .L1S52

    Unknown macro: { color}

    CALL METHOD l_renderer->SET_NO_ESCAPING
        EXPORTING NO_ESCAPING = '&()'.

    didn't work too.

    Regards

    Matthias MEvenkamp

  2. Unknown User (ibck7kw)

    thank you for this post. it was very useful to me. is there any limit on the number of records that can be read? I need to use this approach for a large number of records. Please reply!!

  3. Unknown User (boli1dz)

    Great post.  One question though, when using the DOM to create the data in XLS format how could I include the following line in the output file:

    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"..

     allowing Windows to automatically open the XML file into Excel when opened.

  4. Unknown User (104z9w9st)

    This is an extremely useful - and simple way to get an excel compatible file out of SAP - without the need for a presentation server - your code example / blog is very much appreciated!

  5. Former Member

    This is great post and helped me but it would have bigger size compared to xls in application server how to resolve that issue.

  6. Tobias Bengtsson

    Regarding the <?mso-application progid="Excel.Sheet"?> comment.

    I found an alternativ way to insert this declaration to the XML. ( I did not manage to do it with the xml objects )

    I simple saved the XML to a string instead of a table and then I inserted the mso declaration.

    Then I converted the string back to table which I then download via the method cl_gui_frontend_services=>gui_download.

    Code: XML to string

    data l_xml_table_str type string.
    l_ostream = l_streamfactory->create_ostream_cstring( string = l_xml_table_str ).
    l_renderer = l_ixml->create_renderer( ostream  = l_ostream document = l_document ).
    l_rc = l_renderer->render( ).

    Code: Insert XML decl to string

    REPLACE FIRST OCCURRENCE OF '>' IN l_xml_table_str WITH '><?mso-application progid="Excel.Sheet"?>'.

  7. Unknown User (n0i5eej)

    Hi. Thanks for this article. The comment from Tobias Bengtsson helped me as well. Here is my snippet:

    \[...\]  
 data xml_result type xstring. "xstring ensures UTF-8 encoding
  data: lo_xml_doc        type ref to cl_xml_document,
        ld_rc             type i,
        lv_xml_string type string,
        lcl_conv  type ref to cl_abap_conv_in_ce.
 
    try.
      call transformation z_transformation_excel
      source row = ut_outtab
      result xml xml_result .
 
    catch cx_transformation_error.
*     exception handling
      message 'Transformation error' type 'E'.
  endtry.
 
  * Create instance
  create object lo_xml_doc.
 
* (1) Import from XML string
  ld_rc = lo_xml_doc->parse_xstring( stream = xml_result ).
*  lo_xml_doc->display( ).
 
* (2) Adding excel application id by converting xstring to string and insert progid
  lcl_conv = cl_abap_conv_in_ce=>create( input = xml_result ).
  lcl_conv->read( importing data = lv_xml_string ).
  replace first occurrence of '>' in lv_xml_string with '><?mso-application progid="Excel.Sheet"?>'.
 
* (3) re-import xml-string
  lo_xml_doc->free( ).
  ld_rc = lo_xml_doc->parse_string( stream = lv_xml_string ).
 
* (4) download file
 
 lo_xml_doc->export_to_file( filename = 'file.xml' ).


  8. Former Member

    Hi, That is a very helpful article.

    Do we have any options to merge cells and remove gridlines in excel throuhgh this approach?

  9. Ganesh prasad

    Hi,

    Can you please explain me how to create a hyperlink using this method???

     

    Please reply ASAP.......

  10. Maciej Czech

    A Parsed processor instruction "<?mso-application progid="Excel.Sheet"?>" can also be added with using IXML API. Below delta:

    Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
    ***Contents Unchanged:
REPORT zmc_excel_xml_dom_create.
TYPE-POOLS: ixml.
...
DATA:
  l_element_root       TYPE REF TO if_ixml_element,
***Following lines added:
  l_excel_pi           TYPE REF TO if_ixml_pi_parsed,
***Contents Unchanged:
  ns_attribute         TYPE REF TO if_ixml_attribute,
  r_element_properties TYPE REF TO if_ixml_element,
  r_element            TYPE REF TO if_ixml_element,
...
  l_ixml = cl_ixml=>create( ).
* Creating the dom object model
  l_document = l_ixml->create_document( ).
***Following lines added:
* Adding Parsed processor instruction for Excel <?mso-application progid="Excel.Sheet"?>
  l_excel_pi = l_document->create_pi_parsed( name = 'mso-application' ).
  l_excel_pi->set_attribute( name = 'progid'
                                   value = 'Excel.Sheet').
  l_document->append_child( l_excel_pi ).
***Contents Unchanged:
* Create root node 'Workbook'
  l_element_root  = l_document->create_simple_element(
              name = 'Workbook'
...
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.