ABAP Tutorials

From release 620, ABAP has provided us a way to transform our data using the XML transformation. We can very use the XML to generate the formatted Excel File.

The advantages of the XML technique over the OLE:

• Using XML technique, File can be generated in Background processing. It would not be possible with OLE technique to generate the Formatted excel file because it needs the active connection with the windows.
• Using XML we can send the fancy generated file as an Email Attachment.
• We can create formatted excel file using the XML technique from the Web applications generated using Web Dynpro or BSP.
• It’s faster when compared to OLE technique.

ABAP Program Code:

 

*&---------------------------------------------------------------------*
*& Report  ZTEST_NP_EXCEL_XML
*&
*& Download the formatted excel file using XML
*&---------------------------------------------------------------------*

REPORT  ztest_np_excel_xml.

TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       maktx TYPE char30,
       END   OF ty_mara.

DATA: itab TYPE STANDARD TABLE OF ty_mara,
      la_tab LIKE LINE OF itab,
      xmlstr TYPE string.

START-OF-SELECTION.

*---------
* Test table
*---------
  la_tab-matnr = 'TEST1'.
  la_tab-maktx = 'Test description'.
  APPEND la_tab TO itab.

  la_tab-matnr = 'TEST2'.
  la_tab-maktx = 'Test description 2'.
  APPEND la_tab TO itab.

*---------
* Get the XML data excel
*---------
  CALL TRANSFORMATION ztest_np_xls
    SOURCE table = itab
    RESULT XML xmlstr.

*---------
* Download the file
*---------

* Fill the table
  DATA: xml_table TYPE STANDARD TABLE OF string.

  APPEND xmlstr TO xml_table.

  DATA: window_title TYPE string,
        fullpath TYPE string,
        path TYPE string,
        user_action TYPE i,
        default_extension TYPE string,
        default_file_name TYPE string,
        file_filter TYPE  string,
        filename TYPE string,
        initialpath TYPE string.

* File selection
  MOVE '.XLS' TO default_extension.
  MOVE 'XLS files (*.XLS)|*.XLS' TO file_filter.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_extension = default_extension
      default_file_name = default_file_name
      file_filter       = file_filter
      initial_directory = initialpath
    CHANGING
      filename          = filename
      path              = path
      fullpath          = fullpath
      user_action       = user_action
    EXCEPTIONS
      cntl_error        = 1
      error_no_gui      = 2
      OTHERS            = 3.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

* download file
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename                = fullpath
      filetype                = 'ASC'
    TABLES
      data_tab                = xml_table
    EXCEPTIONS
      file_write_error        = 1
      no_batch                = 2
      gui_refuse_filetransfer = 3
      invalid_type            = 4
      OTHERS                  = 5.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

Code for XML Transformation ( Double click on the name ztest_np_xls after statement Call Transformation) :
 

<tt:transform xmlns:tt=”http://www.sap.com/transformation-templates”>
This test program will generate the formatted excel like this:

<tt:root name=”table”/>

<tt:template>

<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:x=”urn:schemas-microsoft-com:office:excel” xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:html=

“http://www.w3.org/TR/REC-html40″>

<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>

<Author>npatel</Author>

<LastAuthor>npatel</LastAuthor>

<Created>2009-01-01T22:27:09Z</Created>

<Company></Company>

<Version>11.8132</Version>

</DocumentProperties>

<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>

<WindowHeight>12660</WindowHeight>

<WindowWidth>19980</WindowWidth>

<WindowTopX>480</WindowTopX>

<WindowTopY>120</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

<Style ss:ID=”Default” ss:Name=”Normal”>

<Alignment ss:Vertical=”Bottom”></Alignment>

<Borders></Borders>

<Font></Font>

<Interior></Interior>

<NumberFormat/>

<Protection></Protection>

</Style>

<Style ss:ID=”s23″>

<Font ss:Bold=”1″ ss:Size=”26″ x:Family=”Swiss”></Font>

<Interior ss:Color=”#FFFF99″ ss:Pattern=”Solid”></Interior>

</Style>

</Styles>

<Worksheet ss:Name=”Sheet1″>

<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”25″ x:FullColumns=”1″ x:FullRows=”1″>

<Column ss:Width=”152.25″/>

<Column ss:Width=”180″/>

<Column ss:Width=”117″/>

<Row>

<Cell ss:StyleID=”s23″>

<Data ss:Type=”String”>Material No</Data>

</Cell>

<Cell ss:StyleID=”s23″>

<Data ss:Type=”String”>Material Desc</Data>

</Cell>

</Row>

<tt:loop ref=”.table”>

<Row>

<Cell>

<Data ss:Type=”String”>

<tt:value ref=”MATNR”/>

</Data>

</Cell>

<Cell>

<Data ss:Type=”String”>

<tt:value ref=”MAKTX”/>

</Data>

</Cell>

</Row>

</tt:loop>

</Table>

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>

<Selected/>

<Panes>

<Pane>

<Number>3</Number>

<ActiveRow>4</ActiveRow>

<ActiveCol>1</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name=”Sheet2″>

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name=”Sheet3″>

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

</tt:template>

</tt:transform>

 

 

 The output of the program ( including XML Transormation) is shown below.

 

 

Note: It works fine with MS Excel. For others package like Open Office, it wont work.

You might also be interested in these posts:

1. Upload File from Application Server into ABAP Internal Table in SAP
2. ABAP Program with Editable ALV Grid Contents
3. SAP ABAP Transferring Internal Table Contents to a file on the Application Server
4. ABAP Program to Display SE78 pictures on Screen
5. ABAP Automailer program for sending output of SAP TCodes in Background