Upload Excel file to internal table
There are a lot of SAP standard functions to upload Excel spreadsheet file into ABAP internal table.
They have some differences, for example: maximum length of returned cell value, transfer of data with or without clipboard, transfer of hidden columns, opening or not an instance of Excel application, etc.
They have some differences, for example: maximum length of returned cell value, transfer of data with or without clipboard, transfer of hidden columns, opening or not an instance of Excel application, etc.
This sample program should help you to test which function best suits your needs
and show you how to call the functions and take over the returned data.
Attention: all these functions have one thing common: they are not released for customers and SAP doesn't provide you support. Read the OSS note 933420. Use on your own risk.
Attention: all these functions have one thing common: they are not released for customers and SAP doesn't provide you support. Read the OSS note 933420. Use on your own risk.
You can use the sample program to test following functions:
- ALSM_EXCEL_TO_INTERNAL_TABLE
- FILE_READ_AND_CONVERT_SAP_DATA
- IMPORT_FROM_SPREADSHEET
- KCD_EXCEL_OLE_TO_INT_CONVERT
- TEXT_CONVERT_XLS_TO_SAP
- UPLOAD_XLS_FILE_2_ITAB
- OBJECT ORIENTED
*&---------------------------------------------------------------------*
*& Report ZS_EXCEL
*&
*&---------------------------------------------------------------------*
REPORT zs_excel LINE-SIZE 512.
TYPES:
tv_data(256) TYPE c,
BEGIN OF ts_data,
value_0001 TYPE tv_data,
value_0002 TYPE tv_data,
value_0003 TYPE tv_data,
value_0004 TYPE tv_data,
value_0005 TYPE tv_data,
value_0006 TYPE tv_data,
value_0007 TYPE tv_data,
value_0008 TYPE tv_data,
value_0009 TYPE tv_data,
value_0010 TYPE tv_data,
value_0011 TYPE tv_data,
value_0012 TYPE tv_data,
value_0013 TYPE tv_data,
value_0014 TYPE tv_data,
value_0015 TYPE tv_data,
value_0016 TYPE tv_data,
value_0017 TYPE tv_data,
value_0018 TYPE tv_data,
value_0019 TYPE tv_data,
value_0020 TYPE tv_data,
END OF ts_data,
tt_data TYPE TABLE OF ts_data,
tv_index(4) TYPE n.
CONSTANTS:
co_max_col TYPE i VALUE 20,
co_max_row TYPE i VALUE 9999.
*&---------------------------------------------------------------------*
*& SELECTION-SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_fname FOR FIELD pa_fname.
PARAMETERS pa_fname TYPE localfile OBLIGATORY LOWER CASE MEMORY ID fnm.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (83) tx_read0.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read1 FOR FIELD pa_read1.
PARAMETERS pa_read1 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read2 FOR FIELD pa_read2.
PARAMETERS pa_read2 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read3 FOR FIELD pa_read3.
PARAMETERS pa_read3 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read4 FOR FIELD pa_read4.
PARAMETERS pa_read4 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read5 FOR FIELD pa_read5.
PARAMETERS pa_read5 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read6 FOR FIELD pa_read6.
PARAMETERS pa_read6 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read7 FOR FIELD pa_read7.
PARAMETERS pa_read7 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
AT SELECTION-SCREEN OUTPUT.
tx_fname = 'Path and name of Excel to open'.
tx_read0 = 'Function:'.
tx_read1 = 'ALSM_EXCEL_TO_INTERNAL_TABLE'.
tx_read2 = 'FILE_READ_AND_CONVERT_SAP_DATA'.
tx_read3 = 'IMPORT_FROM_SPREADSHEET'.
tx_read4 = 'KCD_EXCEL_OLE_TO_INT_CONVERT'.
tx_read5 = 'TEXT_CONVERT_XLS_TO_SAP'.
tx_read6 = 'UPLOAD_XLS_FILE_2_ITAB'.
tx_read7 = 'OBJECT ORIENTED'.
*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
DATA:
lt_data TYPE tt_data.
IF pa_read1 = 'X'.
WRITE: / 'Function ALSM_EXCEL_TO_INTERNAL_TABLE'.
SKIP.
PERFORM excel_read1 CHANGING lt_data.
ELSEIF pa_read2 = 'X'.
WRITE: / 'Function FILE_READ_AND_CONVERT_SAP_DATA'.
SKIP.
PERFORM excel_read2 CHANGING lt_data.
ELSEIF pa_read3 = 'X'.
WRITE: / 'Function IMPORT_FROM_SPREADSHEET'.
SKIP.
PERFORM excel_read3 CHANGING lt_data.
ELSEIF pa_read4 = 'X'.
WRITE: / 'Function KCD_EXCEL_OLE_TO_INT_CONVERT'.
SKIP.
PERFORM excel_read4 CHANGING lt_data.
ELSEIF pa_read5 = 'X'.
WRITE: / 'Function TEXT_CONVERT_XLS_TO_SAP'.
SKIP.
PERFORM excel_read5 CHANGING lt_data.
ELSEIF pa_read6 = 'X'.
WRITE: / 'Function UPLOAD_XLS_FILE_2_ITAB'.
SKIP.
PERFORM excel_read6 CHANGING lt_data.
ELSEIF pa_read7 = 'X'.
WRITE: / 'OBJECT ORIENTED'.
SKIP.
PERFORM excel_read7 CHANGING lt_data.
ENDIF.
PERFORM itab_display USING lt_data.
*&---------------------------------------------------------------------*
*& Form excel_read1
*&---------------------------------------------------------------------*
FORM excel_read1 CHANGING pt_data TYPE tt_data.
DATA:
lt_excel TYPE STANDARD TABLE OF alsmex_tabline,
ls_excel TYPE alsmex_tabline,
lv_data TYPE tv_data,
lv_error TYPE string.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = pa_fname
i_begin_col = 1
i_begin_row = 1
i_end_col = co_max_col
i_end_row = co_max_row
TABLES
intern = lt_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
LOOP AT lt_excel INTO ls_excel.
lv_data = ls_excel-value.
PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
CHANGING pt_data.
ENDLOOP.
ENDFORM. "excel_read1
*&---------------------------------------------------------------------*
*& Form excel_read2
*&---------------------------------------------------------------------*
FORM excel_read2 CHANGING pt_data TYPE tt_data.
DATA:
lv_fname TYPE filename-fileintern,
lv_error TYPE string.
lv_fname = pa_fname.
CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'
EXPORTING
i_filename = lv_fname
i_servertyp = 'OLE2'
i_fileformat = 'XLS'
* I_FIELD_SEPERATOR =
* I_LINE_HEADER =
TABLES
i_tab_receiver = pt_data
EXCEPTIONS
file_not_found = 1
close_failed = 2
authorization_failed = 3
open_failed = 4
conversion_failed = 5
OTHERS = 6.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
ENDFORM. "excel_read2
*&---------------------------------------------------------------------*
*& Form excel_read3
*&---------------------------------------------------------------------*
FORM excel_read3 CHANGING pt_data TYPE tt_data.
DATA:
lv_url(256) TYPE c,
ls_range TYPE soi_dimension_item,
lt_range TYPE soi_dimension_table,
lt_excel TYPE soi_generic_table,
ls_excel TYPE soi_generic_item,
lv_data TYPE tv_data,
lv_col TYPE tv_index,
lv_row TYPE tv_index.
CONCATENATE 'file://' pa_fname INTO lv_url.
ls_range-row = 1.
ls_range-column = 1.
ls_range-rows = co_max_row.
ls_range-columns = co_max_col.
APPEND ls_range TO lt_range.
CALL FUNCTION 'IMPORT_FROM_SPREADSHEET'
EXPORTING
item_url = lv_url
document_type = 'Excel.Sheet'
TABLES
data_table = lt_excel
ranges = lt_range.
LOOP AT lt_excel INTO ls_excel.
lv_data = ls_excel-value.
lv_col = ls_excel-column.
lv_row = ls_excel-row.
PERFORM itab_insert_value USING lv_col lv_row lv_data
CHANGING pt_data.
ENDLOOP.
ENDFORM. "excel_read3
*&---------------------------------------------------------------------*
*& Form excel_read4
*&---------------------------------------------------------------------*
FORM excel_read4 CHANGING pt_data TYPE tt_data.
DATA:
lt_excel TYPE kcde_intern,
ls_excel TYPE kcde_intern_struc,
lv_data TYPE tv_data,
lv_error TYPE string.
CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = pa_fname
i_begin_col = 1
i_begin_row = 1
i_end_col = co_max_col
i_end_row = co_max_row
TABLES
intern = lt_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
LOOP AT lt_excel INTO ls_excel.
lv_data = ls_excel-value.
PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
CHANGING pt_data.
ENDLOOP.
ENDFORM. "excel_read4
*&---------------------------------------------------------------------*
*& Form excel_read5
*&---------------------------------------------------------------------*
FORM excel_read5 CHANGING pt_data TYPE tt_data.
DATA:
lt_raw_data(4096) TYPE c OCCURS 0,
lt_data TYPE STANDARD TABLE OF string,
lv_error TYPE string.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
* I_LINE_HEADER =
i_tab_raw_data = lt_raw_data
i_filename = pa_fname
TABLES
i_tab_converted_data = pt_data
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
ENDFORM. "excel_read5
*&---------------------------------------------------------------------*
*& Form excel_read6
*&---------------------------------------------------------------------*
FORM excel_read6 CHANGING pt_data TYPE tt_data.
DATA:
lv_error TYPE string.
CALL FUNCTION 'UPLOAD_XLS_FILE_2_ITAB'
EXPORTING
i_filename = pa_fname
TABLES
e_itab = pt_data
EXCEPTIONS
file_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
ENDFORM. "excel_read6
*&---------------------------------------------------------------------*
*& Form excel_read7
*&---------------------------------------------------------------------*
FORM excel_read7 CHANGING pt_data TYPE tt_data.
DATA:
lo_control TYPE REF TO i_oi_container_control,
lo_error TYPE REF TO i_oi_error,
lo_container TYPE REF TO cl_gui_custom_container,
lo_document TYPE REF TO i_oi_document_proxy,
lo_spreadsheet TYPE REF TO i_oi_spreadsheet,
lv_url TYPE char256,
lv_has_sheet TYPE i.
* 1. control get
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = lo_control
error = lo_error.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 2. container create
CREATE OBJECT lo_container
EXPORTING
* parent =
container_name = 'CONTAINER'
* style =
* lifetime = lifetime_default
* repid =
* dynnr =
* no_autodef_progid_dynnr =
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5
OTHERS = 6.
ASSERT sy-subrc = 0.
* 3. control init with container
CALL METHOD lo_control->init_control
EXPORTING
* dynpro_nr = SY-DYNNR
* gui_container = ' '
inplace_enabled = 'X'
* inplace_mode = 0
* inplace_resize_documents = ' '
inplace_scroll_documents = 'X'
* inplace_show_toolbars = 'X'
* no_flush = ' '
* parent_id = cl_gui_cfw=>dynpro_0
r3_application_name = 'Excel'
* register_on_close_event = ' '
* register_on_custom_event = ' '
* rep_id = SY-REPID
* shell_style = 1384185856
parent = lo_container
* name =
* autoalign = 'x'
IMPORTING
error = lo_error
EXCEPTIONS
javabeannotsupported = 1
OTHERS = 2.
ASSERT sy-subrc = 0.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 4. control get proxy
CALL METHOD lo_control->get_document_proxy
EXPORTING
* document_format = 'NATIVE'
document_type = soi_doctype_excel_sheet " 'Excel.Sheet'
* no_flush = ' '
* register_container = ' '
IMPORTING
document_proxy = lo_document
error = lo_error.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 5. document open
CONCATENATE 'FILE://' pa_fname INTO lv_url.
CALL METHOD lo_document->open_document
EXPORTING
* document_title = ' '
document_url = lv_url
* no_flush = ' '
open_inplace = 'X'
open_readonly = 'X'
* protect_document = ' '
* onsave_macro = ' '
* startup_macro = ''
* user_info =
IMPORTING
error = lo_error.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 6. spreadsheet check exists
CALL METHOD lo_document->has_spreadsheet_interface
* EXPORTING
* no_flush = ' '
IMPORTING
error = lo_error
is_available = lv_has_sheet.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
IF NOT lv_has_sheet IS INITIAL.
* 7. spreadsheet get
CALL METHOD lo_document->get_spreadsheet_interface
EXPORTING
no_flush = ' '
IMPORTING
error = lo_error
sheet_interface = lo_spreadsheet.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 8. data read
DATA:
lv_row_start TYPE i value 1, " first row
lv_row_block TYPE i value 100, " number of rows read in one block (range)
lv_row_empty TYPE i value 0, " count of empty rows at the end of block
lt_value TYPE soi_generic_table,
ls_value TYPE soi_generic_item,
lt_rangesdef TYPE soi_dimension_table,
ls_rangesdef TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
ls_data TYPE ts_data,
lv_col_index TYPE tv_index,
lv_fieldname TYPE string.
FIELD-SYMBOLS:
<fs_value> TYPE tv_data.
WHILE lv_row_empty < 5. " max empty rows
* range create
CLEAR ls_rangesdef.
CLEAR lt_rangesdef.
ls_rangesdef-row = lv_row_start.
ls_rangesdef-column = 1.
ls_rangesdef-rows = lv_row_block.
ls_rangesdef-columns = co_max_col.
INSERT ls_rangesdef INTO TABLE lt_rangesdef.
* data read from range
CALL METHOD lo_spreadsheet->get_ranges_data
EXPORTING
* no_flush = ' '
* all = ' '
* updating = -1
rangesdef = lt_rangesdef
IMPORTING
contents = lt_value
error = lo_error
CHANGING
ranges = lt_ranges.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* data takeover
LOOP AT lt_value INTO ls_value.
AT NEW row.
CLEAR ls_data.
ENDAT.
lv_col_index = ls_value-column.
CONCATENATE 'VALUE_' lv_col_index INTO lv_fieldname.
ASSIGN COMPONENT lv_fieldname OF STRUCTURE ls_data TO <fs_value>.
ASSERT sy-subrc = 0.
<fs_value> = ls_value-value.
AT END OF row.
APPEND ls_data TO pt_data.
IF ls_data IS INITIAL.
ADD 1 TO lv_row_empty.
ELSE.
CLEAR lv_row_empty.
ENDIF.
ENDAT.
ENDLOOP.
ADD lv_row_block TO lv_row_start.
ENDWHILE.
* data delete empty rows at the end
DATA:
lv_index TYPE sy-tabix.
lv_index = lines( pt_data ).
WHILE lv_index > 0.
READ TABLE pt_data INTO ls_data INDEX lv_index.
IF ls_data IS INITIAL.
DELETE pt_data INDEX lv_index.
ELSE.
EXIT.
ENDIF.
SUBTRACT 1 FROM lv_index.
ENDWHILE.
ELSE.
MESSAGE e323(bf00) WITH pa_fname RAISING file_error.
ENDIF.
* 9. final cleaning
IF NOT lo_spreadsheet IS INITIAL.
FREE lo_spreadsheet.
ENDIF.
IF NOT lo_document IS INITIAL.
CALL METHOD lo_document->close_document.
CALL METHOD lo_document->release_document.
FREE lo_document.
ENDIF.
IF NOT lo_control IS INITIAL.
CALL METHOD lo_control->destroy_control.
FREE lo_control.
ENDIF.
ENDFORM. "excel_read7
*&---------------------------------------------------------------------*
*& Form itab_insert_value
*&---------------------------------------------------------------------*
FORM itab_insert_value USING pi_col TYPE tv_index
pi_row TYPE tv_index
pi_value TYPE tv_data
CHANGING pt_data TYPE tt_data.
DATA:
lv_fieldname TYPE string.
FIELD-SYMBOLS:
<fs_data> TYPE ts_data,
<fs_value> TYPE tv_data.
CHECK pi_value <> ''.
WHILE pi_row > lines( pt_data ).
INSERT INITIAL LINE INTO TABLE pt_data.
ENDWHILE.
READ TABLE pt_data ASSIGNING <fs_data> INDEX pi_row.
ASSERT sy-subrc = 0.
CONCATENATE 'VALUE_' pi_col INTO lv_fieldname.
ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_data> TO <fs_value>.
ASSERT sy-subrc = 0.
<fs_value> = pi_value.
ENDFORM. "itab_insert_value
*&---------------------------------------------------------------------*
*& Form itab_display
*&---------------------------------------------------------------------*
FORM itab_display USING pt_data TYPE tt_data.
DATA:
lv_count TYPE i,
ls_data TYPE ts_data,
lv_value TYPE tv_data.
lv_count = lines( pt_data ).
WRITE: / 'Number of lines', lv_count.
SKIP.
LOOP AT pt_data INTO ls_data.
WRITE / ''. " sy-tabix.
DO co_max_col TIMES VARYING lv_value FROM ls_data-value_0001
NEXT ls_data-value_0002.
WRITE: lv_value(10).
ENDDO.
ENDLOOP.
ENDFORM. "itab_display
*& Report ZS_EXCEL
*&
*&---------------------------------------------------------------------*
REPORT zs_excel LINE-SIZE 512.
TYPES:
tv_data(256) TYPE c,
BEGIN OF ts_data,
value_0001 TYPE tv_data,
value_0002 TYPE tv_data,
value_0003 TYPE tv_data,
value_0004 TYPE tv_data,
value_0005 TYPE tv_data,
value_0006 TYPE tv_data,
value_0007 TYPE tv_data,
value_0008 TYPE tv_data,
value_0009 TYPE tv_data,
value_0010 TYPE tv_data,
value_0011 TYPE tv_data,
value_0012 TYPE tv_data,
value_0013 TYPE tv_data,
value_0014 TYPE tv_data,
value_0015 TYPE tv_data,
value_0016 TYPE tv_data,
value_0017 TYPE tv_data,
value_0018 TYPE tv_data,
value_0019 TYPE tv_data,
value_0020 TYPE tv_data,
END OF ts_data,
tt_data TYPE TABLE OF ts_data,
tv_index(4) TYPE n.
CONSTANTS:
co_max_col TYPE i VALUE 20,
co_max_row TYPE i VALUE 9999.
*&---------------------------------------------------------------------*
*& SELECTION-SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_fname FOR FIELD pa_fname.
PARAMETERS pa_fname TYPE localfile OBLIGATORY LOWER CASE MEMORY ID fnm.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (83) tx_read0.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read1 FOR FIELD pa_read1.
PARAMETERS pa_read1 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read2 FOR FIELD pa_read2.
PARAMETERS pa_read2 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read3 FOR FIELD pa_read3.
PARAMETERS pa_read3 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read4 FOR FIELD pa_read4.
PARAMETERS pa_read4 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read5 FOR FIELD pa_read5.
PARAMETERS pa_read5 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read6 FOR FIELD pa_read6.
PARAMETERS pa_read6 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read7 FOR FIELD pa_read7.
PARAMETERS pa_read7 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.
AT SELECTION-SCREEN OUTPUT.
tx_fname = 'Path and name of Excel to open'.
tx_read0 = 'Function:'.
tx_read1 = 'ALSM_EXCEL_TO_INTERNAL_TABLE'.
tx_read2 = 'FILE_READ_AND_CONVERT_SAP_DATA'.
tx_read3 = 'IMPORT_FROM_SPREADSHEET'.
tx_read4 = 'KCD_EXCEL_OLE_TO_INT_CONVERT'.
tx_read5 = 'TEXT_CONVERT_XLS_TO_SAP'.
tx_read6 = 'UPLOAD_XLS_FILE_2_ITAB'.
tx_read7 = 'OBJECT ORIENTED'.
*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
DATA:
lt_data TYPE tt_data.
IF pa_read1 = 'X'.
WRITE: / 'Function ALSM_EXCEL_TO_INTERNAL_TABLE'.
SKIP.
PERFORM excel_read1 CHANGING lt_data.
ELSEIF pa_read2 = 'X'.
WRITE: / 'Function FILE_READ_AND_CONVERT_SAP_DATA'.
SKIP.
PERFORM excel_read2 CHANGING lt_data.
ELSEIF pa_read3 = 'X'.
WRITE: / 'Function IMPORT_FROM_SPREADSHEET'.
SKIP.
PERFORM excel_read3 CHANGING lt_data.
ELSEIF pa_read4 = 'X'.
WRITE: / 'Function KCD_EXCEL_OLE_TO_INT_CONVERT'.
SKIP.
PERFORM excel_read4 CHANGING lt_data.
ELSEIF pa_read5 = 'X'.
WRITE: / 'Function TEXT_CONVERT_XLS_TO_SAP'.
SKIP.
PERFORM excel_read5 CHANGING lt_data.
ELSEIF pa_read6 = 'X'.
WRITE: / 'Function UPLOAD_XLS_FILE_2_ITAB'.
SKIP.
PERFORM excel_read6 CHANGING lt_data.
ELSEIF pa_read7 = 'X'.
WRITE: / 'OBJECT ORIENTED'.
SKIP.
PERFORM excel_read7 CHANGING lt_data.
ENDIF.
PERFORM itab_display USING lt_data.
*&---------------------------------------------------------------------*
*& Form excel_read1
*&---------------------------------------------------------------------*
FORM excel_read1 CHANGING pt_data TYPE tt_data.
DATA:
lt_excel TYPE STANDARD TABLE OF alsmex_tabline,
ls_excel TYPE alsmex_tabline,
lv_data TYPE tv_data,
lv_error TYPE string.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = pa_fname
i_begin_col = 1
i_begin_row = 1
i_end_col = co_max_col
i_end_row = co_max_row
TABLES
intern = lt_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
LOOP AT lt_excel INTO ls_excel.
lv_data = ls_excel-value.
PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
CHANGING pt_data.
ENDLOOP.
ENDFORM. "excel_read1
*&---------------------------------------------------------------------*
*& Form excel_read2
*&---------------------------------------------------------------------*
FORM excel_read2 CHANGING pt_data TYPE tt_data.
DATA:
lv_fname TYPE filename-fileintern,
lv_error TYPE string.
lv_fname = pa_fname.
CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'
EXPORTING
i_filename = lv_fname
i_servertyp = 'OLE2'
i_fileformat = 'XLS'
* I_FIELD_SEPERATOR =
* I_LINE_HEADER =
TABLES
i_tab_receiver = pt_data
EXCEPTIONS
file_not_found = 1
close_failed = 2
authorization_failed = 3
open_failed = 4
conversion_failed = 5
OTHERS = 6.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
ENDFORM. "excel_read2
*&---------------------------------------------------------------------*
*& Form excel_read3
*&---------------------------------------------------------------------*
FORM excel_read3 CHANGING pt_data TYPE tt_data.
DATA:
lv_url(256) TYPE c,
ls_range TYPE soi_dimension_item,
lt_range TYPE soi_dimension_table,
lt_excel TYPE soi_generic_table,
ls_excel TYPE soi_generic_item,
lv_data TYPE tv_data,
lv_col TYPE tv_index,
lv_row TYPE tv_index.
CONCATENATE 'file://' pa_fname INTO lv_url.
ls_range-row = 1.
ls_range-column = 1.
ls_range-rows = co_max_row.
ls_range-columns = co_max_col.
APPEND ls_range TO lt_range.
CALL FUNCTION 'IMPORT_FROM_SPREADSHEET'
EXPORTING
item_url = lv_url
document_type = 'Excel.Sheet'
TABLES
data_table = lt_excel
ranges = lt_range.
LOOP AT lt_excel INTO ls_excel.
lv_data = ls_excel-value.
lv_col = ls_excel-column.
lv_row = ls_excel-row.
PERFORM itab_insert_value USING lv_col lv_row lv_data
CHANGING pt_data.
ENDLOOP.
ENDFORM. "excel_read3
*&---------------------------------------------------------------------*
*& Form excel_read4
*&---------------------------------------------------------------------*
FORM excel_read4 CHANGING pt_data TYPE tt_data.
DATA:
lt_excel TYPE kcde_intern,
ls_excel TYPE kcde_intern_struc,
lv_data TYPE tv_data,
lv_error TYPE string.
CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = pa_fname
i_begin_col = 1
i_begin_row = 1
i_end_col = co_max_col
i_end_row = co_max_row
TABLES
intern = lt_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
LOOP AT lt_excel INTO ls_excel.
lv_data = ls_excel-value.
PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
CHANGING pt_data.
ENDLOOP.
ENDFORM. "excel_read4
*&---------------------------------------------------------------------*
*& Form excel_read5
*&---------------------------------------------------------------------*
FORM excel_read5 CHANGING pt_data TYPE tt_data.
DATA:
lt_raw_data(4096) TYPE c OCCURS 0,
lt_data TYPE STANDARD TABLE OF string,
lv_error TYPE string.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
* I_LINE_HEADER =
i_tab_raw_data = lt_raw_data
i_filename = pa_fname
TABLES
i_tab_converted_data = pt_data
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
ENDFORM. "excel_read5
*&---------------------------------------------------------------------*
*& Form excel_read6
*&---------------------------------------------------------------------*
FORM excel_read6 CHANGING pt_data TYPE tt_data.
DATA:
lv_error TYPE string.
CALL FUNCTION 'UPLOAD_XLS_FILE_2_ITAB'
EXPORTING
i_filename = pa_fname
TABLES
e_itab = pt_data
EXCEPTIONS
file_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE: / 'SY-SUBRC = ', sy-subrc.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
WRITE: / lv_error.
STOP.
ENDIF.
ENDFORM. "excel_read6
*&---------------------------------------------------------------------*
*& Form excel_read7
*&---------------------------------------------------------------------*
FORM excel_read7 CHANGING pt_data TYPE tt_data.
DATA:
lo_control TYPE REF TO i_oi_container_control,
lo_error TYPE REF TO i_oi_error,
lo_container TYPE REF TO cl_gui_custom_container,
lo_document TYPE REF TO i_oi_document_proxy,
lo_spreadsheet TYPE REF TO i_oi_spreadsheet,
lv_url TYPE char256,
lv_has_sheet TYPE i.
* 1. control get
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = lo_control
error = lo_error.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 2. container create
CREATE OBJECT lo_container
EXPORTING
* parent =
container_name = 'CONTAINER'
* style =
* lifetime = lifetime_default
* repid =
* dynnr =
* no_autodef_progid_dynnr =
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5
OTHERS = 6.
ASSERT sy-subrc = 0.
* 3. control init with container
CALL METHOD lo_control->init_control
EXPORTING
* dynpro_nr = SY-DYNNR
* gui_container = ' '
inplace_enabled = 'X'
* inplace_mode = 0
* inplace_resize_documents = ' '
inplace_scroll_documents = 'X'
* inplace_show_toolbars = 'X'
* no_flush = ' '
* parent_id = cl_gui_cfw=>dynpro_0
r3_application_name = 'Excel'
* register_on_close_event = ' '
* register_on_custom_event = ' '
* rep_id = SY-REPID
* shell_style = 1384185856
parent = lo_container
* name =
* autoalign = 'x'
IMPORTING
error = lo_error
EXCEPTIONS
javabeannotsupported = 1
OTHERS = 2.
ASSERT sy-subrc = 0.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 4. control get proxy
CALL METHOD lo_control->get_document_proxy
EXPORTING
* document_format = 'NATIVE'
document_type = soi_doctype_excel_sheet " 'Excel.Sheet'
* no_flush = ' '
* register_container = ' '
IMPORTING
document_proxy = lo_document
error = lo_error.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 5. document open
CONCATENATE 'FILE://' pa_fname INTO lv_url.
CALL METHOD lo_document->open_document
EXPORTING
* document_title = ' '
document_url = lv_url
* no_flush = ' '
open_inplace = 'X'
open_readonly = 'X'
* protect_document = ' '
* onsave_macro = ' '
* startup_macro = ''
* user_info =
IMPORTING
error = lo_error.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 6. spreadsheet check exists
CALL METHOD lo_document->has_spreadsheet_interface
* EXPORTING
* no_flush = ' '
IMPORTING
error = lo_error
is_available = lv_has_sheet.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
IF NOT lv_has_sheet IS INITIAL.
* 7. spreadsheet get
CALL METHOD lo_document->get_spreadsheet_interface
EXPORTING
no_flush = ' '
IMPORTING
error = lo_error
sheet_interface = lo_spreadsheet.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* 8. data read
DATA:
lv_row_start TYPE i value 1, " first row
lv_row_block TYPE i value 100, " number of rows read in one block (range)
lv_row_empty TYPE i value 0, " count of empty rows at the end of block
lt_value TYPE soi_generic_table,
ls_value TYPE soi_generic_item,
lt_rangesdef TYPE soi_dimension_table,
ls_rangesdef TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
ls_data TYPE ts_data,
lv_col_index TYPE tv_index,
lv_fieldname TYPE string.
FIELD-SYMBOLS:
<fs_value> TYPE tv_data.
WHILE lv_row_empty < 5. " max empty rows
* range create
CLEAR ls_rangesdef.
CLEAR lt_rangesdef.
ls_rangesdef-row = lv_row_start.
ls_rangesdef-column = 1.
ls_rangesdef-rows = lv_row_block.
ls_rangesdef-columns = co_max_col.
INSERT ls_rangesdef INTO TABLE lt_rangesdef.
* data read from range
CALL METHOD lo_spreadsheet->get_ranges_data
EXPORTING
* no_flush = ' '
* all = ' '
* updating = -1
rangesdef = lt_rangesdef
IMPORTING
contents = lt_value
error = lo_error
CHANGING
ranges = lt_ranges.
IF lo_error->has_failed = 'X'.
CALL METHOD lo_error->raise_message( type = 'E' ).
ENDIF.
* data takeover
LOOP AT lt_value INTO ls_value.
AT NEW row.
CLEAR ls_data.
ENDAT.
lv_col_index = ls_value-column.
CONCATENATE 'VALUE_' lv_col_index INTO lv_fieldname.
ASSIGN COMPONENT lv_fieldname OF STRUCTURE ls_data TO <fs_value>.
ASSERT sy-subrc = 0.
<fs_value> = ls_value-value.
AT END OF row.
APPEND ls_data TO pt_data.
IF ls_data IS INITIAL.
ADD 1 TO lv_row_empty.
ELSE.
CLEAR lv_row_empty.
ENDIF.
ENDAT.
ENDLOOP.
ADD lv_row_block TO lv_row_start.
ENDWHILE.
* data delete empty rows at the end
DATA:
lv_index TYPE sy-tabix.
lv_index = lines( pt_data ).
WHILE lv_index > 0.
READ TABLE pt_data INTO ls_data INDEX lv_index.
IF ls_data IS INITIAL.
DELETE pt_data INDEX lv_index.
ELSE.
EXIT.
ENDIF.
SUBTRACT 1 FROM lv_index.
ENDWHILE.
ELSE.
MESSAGE e323(bf00) WITH pa_fname RAISING file_error.
ENDIF.
* 9. final cleaning
IF NOT lo_spreadsheet IS INITIAL.
FREE lo_spreadsheet.
ENDIF.
IF NOT lo_document IS INITIAL.
CALL METHOD lo_document->close_document.
CALL METHOD lo_document->release_document.
FREE lo_document.
ENDIF.
IF NOT lo_control IS INITIAL.
CALL METHOD lo_control->destroy_control.
FREE lo_control.
ENDIF.
ENDFORM. "excel_read7
*&---------------------------------------------------------------------*
*& Form itab_insert_value
*&---------------------------------------------------------------------*
FORM itab_insert_value USING pi_col TYPE tv_index
pi_row TYPE tv_index
pi_value TYPE tv_data
CHANGING pt_data TYPE tt_data.
DATA:
lv_fieldname TYPE string.
FIELD-SYMBOLS:
<fs_data> TYPE ts_data,
<fs_value> TYPE tv_data.
CHECK pi_value <> ''.
WHILE pi_row > lines( pt_data ).
INSERT INITIAL LINE INTO TABLE pt_data.
ENDWHILE.
READ TABLE pt_data ASSIGNING <fs_data> INDEX pi_row.
ASSERT sy-subrc = 0.
CONCATENATE 'VALUE_' pi_col INTO lv_fieldname.
ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_data> TO <fs_value>.
ASSERT sy-subrc = 0.
<fs_value> = pi_value.
ENDFORM. "itab_insert_value
*&---------------------------------------------------------------------*
*& Form itab_display
*&---------------------------------------------------------------------*
FORM itab_display USING pt_data TYPE tt_data.
DATA:
lv_count TYPE i,
ls_data TYPE ts_data,
lv_value TYPE tv_data.
lv_count = lines( pt_data ).
WRITE: / 'Number of lines', lv_count.
SKIP.
LOOP AT pt_data INTO ls_data.
WRITE / ''. " sy-tabix.
DO co_max_col TIMES VARYING lv_value FROM ls_data-value_0001
NEXT ls_data-value_0002.
WRITE: lv_value(10).
ENDDO.
ENDLOOP.
ENDFORM. "itab_display
See other related notes in my infodepot:
Full list of examples in my infodepot
If you have a question, have found an error or just want to contact me, please use this form.
If you have a question, have found an error or just want to contact me, please use this form.
Copyright (C) 2012 http://www.kerum.pl/infodepot/
Disclaimer: I am not affiliated or related to any division or subsidiary of SAP AG.
Trademarks or registered trademarks of any products or companies referred to on this site belong to those companies.
Anyone using the given solutions, is doing it under his/her own responsibility and at own risk.