Eitan Rosenberg

August 25, 2015 1 minute read

Multi Line Cell In Excel Using CSV by Mail

Hi,

Searching the site using “Search operators”

site:scn.sap.com multi line cell in excel
Does produce some results but unfortunately not a full working program (I might be wrong on this one ) .

Also reading https://en.wikipedia.org/wiki/Comma-separated_values I noticed this example:

Based on this article I wrote program Y_R_EITAN_TEST_10_10 (attached) .

General description

The program create CSV string based on internal table input .

The program create HTML string based on internal table input .

The program also create a multi line cell (follow FORM get_long_text) .

Both strings are sent as mail attachments .

Technical

Local helper classes:
CL_CSV – help to create a valid CSV string (I hope I did not miss something….) .

CL_HTM – help to create HTML string .

Mail

The program use CL_BCS to send the mail .

Output

Attachment_CSV.csv as it appears in Excel (you have to play with the row height…..)

Attachment_HTM as it appears in the browser (IE)

Attachment_CSV.csv as it appears in LibreOffice (The row height is done automatically )

4 Comments

You must be Logged on to comment or reply to a post.

Former Member

March 17, 2017 at 11:06 pm

Good Article, but the attachment of the code is missing...

Saud Shaikh

April 27, 2023 at 9:24 am

can help to provide your program Y_R_EITAN_TEST_10_10

thanks!

Eitan Rosenberg

Blog Post Author

April 27, 2023 at 1:14 pm

Hi there,

I am retired now, see the comment at https://people.sap.com/rosenberg.eitan

You are in luck, I exported the code from SAP at some point.

The code got deleted from the site when SAP change the site.

Here is the code:

REPORT  y_r_eitan_test_10_10 .

*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_htm DEFINITION .

  PUBLIC SECTION.

    DATA: htm_string TYPE string .

    CONSTANTS: c_space TYPE string VALUE '&nbsp;' ."non-breaking space

    CLASS-METHODS: escape
      IMPORTING
        fragment TYPE any
      RETURNING
        value(escaped) TYPE string .

    METHODS: append
      IMPORTING
        fragment TYPE any .

  PRIVATE SECTION.

    METHODS: edit_value
      IMPORTING
        fragment TYPE any
      CHANGING
        buffer TYPE string .

ENDCLASS .                    "cl_htm DEFINITION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_htm IMPLEMENTATION .
*----------------------------------------------------------------------*
  METHOD: escape.

    DATA: unescaped TYPE string .

    unescaped = fragment .

    CALL METHOD cl_http_utility=>if_http_utility~escape_html
      EXPORTING
        unescaped = unescaped
      RECEIVING
        escaped   = escaped.

  ENDMETHOD.                    "escape
*----------------------------------------------------------------------*
  METHOD: append.

    DATA: buffer TYPE string .

    me->edit_value( EXPORTING fragment = fragment CHANGING buffer = buffer ) .

    CONDENSE buffer .

    CONCATENATE htm_string buffer INTO htm_string .

  ENDMETHOD.                    "append
*----------------------------------------------------------------------*
  METHOD edit_value.

    DATA: inttype TYPE inttype .

    DESCRIBE FIELD fragment TYPE inttype . " ABAP data type (C,D,N,...)

    DATA: edited TYPE so_text255 . " Text field length 255: texts

    WRITE fragment TO edited LEFT-JUSTIFIED .

    CASE inttype .
      WHEN 'I' OR 'P' .

        IF fragment LT 0 .
          WRITE fragment TO edited+1 NO-SIGN LEFT-JUSTIFIED .
          WRITE '-' TO edited+0(1) .
        ENDIF . " IF fragment LT 0

        buffer = edited .

      WHEN 'D' .
        buffer = edited .
      WHEN OTHERS .
        buffer = edited .
    ENDCASE .

  ENDMETHOD.                    "EDIT_VALUE
*----------------------------------------------------------------------*
ENDCLASS .                    "cl_htm IMPLEMENTATION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_csv DEFINITION .

  PUBLIC SECTION .

    CONSTANTS: cr_lf TYPE string VALUE '<cr_lf>'  .

    DATA: csv_string TYPE string .

    METHODS: append
      IMPORTING
        fragment TYPE any .

  PRIVATE SECTION .

    METHODS edit_value
      IMPORTING
        fragment TYPE any
      CHANGING
        buffer TYPE string .

ENDCLASS .                    "cl_csv DEFINITION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_csv IMPLEMENTATION .
*----------------------------------------------------------------------*
  METHOD: append .

    DATA: buffer TYPE string .

    DATA: strlen  TYPE i . " Data: strlen of type Integers

    DATA: inttype TYPE inttype .

    me->edit_value( EXPORTING fragment = fragment CHANGING buffer = buffer ) .

    IF buffer EQ cl_csv=>cr_lf .

      strlen = STRLEN( csv_string ) - 1 .

      REPLACE SECTION OFFSET strlen LENGTH 1 OF csv_string WITH ' ' .

      CONCATENATE csv_string cl_abap_char_utilities=>cr_lf INTO csv_string .

      RETURN .

    ENDIF . " IF buffer EQ /iclh/cl_dlm=>cr_lf

    CONDENSE buffer .

    DATA: quote TYPE abap_bool VALUE abap_false .

* Quote this value ?
    IF buffer CS '"' OR
       buffer CS ',' OR
       buffer CS cl_abap_char_utilities=>newline     OR
       buffer CS cl_abap_char_utilities=>cr_lf       OR
       buffer CS cl_abap_char_utilities=>horizontal_tab .
      quote = abap_true .
    ENDIF. " IF buffer CS '

    REPLACE ALL OCCURRENCES OF '"' IN buffer WITH '""' .

    IF quote EQ abap_true .
      CONCATENATE '"' buffer '"' INTO buffer .
    ENDIF . " IF quote EQ abap_true

    CASE inttype .
      WHEN 'N' .
        CONCATENATE '="' buffer '"' INTO buffer .
    ENDCASE .

    CONCATENATE csv_string buffer ',' INTO csv_string .

  ENDMETHOD .                    "APPEND
*----------------------------------------------------------------------*
  METHOD: edit_value.

    DATA: inttype TYPE inttype .

    DESCRIBE FIELD fragment TYPE inttype . " ABAP data type (C,D,N,...)

    DATA: edited TYPE so_text255 . " Text field length 255: texts

    CASE inttype .
      WHEN 'I' OR 'P' .

        WRITE fragment TO edited LEFT-JUSTIFIED .

        IF fragment LT 0 .
          WRITE fragment TO edited+1 NO-SIGN LEFT-JUSTIFIED .
          WRITE '-' TO edited+0(1) .
        ENDIF . " IF fragment LT 0

        REPLACE ',' IN edited WITH '' .

        buffer = edited .

      WHEN 'G' .
        buffer = fragment .
      WHEN OTHERS .
        WRITE fragment TO edited LEFT-JUSTIFIED .
        buffer = edited .
    ENDCASE .


  ENDMETHOD.                    "EDIT_VALUE

ENDCLASS .                    "cl_csv DEFINITION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*

TYPE-POOLS: abap .

TYPES: tp_sflight_tab TYPE TABLE OF sflight .

*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK block04 WITH FRAME .
PARAMETERS: p_sndr   TYPE adsmtp-smtp_addr OBLIGATORY .
PARAMETERS: p_rcpn   TYPE adsmtp-smtp_addr OBLIGATORY .
PARAMETERS: p_sbjct TYPE so_obj_des .
SELECTION-SCREEN END OF BLOCK block04 .
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*

INITIALIZATION.
  PERFORM at_initialization .

START-OF-SELECTION.
  PERFORM at_start_of_selection .

*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
FORM at_initialization .

  DATA: st_address TYPE bapiaddr3 .

  DATA: it_return TYPE bapirettab .

  CALL FUNCTION 'BAPI_USER_GET_DETAIL'
    EXPORTING
      username = sy-uname
    IMPORTING
      address  = st_address
    TABLES
      return   = it_return.

  p_sndr = st_address-e_mail .
  p_rcpn = st_address-e_mail .

  CONCATENATE 'Hi there-' sy-datum '-' sy-uzeit INTO p_sbjct .

  DATA: it_seltexts TYPE TABLE OF rsseltexts .
  data: st_seltexts LIKE LINE OF it_seltexts .

  st_seltexts-name = 'P_SNDR' .
  st_seltexts-kind = 'P' .
  st_seltexts-text = 'Sender mail '  .
  APPEND st_seltexts TO it_seltexts .

  st_seltexts-name = 'P_RCPN' .
  st_seltexts-kind = 'P' .
  st_seltexts-text = 'Recipient mail '  .
  APPEND st_seltexts TO it_seltexts .

  CALL FUNCTION 'SELECTION_TEXTS_MODIFY'
    EXPORTING
      program                     = sy-cprog
    TABLES
      seltexts                    = it_seltexts
    EXCEPTIONS
      program_not_found           = 1
      program_cannot_be_generated = 2
      OTHERS                      = 3.

ENDFORM .                    "at_initialization
*----------------------------------------------------------------------*
FORM at_start_of_selection .

  DATA: it_sflight TYPE tp_sflight_tab .

  PERFORM get_data_1
    CHANGING
      it_sflight .

  PERFORM mail_1_main
    USING
      it_sflight .

ENDFORM.                    "at_start_of_selection
*----------------------------------------------------------------------*
FORM get_data_1
  CHANGING
    it_sflight TYPE tp_sflight_tab .

  SELECT * FROM sflight INTO TABLE it_sflight
  UP TO 100 ROWS  .

ENDFORM.                                                    "get_data_1
*----------------------------------------------------------------------*
FORM get_long_text
  CHANGING
    long_text TYPE string .

  CLEAR long_text .

* Simulate function 'READ_TEXT'
* lines parameter needs to be converted to string

  DATA: line TYPE n LENGTH 2 .

  DO 5 TIMES .

    line = sy-index .

    CONCATENATE long_text 'Line.........' line cl_abap_char_utilities=>newline INTO long_text .

  ENDDO .

ENDFORM.                    "get_long_text
*----------------------------------------------------------------------*
FORM mail_1_main
  USING
    it_sflight TYPE tp_sflight_tab .

  DATA: it_solix TYPE solix_tab .
  DATA: attachment_subject TYPE so_obj_des .

  DATA: ob_document_bcs TYPE REF TO cl_document_bcs.

* Create the body .
  PERFORM mail_1_get_body
    CHANGING
      it_solix .

  CALL METHOD cl_document_bcs=>create_document
    EXPORTING
      i_type    = 'HTM'
      i_subject = p_sbjct
      i_hex     = it_solix
    RECEIVING
      result    = ob_document_bcs.

* Get CSV attachment content
  PERFORM mail_1_get_csv_attachment
    USING
      it_sflight
    CHANGING
      it_solix .

  attachment_subject = 'Attachment_CSV' .

  CALL METHOD ob_document_bcs->add_attachment
    EXPORTING
      i_attachment_type    = 'csv'
      i_attachment_subject = attachment_subject
      i_att_content_hex    = it_solix.

* Get HTM attachment content
  PERFORM mail_1_get_htm_attachment
    USING
      it_sflight
    CHANGING
      it_solix .

  attachment_subject = 'Attachment_HTM' .

  CALL METHOD ob_document_bcs->add_attachment
    EXPORTING
      i_attachment_type    = 'HTM'
      i_attachment_subject = attachment_subject
      i_att_content_hex    = it_solix.

  DATA: ob_bcs TYPE        REF TO cl_bcs.
  DATA: ob_sapuser_bcs     TYPE REF TO cl_sapuser_bcs.
  DATA: ob_cam_address_bcs TYPE REF TO cl_cam_address_bcs .

  ob_bcs = cl_bcs=>create_persistent( ).

* Generate a subject up to 255 byte long
* Not visible in sost .
  DATA: ip_subject TYPE string .

  ip_subject = p_sbjct .

  DATA: counter TYPE n LENGTH 2 .

  DO 50 TIMES .
    counter = sy-index .
    CONCATENATE ip_subject ' long subject: ' counter  INTO ip_subject RESPECTING BLANKS .
  ENDDO .

  CALL METHOD ob_bcs->set_message_subject
    EXPORTING
      ip_subject = ip_subject.

  CALL METHOD ob_bcs->set_document( ob_document_bcs ).

*  ob_sapuser_bcs = cl_sapuser_bcs=>create( sy-uname ).

  ob_cam_address_bcs = cl_cam_address_bcs=>create_internet_address( p_sndr ).

  CALL METHOD ob_bcs->set_sender
    EXPORTING
      i_sender = ob_cam_address_bcs.

  ob_cam_address_bcs = cl_cam_address_bcs=>create_internet_address( p_rcpn ).

  CALL METHOD ob_bcs->add_recipient
    EXPORTING
      i_recipient = ob_cam_address_bcs.

  DATA: result TYPE abap_bool .

  CALL METHOD ob_bcs->send
    EXPORTING
      i_with_error_screen = abap_true
    RECEIVING
      result              = result.

  COMMIT WORK .

ENDFORM .                    "mail_1_main
*----------------------------------------------------------------------*
FORM mail_1_get_body
  CHANGING
    it_solix TYPE solix_tab .

  CLEAR it_solix[] .

  DATA: ob_htm TYPE REF TO cl_htm .

  CREATE OBJECT ob_htm .

  CALL METHOD ob_htm->append
    EXPORTING
      fragment = : 'Date:' , cl_htm=>c_space , sy-datum , '<br>' ,
                   'Time:' , cl_htm=>c_space , sy-uzeit , '<br><br>' .

  CALL METHOD ob_htm->append
    EXPORTING
      fragment = : 'To: ' ,  p_rcpn , '<br><br>' .

  CALL METHOD ob_htm->append
    EXPORTING fragment = : 'This is the body of the mail message' , '<br><br>'  .

  CALL METHOD ob_htm->append
    EXPORTING
      fragment = : '<U>Hello world!</U>  '   ,
                   '<H3>Hello world!</H3>' , '<br><br>'  .

  CALL METHOD cl_bcs_convert=>string_to_solix
    EXPORTING
      iv_string = ob_htm->htm_string
    IMPORTING
      et_solix  = it_solix.

ENDFORM.                    "mail_1_get_body
*----------------------------------------------------------------------*
FORM mail_1_get_csv_attachment
  USING
    it_data  TYPE table
  CHANGING
    it_solix TYPE solix_tab .

  CLEAR it_solix[] .

  DATA: it_ddfields TYPE ddfields .
  FIELD-SYMBOLS: <st_ddfields> LIKE LINE OF it_ddfields .

* Get DDIC Information
  CALL METHOD cl_salv_ddic=>get_by_data
    EXPORTING
      data    = it_data
    RECEIVING
      t_dfies = it_ddfields.

  DATA: ob_csv TYPE REF TO cl_csv .

  CREATE OBJECT ob_csv .

  LOOP AT it_ddfields ASSIGNING <st_ddfields>  .

    ob_csv->append( fragment = <st_ddfields>-scrtext_m ) .

* Insert Extra long text headings .
* This is done just to test the long text behaviour
    IF 'SEATSOCC_F PAYMENTSUM ' CS <st_ddfields>-fieldname .
      ob_csv->append( fragment = 'Long text' ) .
      <st_ddfields>-dynpfld = abap_true .
    ELSE .
      <st_ddfields>-dynpfld = abap_false .
    ENDIF .

  ENDLOOP .

  ob_csv->append( cl_csv=>cr_lf ) .

  FIELD-SYMBOLS: <st_data>   TYPE ANY .
  FIELD-SYMBOLS: <component> TYPE ANY .

  DATA: dref TYPE REF TO data .

  CREATE DATA dref LIKE LINE OF it_data .

  ASSIGN dref->* TO <st_data> .

  DATA: long_text TYPE string .

  PERFORM get_long_text
    CHANGING
      long_text .

  LOOP AT it_data ASSIGNING <st_data> .

    LOOP AT it_ddfields ASSIGNING <st_ddfields>  .

      ASSIGN COMPONENT <st_ddfields>-lfieldname OF STRUCTURE <st_data> TO <component> .

      ob_csv->append( fragment = <component>  ) .

* Insert long text fragment
      IF <st_ddfields>-dynpfld EQ abap_true .
        ob_csv->append( fragment = long_text ) .
      ENDIF .

    ENDLOOP .

    ob_csv->append( fragment = cl_csv=>cr_lf ) .

  ENDLOOP .

  CALL METHOD cl_bcs_convert=>string_to_solix
    EXPORTING
      iv_string = ob_csv->csv_string
    IMPORTING
      et_solix  = it_solix.

ENDFORM.                    "mail_1_get_csv_attachment
*----------------------------------------------------------------------*
FORM mail_1_get_htm_attachment
  USING
    it_data  TYPE table
  CHANGING
    it_solix TYPE solix_tab .

  CLEAR it_solix[] .

  DATA: it_ddfields TYPE ddfields .
  FIELD-SYMBOLS: <st_ddfields> LIKE LINE OF it_ddfields .

* Get DDIC Information
  CALL METHOD cl_salv_ddic=>get_by_data
    EXPORTING
      data    = it_data
    RECEIVING
      t_dfies = it_ddfields.

  DATA: ob_htm TYPE REF TO cl_htm .

  CREATE OBJECT ob_htm .

  ob_htm->append( '<!DOCTYPE html>' ) .

* Some style (css)
  CALL METHOD ob_htm->append
    EXPORTING
      fragment = : '<style>' ,
                    'table, th, td {' ,
                    'border: 1px solid black ;'  ,
                    'border-collapse: collapse ;'  ,
                    'vertical-align: top ; ' ,
                    '}' ,
                    '</style>' .

  ob_htm->append( '<table>' ) .
  ob_htm->append( '<tr>' ) .

  LOOP AT it_ddfields ASSIGNING <st_ddfields>  .

    CALL METHOD ob_htm->append
     EXPORTING
       fragment = : '<th>' , <st_ddfields>-scrtext_m , '</th>' .

* Insert Extra long text headings .
* This is done just to test the long text behaviour
    IF 'SEATSOCC_F PAYMENTSUM ' CS <st_ddfields>-fieldname .
      CALL METHOD ob_htm->append
       EXPORTING
         fragment = : '<th>' , 'Long text' , '</th>' .
      <st_ddfields>-dynpfld = abap_true .
    ELSE .
      <st_ddfields>-dynpfld = abap_false .
    ENDIF .

  ENDLOOP .

  ob_htm->append( '</tr>' ) .

  FIELD-SYMBOLS: <st_data>   TYPE ANY .
  FIELD-SYMBOLS: <component> TYPE ANY .

  DATA: dref TYPE REF TO data .

  CREATE DATA dref LIKE LINE OF it_data .

  ASSIGN dref->* TO <st_data> .

  DATA: long_text TYPE string .

  PERFORM get_long_text
    CHANGING
      long_text .

  REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>newline IN long_text WITH '<BR>'  .

  LOOP AT it_data ASSIGNING <st_data> .

    ob_htm->append( '<tr>' ) .

    LOOP AT it_ddfields ASSIGNING <st_ddfields>  .

      ASSIGN COMPONENT <st_ddfields>-lfieldname OF STRUCTURE <st_data> TO <component> .

      CALL METHOD ob_htm->append
        EXPORTING
          fragment = : '<td>' , <component> , '</td>' .

* Insert long text fragment
      IF <st_ddfields>-dynpfld EQ abap_true .

        CALL METHOD ob_htm->append
          EXPORTING
            fragment = : '<td>' , long_text , '</td>' .

      ENDIF .

    ENDLOOP .

    ob_htm->append( '</tr>' ) .

  ENDLOOP .

  ob_htm->append( '</table>' ) .

  CALL METHOD cl_bcs_convert=>string_to_solix
    EXPORTING
      iv_string = ob_htm->htm_string
    IMPORTING
      et_solix  = it_solix.

ENDFORM.                    "mail_1_get_HTM_attachment
*----------------------------------------------------------------------*


*Selection texts
*----------------------------------------------------------
* P_RCPN D       .
* P_SBJCT D       .
* P_SNDR D       .

----------------------------------------------------------------------------------
Extracted by Mass Download version 1.5.2 - E.G.Mellodew. 1998-2018. Sap Release 701

Saud Shaikh

April 28, 2023 at 4:21 am

Thanks a lot! Appreciate your consideration to provide the code. Happy Retirement! Hope you enjoy the life ahead.

Multi Line Cell In Excel Using CSV by Mail

Assigned Tags