Skip to Content

Creating Excel 2003 XML file from internal table

Sometimes we need original Excel file because of time/date, number format or different decimal/thousand separator. I am sharing generic ABAP form below:

*&---------------------------------------------------------------------*
 *&      Form  CONVERT_EXCEL_2003_XML
 *&---------------------------------------------------------------------*
 *       Creating Excel 2003 XML file from internal table
 *----------------------------------------------------------------------*
 *      -->PT_FIELDCAT  Standart ALV filedcat table
 *      -->PT_OUTTAB  Table included datas.
 *      <--PCV_XML  Created XML file
 *----------------------------------------------------------------------*
 form convert_excel_2003_xml  tables   pt_fieldcat type slis_t_fieldcat_alv
                                       pt_outtab structure gt_outtab
                              changing pcv_xml.
   data: struct type ref to cl_abap_structdescr,
         ls_component type abap_compdescr,
         ls_fieldcat  type slis_fieldcat_alv.
   field-symbols: <lfs_file>  type any.
   data: lv_created  type c length 19,
         lv_colcount type c length 3,
         lv_text     type c length 255.
   lv_created = '2012-12-10T10:55:52'.
   lv_created(4)    = sy-datum(4).
   lv_created+5(2)  = sy-datum+4(2).
   lv_created+8(2)  = sy-datum+6(2).
   lv_created+11(2) = sy-uzeit(2).
   lv_created+14(2) = sy-uzeit+2(2).
   lv_created+17(2) = sy-uzeit+4(2).
   struct ?= cl_abap_typedescr=>describe_by_data( pt_outtab ).
   describe table struct->components lines lv_colcount.
   "Excel 2003 XML header
   concatenate
   '<?xml version="1.0"?>'
   '<?mso-application progid="Excel.Sheet"?>'
   '<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>Mustafa Kerim Yılmaz</Author>'
   '<LastAuthor>Mustafa Kerim Yılmaz</LastAuthor>'
   '<Created>' lv_created 'Z</Created>'
   '<Version>14.00</Version>'
   '</DocumentProperties>'
   '<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">'
   '<AllowPNG/></OfficeDocumentSettings>'
   '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'
   '<WindowHeight>7740</WindowHeight>'
   '<WindowWidth>11760</WindowWidth>'
   '<WindowTopX>120</WindowTopX>'
   '<WindowTopY>120</WindowTopY>'
   '<ProtectStructure>False</ProtectStructure>'
   '<ProtectWindows>False</ProtectWindows>'
   '</ExcelWorkbook>'
   '<Styles/>'
   '<Worksheet ss:Name="Sheet1">'
   '<Table ss:ExpandedColumnCount="' lv_colcount '" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">'
   into pcv_xml.
   "Column headers
   concatenate pcv_xml
               '<Row ss:AutoFitHeight="0">'
          into pcv_xml.
   loop at struct->components into ls_component.
     read table pt_fieldcat into ls_fieldcat with key fieldname = ls_component-name.
     lv_text = ls_fieldcat-seltext_m.
     condense lv_text.
     concatenate pcv_xml
                 '<Cell><Data ss:Type="String">'
                 lv_text
                 '</Data></Cell>'
            into pcv_xml.
   endloop.
   concatenate pcv_xml
               '</Row>'
          into pcv_xml.
   "body
   set country 'US'.
   loop at pt_outtab.
     concatenate pcv_xml '<Row ss:AutoFitHeight="0">'
            into pcv_xml.
     loop at struct->components into ls_component.
       read table gt_fieldcat into ls_fieldcat with key fieldname = ls_component-name.
       check sy-subrc eq 0.
       assign component ls_component-name of structure gt_outtab to <lfs_file>.
       check sy-subrc eq 0.
       case ls_fieldcat-datatype.
         when 'DATS' or 'DATUM'.
           lv_text = '00.00.0000 00:00:00'.
           lv_text(2)   = <lfs_file>+6(2).
           lv_text+3(2) = <lfs_file>+4(2).
           lv_text+6(4) = <lfs_file>(4).
           concatenate pcv_xml
                       '<Cell><Data ss:Type="String">'
                       lv_text
                       '</Data></Cell>'
                  into pcv_xml.
         when 'QUAN' or 'CURR'.
           write <lfs_file> to lv_text no-grouping.
           call function 'CLOI_PUT_SIGN_IN_FRONT'
             changing
               value = lv_text.
           concatenate pcv_xml
                       '<Cell><Data ss:Type="Number">'
                       lv_text
                       '</Data></Cell>'
                  into pcv_xml.
         when 'INT4'.
           write <lfs_file> to lv_text decimals 0 no-grouping.
           call function 'CLOI_PUT_SIGN_IN_FRONT'
             changing
               value = lv_text.
           concatenate pcv_xml
                       '<Cell><Data ss:Type="Number">'
                       lv_text
                       '</Data></Cell>'
                  into pcv_xml.
         when others.
           write <lfs_file> to lv_text.
           concatenate pcv_xml
                       '<Cell><Data ss:Type="String">'
                       lv_text
                       '</Data></Cell>'
                  into pcv_xml.
       endcase.
     endloop.
     concatenate pcv_xml '</Row>'
            into pcv_xml.
   endloop.
   "Excel 2003 XML footer
   concatenate pcv_xml
               '</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>'
               '<Unsynced/>'
               '<Selected/>'
               '<Panes/>'
               '<ProtectObjects>False</ProtectObjects>'
               '<ProtectScenarios>False</ProtectScenarios>'
               '</WorksheetOptions>'
               '</Worksheet>'
               '</Workbook>'
               into pcv_xml.
endform.                    " CONVERT_EXCEL_2003_XML

We can fiil field catalog with standart function module REUSE_ALV_FIELDCATALOG_MERGE.

We can use form changing value PCV_XML for download with gui or send with mail. For gui download:

data :fullpath    type string,
          lv_xml      type string,
        gv_data     type xstring,
        gt_fieldcat type slis_t_fieldcat_alv,
        gt_outtab  like kna1 occours 0 with header line,
 GT_OBJHEX    like SOLIX occurs 10. 
perform convert_excel_2003_xml   tables gt_fieldcat gt_outtab
                                  changing lv_xml.
call function 'SCMS_STRING_TO_XSTRING'
       exporting
         text   = lv_xml
       importing
         buffer = gv_data
       exceptions
         others = 0.
     refresh gt_objhex.
     call function 'SCMS_XSTRING_TO_BINARY'
       exporting
         buffer          = gv_data
         append_to_table = 'X'
       tables
         binary_tab      = gt_objhex
       exceptions
         others = 0.
fullpath = 'c:\test.xls'.
call function 'GUI_DOWNLOAD'
       exporting
         filename                = fullpath
         filetype                = 'BIN'
         replacement             = '#'
       tables
         data_tab                = gt_objhex   "it_data[]
       exceptions
         file_write_error        = 1
         no_batch                = 2
         gui_refuse_filetransfer = 3
         invalid_type            = 4
         no_authority            = 5
         unknown_error           = 6
         header_not_allowed      = 7
         separator_not_allowed   = 8
         filesize_not_allowed    = 9
         header_too_long         = 10
         dp_error_create         = 11
         dp_error_send           = 12
         dp_error_write          = 13
         unknown_dp_error        = 14
         access_denied           = 15
         dp_out_of_memory        = 16
         disk_full               = 17
         dp_timeout              = 18
         file_not_found          = 19
         dataprovider_exception  = 20
         control_flush_error     = 21
         others                  = 22.
     if sy-subrc <> 0.
       message id sy-msgid type sy-msgty number sy-msgno
               with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
endif.
10 Comments
You must be Logged on to comment or reply to a post.
    • That depends on how sensible your development manager is. abap2xlsx is in use in one my clients right now – a very large multinational.

      Installed using SAPLINK that leaves no footprint and requires no basis assistance.

    • I know that you have to follow those rules, but to be honest i dont understand them having doubts due to dataelements and tables, What can harm your environment is coding. I habe meanwhile a lot of satisfied customers with true excel sheets instead of that CSV stuff.

    • I know that you have to follow those rules, but to be honest i dont understand them having doubts due to dataelements and tables, What can harm your environment is coding. I habe meanwhile a lot of satisfied customers with true excel sheets instead of that CSV stuff.

    • I know that you have to follow those rules, but to be honest i dont understand them having doubts due to dataelements and tables, What can harm your environment is coding. I habe meanwhile a lot of satisfied customers with true excel sheets instead of that CSV stuff.

    • I know that you have to follow those rules, but to be honest i dont understand them having doubts due to dataelements and tables, What can harm your environment is coding. I habe meanwhile a lot of satisfied customers with true excel sheets instead of that CSV stuff.

  • Any development manager who refuses to let developers install things like SAPLINK or ABAP2XLS is a very strange development manager indeed. That is not to say I have not encountered such people, because I have.

    I would love to know what companies have development managers like this, so I could buy shares in their competitors.