Query on downloading to XLS file

[Former Member]

Hi ,

Iam facing some problem in downloading into xls format .

Here is an example of the query ,

data : OBJBIN LIKE SOLISTI1 OCCURS 10 WITH HEADER LINE

OBJBIN = 'ABC'.

APPEND OBJBIN.

OBJBIN = 'XYZ'.

APPEND OBJBIN.

OBJBIN = 'LMNO'.

APPEND OBJBIN.

using the fm SO_NEW_DOCUMENT_ATT_SEND_API1 iam trying to send the file as an attachtment .

but in the attachtment file all the three values displaying on a single cell .but need to be displayed in different cells.

can some one give a solution for this

Thanks ,

vinay .

[Former Member]

CALL FUNCTION 'CONVERT_OTF_2_PDF'

IMPORTING

bin_filesize = pdf_bytecount

TABLES

otf = otf_data

doctab_archive = lt_docs

lines = pdfout

EXCEPTIONS

err_conv_not_possible = 1

err_otf_mc_noendmarker = 2

OTHERS = 3.

CALL FUNCTION 'SX_TABLE_LINE_WIDTH_CHANGE'

TABLES

content_in = pdfout

content_out = pdfout2.

CLEAR: maildata, mailtxt, mailbin, mailpack, mailhead, mailrec.

REFRESH: mailtxt, mailbin, mailpack, mailhead, mailrec.

• Creation of the document to be sent File Name

maildata-obj_name = 'Order output'.

• Mail Subject

CONCATENATE 'Order :' vbdka-vbeln INTO maildata-obj_descr .

• Mail Contents

mailtxt-line = 'Order output '.

APPEND mailtxt.

• Set recipient - email address here!!!

SELECT SINGLE adrnr FROM kna1

INTO adrnr_sv

WHERE kunnr = vbdka-kunnr.

SELECT SINGLE *

FROM adrc

INTO adrc_sv

WHERE addrnumber = adrnr_sv.

SELECT SINGLE smtp_addr FROM adr6

INTO email

WHERE addrnumber = adrnr_sv

AND flgdefault = 'X'.

mailrec-receiver = email.

mailrec-rec_type = 'U'.

APPEND mailrec.

• Sending the document

CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

EXPORTING

document_data = maildata

• put_in_outbox = ' '

TABLES

packing_list = mailpack

object_header = mailhead

contents_bin = mailbin

contents_txt = mailtxt

receivers = mailrec

EXCEPTIONS

too_many_receivers = 1

document_not_sent = 2

operation_no_authorization = 4

OTHERS = 99.

[Former Member]

Hi naresh ,

This might be help full to me i feel ,but what the thing is just iam checking the mail only throught the

business work place .while checking through ,the attachtment sent is not the correct format .

Just gud me on this alone .

thanks ,

vinay .

[former_member188685]

Hi,

check this link..

http://www.sapdevelopment.co.uk/reporting/email/attach_xls.htm

Regards

vijay

[Former Member]

Hai Vinay

Try with the following Code for Internal Table Population Using GUI_UPLOAD

data : begin of it_attach occurs 0,

field1(4) type c,

field2(4) type c,

field3(4) type c,

end of it_attach.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'c:\upload.txt'

FILETYPE = 'ASC'

TABLES

DATA_TAB = it_attach.

if sy-subrc = 0.

loop at it_attach.

write 😕 it_attach-field1,

it_attach-field2,

it_attach-field3.

endloop.

endif.

flat file structure

ABC

XYZ

LMNO

Thanks & regards

Sreenivasulu P

[Former Member]

HI

GOOD

GO THROUGH THIS LINKS

https://weblogs.sdn.sap.com/pub/u/31132 [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken]

THANKS

MRUTYUN

[Former Member]

Hi

Have you taken care ( passed ) of all these table parameter's properly . please check.

PACKING_LIST

This table requires information on how the data from tables OBJECT_HEADER, CONTENTS_BIN and CONTENTS_TXT should be distributed to the document and its attachments. The first line stands for the document and each of the following lines for one attachment.

TRANSF_BIN

If this flag is set ('X'), the table entry indicates an object stored in binary format. The corresponding object contents are located in table CONTENTS_BIN. If the flag is not set, the object contents are located in table CONTENTS_TXT in ASCII format.

HEAD_START

Requires the starting line of table OBJECT_HEADER, starting from which the specific header data of the object is stored.

HEAD_NUM

Requires the number of lines in table OBJECT_HEADER which are used for the specific header data of the object. The lines involved are arranged in a block and uniquely defined together with the specification in HEAD_START.

BODY_START

Requires the starting lines of the tables dependent on the flag TRANSF_BIN, starting from which the object contents are stored.

BODY_NUM

Requires the number of lines of the table dependent on flag TRANSF_BIN which are used to store the object contents. The lines involved are arranged in a block and uniquely defined together with the specification in HEAD_START.

DOC_TYPE

Attachment type. This field is not used in the first line of the table.

OBJ_NAME

Attachment name. This field is not used in the first line of the table.

OBJ_DESCR

Title (short description) of the attachment. This field is not used in the first line of the table.

OBJ_LANGU

Attachment language. This field is not used in the first line of the table.

DOC_SIZE

Size of the attachment in bytes. The size of PC object files should be entered here, and the size of RAW and SCR objects is calculated by "length of last line" + "number of remaining lines multiplied by 255". This field is not used in the first line of the table.

MESS_TYPE

This field is not used.

OBJECT_HEADER

This table must contain the summarized data dependent on the respective object type. SAPscript objects save here, for example, information about layout sets, styles, and Excel Listviewer objects, including the number of lines and columns and the original file names of PC objects.

LINE

Requires the type-dependent data of the object for each line. Which section belongs to which object is determined by the fields HEAD_START and HEAD_NUM in table PACKING_LIST.

CONTENTS_BIN

This table must contain the summarized contents of objects flagged as binary objects.

LINE

Requires the contents of the objects for each line. Which section belongs to which object is determined by the fields BODY_START and BODY_NUM in table PACKING_LIST.

CONTENTS_TXT

This table must contain the summarized contents of the objects flagged as ASCII objects.

LINE

Requires the contents of the objects for each line. Which section belongs to which object is determined by the fields BODY_START and BODY_NUM in table PACKING_LIST.

OBJECT_PARA

This table is only needed if the document to be sent should be processed. It must contain the SET/GET parameters which are passed on to the processing element during processing.

It is not possible to assign processing parameters to attachments to documents.

NAME

Name of the SET/GET parameter. Only the first three characters are used.

OPTION

This field is not used.

LOW

Contains the value of the parameter in NAME.

HIGH

This field is not used.

OBJECT_PARB

This table is only needed if the document to be sent is assigned to a specific processing type. Its meaning changes depending on the type. If the processing element is a report or transaction which passes values to the global memory, then the table contents are interpreted as a quantity of parameters with corresponding values and transported to the memory ID listed on the first line. If the processing element is a function or dialog module, then the table is passed to these as table parameter MSGDIAL.

It is not possible to pass processing parameters to the attachments to documents.

NAME

If the processing element is a report or transaction which passes values to the global memory, then the field in the first table line must contain the name of the memory ID used for exporting. The fields in the remaining lines are used for the names of the parameters. If the processing element is a function or dialog module, then the fields must be filled accordingly.

VALUE

If the processing element is a report or transaction which passes values to the global memory, then the field in the first table line must remain empty. The fields in the remaining lines are used for parameter values belonging to NAME. If the processing element is a function or dialog module, then the fields must be filled accordingly.

RECEIVERS

This table must contain the recipients of the document.

RECEIVER

Name of the recipient.

The following entry types are possible:

the SAP user name of the recipient

the SAPoffice name of the recipient

a shared distribution list

a fax number in form of the structure SADRFD

an Internet address in form of the structure SADRUD

a remote SAP name in form of the structure SADR7D

an X.400 address in form of the structure SADR8D

ADR_TYPE

Type of RECEIVER entry.

The following values are allowed:

'B' : SAP user name

' ' : SAPoffice name

'C' : shared distribution list

'F' : fax number

'U' : Internet address

'R' : remote SAP name

'X' : X.400 address

REC_ID

If the recipient is a SAPoffice user, then the ID of the user can be entered in this field instead of the recipient name in RECEIVER.

REPLY_DOC

If this field is filled, then the document sent represents a reply to the folder entry flagged with the ID specified. A correspondence history is automatically created and maintained.

REC_DATE

Date on which the document should reach the recipient. No guarantee can be made that the date will be met for external recipients, as this depends on the performance of the products involved.

PROXY_ID

If the document recipient has the automatic forwarding function active, then this field contains the SAP user ID or the address ID of the external address to which this document was sent in the end.

RETRN_CODE

If the document recipient successfully receives the document, then the function module fills this field with the value '0', otherwise a value unequal to '0' is entered.

EXPRESS

If this flag is set ('X'), then the document is sent with the attribute 'Express'. If the recipient is a SAPoffice user who is logged on at the time, then this user immediately receives a message informing him/ her of the express mail.

COPY

If this flag is set ('X'), then the document is sent with the attribute 'Copy'.

BLIND_COPY

IF this flag is set ('X'), then the document is sent with the attribute 'Blind copy'. If the recipient is a SAPoffice user, then the recipient can neither print nor forward the document.

NO_FORWARD

If this flag is set ('X') and the recipient is a SAPoffice user, then the recipient cannot forward the document.

NO_PRINT

If this flag is set ('X') and the recipient is a SAPoffice user, then the recipient cannot print the document.

TO_ANSWER

If this flag is set ('X') and the recipient is a SAPoffice user, then the recipient has to send a reply to the document before he/she can delete it from his/her inbox.

TO_DO_EXPL

If this flag is set ('X') and the recipient is a SAPoffice user, then he/she has to process the document and set its status to 'Done' before it can be deleted from from his/her inbox.

TO_DO_GRP

If this field has a value between '1' and '9', then a SAPoffice user belonging to one of the recipient groups defined by these numbers has to process the document before the affected persons can delete it from their inboxes. If the value '0' is entered, then processing is not required.

COM_TYPE

Communication method with which the document should be sent. This field is only relevant when the recipient is an address number, that is, when the document is being sent via external address management. If the field is not filled in this case, then the standard communication method defined in address management will be used.

The followig values are allowed:

'INT' : Send via Internet

'FAX' : Send as fax

'X40' : Send via X.400

'RML' : Send in another SAP system

LFDNR

Serial number from address management. This field is only relevant when the recipient is an address number, that is, when the document is being sent via external address management. If the field is not filled in this case, then the serial number defined in external address management will be used.

FAX

This field is not used.

COUNTRY

This field is not used.

SPOOL_ID

This field is not used.

NOTIF_DEL

If this flag is set ('X'), then the sender receives confirmation as soon as the document is delivered to the recipient. The sender also receives a message if the document could not be delivered. It is only logical to set this flag when sending externally because the internal send process runs synchronously. Confirmation, however, is only supported by a few mail systems, including X.400 and SAP SAP.

NOTIF_READ

If this flag is set ('X'), then the sender is informed as soon as the recipient reads the document. Setting this flag is only logical when sending externally because the internal send process runs synchronously. The read confirmation, however, is only supported by a few mail systems, including X.400 and SAP SAP.

NOTIF_NDEL

If this flag is set ('X'), then the sender receives a message if the document could not be sent to a recipient. It is only logical to set this flag when sending externally because the internal send process runs synchronously. The message, however, is only supported by a few mail systems, including X.400 and SAP SAP.

SAP_BODY

If this flag is set ('X'), then the SAP specific data is included with the document in an external body part when sending via X.400. This is only logical when the target system is an SAP system.

Exceptions

[Former Member]

Hii Vinay

Try out these

lRecipients-Receiver = <theemailaddress>

lRecipients-Rec_Type = 'U'.

lRecipients-Com_Type = 'INT'.

lRecipients-Notif_Del = ' '.

lRecipients-Notif_nDel = ' '.

Append lRecipients.

if this doesnt solve ..explain your problem bit more clearly

Regards

Naresh

[Former Member]

Hi naresh ,

Here i have pasted the sample program .just execute as such and check the business work place ,and see in the attachment .

DATA: OBJPACK LIKE SOPCKLSTI1 OCCURS 2 WITH HEADER LINE.

• This table must contain the summarized data dependent on each object type.

• SAPscript objects store information here about forms and styles,

• for example. Excel list viewer objects store the number of rows and columns

• amongst other things and PC objects store their original file name.

DATA: OBJHEAD LIKE SOLISTI1 OCCURS 1 WITH HEADER LINE.

• This table must contain the summarized content of the objects identified as binary objects.

DATA: OBJBIN LIKE SOLISTI1 OCCURS 10 WITH HEADER LINE.

• This table must contain the summarized content of the objects identified as ASCII objects.

DATA: OBJTXT LIKE SOLISTI1 OCCURS 10 WITH HEADER LINE.

• This table must contain the document recipients.

DATA: RECLIST LIKE SOMLRECI1 OCCURS 5 WITH HEADER LINE.

• This structure must contain the attributes of the document to be sent.

DATA: DOC_CHNG LIKE SODOCCHGI1.

DATA: TAB_LINES LIKE SY-TABIX.

• Creating the document to be sent

DOC_CHNG-OBJ_NAME = 'OFFER'. " input contains the attributes of the document to be sent

DOC_CHNG-OBJ_DESCR = 'Program mail from narsh'. "input contains title/subject of the document

OBJTXT = 'Reserve price : $250000'. " Text in the mail

APPEND OBJTXT.

OBJTXT = 'A reproduction of the painting to be auctioned'. " Text in the mail

APPEND OBJTXT.

OBJTXT = 'is enclosed as an attachment.'. " Text in the mail

APPEND OBJTXT.

DESCRIBE TABLE OBJTXT LINES TAB_LINES.

READ TABLE OBJTXT INDEX TAB_LINES.

DOC_CHNG-DOC_SIZE = ( TAB_LINES - 1 ) * 255 + STRLEN( OBJTXT ).

• Creating the entry for the compressed document

CLEAR OBJPACK-TRANSF_BIN.

OBJPACK-HEAD_START = 1.

OBJPACK-HEAD_NUM = 0.

OBJPACK-BODY_START = 1.

OBJPACK-BODY_NUM = TAB_LINES.

OBJPACK-DOC_TYPE = 'RAW'.

APPEND OBJPACK.

• Creating the document attachment

• (Assume the data in OBJBIN are given in BMP format)

OBJBIN = 'ABC'.

APPEND OBJBIN.

OBJBIN = 'XYZ'.

APPEND OBJBIN.

OBJBIN = 'LMNO'.

APPEND OBJBIN.

DESCRIBE TABLE OBJBIN LINES TAB_LINES.

OBJHEAD = 'ABC.XLS'. APPEND OBJHEAD.

• Creating the entry for the compressed attachment

OBJPACK-TRANSF_BIN = 'X'.

OBJPACK-HEAD_START = 1.

OBJPACK-HEAD_NUM = 1.

OBJPACK-BODY_START = 1.

OBJPACK-BODY_NUM = TAB_LINES.

OBJPACK-DOC_TYPE = 'XLS'.

OBJPACK-OBJ_NAME = 'ATTACHMENT'.

OBJPACK-OBJ_DESCR = 'TEST'.

OBJPACK-DOC_SIZE = TAB_LINES * 255.

APPEND OBJPACK..

• Entering names in the distribution list

RECLIST-RECEIVER = 'Naresh'.

RECLIST-REC_TYPE = 'B'.

APPEND RECLIST.

*RECLIST-RECEIVER = 'DLI-NEUREICH'.

*

*RECLIST-REC_TYPE = 'P'.

*

*APPEND RECLIST.

• Sending the document

CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

EXPORTING

DOCUMENT_DATA = DOC_CHNG

PUT_IN_OUTBOX = 'X'

COMMIT_WORK = 'X'

TABLES

PACKING_LIST = OBJPACK

OBJECT_HEADER = OBJHEAD

CONTENTS_BIN = OBJBIN

CONTENTS_TXT = OBJTXT

RECEIVERS = RECLIST

EXCEPTIONS

TOO_MANY_RECEIVERS = 1

DOCUMENT_NOT_SENT = 2

OPERATION_NO_AUTHORIZATION = 4

OTHERS = 99.

CASE SY-SUBRC.

WHEN 0.

WRITE: / 'Result of the send process:'.

LOOP AT RECLIST.

WRITE: / RECLIST-RECEIVER(48), ':'.

IF RECLIST-RETRN_CODE = 0.

WRITE 'sent successfully'.

ELSE.

WRITE 'not sent'.

ENDIF.

ENDLOOP.

WHEN 1.

WRITE: / 'no authorization to send to the specified number of', 'recipients!'.

WHEN 2.

WRITE: / 'document could not be sent to any of the recipients!'.

WHEN 4.

WRITE: / 'no authorization to send !'.

WHEN OTHERS.

WRITE: / 'error occurred during sending !'.

ENDCASE.

[Former Member]

Hi naresh ,

can u able to get my query ,since its very urgent .

a solution from u would be very helpfull .

the output in the xls should be on the seperate cells.

Thanks ,

shankar.

[Former Member]

hii Vinay

I tried with your code ..its working fine for me..

in your excel..go to tools --> macro --> security

and change the setting to medium or less .

hope it will solve your problem..

Also please mention the solution once you solve the problem .

Please reward points if helpful

Regards

Naresh

[Former Member]

Hi narsh ,

In the XLS all the outputs are getting downloaded ,

correctly ,but the only thing is the all are in asingle cell.

but i need each and every fields in a seperate cell.

that is the query to be solved.

vinay

[Former Member]

Hii Vinay

The problem is when we append after describe lines ..have a look at this code if you can draw some idea ..

FORM build_xls_data_table.

CONSTANTS: con_cret TYPE x VALUE '0D', "OK for non Unicode

con_tab TYPE x VALUE '09'. "OK for non Unicode

*If you have Unicode check active in program attributes thnen you will

*need to declare constants as follows

*class cl_abap_char_utilities definition load.

*constants:

• con_tab type c value cl_abap_char_utilities=>HORIZONTAL_TAB,

• con_cret type c value cl_abap_char_utilities=>CR_LF.

CONCATENATE 'EBELN' 'EBELP' 'AEDAT' 'MATNR'

INTO it_attach SEPARATED BY con_tab.

CONCATENATE con_cret it_attach INTO it_attach.

APPEND it_attach.

LOOP AT it_ekpo INTO wa_charekpo.

CONCATENATE wa_charekpo-ebeln wa_charekpo-ebelp

wa_charekpo-aedat wa_charekpo-matnr

INTO it_attach SEPARATED BY con_tab.

CONCATENATE con_cret it_attach INTO it_attach.

APPEND it_attach.

ENDLOOP.

ENDFORM. " BUILD_XLS_DATA_TABLE

also dont forget to post the solution ..

and also reward points

Regards

Naresh

[Former Member]

DATA : DOC_CHNG LIKE SODOCCHGI1.

DATA: objpack TYPE SOPCKLSTI1 OCCURS 0 WITH HEADER LINE ,

objhead TYPE SOLISTI1 OCCURS 0 WITH HEADER LINE,

objbin TYPE SOLISTI1 OCCURS 0 WITH HEADER LINE,

objtxt TYPE SOLISTI1 OCCURS 0 WITH HEADER LINE,

reclist TYPE SOMLRECI1 OCCURS 0 WITH HEADER LINE .

DATA : TAB_LINES TYPE SYTABIX.

DATA : IT_ROLS TYPE STANDARD TABLE OF MARA,

WA_ROL TYPE MARA.

SELECT * FROM MARA

UP TO 5 ROWS

INTO TABLE IT_ROLS.

• Creation of the document to be sent

• File Name

doc_chng-obj_name = 'SENDFILE'.

• Mail Subject

doc_chng-obj_descr = 'Materials below the safety stock level ... '.

• Mail Contents

objtxt = 'Attachment below contain the details'.

APPEND objtxt.

DESCRIBE TABLE objtxt LINES tab_lines.

READ TABLE objtxt INDEX tab_lines.

doc_chng-doc_size = ( tab_lines - 1 ) * 255 + STRLEN( objtxt ).

• Creation of the entry for the compressed document

CLEAR objpack-transf_bin.

objpack-head_start = 1.

objpack-head_num = 0.

objpack-body_start = 1.

objpack-body_num = tab_lines.

objpack-doc_type = 'RAW'.

APPEND objpack.

• Creation of the document attachment

CONSTANTS:

con_tab TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,

con_cret TYPE c VALUE cl_abap_char_utilities=>cr_lf.

CONCATENATE 'Mat Type' 'Mat Grp.' 'MAT NO' 'Mat desc' 'Stock' 'UOM'

'Safety Stk' 'Min Safety stk' 'Val Stk' 'Store Loc'

'Plant' 'Busi. Area'

INTO objbin SEPARATED BY con_tab.

CONCATENATE con_cret objbin INTO objbin.

APPEND objbin.

• Making the attachment from internal table

LOOP AT it_rols INTO wa_rol.

• l_mbw = wa_rol-mbwbest.

• l_bas = wa_rol-basme.

• l_eisb = wa_rol-eisbe.

• l_eisl = wa_rol-eislo.

• l_wbw = wa_rol-wbwbest.

CONCATENATE wa_rol-mATNR

wa_rol-ERSDA

wa_rol-ERNAM

wa_rol-LAEDA

INTO objbin SEPARATED BY con_tab.

CONCATENATE con_cret objbin INTO objbin.

APPEND objbin.

ENDLOOP.

DESCRIBE TABLE objbin LINES tab_lines.

objhead = 'ORDERS'.

APPEND objhead.

• Creation of the entry for the compressed attachment

objpack-transf_bin = 'X'.

objpack-head_start = 1.

objpack-head_num = 1.

objpack-body_start = 1.

objpack-body_num = tab_lines.

objpack-doc_type = 'XLS'.

objpack-obj_name = 'WEBSITE'.

objpack-obj_descr = 'ORDERS.XLS'.

objpack-doc_size = tab_lines * 255.

APPEND objpack.

• Completing the recipient list

• target recipent

CLEAR reclist.

reclist-receiver = "give receiver mai id here"

reclist-express = 'X'.

reclist-rec_type = 'U'.

APPEND reclist.

• Sending the document

CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

EXPORTING

document_data = doc_chng

TABLES

packing_list = objpack

object_header = objhead

contents_bin = objbin

contents_txt = objtxt

receivers = reclist

EXCEPTIONS

too_many_receivers = 1

document_not_sent = 2

operation_no_authorization = 4

OTHERS = 99.

commit work.

IF SY-SUBRC = 0.

WAIT UP TO 2 SECONDS.

SUBMIT rsconn01 WITH mode = 'INT'

WITH output = 'X'

AND RETURN.

WRITE : 'MAIL SEND SUCCESSFULLY' .

regards,