Function Module ALSM_EXCEL_TO_INTERNAL_TABLE - Par...
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
Very straight forward: the name of your excel file and the number of the starting row and column and ending row and column. The result is stored in an internal table.
DATA: fname(40),
w_line TYPE i VALUE 1,
w_file TYPE rlgrap-filename.
DATA:
t_tab LIKE
TABLE OF ALSMEX_TABLINE
WITH HEADER LINE.
DATA: fs_tab LIKE LINE OF t_tab.
w_file = 'D:\Book.xls'.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = w_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 10
i_end_row = 100
tables
intern = t_tab
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3
.
IF sy-subrc 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
WRITE: 'UPLOAD SUCCESSFUL'.
ENDIF.
fname = '.\z_file.xls'.
OPEN DATASET fname FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
LOOP AT t_tab INTO fs_tab.
TRANSFER fs_tab TO fname.
ENDLOOP.
IF sy-subrc EQ 0.
WRITE: / 'FILE OPENED ON APPS SERVER'.
ELSE.
WRITE: / 'FILE COULD NOT BE OPENED'.
ENDIF.
Report z_alsm_excel_fm.
FIELD-SYMBOLS: <fs> TYPE ANY.
*Selection screen elements.............................................
PARAMETERS:
p_pfname TYPE rlgrap-filename
OBLIGATORY. " Presentation server File name
*"--------------------------------------------------------------------*
* Internal table declaration to hold excel file data *
*"--------------------------------------------------------------------*
DATA:
w_data LIKE
TABLE OF alsmex_tabline, " Copy of data of excel file
w_fname TYPE string,
w_check TYPE i,
*Workarea declaration
wa_excel LIKE LINE OF w_data. " Workarea for lw_data table
DATA:
t_bkpf LIKE STANDARD TABLE OF bkpf,
wa_bkpf LIKE LINE OF t_bkpf.
*---------------------------------------------------------------------*
* AT SELECTION-SCREEN ON VALUE-REQUEST FOR FIELD EVENT *
*---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_pfname.
" F4 help for file.
CALL FUNCTION 'F4_FILENAME'
* EXPORTING
* PROGRAM_NAME = SYST-CPROG
* DYNPRO_NUMBER = SYST-DYNNR
* FIELD_NAME = ' '
IMPORTING
file_name = p_pfname.
*---------------------------------------------------------------------*
* AT SELECTION-SCREEN EVENT *
*---------------------------------------------------------------------*
AT SELECTION-SCREEN.
PERFORM retrieve_file_data.
*---------------------------------------------------------------------*
* START-OF-SELECTION EVENT *
*---------------------------------------------------------------------*
*START-OF-SELECTION.
* PERFORM retrieve_file_data.
*---------------------------------------------------------------------*
* END-OF-SELECTION EVENT *
*---------------------------------------------------------------------*
END-OF-SELECTION.
PERFORM display_bkpf_data.
*&--------------------------------------------------------------------*
*& Form retrieve_file_data
*&--------------------------------------------------------------------*
* This subroutine is used to retrieve records from file
*---------------------------------------------------------------------*
* No interface parameters
*---------------------------------------------------------------------*
FORM retrieve_file_data .
DATA:
lw_text TYPE string, " Message text
lw_fname TYPE string, " Presentation file name
lw_ref TYPE REF TO cx_root. " Reference variable
TRY.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_pfname " File name
i_begin_col = 1 " Begin column
i_begin_row = 1 " Begin row
i_end_col = 1 " End column
i_end_row = 3 " End row
TABLES
intern = w_data " internal table to get the data
EXCEPTIONS " Uncomment the Exceptions
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno " Give type as 'S' instead of standard message type
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF. " IF SY-SUBRC NE 0
CATCH cx_root INTO lw_ref. " This is used to handle exception if any
lw_text = lw_ref->get_text( ).
MESSAGE lw_text TYPE 'W'.
ENDTRY.
CLOSE DATASET p_pfname.
ENDFORM. " retrieve_file_data
*&--------------------------------------------------------------------*
*& Form display_bkpf_data
*&--------------------------------------------------------------------*
* This subroutine is used to display retrieved bkpf data
*---------------------------------------------------------------------*
* No interface parameters
*---------------------------------------------------------------------*
FORM display_bkpf_data .
LOOP AT w_data INTO wa_excel.
AT NEW row.
SKIP.
WRITE:
/ wa_excel-row.
ENDAT.
WRITE:
/15 wa_excel-col,
wa_excel-value.
ENDLOOP. " LOOP AT W_DATA INTO WA_EXCEL
" Use the part of code if you want to populate data into an internal table in structure
* LOOP AT w_data INTO wa_excel.
*
* ASSIGN COMPONENT wa_excel-col OF STRUCTURE wa_bkpf TO <fs>.
*
* WRITE wa_excel-value TO <fs>.
*
* APPEND wa_bkpf TO t_bkpf.
*
* ENDLOOP.
*
* UNASSIGN <fs>.
*
* LOOP AT t_bkpf INTO wa_bkpf.
* WRITE:
* / wa_bkpf-bukrs,
* wa_bkpf-belnr.
** wa_bkpf-gjahr.
*
* ENDLOOP.
ENDFORM. " display_bkpf_data
Bluesky
