topic Re: Excel Sheet with 2 sheets using XML -- Large size in Application Development and Automation Discussions

Excel Sheet with 2 sheets using XML -- Large size

Former Member — Thu, 21 May 2015 11:54:06 GMT

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:

Removing the formatting of cells for data -- > This reduced the size but not significantly.
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.

Re: Excel Sheet with 2 sheets using XML -- Large size

former_member212124 — Thu, 21 May 2015 12:29:34 GMT

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

Re: Excel Sheet with 2 sheets using XML -- Large size

Former Member — Thu, 21 May 2015 12:47:17 GMT

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.

Re: Excel Sheet with 2 sheets using XML -- Large size

Former Member — Fri, 22 May 2015 13:12:41 GMT

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.

Re: Excel Sheet with 2 sheets using XML -- Large size

Former Member — Sun, 19 Jun 2016 12:10:47 GMT

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.

Re: Excel Sheet with 2 sheets using XML -- Large size

Former Member — Tue, 14 May 2019 05:50:20 GMT

Hi Priyaranjan,

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

Regards,

Rajeshwari