How to upload an excel file into SAP internal tables

Excel file

 1

Selection-Screen

 

Parameter to get excel file destination.

 2

Result:

 3

 

Codes:

 

** Job: Upload from excel into internal table..

report  zfile_upload_from_excel.

type-pools truxs.

parameter p_excel type rlgrapfilename

               default ‘C:\EXCEL_EXAMPLE.xls’

            obligatory.

* Excel Structure

typesbegin of itab,

       val1 type string,

       val2 type string,

       val3 type string,

       end of itab.

data lt_itab type standard table of itab,

       ls_itab type itab,

       it_type type truxs_t_text_data.

* Getting Excel File Destination

at selection-screen 

   on value-request for p_excel.

call function ‘F4_FILENAME’

 exporting

  field_name    ‘P_EXCEL’

 importing

  file_name     p_excel.

start-of-selection.

* Converting Excel File

call function ‘TEXT_CONVERT_XLS_TO_SAP’

 exporting

  i_tab_raw_data       it_type

  i_filename           p_excel

 tables

  i_tab_converted_data lt_itab[].

end-of-selection.

loop at lt_itab into ls_itab.

  write:/ ls_itabval1.

  write space.

  write ls_itabval2.

  write space.

  write ls_itabval3.

endloop.

FM to Create Transport Request

Function module to create transport request with code.

Import Parameters:

import

Result:

 result

Codes:

FUNCTION ZTB_CREATE_TRANSPORT_REQUEST.

*”———————————————————————-

*”*”Local Interface:

*”  IMPORTING

*”     REFERENCE(I_TYPE) TYPE  STRING

*”     REFERENCE(I_TEXT) TYPE  STRING OPTIONAL

*”     REFERENCE(I_OWNER) TYPE  STRING OPTIONAL

*”     REFERENCE(I_TARGET) TYPE  STRING

*”     REFERENCE(IT_USERS) TYPE  STRING_TABLE OPTIONAL

*”———————————————————————-

* iv_type = ‘W’ or iv_type = ‘K’

* W – Customized

* K – Workbench request

* iv_text   = Description of request

* iv_owner  = Owner of the request

* iv_target = Target system of request

* it_users  = Users who want to create task

typestrwbo_charflag(1type c.

typesbegin of trwbo_request_header.

include   structure e070.

typesas4text        like e07tas4text,

       as4text_filled type trwbo_charflag,

       client         like e070cclient,

       tarclient      like e070ctarclient,

       clients_filled type trwbo_charflag,

       tardevcl       like e070mtardevcl,

       devclass       like e070mdevclass,

       tarlayer       like e070mtarlayer,

       e070m_filled   type trwbo_charflag,

       end of   trwbo_request_header.

typestrwbo_request_headers type trwbo_request_header  occurs 0.

data:

“Import Data

lv_type   type trfunction,

lv_text   type as4text,

lv_owner  type as4user,

lv_target type tr_target,

lt_users  type scts_users,

ls_users  type scts_user,

“Export Data

ls_request_header type trwbo_request_header,

lt_task_headers   type trwbo_request_headers,

“General Data

lv_string type string.

lv_type   i_type.

lv_text   i_text.

lv_owner  i_owner.

if lv_owner is initial.

   lv_owner syuname.

endif.

lv_target i_target.

loop at it_users into lv_string.

  clear ls_users.

  ls_usersuser lv_string.

  ls_userstype lv_type.

  append ls_users to lt_users.

endloop.

call function ‘TR_INSERT_REQUEST_WITH_TASKS’

  exporting

    iv_type           lv_type

    iv_text           lv_text

    iv_owner          lv_owner

    iv_target         lv_target

*   it_attributes     =

    it_users          lt_users

*   iv_tardevcl       =

*   iv_devclass       =

*   iv_tarlayer       =

 importing

   es_request_header ls_request_header

   et_task_headers   lt_task_headers

* exceptions

*   insert_failed     = 1

*   enqueue_failed    = 2

*   others            = 3

.

Function to send html mail including html table.

This function provides developers to send html mails

pratically.

Import Parameters:

importparameters

Tables:

tables

Example IT_BODY data:

Line1: This is a test html mail

Line2: Thanks,

Example IT_TABLE_HEADER data:

IT_TABLE HEADER data must have only one line

Line1: Header1|Header2

Example IT_TABLE_ITEM data:

(2 row table )

Line1: val11|val12

Line2: val21|val22

Example IT_END data:

Line1:Regards

Result:

result

Source Code:

FUNCTION ZTB_SEND_HTML_MAIL.

*”———————————————————————-

*”*”Local Interface:

*”  IMPORTING

*”     REFERENCE(I_SUBJECT) TYPE  STRING OPTIONAL

*”     REFERENCE(I_SENDER) TYPE  STRING

*”  TABLES

*”      IT_BODY TYPE  STRING_TABLE OPTIONAL

*”      IT_TABLE_HEADER TYPE  STRING_TABLE OPTIONAL

*”      IT_TABLE_ITEM TYPE  STRING_TABLE OPTIONAL

*”      IT_END TYPE  STRING_TABLE OPTIONAL

*”      IT_CC TYPE  STRING_TABLE OPTIONAL

*”      IT_RECEIPENTS TYPE  STRING_TABLE

*”      ET_RETURN TYPE  STRING_TABLE OPTIONAL

*”———————————————————————-

* Mail Data

data send_request     type ref to cl_bcs                ,

       document         type ref to cl_document_bcs       ,

       text             type bcsy_text                    ,

       sender_id        type ref to if_sender_bcs         ,

       recipient        type ref to if_recipient_bcs      ,

       bcs_exception    type ref to cx_bcs                ,

       sent_to_all      type os_boolean                   ,

       conlength        type i                            ,

       conlengths       type so_obj_len                   ,

       result_content   type string                       ,

       content_length   type w3paramcont_len             ,

       content_type     type w3paramcont_type            ,

       return_code      type w3paramret_code             ,

       listobject       type table of abaplist            ,

       html_wa          type w3html                       ,

       html             type standard table of w3html     ,

       wa_rec           type ad_smtpadr                   ,

       bcs_message      type string                       ,

       tmp_str          type string                       .

data sender           type ad_smtpadr                   ,

       subject          type so_obj_des                   ,

       recepients       type bcsy_smtpa                   ,

       return           type table_of_strings             ,

       lt_receipents    type bcsy_smtpa                   ,

       lt_cc            type bcsy_smtpa                   ,

       e_subrc          type subrc                        ,

       lv_subject       type so_obj_des                   ,

       lt_html          type soli_tab                     ,

       ls_html          type soli.

data:  begin of itab occurs 0                             ,

       line(15)                                           ,

       end of itab                                        ,

       ls_itab       like line of itab                    ,

       lv_string     type string                          ,

       lv_tabix      type sytabix                        .

* Conversions

  sender i_sender.

* Mail Subject.

  lv_subject i_subject.

* Mail Body

  refresh lt_html.

  ls_html ‘<html> <body style=”background-color:#FFFFFF;”>’.

  append ls_html to lt_htmlclear ls_html.

  loop at it_body into lv_string.

    ls_html ‘<p>’.

    append ls_html to lt_htmlclear ls_html.

    ls_html lv_string.

    append ls_html to lt_htmlclear ls_html.

    ls_html ‘</p>’.

    append ls_html to lt_htmlclear ls_html.

  endloop.

* Table Header

  if it_table_header[] is not initial.

    read table it_table_header into lv_string index 1.

    if lv_string is not initial.

    split lv_string at‘|’ into table itab.

    endif.

    if itab[] is not initial.

      clearls_htmllv_string.

      concatenate ‘<table style=”MARGIN: 10px; font-size:10px;’

                  ‘” bordercolor=”#90EE90″ width=”750″ ‘

                  ‘border=”1″><tbody><tr>’ into lv_string.

      ls_html lv_stringclear lv_string.

      append ls_html to lt_html.

    endif.

    loop at itab into ls_itab.

      clear ls_html.

      ls_html ‘<th bgcolor=”#CCCCCC”>’.

      append ls_html to lt_html.

      clear ls_html.

      ls_html ls_itab.

      append ls_html to lt_html.

      clear ls_html.

      ls_html ‘</th>’.

      append ls_html to lt_html.

    endloop.

  endif.

 “Table Item

 if it_table_item[] is not initial.

    clearitabitab[].

    loop at it_table_item into lv_string.

      split lv_string at‘|’ into table itab.

      if itab[] is not initial.

         lv_tabix 1.

         loop at itab into ls_itab.

          clear ls_html.

          if lv_tabix 1.

             concatenate ‘<tr style=”background-color:#eeeeee;”><td>’ 

             ls_itab ‘</td>’ into ls_html.

          else.

              clear ls_html.

              concatenate ‘<td>’ ls_itab ‘</td>’ into ls_html.

          endif.

          append ls_html to lt_html.

          lv_tabix lv_tabix + 1.

         endloop.

      endif.

    endloop.

    clear ls_html.

    ls_html ‘</tbody> </table>’.

    append ls_html to lt_html.

 endif.

” Conclusion

 loop at it_end into lv_string.

    ls_html ‘<p>’.

    append ls_html to lt_htmlclear ls_html.

    ls_html lv_string.

    append ls_html to lt_htmlclear ls_html.

    ls_html ‘</p>’.

    append ls_html to lt_htmlclear ls_html.

 endloop.

 “End of mail..

 clear ls_html.

 ls_html ‘</body> </html>’.

 append ls_html to lt_html.

 “Receipents conv.

 loop at it_receipents into lv_string.

    clear wa_recwa_rec lv_string.

    append wa_rec to lt_receipents.

 endloop.

 “CC conv.

 loop at it_cc into lv_string.

    clear wa_recwa_rec lv_string.

    append wa_rec to lt_cc.

 endloop.

try.

   clear send_request .

   send_request cl_bcs=>create_persistent( ).

   clear document .

   document cl_document_bcs=>create_document

                                               i_type    ‘HTM’

                                                i_text    lt_html

                                                i_length  conlengths

                                                i_subject lv_subject ).

   call method send_request->set_documentdocument ).

   clear sender_id .

   sender_id =

          cl_cam_address_bcs=>create_internet_addresssender ).

   call method send_request->set_sender

     exporting

       i_sender sender_id.

   clear wa_rec .

   loop at lt_receipents into wa_rec .

     clear recipient .

     recipient cl_cam_address_bcs=>create_internet_address(

     wa_rec ).

     call method send_request->add_recipient

       exporting

         i_recipient recipient

         i_express   ‘X’.

   endloop .

   loop at lt_cc into wa_rec.

     clear recipient .

     recipient cl_cam_address_bcs=>create_internet_address(

     wa_rec ).

     call method send_request->add_recipient

       exporting

         i_recipient recipient

         i_express   ‘X’

         i_copy      ‘X’.

     endloop.

      call method send_request->set_status_attributes

        exporting

          i_requested_status ‘E’

          i_status_mail      ‘E’.

      call method send_request->set_send_immediately‘X’ ).

      call method send_request->send(

      exporting

      i_with_error_screen ‘X’

      receiving

      result sent_to_all ).

      if sent_to_all ‘X’.

        append ‘Mail sent successfully ‘ to et_return .

        e_subrc 0.

      else.

        e_subrc 4.

      endif.

      commit work.

    catch cx_bcs into bcs_exception.

      bcs_message bcs_exception->get_text( ).

      append bcs_message to et_return .

      exit.

  endtry.

ENDFUNCTION.