Skip to Content

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.
To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

    1. Matthew Billingham

      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.

      (0) 
    2. Rainer Hübenthal

      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.

      (0) 
    3. Rainer Hübenthal

      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.

      (0) 
    4. Rainer Hübenthal

      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.

      (0) 
    5. Rainer Hübenthal

      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.

      (0) 
  1. Paul Hardy

    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.

    (0) 

Leave a Reply