Excel Sheet with 2 sheets using XML -- Large size

[priyaranjan_gupta]

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.

[former_member212124]

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

[priyaranjan_gupta]

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.

[priyaranjan_gupta]

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.

[Former Member]

Hi Priyaranjan,

I have to create an excel file (.xls or .xlsx) in the application server. Everything works fine just that I'm not able to avoid the pop up "The file you are trying to open, 'filename.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

When I open the file the data is correctly formatted. I don't know how to solve this error. Please advice.

[former_member574546]

Hi Priyaranjan,

I have the same requirement can you pls help with the piece of code.

Regards,

Rajeshwari