Excel attachments with more than 50 columns

[arpita_churi3]

Hi All,

I developed a program which generates list as an output with more than 50 columns.

Now user wants to send the data as an excel attachment. I developed a programs for sending excel attachment but the data was not exceeding 25 columns. I am using function SO_DOCUMENT_SEND_API1.

Thanks,

Arpita

[alok_patra]

Hi Arpita,

I think it is length of SOLISTI1 / SOLIX restricting the data, not the number of columns. Can you please post the code related to attachment ?

Thanks,

Alok

[arpita_churi3]

Thanks Alok.

Please find below code:

"fill the document data.

  xdocdata-doc_size = 1.

  "populate the subject/generic message attributes

  xdocdata-obj_langu = sy-langu .

  xdocdata-obj_name  = 'SAPRPT' .

  xdocdata-obj_descr = p_mtitle .

  xdocdata-sensitivty = lc_false.

*

  DESCRIBE TABLE pit_attach LINES lv_lines.

  lv_cnt = 1.

  DO lv_lines TIMES.

    "fill the document data and get size of attachment

    CLEAR xdocdata.

    READ TABLE pit_attach INDEX lv_cnt.

    lv_cnt = lv_cnt + 1.

    xdocdata-doc_size = ( lv_cnt - 1 ) * 255 + strlen( iattach ).

    xdocdata-obj_langu  = sy-langu.

    xdocdata-obj_name   = 'SAPRPT'.

    xdocdata-obj_descr  = p_mtitle.

    xdocdata-sensitivty = lc_false.

    CLEAR lt_attachment.

    REFRESH lt_attachment.

    lt_attachment[] = pit_attach[].

  ENDDO.

  "DESCRIBE the body OF the MESSAGE

  CLEAR ipacking_list.

  REFRESH ipacking_list.

  ipacking_list-transf_bin = space.

  ipacking_list-head_start = 1.

  ipacking_list-head_num = 0.

  ipacking_list-body_start = 1.

  DESCRIBE TABLE imessage LINES ipacking_list-body_num.

  ipacking_list-doc_type = 'RAW'.

  APPEND ipacking_list.

  "CREATE attachment notification

  ipacking_list-transf_bin = 'X'.

  ipacking_list-head_start = 1.

  ipacking_list-head_num   = 1.

  ipacking_list-body_start = 1.

  DESCRIBE TABLE lt_attachment[] LINES ipacking_list-body_num.

  ipacking_list-doc_type   =  p_format.

  ipacking_list-obj_descr  =  p_attdescription.

  ipacking_list-obj_name   =  p_filename.

  ipacking_list-doc_size   =  ipacking_list-body_num * 255.

  APPEND ipacking_list.

  "ADD the recipients email address

  CLEAR ireceivers.

  REFRESH ireceivers.

  ireceivers-receiver = gv_email.

  ireceivers-rec_type = 'U'.

  ireceivers-com_type = 'INT'.

  ireceivers-notif_del = 'X'.

  ireceivers-notif_ndel = 'X'.

  APPEND ireceivers.

CALL FUNCTION 'SO_DOCUMENT_SEND_API1'

    EXPORTING

      document_data              = xdocdata

      put_in_outbox              = 'X'

     sender_address             = 'WF-BATCH'

      commit_work                = 'X'

    TABLES

      packing_list               = ipacking_list

      contents_bin               = lt_attachment[]

      contents_txt               = imessage

      receivers                  = ireceivers

    EXCEPTIONS

      too_many_receivers         = 1

      document_not_sent          = 2

      document_type_not_exist    = 3

      operation_no_authorization = 4

      parameter_error            = 5

      x_error                    = 6

      enqueue_error              = 7

      OTHERS                     = 8.

  IF sy-subrc = 0.

    COMMIT WORK.

  ENDIF.

[alok_patra]

Hi Arpita,

You have to concatenate all excel data to a string. Then convert string to Solix format using CL_BCS_CONVERT->STRING_TO_SOLIX.

Refer Send Mail Greater than 255 Char | SCN

[Former Member]

Hi Arpita

I tried using the below class and method as interface and not the FM you mentioned.

oref_container   TYPE REF TO cl_gui_custom_container,

iref_control     TYPE REF TO i_oi_container_control,

iref_document    TYPE REF TO i_oi_document_proxy,

iref_spreadsheet TYPE REF TO i_oi_spreadsheet,

iref_error       TYPE REF TO i_oi_error.

Also pl specify when you use your program do you get an error if columns are more than 25 ?

Also refer link below .

I tried to fix the number of columns as 50 and then created a loop to read each of them one by one.

Let me know if this helps ?

https://wiki.scn.sap.com/wiki/display/Snippets/Read+multiple+sheets+of+an+Excel+file+into+SAP+throug...

CALL METHOD iref_document->get_spreadsheet_interface " Returns an Interface Reference

     EXPORTING

       no_flush        = ''

     IMPORTING

       error           = iref_error

       sheet_interface = iref_spreadsheet.

   IF iref_error->has_failed = 'X'.

     CALL METHOD iref_error->raise_message

       EXPORTING

         type = 'I'.

     LEAVE LIST-PROCESSING.

   ENDIF.

   CALL METHOD iref_spreadsheet->get_sheets " Get the Names of Worksheets from excel

   EXPORTING

     no_flush = ''

   IMPORTING

     sheets   = i_sheets

     error    = iref_error.

   IF iref_error->has_failed = 'X'.

     CALL METHOD iref_error->raise_message

       EXPORTING

         type = 'I'.

     LEAVE LIST-PROCESSING.

   ENDIF.

* <<CR001-SRDK902521 start ins

  CALL METHOD iref_spreadsheet->get_ranges_names

     EXPORTING

       no_flush = ' '

       updating = -1

     IMPORTING

       error    = iref_error

*     retcode  =

      ranges   = i_ranges.

* Now delete current range, in next step,

*   new range will be define.

  CALL METHOD iref_spreadsheet->delete_ranges

     EXPORTING

       ranges = i_ranges.

* <<CR001-SRDK902521 end ins

  LOOP AT i_sheets INTO wa_sheets.

     CALL METHOD iref_spreadsheet->select_sheet " Activate a single WorkSheet

       EXPORTING

         name  = wa_sheets-sheet_name " Worksheet name

       IMPORTING

         error = iref_error.

     IF iref_error->has_failed = 'X'.

       EXIT.

     ENDIF.

     CALL METHOD iref_spreadsheet->set_selection " Set a Selection

       EXPORTING

         top     = 1

         left    = 1

         rows    = 1

         columns = 1.

    DATA lv_rows TYPE i.

     CALL METHOD iref_spreadsheet->insert_range " Insert a New Range

         EXPORTING

           name     = 'Test'

           rows     = 1

           columns  = 2

           no_flush = ''

         IMPORTING

           error    = iref_error.

     IF iref_error->has_failed = 'X'.

       EXIT.

     ENDIF.

     CALL METHOD iref_spreadsheet->get_ranges_data " Get Name and Data for Range

       EXPORTING

         all      = abap_true

       IMPORTING

         contents = i_data

         error    = iref_error

       CHANGING

         ranges   = i_ranges.

     DELETE i_data WHERE value EQ space.

     IF NOT i_data[] IS INITIAL.

       REPLACE ALL OCCURRENCES OF  'Number of Records :'

       IN TABLE i_data WITH space.

       REPLACE ALL OCCURRENCES OF ','

       IN TABLE i_data WITH space.

       READ TABLE i_data INTO ls_data INDEX 1.

       IF sy-subrc = 0.

         MOVE ls_data-value TO lv_rows.

         add 7 to lv_rows.

       ENDIF.

     ENDIF.

    CALL METHOD iref_spreadsheet->insert_range " Insert a New Range

         EXPORTING

           name     = 'Test'

           rows     = lv_rows                     "<<CR001-SRDK902993 ins

         columns  = 50

           no_flush = ''

         IMPORTING

           error    = iref_error.

    IF iref_error->has_failed = 'X'.

       EXIT.

     ENDIF.

     REFRESH: i_data.

     CALL METHOD iref_spreadsheet->get_ranges_data " Get Name and Data for Range

       EXPORTING

         all      = abap_true

       IMPORTING

         contents = i_data

         error    = iref_error

       CHANGING

         ranges   = i_ranges.

     DELETE i_data WHERE value EQ space.

    CASE wa_sheets-sheet_name.

*===>" Fetching Header data from the worksheet

      WHEN 'Header'.

         DELETE i_data WHERE row BETWEEN 1 AND 7.

*===>" Fetching Header text data from the worksheet

      WHEN 'Header Text'.

         DELETE i_data WHERE row BETWEEN 1 AND 6.

*===>" Fetching Header Condition data from the worksheet

      WHEN 'Header Condition'.

         DELETE i_data WHERE row BETWEEN 1 AND 7.

*===>" Fetching Line item data from the worksheet

      WHEN 'Line item'.

         DELETE i_data WHERE row BETWEEN 1 AND 7.

*===>" Fetching Line item text data from the worksheet

      WHEN 'Item Text'.

         DELETE i_data WHERE row BETWEEN 1 AND 6.

*===>" Fetching Line item condition data from the worksheet

      WHEN 'Item Condition'.

         DELETE i_data WHERE row BETWEEN 1 AND 7.

*===>" Fetching Line item distribution data from the worksheet

      WHEN 'Distribution'.

         DELETE i_data WHERE row BETWEEN 1 AND 7.

     ENDCASE.

     CHECK i_data[] IS NOT INITIAL.

     DATA: lv_index         TYPE i.

     DATA: lv_percentage    TYPE i.

     DATA: lv_bdoccount     TYPE i.

   DATA : li_data          TYPE soi_generic_table.

     li_data[] = i_data[].

     DELETE ADJACENT DUPLICATES FROM li_data COMPARING row.

     DESCRIBE TABLE li_data[] LINES lv_bdoccount.

Regards

Vinita

[arpita_churi3]

Thanks Vinita..

It is not related to multiple sheets.

I am getting each row of the ineternal table as single line . then i am using below code to add delimiter.

LOOP AT t_get_spooldata INTO ls_data.

         REPLACE ALL  OCCURRENCES OF '|' IN 

            ls_data-line WITH cl_abap_char_utilities=>horizontal_tab.

          CONCATENATE   cl_abap_char_utilities=>cr_lf ls_data INTO iattach.

          APPEND  iattach.

          MODIFY t_get_spooldata FROM ls_data.

          CLEAR:ls_data.

ENDLOOP.

and after adding delimiter my Internal table has more than 50 columns.

and i want to send this internal table as excel attachment.

But if i checked in email attachment , It is taking few columns only.

Thanks,

Arpita

[Chintu6august]

Hi,

you can achieve the same by using CL_BCS and CL_DOCUMENT_BCS class method, more over Function Module SO_DOCUMENT_SEND_API1 is now obsolete.

Methods: CREATE_DOCUMENT . ADD_ATTACHMENT to create attachment.

use FM SCM_XSTRING_TO_BINARY , SCM_STRING_TO_XSTRING. " to convert internal table data

thanks!!