topic Re: How to get the binary data into excel format? in Application Development and Automation Discussions

How to get the binary data into excel format?

Former Member — Sat, 17 May 2008 05:07:07 GMT

Hi Gurus.

I have a requirement which is like I need to get the files from the application server which is DMS and need to send the files as an attachment to the mail.

I am using a FM SDOK_PHIO_LOAD_CONTENT which will get the file in binary format from the DMS server and its mimetype.

I could convert the data into word, txt and PDF files but i could not comvert the binary data into excel file.

Can any body please suggest me as this is very urgent.

Thanks & Regards

Arun

Re: How to get the binary data into excel format?

Former Member — Wed, 28 May 2008 23:42:05 GMT

Arun,

I am trying to recreate your scenario to try and find the answer. Can you describe how you are able to convert the binary data into pdf?

Also, what results do you get with Excel files? Does Excel provide an error message?

Regards,

DBreit

Re: How to get the binary data into excel format?

Former Member — Tue, 24 Jun 2008 20:21:35 GMT

Hi DBreit,

Do find the below code which is working for excel file.

get the attachments of the PO for each line item

SELECT ebeln

ebelp

FROM ekpo

INTO TABLE it_ekpo

WHERE ebeln = l_doc-xekko-ebeln.

concatenate PO and the Line Item Numebr to get the Object key

LOOP AT it_ekpo INTO wa_ekpo.

CONCATENATE wa_ekpo-ebeln wa_ekpo-ebelp INTO wa_temp-objky.

APPEND wa_temp TO it_temp.

CLEAR: wa_temp,wa_ekpo.

ENDLOOP.

*Get data from table DRAD

IF it_temp[] IS NOT INITIAL.

SELECT dokar

doknr

dokvr

doktl

objky

FROM drad

client specified

INTO TABLE it_drad

FOR ALL ENTRIES IN it_temp

WHERE mandt = sy-mandt

AND objky = it_temp-objky.

ENDIF.

IF NOT it_drad IS INITIAL.

SELECT * FROM dms_doc2loio

INTO TABLE it_dms_doc2loio

FOR ALL ENTRIES IN it_drad

WHERE dokar = 'SRM'

AND doknr = it_drad-doknr

AND dokvr = it_drad-dokvr

AND doktl = it_drad-doktl.

ENDIF.

Get the Physical object class and Object ID of the attachment

IF NOT it_dms_doc2loio IS INITIAL.

SELECT * FROM dms_ph_cd1

INTO TABLE it_dms_ph_cd1

FOR ALL ENTRIES IN it_dms_doc2loio

WHERE loio_id = it_dms_doc2loio-lo_objid.

ENDIF.

Get the file from the DMS server using the below function module.

LOOP AT it_dms_ph_cd1 INTO wa_dms_ph_cd1.

wa_object_id-class = wa_dms_ph_cd1-ph_class.

wa_object_id-objid = wa_dms_ph_cd1-phio_id.

CALL FUNCTION 'SDOK_PHIO_LOAD_CONTENT'

EXPORTING

object_id = wa_object_id

client = sy-mandt

TABLES

file_access_info = it_file_access_info

file_content_binary = it_sdokcntbin

EXCEPTIONS

not_existing = 1

not_authorized = 2

no_content = 3

bad_storage_type = 4

OTHERS = 5.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

READ TABLE it_file_access_info INTO wa_file_access_info INDEX 1.

IF wa_file_access_info-mimetype = 'application/msword'.

l_doc_type = 'DOC'.

CLEAR: a_xstring,len.

LOOP AT it_sdokcntbin INTO wa_sdokcntbin.

CONCATENATE a_xstring wa_sdokcntbin-line

INTO a_xstring IN BYTE MODE.

ENDLOOP.

len = XSTRLEN( a_xstring ).

transform to solix tab - adding additional attachments

REFRESH lt_solix.

lt_solix =

cl_document_bcs=>xstring_to_solix(

ip_xstring = a_xstring ).

CLEAR l_filename.

Get the file name

l_filename = wa_file_access_info-file_name.

bcs_doc1 = cl_document_bcs=>create_document(

i_type = l_doc_type

i_subject = l_filename

i_length = len

i_language = sy-langu

i_hex = lt_solix

*Type casting

obj_file ?= bcs_doc1.

Add Attachment as a document to the email object

CALL METHOD l_email_object->add_document_as_attachment

EXPORTING

im_document = obj_file.

for PDF document

ELSEIF wa_file_access_info-mimetype = 'application/pdf'.

l_doc_type = 'PDF'.

file_size = wa_file_access_info-file_size.

Convert the 1022 binary data to xstring

CLEAR a_xstring.

CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'

EXPORTING

input_length = file_size

IMPORTING

buffer = a_xstring

TABLES

binary_tab = it_sdokcntbin

EXCEPTIONS

failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

DATA: w_output_length TYPE i.

Convert Xstring to 255 RAW

CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'

EXPORTING

buffer = a_xstring

IMPORTING

output_length = w_output_length

TABLES

binary_tab = lt_content_bin.

len = w_output_length.

CLEAR l_filename.

Get the file name

l_filename = wa_file_access_info-file_name.

bcs_doc1 = cl_document_bcs=>create_document(

i_type = l_doc_type

i_subject = l_filename

i_length = len

i_language = sy-langu

i_hex = lt_content_bin

type casting

obj_file ?= bcs_doc1.

Add Attachment as a document to the email object

CALL METHOD l_email_object->add_document_as_attachment

EXPORTING

im_document = obj_file.

for Excel Document

ELSEIF wa_file_access_info-mimetype = 'application/msexcel'.

l_doc_type = 'XLS'.

CLEAR: a_xstring,len.

LOOP AT it_sdokcntbin INTO wa_sdokcntbin.

CONCATENATE a_xstring wa_sdokcntbin-line

INTO a_xstring IN BYTE MODE.

ENDLOOP.

len = XSTRLEN( a_xstring ).

transform to solix tab - adding additional attachments

REFRESH lt_solix.

lt_solix =

cl_document_bcs=>xstring_to_solix(

ip_xstring = a_xstring ).

CLEAR l_filename.

Get the file name

l_filename = wa_file_access_info-file_name.

bcs_doc1 = cl_document_bcs=>create_document(

i_type = l_doc_type

i_subject = l_filename

i_length = len

i_language = sy-langu

i_hex = lt_solix

*Type casting

obj_file ?= bcs_doc1.

Add Attachment as a document to the email object

CALL METHOD l_email_object->add_document_as_attachment

EXPORTING

im_document = obj_file.

for text doxument

ELSEIF wa_file_access_info-mimetype = 'plain/text'.

l_doc_type = 'TXT'.

file_size = wa_file_access_info-file_size.

Get the file name

l_filename = wa_file_access_info-file_name.

CALL FUNCTION 'SCMS_BINARY_TO_TEXT'

EXPORTING

input_length = file_size

TABLES

binary_tab = it_sdokcntbin

text_tab = file_data

EXCEPTIONS

failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

CALL METHOD cl_document_bcs=>create_document

EXPORTING

i_type = l_doc_type

i_subject = l_filename

i_text = file_data

RECEIVING

result = bcs_doc1.

obj_file ?= bcs_doc1.

Add Attachment as a document to the email object

CALL METHOD l_email_object->add_document_as_attachment

EXPORTING

im_document = obj_file.

ENDIF.

CLEAR:it_file_access_info,it_sdokcntbin.

CLEAR:wa_object_id,wa_file_access_info,file_size.

ENDLOOP.

CALL METHOD send_request->set_document( l_email_object ).

*Get the address number for the vendor

SELECT SINGLE adrnr

FROM lfa1

INTO l_adrnr

WHERE lifnr = l_doc-xekko-lifnr.

MOVE l_adrnr TO l_addrno.

Get the Email Address for the vendor using address number

CALL FUNCTION 'ADDR_GET_REMOTE'

EXPORTING

addrnumber = l_addrno

TABLES

adsmtp = it_adsmtp

EXCEPTIONS

parameter_error = 1

address_not_exist = 2

internal_error = 3

OTHERS = 4.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

sender = cl_sapuser_bcs=>create( sy-uname ).

CALL METHOD send_request->set_sender

EXPORTING

i_sender = sender.

READ TABLE it_adsmtp INTO wa_adsmtp WITH KEY flg_nouse = 'X'.

IF sy-subrc = 0.

LOOP AT it_adsmtp INTO wa_adsmtp WHERE flg_nouse = 'X'.

CALL METHOD cl_cam_address_bcs=>create_internet_address

EXPORTING

i_address_string = wa_adsmtp-smtp_addr

RECEIVING

result = recipient.

CALL METHOD send_request->add_recipient

EXPORTING

i_recipient = recipient

i_express = 'X'.

ENDLOOP.

ELSE.

READ TABLE it_adsmtp INTO wa_adsmtp WITH KEY flgdefault = 'X'.

IF sy-subrc = 0.

CALL METHOD cl_cam_address_bcs=>create_internet_address

EXPORTING

i_address_string = wa_adsmtp-smtp_addr

RECEIVING

result = recipient.

CALL METHOD send_request->add_recipient

EXPORTING

i_recipient = recipient

i_express = 'X'.

ENDIF.

CALL METHOD send_request->send(

EXPORTING
i_with_error_screen = 'X'

RECEIVING

result = l_sent_to_all ).

CATCH cx_bcs INTO bcs_exception.

WRITE: 'Fehler aufgetreten.'(001).

WRITE: 'Fehlertyp:'(002), bcs_exception->error_type.

EXIT.

ENDTRY.

ENDIF.

Re: How to get the binary data into excel format?

Former Member — Wed, 19 Oct 2011 08:35:18 GMT

Hi Arun,

I have the same requirement as you had,

How did you solve it?

thanks in advance,

Aupalaura

Re: How to get the binary data into excel format?

Former Member — Thu, 11 Jul 2013 14:15:33 GMT

HI Arun,

We have a similar requirement to have the excel attachments sent through email. I did check with your code and see the excel file contents are corrupted. Can you please let me know how you solved the problem.

Thanks

Praveen

Re: How to get the binary data into excel format?

Former Member — Wed, 17 Jul 2013 19:19:05 GMT

I was able to send with all formats including excel

"----------------------------------------------------------------------

*"*"Lokale Schnittstelle:

*" IMPORTING

*" VALUE(PHIOS) TYPE SKWF_IOS

*" VALUE(COMMIT) TYPE SKWF_FLAG DEFAULT ' '

*" VALUE(USERS) TYPE SWDTUSER OPTIONAL

*" VALUE(EMAIL_ADDRESSES) TYPE BCSY_SMTPA OPTIONAL

*" VALUE(FAX_ADDRESSES) TYPE SDOK_FAX_RECIPIENTS OPTIONAL

*" VALUE(SUBJECT) TYPE SO_OBJ_DES OPTIONAL

*" VALUE(TEXT) TYPE SOLI_TAB OPTIONAL

*" VALUE(SENDER_ID) TYPE SYUNAME OPTIONAL

*" EXPORTING

*" VALUE(ERROR) TYPE SKWF_ERROR

*"----------------------------------------------------------------------

data: send_request type ref to cl_bcs.

data: document type ref to cl_document_bcs.

data: bcs_exception type ref to cx_bcs.

data: lv_recipient type ad_smtpadr.

data: lt_phio type skwf_ios.

data: ls_phio type skwf_io.

data: ls_error type skwf_error.

data: ls_file_access_info type sdokfilaci,

lt_file_access_info type sdokfilacis,

lt_file_content_ascii type table of sdokcntasc,

lt_file_content_binary type table of sdokcntbin.

data: lt_text type soli_tab.

data: lt_mail_text type soli_tab.

data: lv_text type soli.

data: lv_size type so_obj_len.

data: lv_size_i type i.

data: lv_buffer type xstring.

data: lv_buffer_txt type string.

data: lt_content type solix_tab.

data: lt_content_txt type soli_tab.

data: lv_type type soodk-objtp.

data: lv_subject type sood-objdes.

data: lv_length type i.

data: lt_attachments type bcsy_ifdoc.

data: lv_attachment type ref to if_document_bcs.

data: sent_to_all type os_boolean.

data: l_my_error_type type bcs_cxerr.

data: lr_recipient type ref to if_recipient_bcs.

data: lt_recipients type bcsy_re3.

data: ls_recipient type bcss_re3.

data: lv_email type ad_smtpadr.

data: lv_user type syuname.

data: ls_fax type sdok_fax_recipient.

data: sender TYPE REF TO cl_sapuser_bcs.

lt_phio[] = phios[].

try.

lv_text = 'Anlagen:'(001).

append lv_text to lt_text.

append initial line to lt_text.

* -> add content management documents as attachments

loop at lt_phio into ls_phio.

refresh: lt_file_content_ascii,

lt_file_content_binary,

lt_file_access_info.

data: ls_OBJECT_ID type SDOKOBJECT.

ls_object_id-class = 'DMS_PCD1'.

ls_object_id-objid = '51DA751424DF22A5E10000000A01B002'.

* --> get content of cm document

call function 'SDOK_PHIO_LOAD_CONTENT'

exporting

OBJECT_ID = ls_object_id

importing

error = ls_error

tables

file_access_info = lt_file_access_info

file_content_ascii = lt_file_content_ascii

file_content_binary = lt_file_content_binary.

if not ls_error is initial.

error = ls_error.

endif.

check not lt_file_access_info[] is initial.

read table lt_file_access_info

into ls_file_access_info index 1.

* --> append info text about current document to mail text

lv_size = ls_file_access_info-file_size.

condense lv_size.

concatenate '(' lv_size 'Byte'(002) ')' into lv_text.

concatenate ls_file_access_info-file_name

lv_text

into lv_text

separated by space.

append lv_text to lt_text.

* --> transform cm table (1022 bytes) into bcs table (255 bytes)

lv_size_i = ls_file_access_info-file_size.

refresh: lt_content, lt_content_txt.

if not ls_file_access_info-binary_flg is initial.

call function 'SCMS_BINARY_TO_XSTRING'

exporting

input_length = lv_size_i

importing

buffer = lv_buffer

tables

binary_tab = lt_file_content_binary.

call function 'SCMS_XSTRING_TO_BINARY'

exporting

buffer = lv_buffer

tables

binary_tab = lt_content.

else.

call function 'SCMS_FTEXT_TO_STRING'

exporting

length = lv_size_i

importing

ftext = lv_buffer_txt

tables

ftext_tab = lt_file_content_ascii.

call function 'SCMS_STRING_TO_FTEXT'

exporting

text = lv_buffer_txt

tables

ftext_tab = lt_content_txt.

endif.

* Get extension of file

data: l_file_parts type table of skwf_filnm,

l_file_ext type skwf_filnm,

l_i TYPE i,

l_ext TYPE i,

l_fn TYPE i.

split ls_file_access_info-file_name

at '.' into table l_file_parts. "#EC NOTEXT

describe table l_file_parts lines l_i.

clear l_file_ext.

if l_i > 1.

read table l_file_parts into l_file_ext index l_i.

* the extension will be added later,no need to provide it with filename

l_ext = STRLEN( l_file_ext ).

l_fn = STRLEN( ls_file_access_info-file_name ).

l_fn = l_fn - l_ext - 1.

if l_fn GT 0.

ls_file_access_info-file_name = ls_file_access_info-file_name+0(l_fn).

else.

clear ls_file_access_info-file_name.

endif.

translate l_file_ext to upper case. "#EC SYNTCHAR

lv_type = l_file_ext.

lv_subject = ls_file_access_info-file_name.

lv_size = ls_file_access_info-file_size.

call method cl_document_bcs=>create_document

exporting

i_type = lv_type

i_subject = lv_subject

i_length = lv_size

i_hex = lt_content

i_text = lt_content_txt

receiving

result = document.

append document to lt_attachments.

endloop.

if text[] is initial.

* -> append info text about documents to mail text

append lines of lt_text to lt_mail_text.

else.

lt_mail_text[] = text[].

endif.

* -> create recipients table

loop at email_addresses into lv_email.

lr_recipient = cl_cam_address_bcs=>create_internet_address( lv_email ).

clear ls_recipient.

ls_recipient-recipient = lr_recipient.

append ls_recipient to lt_recipients.

endloop.

loop at fax_addresses into ls_fax.

lr_recipient = cl_cam_address_bcs=>create_fax_address( i_country = ls_fax-country

i_number = ls_fax-number ).

clear ls_recipient.

ls_recipient-recipient = lr_recipient.

append ls_recipient to lt_recipients.

endloop.

loop at users into lv_user.

lr_recipient ?= cl_sapuser_bcs=>create( lv_user ).

clear ls_recipient.

ls_recipient-recipient = lr_recipient.

append ls_recipient to lt_recipients.

endloop.

if sy-batch NE 'X' .

* -> send screen

call method cl_bcs=>short_message

exporting

i_subject = subject

i_text = lt_mail_text

i_attachments = lt_attachments

i_recipients = lt_recipients

i_starting_at_x = 10

i_starting_at_y = 2

i_ending_at_y = 30

i_ending_at_x = 100

receiving

result = send_request.

if not commit is initial.

commit work.

endif.

else.

* the batch send

send_request = cl_bcs=>create_persistent( ).

document = cl_document_bcs=>create_document(

i_type = 'RAW'

i_text = lt_mail_text

i_subject = subject ).

CALL METHOD send_request->set_document( document ).

clear lv_attachment.

loop at lt_attachments into lv_attachment.

CALL METHOD DOCUMENT->ADD_DOCUMENT_AS_ATTACHMENT

EXPORTING

IM_DOCUMENT = lv_attachment

endloop.

clear lr_recipient.

loop at lt_recipients into ls_recipient .

CALL METHOD send_request->add_recipient

EXPORTING

i_recipient = ls_recipient-recipient

i_express = ls_recipient-sndex.

endloop.

if not sender_id is initial.

sender = cl_sapuser_bcs=>create( sender_id ).

CALL METHOD send_request->set_sender

EXPORTING i_sender = sender.

endif.

CALL METHOD send_request->send(

exporting

i_with_error_screen = ' '

receiving

result = sent_to_all ).

if sent_to_all NE 'X'.

MESSAGE I045(SKWG_ERRS).

* Das Dokument wurde nicht an alle Empfänger gesendet

endif.

if not commit is initial.

commit work.

endif.

* -> exception handling

CATCH CX_DOCUMENT_BCS cx_bcs INTO bcs_exception.

l_my_error_type = bcs_exception->error_type.

perform error_handling using l_my_error_type error.

exit.

endtry.

Re: How to get the binary data into excel format?

Former Member — Thu, 01 May 2014 14:02:52 GMT

Hi Arun,

As you mentioned in your earlier post that you are able to convert the word into PDF.

I could convert the data into word, txt and PDF files but i could not comvert the binary data into excel file.

Could you please help on this , how we convert a word file into PDF , if you have code can you please share with me .

It is really urgent for us !!!!

Thank you,

Srini