Skip to Content

Excel Sheet with 2 sheets using XML -- Large size

Hi All,

I had a requirement to send an excel file having 2 sheets as an attachment in background mode.

I have converted the data to XML  using IXML and  ixml_stream_factory classes  and created 2 sheets as required.

The mail is being sent using  SO_NEW_DOCUMENT_ATT_SEND_API1. However the email attachment is of size 30MB as the Excel file is of type XML DATA ( *.xml).

  • When I open the file in MS Excel and do save as .XLS / .XLSX --> the same file is saved with a size of around 3 -4 MB.

  • Below changes / options have been tried to reduce mail size:
  1. Removing the formatting of cells for data -- > This reduced the size but not significantly.
  2. Converting the XML data in L_XML_TABLE to binary  and the using CL_ABAP_ZIP => compress_binary . This was giving garbled data .It may be possible i am not doing this compression in correct way.

Below is the code for sending mail:

*--Creating a Stream Factory

   l_streamfactory = l_ixml->create_stream_factory( ).

*--Connect Internal XML Table to Stream Factory

   l_ostream = l_streamfactory->create_ostream_itable( table = l_xml_table ).

*--Rendering the Document

   l_renderer = l_ixml->create_renderer( ostream  = l_ostream  document = l_document ).

   l_rc = l_renderer->render( ).

*--Saving the XML Document

   l_xml_size = l_ostream->get_num_written_raw( ).

   ls_doc_chng-obj_descr = text-060.

*--Creation of the Document Attachment

*--  L_XML_TABLE is the converted XML data in RAW format.

*--TYPES: BEGIN OF xml_line,

*--        data(255) TYPE x,

*--    END OF xml_line.



   LOOP AT l_xml_table INTO wa_xml.

     CLEAR ls_objbin.

     ls_objbin-line = wa_xml-data.

     APPEND ls_objbin TO lt_objbin.

   ENDLOOP.

   DESCRIBE TABLE lt_objbin LINES tab_lines.

   ls_objhead-line = text-060.

   APPEND ls_objhead TO lt_objhead.

*--Packing List For the E-mail Attachment

   ls_objpack-transf_bin = 'X'.

   ls_objpack-head_start = 1.

   ls_objpack-head_num   = 0.

   ls_objpack-body_start = 1.

   ls_objpack-body_num = tab_lines.

  

   ls_objpack-doc_type = 'XLS'.

   ls_objpack-doc_size = tab_lines * 255.

   APPEND ls_objpack TO lt_objpack.

*--Target Recipent

   lv_email = gv_high.

   CLEAR ls_reclist.

   ls_reclist-receiver = lv_email.

   ls_reclist-rec_type = 'U'.

   ls_reclist-express = 'X'.

   APPEND ls_reclist TO lt_reclist.

*--Sending the document

   CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

     EXPORTING

       document_data              = ls_doc_chng

       put_in_outbox              = 'X'

       commit_work                = 'X'

     TABLES

       packing_list               = lt_objpack

       object_header              = lt_objhead

       contents_txt               = lt_objtxt

       contents_hex               = lt_objbin

       receivers                  = lt_reclist

     EXCEPTIONS

       too_many_receivers         = 1

       document_not_sent          = 2

       operation_no_authorization = 4

       OTHERS                     = 99.




Any pointers on how to reduce the mail size will be helpful.



Thanks.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 22, 2015 at 01:12 PM

    Hi All,

    I was able to achieve the requirement by zipping the file before sending it.

    • The XML data received in L_XML_TABLE is of type x. Converted this to type xstring.
    • Use class CL_ABAP-ZIP  methods add and save to zip the xstring data.
    • Converted xstring to binary using SCMS_XSTRING_TO_BINARY.
    • Sent the binary content using CL_BCS_SEND.

    Compression Ratio achieved  in range of 97 - 99 % . ( 58 MB file was reduced to 671 KB in zip file ).

    Regards.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 21, 2015 at 12:29 PM
    -1

    Hi Priyaranjan,

    You can use following method for creation of Excel.

    Example:

    CONCATENATE 'Field Header1'

                              'Field Header2'

                              'Field Header3'

    INTO lv_string SEPARATED BY %_horizontal_tab.

    loop at lt_tab into ls_tab.

    CONCATENATE lv_string      %_cr_lf

                        ls_tab-field1       %_horizontal_tab

                        ls_tab-field2       %_horizontal_tab

                        ls_tab-field3       %_horizontal_tab

                        INTO lv_string SEPARATED BY space.

    endloop.

    CALL FUNCTION 'SCMS_STRING_TO_XSTRING'

           EXPORTING

             text   = lv_string

           IMPORTING

             buffer = lv_xstring.

    Please ask if any help/clarification required.

    thanks,

    vidyasagar

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Vidyasagar,

      Thanks for your reply. However in my case, concatenating the fields into 1 string will not work as the user wants this data in separate fields to be able to use filter / sort functionality.

      Regards.