SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English

Blogs - Weblogs News

Upload from Excel to Internal table (different functions)

Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord

View previous topic :: View next topic

Author

Message

admin
Администратор

Joined: 01 Sep 2007
Posts: 1640

Posted: Tue Dec 13, 2016 4:30 pm Post subject: Upload from Excel to Internal table (different functions)

Code:

*&---------------------------------------------------------------------*
*& Report ZUPLOAD_XLS
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZUPLOAD_XLS 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.

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'.

*&---------------------------------------------------------------------*
*& 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.
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 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:
ls_data TYPE ts_data,
lv_value TYPE tv_data.

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

Display posts from previous:

	Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord	All times are GMT + 4 Hours
Page 1 of 1

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum

All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.

SAP R/3 форум ABAP консультантовRussian ABAP Developer's Club

Upload from Excel to Internal table (different functions)

SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club