How to upload Excel to SAP(using ABAP)
Posted by Superadmin on September 05 2024 16:32:31

How to upload Excel to SAP(using ABAP)

Posted on: December 30, 2019 | By:  – 32 Comments

ABAP code to upload XLSX file to SAP using ABAP. The method shown in the blog will work in SAP GUI and Webgui (program launched from Fiori Launchpad). This approach will also work with OData.

Code is based on class CL_FDT_XL_SPREADSHEET which can be instantiated using the file name and excel file in XSTRING variable. This processing of file as XSTRING is particularly useful in case of Fiori Apps, OData CREATE_STREAM method pass uploaded file already in XSTRING which can be simply passed on to class CL_FDT_XL_SPREADSHEET constructor.

After instantiating the class get list of worksheets in the file using IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES. Method GET_WORKSHEET_NAMES returns internal table. In most of the case, you would read the first worksheet name and call method IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET with worksheet name to get data in a dynamic internal table. However, if you have data over multiple worksheets then you would have to call method GET_ITAB_FROM_WORKSHEET in the loop for each worksheet.

There are some function modules (TEXT_CONVERT_XLS_TO_SAP) available in SAP which can help you read data from XLS/XLSX file but these are based on Office Integration and requires MS Office installed on your PC. Also, this Office Integration solution (along with a range of other features) is not supported in WebGUI. In other words, these FMs will only work when you are running your program in SAP WebGUI.

I have structured the logic in standalone SE38 program so you can copy-paste and have it ready for testing in your system. Obviously, you would have to adapt the code to use it in your application but you get the idea of how it works.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
*---------------------------------------------------------------------*
* Report ZPW_EXCELUPLOAD
*---------------------------------------------------------------------*
REPORT zpw_excelupload.
 
FIELD-SYMBOLS : <gt_data>       TYPE STANDARD TABLE .
 
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME .
  PARAMETERS : p_file TYPE ibipparms-path OBLIGATORY,
               p_ncol TYPE i OBLIGATORY DEFAULT 10.
SELECTION-SCREEN END OF BLOCK b1 .
 
*--------------------------------------------------------------------*
* at selection screen
*--------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
 
  DATA: lv_rc TYPE i.
  DATA: lt_file_table TYPE filetable,
        ls_file_table TYPE file_table.
 
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title = 'Select a file'
    CHANGING
      file_table   = lt_file_table
      rc           = lv_rc.
  IF sy-subrc = 0.
    READ TABLE lt_file_table INTO ls_file_table INDEX 1.
    p_file = ls_file_table-filename.
  ENDIF.
 
START-OF-SELECTION .
 
  PERFORM read_file .
  PERFORM process_file.
 
*---------------------------------------------------------------------*
* Form READ_FILE
*---------------------------------------------------------------------*
FORM read_file .
 
  DATA : lv_filename      TYPE string,
         lt_records       TYPE solix_tab,
         lv_headerxstring TYPE xstring,
         lv_filelength    TYPE i.
 
  lv_filename = p_file.
 
  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.
 
  "convert binary data to xstring
  "if you are using cl_fdt_xl_spreadsheet in odata then skips this step
  "as excel file will already be in xstring
  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.
 
ENDFORM.
 
*---------------------------------------------------------------------*
* Form PROCESS_FILE
*---------------------------------------------------------------------*
FORM process_file .
 
  DATA : lv_numberofcolumns   TYPE i,
         lv_date_string       TYPE string,
         lv_target_date_field TYPE datum.
 
 
  FIELD-SYMBOLS : <ls_data>  TYPE any,
                  <lv_field> TYPE any.
 
  "you could find out number of columns dynamically from table <gt_data>
  lv_numberofcolumns = p_ncol .
 
  LOOP AT <gt_data> ASSIGNING <ls_data> FROM 2 .
 
    "processing columns
    DO lv_numberofcolumns TIMES.
      ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_field> .
      IF sy-subrc = 0 .
        CASE sy-index .
*          when 1 .
*          when 2 .
          WHEN 10 .
            lv_date_string = <lv_field> .
            PERFORM date_convert USING lv_date_string CHANGING lv_target_date_field .
            WRITE lv_target_date_field .
          WHEN OTHERS.
            WRITE : <lv_field> .
        ENDCASE .
      ENDIF.
    ENDDO .
    NEW-LINE .
  ENDLOOP .
ENDFORM.
 
*---------------------------------------------------------------------*
* Form DATE_CONVERT
*---------------------------------------------------------------------*
FORM date_convert USING iv_date_string TYPE string CHANGING cv_date TYPE datum .
 
  DATA: lv_convert_date(10) TYPE c.
 
  lv_convert_date = iv_date_string .
 
  "date format YYYY/MM/DD
  FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN lv_convert_date.
  IF sy-subrc = 0.
    CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
      EXPORTING
        date_in             = lv_convert_date
        date_format_in      = 'DYMD'
        to_output_format    = ' '
        to_internal_format  = 'X'
      IMPORTING
        date_out            = lv_convert_date
      EXCEPTIONS
        illegal_date        = 1
        illegal_date_format = 2
        no_user_date_format = 3
        OTHERS              = 4.
  ELSE.
 
    " date format DD/MM/YYYY
    FIND REGEX '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' IN lv_convert_date.
    IF sy-subrc = 0.
      CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
        EXPORTING
          date_in             = lv_convert_date
          date_format_in      = 'DDMY'
          to_output_format    = ' '
          to_internal_format  = 'X'
        IMPORTING
          date_out            = lv_convert_date
        EXCEPTIONS
          illegal_date        = 1
          illegal_date_format = 2
          no_user_date_format = 3
          OTHERS              = 4.
    ENDIF.
 
  ENDIF.
 
  IF sy-subrc = 0.
    cv_date = lv_convert_date .
  ENDIF.
 
ENDFORM .

Uploaded this excel file.

Below results from SAP GUI and WebGUI.

Share this: