Scripting: Uploading an Excel Spreadsheet on SAP GUI for windows 7.60

[pataselano]

Introduction

For posting shipping invoice in tcode MIRO/MIR7, user must select condition type manually which valid for that vendor invoice. Imagine if the user had to select dozens of rows (condition types) from hundreds of rows. Unfortunately, no BAPI/FM for do that. So, impossible for create ABAP program for speed up that process.

SAP Screen Personas scripting can speed up that process. Very easy for upload excel file on web gui using method in wiki – Scripting: Uploading an Excel Spreadsheet, but that method can not running on SAP GUI for Windows.

This blog will explain how to upload excel/txt file using SAP Screen Personas (3.0 sp 10) scripting on SAP GUI for Windows 7.60.

Note:

• Search help to search for the file name from local drive not working properly on SAP GUI for Windows 7.50.


• The security configuration for uploading from a local file must allow it to avoid the 'allow / deny' pop-up window.


• FM to read excel / txt file from local drive only runs on SAP GUI for Windows (both version 7.50 sp 10 and 7.60), but will get error if running on webgui because security configuration to upload from local file can't allow in webgui. So, the javascript debugger on webgui doesn't allow.

 

Main

Create search help for get file name

Step 1.

Refer to Personas 3.0 Search Help Scenario: Code triggered F4 Help pop-up by krishnakishor.kammaje2 (thank you for great blog), create FM (copy from FM F4IF_SHLP_EXIT_EXAMPLE) to search for the file name from local drive with coding as follows:

IF CALLCONTROL-STEP = 'DISP'.

*   PERFORM AUTHORITY_CHECK TABLES RECORD_TAB SHLP_TAB

*                           CHANGING SHLP CALLCONTROL.

    DATA: w_file_tab TYPE filetable,

          w1 LIKE LINE OF w_file_tab,

          w_rc TYPE i,

          ls_record_tab type SEAHLPRES.



    CALL METHOD cl_gui_frontend_services=>file_open_dialog

      EXPORTING

        default_extension       = 'txt'

        file_filter = |{ cl_gui_frontend_services=>FILETYPE_EXCEL } { cl_gui_frontend_services=>FILETYPE_TEXT }|

      CHANGING

        file_table              = w_file_tab

        rc                      = w_rc

      EXCEPTIONS

        file_open_dialog_failed = 1

        cntl_error              = 2

        error_no_gui            = 3

        not_supported_by_gui    = 4

        OTHERS                  = 5.

    IF sy-subrc <> 0.

      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ENDIF.

    READ TABLE w_file_tab INTO w1 INDEX 1.

    IF sy-subrc = 0.

      ls_record_tab-string = w1-filename.

      append ls_record_tab to record_tab.

    ENDIF.

    EXIT.

  ENDIF.

Step 2.



Create search help and assign the above FM as the search help ex

Step 2.

Create search help and assign the above FM as the search help exit.

Step 3.

Assign that custom search help using method in wiki – Using Search Helps.

 

Create RFC FM for read data from Excel/Txt file

Step 1.

with coding as follows:

FUNCTION ZRFC_FILE_TO_INTERNAL_TABLE.

*"----------------------------------------------------------------------

*"*"Local Interface:

*"  IMPORTING

*"     VALUE(P_FILENAME) LIKE  RLGRAP-FILENAME

*"     VALUE(P_COL) TYPE  I

*"     VALUE(P_ROW_DATA_START) TYPE  I DEFAULT 2

*"  TABLES

*"      LT_DATA STRUCTURE  MEDISPLCHLONG

*"----------------------------------------------------------------------

FIELD-SYMBOLS : <gt_data>  TYPE STANDARD TABLE,

                <ls_data>  TYPE any,

                <lv_field> TYPE any.



data: lv_filename          TYPE string,

      lv_data              TYPE MEDISPLCHLONG,

      lv_filelength        TYPE i,

      lt_records           TYPE solix_tab,

      lv_record            TYPE SOLIX,

      lv_headerxstring     TYPE xstring,

      lv_numberofcolumns   TYPE i,

      lv_date_string       TYPE string,

      lv_target_date_field TYPE datum,

      lt_file_data         TYPE table of text4096,

      lv_file_data         TYPE text4096.



                    .



  lv_filename = P_FILENAME.

  break ab_andy.



  if lv_filename cp '*.TXT'.

    CALL FUNCTION 'GUI_UPLOAD'

      EXPORTING

        FILENAME                      = lv_filename

       FILETYPE                      = 'ASC'

*     IMPORTING

*       FILELENGTH                    =

      TABLES

        DATA_TAB                      = lt_file_data

     EXCEPTIONS

       FILE_OPEN_ERROR               = 1

       FILE_READ_ERROR               = 2

       NO_BATCH                      = 3

       GUI_REFUSE_FILETRANSFER       = 4

       INVALID_TYPE                  = 5

       NO_AUTHORITY                  = 6

       UNKNOWN_ERROR                 = 7

       BAD_DATA_FORMAT               = 8

       HEADER_NOT_ALLOWED            = 9

       SEPARATOR_NOT_ALLOWED         = 10

       HEADER_TOO_LONG               = 11

       UNKNOWN_DP_ERROR              = 12

       ACCESS_DENIED                 = 13

       DP_OUT_OF_MEMORY              = 14

       DISK_FULL                     = 15

       DP_TIMEOUT                    = 16

       OTHERS                        = 17

              .

*    break ab_andy.

    IF SY-SUBRC <> 0.

*     Implement suitable error handling here

    else.

      loop at lt_file_data into lv_file_data.

        if sy-tabix < P_ROW_DATA_START.

          continue.

        endif.

        lv_data-tabtext = lv_file_data.

        append lv_data to lt_data.

      endloop.

    ENDIF.

  else.

    CALL FUNCTION 'GUI_UPLOAD'

      EXPORTING

        filename                = lv_filename

        filetype                = 'BIN'

      IMPORTING

        filelength              = lv_filelength

        header                  = lv_headerxstring

      TABLES

        data_tab                = lt_records

      EXCEPTIONS

        file_open_error         = 1

        file_read_error         = 2

        no_batch                = 3

        gui_refuse_filetransfer = 4

        invalid_type            = 5

        no_authority            = 6

        unknown_error           = 7

        bad_data_format         = 8

        header_not_allowed      = 9

        separator_not_allowed   = 10

        header_too_long         = 11

        unknown_dp_error        = 12

        access_denied           = 13

        dp_out_of_memory        = 14

        disk_full               = 15

        dp_timeout              = 16

        OTHERS                  = 17.



    CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'

      EXPORTING

        input_length = lv_filelength

      IMPORTING

        buffer       = lv_headerxstring

      TABLES

        binary_tab   = lt_records

      EXCEPTIONS

        failed       = 1

        OTHERS       = 2.



    IF sy-subrc <> 0.

      "Implement suitable error handling here

    ENDIF.



    DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .



    TRY .

        lo_excel_ref = NEW cl_fdt_xl_spreadsheet(

                                document_name = lv_filename

                                xdocument     = lv_headerxstring ) .

      CATCH cx_fdt_excel_core.

        "Implement suitable error handling here

    ENDTRY .

    "Get List of Worksheets

    lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(

      IMPORTING

        worksheet_names = DATA(lt_worksheets) ).



    IF NOT lt_worksheets IS INITIAL.

      READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.



      DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(

                                               lv_woksheetname ).

      "now you have excel work sheet data in dyanmic internal table

      ASSIGN lo_data_ref->* TO <gt_data>.

    ENDIF.



    "you could find out number of columns dynamically from table <gt_data>

    lv_numberofcolumns = p_col.



    LOOP AT <gt_data> ASSIGNING <ls_data> FROM P_ROW_DATA_START.



      "processing columns

      clear lv_data.

      DO lv_numberofcolumns TIMES.



        ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_field> .

        IF sy-subrc = 0 .

          if lv_data-tabtext is initial.

            lv_data-tabtext = <lv_field>.

          else.

            concatenate lv_data-tabtext <lv_field> into lv_data-tabtext separated by cl_abap_char_utilities=>horizontal_tab.

          endif.

        ENDIF.

      ENDDO .

      if lv_data is not initial.

        append lv_data to lt_data.

      endif.

    ENDLOOP .

  endif.











ENDFUNCTION.

Step 2.

Assign RFC FM using method in wiki – How to call Remote Enabled ABAP Function Modules in Personas 3.0.

 

Create Button Script

For process file, create ‘upload’ script button

with coding as follows:

var input = session.findById("wnd[0]/usr/ctxtPersonas_161000174728578").text;

input = input.replace(/\\/g, "\\\\");

var oRFC = session.createRFC("ZRFC_FILE_TO_INTERNAL_TABLE");

oRFC.setParameter("P_COL", "28"); //number of column

oRFC.setParameter("P_FILENAME", input);

oRFC.setParameter("P_ROW_DATA_START", "2"); //start of row data

oRFC.setParameter("LT_DATA", [{"TABTEXT":""}]);

oRFC.requestResults(["LT_DATA"]);

oRFC.send();

var _LT_DATA = oRFC.getResultObject("LT_DATA");

 

for (var index = 0; index < _LT_DATA.length; index++) {

var data = _LT_DATA[index].TABTEXT.split('\t');

//Input Invoice Date

		session.findById("wnd[0]/usr/subHEADER_AND_ITEMS:SAPLMR1M:6005/tabsHEADER/tabpHEADER_TOTAL/ssubHEADER_SCREEN:SAPLFDCB:0010/ctxtINVFO-BLDAT").text = data[1];		



session.findById("wnd[0]/usr/subHEADER_AND_ITEMS:SAPLMR1M:6005/tabsHEADER/tabpHEADER_TOTAL/ssubHEADER_SCREEN:SAPLFDCB:0010/ctxtINVFO-BLDAT").setFocus();

		session.findById("wnd[0]").sendVKey(0);



// continue coding for the next entry to another fields using the data in the data[] array

...........

}

 

Conclusion

Apart from using webgui, the SAP Screen Personas script can upload excel / txt files using the SAP GUI for Windows which is faster than the process on webgui.