ABAP to Upload Excel document into internal table on SAP system including .xlsx
ABAP code for uploading an Excel document into an internal table using function module KCD_EXCEL_OLE_TO_INT_CONVERT. The code is based on uploading a simple Excel spreadsheet. This code also works with the new MS excel format .xlsx. Be carefull though for some reason SAP have decided to use a table structure which only allows a length of 32 characters per spreadsheet cell.
*.............................................................. *: Description : *: ----------- : *: This is a simple example program to get data from an excel : *: file and store it in an internal table. : *: : *: Author : www.SAP Development : *: : *: SAP Version : 4.7 : *:............................................................: REPORT zupload_excel_to_itab NO STANDARD PAGE HEADING. TYPES: BEGIN OF t_datatab , col1(25) TYPE c, col2(30) TYPE c, col3(30) TYPE c, col4(30) TYPE c, col5(30) TYPE c, col6(30) TYPE c, col7(30) TYPE c, col8(30) TYPE c, col9(30) TYPE c, col10(30) TYPE c, col11(30) TYPE c, END OF t_datatab. DATA: it_datatab TYPE STANDARD TABLE OF t_datatab INITIAL SIZE 0, wa_datatab TYPE t_datatab. DATA : gd_scol TYPE i VALUE '1', gd_srow TYPE i VALUE '1', gd_ecol TYPE i VALUE '256', gd_erow TYPE i VALUE '65536'. DATA: it_tab TYPE filetable, gd_subrc TYPE i. *Selection screen definition SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001. PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'c:\test.xls' OBLIGATORY. " File Name SELECTION-SCREEN END OF BLOCK b1. *********************************************************************** * AT SELECTION-SCREEN AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. REFRESH: it_tab. CALL METHOD cl_gui_frontend_services=>file_open_dialog EXPORTING window_title = 'Select File' default_filename = '*.xls' multiselection = ' ' CHANGING file_table = it_tab rc = gd_subrc. LOOP AT it_tab INTO p_file. * so_fpath-sign = 'I'. * so_fpath-option = 'EQ'. * append so_fpath. ENDLOOP. *********************************************************************** * START-OF-SELECTION. START-OF-SELECTION. PERFORM upload_excel_file TABLES it_datatab USING p_file gd_scol gd_srow gd_ecol gd_erow. *********************************************************************** * END-OF-SELECTION. END-OF-SELECTION. LOOP AT it_datatab INTO wa_datatab. WRITE:/ wa_datatab-col1, wa_datatab-col2, wa_datatab-col3, wa_datatab-col4, wa_datatab-col5, wa_datatab-col6, wa_datatab-col7, wa_datatab-col8, wa_datatab-col9, wa_datatab-col10, wa_datatab-col11. ENDLOOP. *&--------------------------------------------------------------------* *& Form UPLOAD_EXCEL_FILE *&--------------------------------------------------------------------* * upload excel spreadsheet into internal table *---------------------------------------------------------------------* * -->P_TABLE Table to return excel data into * -->P_FILE file name and path * -->P_SCOL start column * -->P_SROW start row * -->P_ECOL end column * -->P_EROW end row *---------------------------------------------------------------------* FORM upload_excel_file TABLES p_table USING p_file p_scol p_srow p_ecol p_erow. DATA : lt_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE. * Has the following format: * Row number | Colum Number | Value * --------------------------------------- * i.e. 1 1 Name1 * 2 1 Joe DATA : ld_index TYPE i. FIELD-SYMBOLS : <fs>. * Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE' CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT' EXPORTING filename = p_file i_begin_col = p_scol i_begin_row = p_srow i_end_col = p_ecol i_end_row = p_erow TABLES intern = lt_intern EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 OTHERS = 3. IF sy-subrc <> 0. FORMAT COLOR COL_BACKGROUND INTENSIFIED. WRITE:/ 'Error Uploading file'. EXIT. ENDIF. IF lt_intern[] IS INITIAL. FORMAT COLOR COL_BACKGROUND INTENSIFIED. WRITE:/ 'No Data Uploaded'. EXIT. ELSE. SORT lt_intern BY row col. LOOP AT lt_intern. MOVE lt_intern-col TO ld_index. ASSIGN COMPONENT ld_index OF STRUCTURE p_table TO <fs>. MOVE lt_intern-value TO <fs>. AT END OF row. APPEND p_table. CLEAR p_table. ENDAT. ENDLOOP. ENDIF. ENDFORM. "UPLOAD_EXCEL_FILE
See here for links to other methods of uploading excel files to SAP
Related Articles
HEX codes and there associated ABAP declaration within SAP
Display files on SAP Application Server(UNIX)
Browse files on SAP Application Server(UNIX)
Downloading files to PC(Presentation Server)
Downloading files to SAP Application Server
Popup window to select a File using function module WS_FILENAME_GET
Get list of files within specific directory or SAP Application server
Directory selection for ABAP report using SAP method DIRECTORY_BROWSE
File Selection for ABAP report using SAP object method FILE_OPEN_DIALOG
Save file location popup on ABAP report selection screen using FILE_SAVE_DIALOG
ABAP Upload and download Function Modules in SAP
SAP File Selection Window - various methods of adding a file selection popup to your ABAP report
Upload Tab delimited file from PC into ABAP internal table
Upload and download files into and out of SAP or your PC
ABAP to check if file exists before downloading from SAP
SAP Upload Excel document into internal table
Upload Excel document into ABAP internal table
ABAP Uploading files from PC to SAP R/3 system
ABAP to Upload files from SAP Application Server
Upload Tab delimited file from PC into ABAP internal table
Upload Tab delimited file from application server into ABAP internal table
Example Excel Spreadsheet used to demo SAP upload and download using ABAP
Download file from SAP unix system to your desktop using transaction SXDA