-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathEXCEL_UPLOAD_PRESENTATION_SERVER.abap
215 lines (180 loc) · 6.25 KB
/
EXCEL_UPLOAD_PRESENTATION_SERVER.abap
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
212
213
214
215
*&---------------------------------------------------------------------*
*& Report ZEXCEL_UPLOAD
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZEXCEL_UPLOAD.
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 .
BREAK-POINT.
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 .