Upload EXCEL XLSX
*&---------------------------------------------------------------------*
*& Form UPLOAD_XLSX
*&---------------------------------------------------------------------*
* Automatisches uploaden in ein EXCEL
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM upload_xlsx .
"Datendeklaration
DATA: lo_uploader TYPE REF TO zcl_excel_uploader.
"Erstellen einer Instanz
CREATE OBJECT lo_uploader.
"Maximale Zeilen mitgeben
lo_uploader->max_rows = 65536.
"Filenamen mit geben
lo_uploader->filename = pa_file.
"Anzahl Kopfzeilen mitgeben
lo_uploader->header_rows_count = 1.
"Excel uploaden
lo_uploader->upload( CHANGING ct_data = gt_data ).
ENDFORM. " UPLOAD_XLSX
"Folgender Fuba ist im Hintergrund
dieser kann auch manuell befülllt werden
* CLEAR: gt_data.
* CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
* EXPORTING
* filename = pa_file
* i_begin_col = 1
* i_begin_row = 1
* i_end_col = 256
* i_end_row = 65536
* TABLES
* intern = gt_data
* 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.
* ENDIF.
Attributte
HEADER_ROWS_COUNT Instance Attribute Public Type I Kopf Zeilen Zähler
MAX_ROWS Instance Attribute Public Type I Maximal Zeilen
FILENAME Instance Attribute Public Type LOCALFILE Fielname
LV_TOT_COMPONENTS Instance Attribute Private Type I LV Tot Components
Methoden
CONSTRUCTOR Instance Method Public Defaultwerte
UPLOAD Instance Method Public Upload starten
DO_UPLOAD Instance Method Public Excel Uploaden
Parameter Methode UPLOAD
CT_DATA Changing Type ANY TABLE Tabelle die befüllt werden soll
Parameter Methode DO_UPLOAD
IV_BEGIN Importing Type I Beginn
IV_END Importing Type I Ende
RV_EMPTY Exporting Type FLAG allgemeines flag
CT_DATA Changing Type STANDARD TABLE Daten Tabelle
method CONSTRUCTOR.
max_rows = 9999.
endmethod.
METHOD upload.
DATA: lo_struct TYPE REF TO cl_abap_structdescr,
lo_table TYPE REF TO cl_abap_tabledescr,
lt_comp TYPE cl_abap_structdescr=>component_table.
"Beschreibung des Typs eines Datenobjekts
lo_table ?= cl_abap_structdescr=>describe_by_data( ct_data ).
"Navigierungsmethode Tabellentyp -> Zeilentyp
lo_struct ?= lo_table->get_table_line_type( ).
"Liefert Komponenten-Beschreibungstabelle der Struktur
lt_comp = lo_struct->get_components( ).
*
lv_tot_components = lines( lt_comp ).
*
DATA: lv_empty TYPE flag,
lv_begin TYPE i,
lv_end TYPE i.
*
lv_begin = header_rows_count + 1.
lv_end = max_rows.
WHILE lv_empty IS INITIAL.
do_upload(
EXPORTING
iv_begin = lv_begin
iv_end = lv_end
IMPORTING
rv_empty = lv_empty
CHANGING
ct_data = ct_data
).
lv_begin = lv_end + 1.
lv_end = lv_begin + max_rows.
ENDWHILE.
ENDMETHOD.
method DO_UPLOAD.
DATA: li_exceldata TYPE STANDARD TABLE OF alsmex_tabline.
DATA: ls_exceldata LIKE LINE OF li_exceldata.
DATA: lv_tot_rows TYPE i.
DATA: lv_packet TYPE i.
FIELD-SYMBOLS: <struc> TYPE ANY,
<field> TYPE ANY.
* Upload this packet
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = filename
i_begin_col = 1
i_begin_row = iv_begin
i_end_col = lv_tot_components
i_end_row = iv_end
TABLES
intern = li_exceldata
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
* something wrong, exit
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
rv_empty = 'X'.
EXIT.
ENDIF.
* No rows uploaded, exit
IF li_exceldata IS INITIAL.
rv_empty = 'X'.
EXIT.
ENDIF.
* Move from Row, Col to Flat Structure
LOOP AT li_exceldata INTO ls_exceldata.
" Append new row
AT NEW row.
APPEND INITIAL LINE TO ct_data ASSIGNING <struc>.
ENDAT.
" component and its value
ASSIGN COMPONENT ls_exceldata-col OF STRUCTURE <struc> TO <field>.
IF sy-subrc EQ 0.
<field> = ls_exceldata-value.
ENDIF.
" add the row count
AT END OF row.
IF <struc> IS NOT INITIAL.
lv_tot_rows = lv_tot_rows + 1.
ENDIF.
ENDAT.
ENDLOOP.
* packet has more rows than uploaded rows,
* no more packet left. Thus exit
lv_packet = iv_end - iv_begin.
IF lv_tot_rows LT lv_packet.
rv_empty = 'X'.
ENDIF.
endmethod.