Skip to Content
Technical Articles
Author's profile photo Rajesh Rajgor

Formatted excel using XML

In every business, it is necessary to convey data within or outside of SAP. When we think about exporting data from SAP to a non-SAP system, the very first option that comes in mind is excel. Because, it is a fast and easy way but the only drawback is, it gives plain text without color, alignment, and style. So in this blog post, you are going to learn, how to generate a formatted excel file.

Steps 1:

Create your own excel file in Microsoft-Excel and write content as per your requirement. Like, heading, columns, row data, footer and apply alignment, color, formula and so on.

Note: I have used all dummy data based on materials and plants. so you can understand how it works and you can refer or enhance it as per your requirement. This example does not follow any business process.

(Screenshot – 1)

Note: Keep less row-data so after converting this file to .XML, it will be easy to use in the report.

Steps 2:

Save an Excel file with “XML Spreadsheet 2003”. ( Save As )

(Screenshot – 2)

Steps 3:

Create a report in SAP using SE38 as bellow.

*&---------------------------------------------------------------------*
*& Report ZTEST1
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ztest1.

INCLUDE ztest1_top.
INCLUDE ztest1_ss.
INCLUDE ztest1_cl_def.
INCLUDE ztest1_cl_impl.

START-OF-SELECTION.
  CLEAR : _obj.
  CREATE OBJECT _obj. " OBJECT OF ZCL_EXCEL CLASS.
  _obj->_header( ).       " HEADER, METHOD TO ADD TITLE AND DYNAMIC COLUMNS IN EXCEL.
  _obj->_body( ).         " BODY, METHOD TO ADD DYNAMIC ROW DATA IN EXCEL.
  _obj->_footer( ).       " FOOTER, METHOD TO ADD TOTAL OF ROW DATA IN EXCEL.
  _obj->_download( ).     " DOWNLOAD, METHOD THAT ASK USER TO SELECT LOCATION TO DOWNLOAD AND OPEN EXCEL.

Steps 4:

Double click on ztest1_top and write code as bellow,

*&---------------------------------------------------------------------*
*& Include          ZTEST1_TOP
*&---------------------------------------------------------------------*
TYPE-POOLS: slis.
TABLES : mara,t001w,sscrfields.

CLASS zcl_excel DEFINITION DEFERRED.          " DECLARE CLASS AS DEFERRED.

TYPES : BEGIN OF plant,
          werks TYPE werks_d,
        END OF plant,
        BEGIN OF mat,
          matnr TYPE matnr,
        END OF mat.
DATA : iplant TYPE TABLE OF plant,
       wplant TYPE plant.
DATA : imat TYPE TABLE OF mat,
       wmat TYPE mat.

DATA : _obj TYPE REF TO zcl_excel.             " DECLARE zcl_excel OBJECT
DATA : th TYPE string VALUE ''.                " STRING VARIABLE TO STORE 'N' COLUMNS.
DATA : td TYPE string VALUE ''.                " STRING VARIABLE TO STORE 'N' ROWS
DATA : randn TYPE i.                           " INTEGER TO STORE RANDOM NUMBER (DUMMY NETWR)
DATA : col_n(5),                               " TOTAL NO OF COLUMNS
       row_n(5).                               " TOTAL NO OF ROWS.

DATA: ld_filename TYPE string,                 " EXCEL FILENAME
      ld_path     TYPE string,                 " PATH
      ld_fullpath TYPE string,                 " FULL PATH WHERE EXCEL STORED IN DRIVE
      ld_result   TYPE i.
DATA : oref TYPE REF TO cx_root.

DATA xml_tab TYPE STANDARD TABLE OF string.    " INTERNAL TABLE THAT USED TO ASSIGN XML STRING TO FM

Steps 5:

Double click on ztest1_ss and write code as bellow,

*&---------------------------------------------------------------------*
*& Include          ZTEST1_SS
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS : s_matnr FOR mara-matnr ,            " N ROWS
                 s_plant FOR t001w-werks.            " M COLS
SELECTION-SCREEN END OF BLOCK b1.

Steps 6:

Double click on ztest1_cl_def and write code as bellow,

*&---------------------------------------------------------------------*
*& Include          ZTEST1_CL_DEF
*&---------------------------------------------------------------------*

CLASS zcl_excel DEFINITION.
  PUBLIC SECTION.
    DATA : _excel TYPE string.      " STRING VARIABLE TO STORE ENTIRE XML DATA
    METHODS : constructor,          " DECLARE EXCEL DOCUMENT,WORKSHEET,STYLES.....
              _header,              " HEADER, METHOD TO ADD TITLE AND DYNAMIC COLUMNS IN EXCEL.
              _body,                " BODY, METHOD TO ADD DYNAMIC ROW DATA IN EXCEL.
              _footer,              " FOOTER, METHOD TO ADD TOTAL OF ROW DATA IN EXCEL.
              _download.            " DOWNLOAD, METHOD THAT ASK USER TO SELECT LOCATION TO DOWNLOAD AND OPEN EXCEL.

ENDCLASS.

Steps 6:

Double click on ztest1_cl_impl and write code as bellow,

*&---------------------------------------------------------------------*
*& Include          ZTEST1_CL_IMPL
*&---------------------------------------------------------------------*

CLASS zcl_excel IMPLEMENTATION.
  METHOD constructor.   " OPEN DEMO.XML IN NOTEPAD AND COPY TILL HEADING TAG (<XML><WORKBOOK><STYLES></STYLES><WORKFHEET><TABLE><ROW>HEADING</ROW>)

***XML STARTING TAGS + STYLES
    CONCATENATE
        '<?xml version="1.0" encoding="UTF-16"?>'
        '   <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">  '
        '    <Styles>  '
        '  <Style ss:ID="Default" ss:Name="Normal">'
        '   <Alignment ss:Vertical="Bottom"/>'
        '   <Borders/>'
        '   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>'
        '   <Interior/>'
        '   <NumberFormat/>'
        '   <Protection/>'
        '  </Style>'
        '  <Style ss:ID="m2145582194624">'
        '   <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="Calibri" x:Family="Swiss" ss:Size="28" ss:Color="#000000"/>'
        '   <Interior ss:Color="#808080" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="m2145582194644">'
        '   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"'
        '    ss:Bold="1"/>'
        '   <Interior ss:Color="#808080" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s16">'
        '   <Alignment ss:Vertical="Center"/>'
        '  </Style>'
        '  <Style ss:ID="s17">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#FFD966" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s18">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#D0D8E2" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s19">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#A5C480" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s20">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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"/>'
        '   </Borders>'
        '   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#ACB9CA" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s21">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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"/>'
        '   </Borders>'
        '   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#88B157" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s22">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"'
        '    ss:Bold="1"/>'
        '   <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s23">'
        '   <Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#FFE9A3" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s24">'
        '   <Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <Borders>'
        '    <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#FFE9A3" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s25">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <Borders>'
        '    <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#D0D8E2" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s26">'
        '   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <Borders>'
        '    <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#A5C480" ss:Pattern="Solid"/>'
        '  </Style>'
        '  <Style ss:ID="s27">'
        '   <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>'
        '   <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="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
        '   <Interior ss:Color="#D0D8E2" ss:Pattern="Solid"/>'
        '  </Style>'
        ' </Styles>'
        ' <Worksheet ss:Name="DEMO">'
        '  <Table ss:ExpandedColumnCount="@cols" ss:ExpandedRowCount="@rows" x:FullColumns="1"'
        '   x:FullRows="1" ss:DefaultRowHeight="14.4">'
        '   <Column ss:Width="22.200000000000003"/>'
        '   <Column ss:AutoFitWidth="0" ss:Width="76.8"/>'
        '   <Column ss:AutoFitWidth="0" ss:Width="145.79999999999998"/>'
        '   <Column ss:Width="35.4" ss:Span="4"/>'
        '   <Row ss:AutoFitHeight="0" ss:Height="27">'
        '    <Cell ss:MergeAcross="2" ss:StyleID="m2145582194624"><Data ss:Type="String">Heading</Data></Cell>'
        '   </Row>'
        INTO _excel.
***XML STARTING TAGS + STYLES

  ENDMETHOD.
  METHOD _header.   " COPY FIRST 3 COLUMN BECAUSE ITS STATIC

    th = |<Row ss:StyleID="s16">|.
    th = |{ th }<Cell ss:StyleID="s20"><Data ss:Type="String">Sr.#</Data></Cell>|.
    th = |{ th }<Cell ss:StyleID="s21"><Data ss:Type="String">Material #</Data></Cell>|.
    th = |{ th }<Cell ss:StyleID="s20"><Data ss:Type="String">Material Description</Data></Cell>|.

***********************************************************************************************************
    " IF THERE IS ONLY ONE PLANT OR NO PLANT IN YOUR SYSTE, IN THAT CASE YOU CAN COMMENT THIS SELECT QUERY
    " AND PASS MULTIPLE DUMMY PLANTS IN SELECT-OPTIONS LIKE P001,P002,P003 UPTO N SO THAT YOU CAN UNDERSTAND THE PROCESS.
    " MAKE SURE IF YOU ARE COMMENTING QUERY, REPLACE "wplant-werks" TO "wplant-low".
***********************************************************************************************************
    SELECT werks FROM t001w INTO TABLE iplant WHERE werks IN s_plant.
    LOOP AT iplant INTO wplant.
      th = |{ th }<Cell ss:StyleID="s17"><Data ss:Type="String">{ wplant-werks }</Data></Cell>|.
      CLEAR wplant.
    ENDLOOP.
    th = |{ th }</Row>|.
    _excel = |{ _excel } { th }|.

  ENDMETHOD.
  METHOD _body.

***********************************************************************************************************
    " IF THERE IS NO MATERIALS IN YOUR SYSTE, IN THAT CASE YOU CAN COMMENT THIS SELECT QUERY
    " AND PASS MULTIPLE DUMMY MATERIALS IN SELECT-OPTIONS LIKE 1,2,3,4 UPTO N SO THAT YOU CAN UNDERSTAND THE PROCESS.
    " MAKE SURE IF YOU ARE COMMENTING QUERY, REPLACE "wmat-matnr" TO "wmat-low".
***********************************************************************************************************
    SELECT matnr FROM mara INTO TABLE imat WHERE matnr IN s_matnr.
    LOOP AT imat INTO wmat.
      CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
        EXPORTING
          input  = wmat-matnr
        IMPORTING
          output = wmat-matnr.

      td = |{ td }<Row>|.
      td = |{ td }<Cell ss:StyleID="s18"><Data ss:Type="String">{ sy-tabix }</Data></Cell>|.
      td = |{ td }<Cell ss:StyleID="s19"><Data ss:Type="String">{ wmat-matnr }</Data></Cell>|.
      td = |{ td }<Cell ss:StyleID="s27"><Data ss:Type="String">Description { sy-tabix }</Data></Cell>|.

      LOOP AT iplant INTO wplant.
        CALL FUNCTION 'QF05_RANDOM_INTEGER'     " USED THIS FM TO GENERATE RANDOM NO BETWEEN 10 TO 100 FOR DUMMY NETWR.
          EXPORTING
            ran_int_max   = 100
            ran_int_min   = 10
          IMPORTING
            ran_int       = randn
          EXCEPTIONS
            invalid_input = 1
            OTHERS        = 2.
        td = |{ td }<Cell ss:StyleID="s23"><Data ss:Type="Number">{ randn }</Data></Cell>|.
        CLEAR wplant.
      ENDLOOP.
      td = |{ td }</Row>|.
      CLEAR wmat.
    ENDLOOP.
    _excel = |{ _excel } { td }|.

  ENDMETHOD.
  METHOD _footer.

    DESCRIBE TABLE imat LINES row_n.        " GET TOTAL NO OF ROWS BASED ON LIST OF MATERIALS
    DESCRIBE TABLE iplant LINES col_n.      " GET TOTAL NO OF COLS BASED ON LIST OF PLANTS

    CONDENSE : row_n,col_n.                 " REMOVE BLANK SPACES

    " TOTAL BY USING R1C1 FORMULA
    _excel = |{ _excel } <Row>|.
    _excel = |{ _excel } <Cell ss:MergeAcross="2" ss:StyleID="m2145582194644"><Data ss:Type="String">Total</Data></Cell>|.
    LOOP AT iplant INTO wplant.
      _excel = |{ _excel }<Cell ss:StyleID="s22" ss:Formula="=SUM(R[-{ row_n }]C:R[-1]C)"><Data ss:Type="Number"></Data></Cell>|.
    ENDLOOP.
    _excel = |{ _excel } </Row>|.

    " FINALLY CLOSING ALL TAGS
    CONCATENATE  _excel
             '   </Table>  '
             '     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  '
             '      <PageSetup>  '
             '       <Header x:Margin="0.3"/>  '
             '       <Footer x:Margin="0.3"/>  '
             '       <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>  '
             '      </PageSetup>  '
             '      <Print>  '
             '       <ValidPrinterInfo/>  '
             '       <HorizontalResolution>300</HorizontalResolution>  '
             '       <VerticalResolution>300</VerticalResolution>  '
             '      </Print>  '
             '      <Selected/>  '
             '      <Panes>  '
             '       <Pane>  '
             '        <Number>3</Number>  '
             '        <ActiveRow>2</ActiveRow>  '
             '        <ActiveCol>1</ActiveCol>  '
             '       </Pane>  '
             '      </Panes>  '
             '      <ProtectObjects>False</ProtectObjects>  '
             '      <ProtectScenarios>False</ProtectScenarios>  '
             '     </WorksheetOptions>  '
             '    </Worksheet>  '
             '  </Workbook>  '
             INTO _excel.
  ENDMETHOD.
  METHOD _download.

    " THIS FM ALLOW USER TO SELECT PATH WHERE TO STORE EXCEL FILE
    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
*       window_title      = ' '
        default_extension = 'xls'
        default_file_name = 'Test_Report'
        initial_directory = 'D:\'
      CHANGING
        filename          = ld_filename
        path              = ld_path
        fullpath          = ld_fullpath
        user_action       = ld_result.

*<Table ss:ExpandedColumnCount="@cols" ss:ExpandedRowCount="@rows" x:FullColumns="1"' x:FullRows="1" ss:DefaultRowHeight="14.4">

    row_n = row_n + 3.                  " + 3 MEANS, ADDING HEADING LINE, COLUMN AND TOTAL LINE SO expandedRowCount will be row_n + 3
    col_n = col_n + 3.                  " + 3 MEANS, ADDING Sr #, Material # AND Description BECAUSE THESE ARE STATIC COLUMNS SO expandedColumnCount will be row_n + 3
    " IT IS NOT NECESSARY TO + 3 EVERY TIME, IT IS COMPLETELY BASED ON YOUR STATIC AND DYANAMIC DATA COUNT.

    CONDENSE : row_n,col_n.

    REPLACE REGEX '@cols' IN _excel WITH col_n.    " SEE <TABLE> TAG IN CONSTRUCTOR
    REPLACE REGEX '@rows' IN _excel WITH row_n.    " SEE <TABLE> TAG IN CONSTRUCTOR

    APPEND _excel TO xml_tab.                      " ADD FINAL XML TO XML_TABLE.

    CALL METHOD cl_gui_frontend_services=>gui_download
      EXPORTING
        filename              = ld_fullpath
        filetype              = 'ASC'
        write_field_separator = 'X'
      CHANGING
        data_tab              = xml_tab
      EXCEPTIONS
        access_denied         = 15.

    IF sy-subrc EQ 15.
      MESSAGE 'Access denied' TYPE 'I' DISPLAY LIKE 'E'.
    ELSE.
      CALL METHOD cl_gui_frontend_services=>execute
        EXPORTING
          application = 'EXCEL'
          parameter   = ld_fullpath.
    ENDIF.
  ENDMETHOD.
ENDCLASS.

This is how I had enhanced.

(Screenshot – 3 )

Useful links.

I hope this example helps you to fulfill your requirements.

 

Do connect me if there is any issue or if I missed some points.

 

Thanks & Regards,

Rajesh Kalyanji Rajgor

 

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chandresh Acharya
      Chandresh Acharya

      Extremely useful, Thank you so much for sharing very useful information to generate formatted excel report.

      Author's profile photo Yograjsinh Zala
      Yograjsinh Zala

      Very useful blog Mr. Rajesh.

      Author's profile photo Paul Hardy
      Paul Hardy

      Have you heard of ABAP2XLSX?

      https://blogs.sap.com/2010/07/12/abap2xlsx-generate-your-professional-excel-spreadsheet-from-abap/

      https://github.com/sapmentors/abap2xlsx

      This has been out for TEN YEARS now and is all about how to generate formatted spreadsheets from ABAP.

      Every month during those ten years someone who has not heard of it works out a way to do something similar and posts a blog on SCN.

       

      Author's profile photo Rajesh Rajgor
      Rajesh Rajgor
      Blog Post Author

      Hi,

      You are right, Paul. I have never heard ABAP2XLSX before. Please consider this blog post as a different approach for the same. I will try to learn ABAP2XLSX and use it in the future.

      Author's profile photo Prasenjit Bist
      Prasenjit Bist

      Paul Hardy  I request you to educate the people who are unaware through a series of blog posts. Many people find it daunting though the code is powerful. Thanks in advance for your tutorials on the same and guiding the next gen of developers

       

      Kindest regards,

      PSB

      Author's profile photo Jaime Rodriguez Capote
      Jaime Rodriguez Capote

      You can use an XSLT transformation instead of using hardcoding XML strings, to just pass internal table and retrieve formated XML

      Author's profile photo Michael Keller
      Michael Keller

      Hi Rajesh. Thanks for sharing. Did you try your solution with a big amount of data?

      Author's profile photo Rajesh Rajgor
      Rajesh Rajgor
      Blog Post Author

      Hi Michael,

      I tried with big amount of data,

      The file size was 326MB. There may be an issue regarding size of string(_excel), for that we can append it to xml_tab(internal Table) like a string array.

      Author's profile photo Michael Keller
      Michael Keller

      I asked because I worked with an Excel-XML-solution back in 2007. My problem was that the amount of XML-tags in comparison to the amount of business data was quite big. So files were very big and not to handle. While looking for a solution, I discovered that modern Excel-files (xlsx) are zipped archives.

      Author's profile photo Rajesh Rajgor
      Rajesh Rajgor
      Blog Post Author

      Ohh ok. so i think this approach is for a small amount of data. By the way, i am trying ABAP2XLSX library, its working fine and it also generates.XLSX file.

      Author's profile photo Balaji M
      Balaji M

      Can you share that program for send mail logic