topic Re: attachment in excel in Application Development and Automation Discussions

attachment in excel

Former Member — Mon, 08 Nov 2004 11:46:27 GMT

Hi,

I want to send an excel file as an attachment with the mail.

I am usinf SAP standard Function module SO_NEW_DOCUMENT_ATT_SEND_API1.

I am geting an excel file, but it is not coming in a proper format.

I want it in Tab delimited format.

Thanks

Regards

Neelav

Re: attachment in excel

Former Member — Mon, 08 Nov 2004 13:20:54 GMT

Hi Neelav,

Use the following code to upload the excel sheet to SAP:

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = zzl_fname

filetype = 'BIN'

CHANGING

data_tab = zt_objcont

EXCEPTIONS

OTHERS = 0.

and this to calculate the size of the sheet for the packing list:

zzl_packline-transf_bin = 'X'.

zzl_packline-head_start = 1.

zzl_packline-head_num = 1.

zzl_packline-body_start = 1.

zzl_packline-body_num = <nr of lines in table>.

zzl_packline-doc_type = <XLS>.

zzl_packline-obj_name = <name>.

zzl_packline-obj_descr = <name>.

zzl_packline-doc_size = <nr of lines in table> * 255.

APPEND zzl_packline

TO pt_packlist.

Regards,

John.

Re: attachment in excel

Former Member — Tue, 09 Nov 2004 11:41:04 GMT

Hi John,

Thanks for the solution but this is not solving my issue exactly.

My problem is that I am getting data from the database table into an internal table. Now from this internal table, I have to generate an excel file and send this excel file. I have created a new funciton module to do the same and I have inserted hexadecimal '0A' for new line and and '09' for a new cell, but the excel file is not coming in a proper format.

The data is getting scattered in a haphazard manner.

Any poinetr to this problem?

Thanks in advance

Re: attachment in excel

Former Member — Tue, 09 Nov 2004 11:54:53 GMT

Hi,

try to save you file with ';' as the field separator and save it as file.csv. EXCEL can handle that format.

Hope this helps!

regards

Siggi

Re: attachment in excel

Former Member — Tue, 09 Nov 2004 12:11:16 GMT

Hi Neelav,

If you want to create a "real" Excel file (and not a CSV-file), you will have to use OLE to create the excel sheet first, which you can then upload into SAP and sent as email attachment.

Regards,

John.

Re: attachment in excel

Former Member — Mon, 24 Jan 2005 10:47:04 GMT

Hi Neelav,

Found this set of notes regarding sending Excel file attachments. We are doing the same thing and getting similar results, all fields going into one cell.

Did you get to a solution that worked ?

Any help gratefully received.

Many thanks

Steve Hammond

Re: attachment in excel

Former Member — Mon, 24 Jan 2005 22:47:49 GMT

Hi All,

To take an internal table and convert the data to be used as a attachment in an email, I did the following:

Note that I set sy-batch to allow function module SAP_CONVERT_TO_TXT_FORMAT to work if the program is run in background mode.

Also, concatenate carriage return and line feed to the converted data into objbin. Objbin is passed in as a table on function module SO_DOCUMENT_SEND_API1 for table CONTENTS_BIN.

I hope this helps.

Mike Vondran

eBay


form f_convert_data_to_excel.
   data: begin of crlf,
        x(1) type x value '0A',
      end of crlf.
   data: wa_batch like sy-batch.

* Program run in Foreground.
  if sy-batch is initial. 
    sy-batch = 'X'.
    wa_batch = 'X'.
  endif.

* Call table to text function module
  call function 'SAP_CONVERT_TO_TXT_FORMAT'
    exporting
      i_line_header        = 'X'
    tables
      i_tab_sap_data       = i_output_data
    changing
      i_tab_converted_data = wa_data
    exceptions
      conversion_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.

* Set back if changed
  if wa_batch = 'X'.
    sy-batch = ' '.
  endif.

* Append formated data and CRLF to objnin
  loop at wa_data into wa_format_data.
    concatenate wa_format_data crlf into objbin-line.
    append objbin.
  endloop.


endform.                    " F_CONVERT_DATA_TO_EXCEL

Re: attachment in excel

Former Member — Wed, 02 May 2007 16:01:50 GMT

I'm sure Neelav came to some resolution long ago, but I had a different solution and thought I might share it.

Instead of e-mailing tab-delimited spreadsheets, I e-mail .csv files. An advantage of this format is that double-clicking in MS Outlook causes Excel to open the attachment directly, without any import conversion prompts.

Problem 1 is converting the internal table to .csv format. I'd loop at the internal table and use this function module to convert each row:

FUNCTION z_csv_from_structure.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     REFERENCE(LR_STRUCT)
*"  EXPORTING
*"     REFERENCE(LW_CSV)
*"----------------------------------------------------------------------

  FIELD-SYMBOLS: <field>.

  DATA: lw_string(1023) TYPE c,
        lw_index LIKE sy-tabix.

* Init.
  CLEAR: lw_csv.

* For each field...
  DO.
    lw_index = sy-index.

*   Capture the first field.
    ASSIGN COMPONENT lw_index OF STRUCTURE lr_struct TO <field>.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.

*   Convert to text.
    CLEAR: lw_string.
    WRITE <field> TO lw_string LEFT-JUSTIFIED.
    SHIFT lw_string LEFT DELETING LEADING space.

*   Escape any double quotes in the text.
    WHILE lw_string CS '"'.
      REPLACE '"' WITH '(~~&!!@!!&~~)' INTO lw_string.
    ENDWHILE.
    WHILE lw_string CS '(~~&!!@!!&~~)'.
      REPLACE '(~~&!!@!!&~~)' WITH '""' INTO lw_string.
    ENDWHILE.

*   Encapsulate in double quotes if needed.
    IF lw_string CS ',' OR lw_string CS '"'.
      CONCATENATE '"' lw_string '"' INTO lw_string.
    ENDIF.

*   Comma-separate the values.
    IF lw_index = 1.
      lw_csv = lw_string.
    ELSE.
      CONCATENATE lw_csv lw_string INTO lw_csv SEPARATED BY ','.
    ENDIF.

  ENDDO.                             " for each field.

ENDFUNCTION.

I send the e-mail using SAPOffice function module SO_NEW_DOCUMENT_ATT_SEND_API1.

A challenge of using this function module is that the attached spreadsheet must be passed in a table of 256-character rows. That's rather narrow. My work-around is to treat the spreadsheet as a character stream, embed my own end-of-line markers in the stream (hex '0D0A' in non-Unicode systems), and chop the stream into 256-character chunks. In the e-mail API's attachment packing list, I specify document type 'CSV' and set binary transfer mode = 'X'.

(If binary transfer mode is off, SAP appends an EOL to each line of the table you pass it. Bad.)

Here's my code for converting one table of .csv lines into another table of 256-character rows. Note the .csv lines are already EOL-terminated.

  CLEAR: lr_attachment, lt_attachment, lw_buffer, lw_buflen, lw_index.
  IF NOT lt_spreadsheet[] IS INITIAL.
    DESCRIBE TABLE lt_spreadsheet LINES lw_tab_lines.
    WHILE lw_index < lw_tab_lines OR lw_buflen > 0.
      WHILE lw_buflen < lw_linlen AND lw_index < lw_tab_lines.
        ADD 1 TO lw_index.
        READ TABLE lt_spreadsheet INTO lr_spreadsheet INDEX lw_index.
        CONCATENATE lw_buffer lr_spreadsheet INTO lw_buffer.
        lw_buflen = strlen( lw_buffer ).
      ENDWHILE.
      CLEAR: lr_attachment.
      lr_attachment-line = lw_buffer.
      APPEND lr_attachment TO lt_attachment.
      SHIFT lw_buffer LEFT BY lw_linlen PLACES.
      lw_buflen = strlen( lw_buffer ).
    ENDWHILE.

For the rest, see the documentation of the e-mail function module.

Eric

Re: attachment in excel

Former Member — Wed, 02 May 2007 16:56:15 GMT

Hi,

Try with FM: 'SO_DOCUMENT_REPOSITORY_MANAGER'

Check the following example program:

http://www.sap-img.com/abap/sending-email-with-attachment.htm

Regards,

Bhaskar

Re: attachment in excel

anupam_anand — Thu, 23 Apr 2015 21:49:00 GMT

Hi,

Concatenate cl_abap_char_utilities=>cr_lf before every line/record of the file.

This should resolve the issue.

Thanks,

Anupam