Cómo subir XLS ABAP

 

How to upload Excel to SAP(using ABAP)

Posted on: December 30, 2019 | By: Pawan Kesari – 30 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.

Fuente:

https://www.samplecodeabap.com/how-to-upload-excel-to-sapusing-abap/