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.
