Creating Excel 2003 XML file from internal table

[mkysoft]

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.