Code: Alles auswählen.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = gc_start_col
i_begin_row = gc_start_row
i_end_col = gc_max_col
i_end_row = gc_max_row
TABLES
intern = gt_werte
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
Code: Alles auswählen.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
CHANGING
file_table = lt_file_table
.
.
.
.
.
Folgende Benutzer bedankten sich beim Autor Romaniac für den Beitrag:
SAP_ENTWICKLER
Folgende Benutzer bedankten sich beim Autor abuma für den Beitrag:
SAP_ENTWICKLER
Folgende Benutzer bedankten sich beim Autor Romaniac für den Beitrag:
SAP_ENTWICKLER
Diese Methode wäre sehr hilfreich. Kannst du sie bitte reinstellen?
Code: Alles auswählen.
@78\QImporting@ VALUE( I_FILENAME ) TYPE RLGRAP-FILENAME Lokale Datei für Upload bzw. Download
@78\QImporting@ VALUE( I_BEGIN_COL ) TYPE I
@78\QImporting@ VALUE( I_BEGIN_ROW ) TYPE I
@78\QImporting@ VALUE( I_END_COL ) TYPE I
@78\QImporting@ VALUE( I_END_ROW ) TYPE I
@78\QImporting@ VALUE( IT_TAB_SHEET_NAMES ) TYPE TABLE
@79\QExporting@ VALUE( ET_ALSMEX_TABLINES ) TYPE ZT_ALSMEX_TABLINES Tabelle für Exceldaten
@03\QException@ INCONSISTENT_PARAMETERS inconsistent parameters
@03\QException@ UPLOAD_OLE UPLOAD OLE
METHOD excel_all_sheets_to_itab.
TYPE-POOLS: ole2.
TYPES:
BEGIN OF tp_excel_tab,
line(4096) TYPE c,
END OF tp_excel_tab.
DATA: t_excel_tab TYPE TABLE OF tp_excel_tab,
t_alsmex_tabline TYPE TABLE OF alsmex_tabline,
ld_separator TYPE c,
application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object,
h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object,
ld_rc TYPE i,
ls_sheet_name TYPE string,
l_sic_tabix LIKE sy-tabix,
l_sic_col TYPE kcd_ex_col,
l_fdpos LIKE sy-fdpos.
FIELD-SYMBOLS:
<w_tab_sheet_names> TYPE ANY,
<w_excel_tablines> TYPE alsmex_tabline,
<w_alsmex_tablines> TYPE ANY,
<w_alsmex_tabline> TYPE alsmex_tabline,
<w_excel_tab> TYPE string.
* Makro für Fehlerbehandlung der Methods
DEFINE m_message.
case sy-subrc.
when 0.
when 1.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
when others. raise upload_ole.
endcase.
END-OF-DEFINITION.
* check parameters
IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
* Get TAB-sign for separation of fields
CLASS cl_abap_char_utilities DEFINITION LOAD.
ld_separator = cl_abap_char_utilities=>horizontal_tab.
* open file in Excel
IF application-header = space OR application-handle = -1.
CREATE OBJECT application 'Excel.Application'.
m_message.
ENDIF.
CALL METHOD OF application 'Workbooks' = workbook.
m_message.
CALL METHOD OF workbook 'Open'
EXPORTING
#1 = i_filename.
m_message.
* SET PROPERTY OF application 'VISIBLE' = 1.
LOOP AT it_tab_sheet_names ASSIGNING <w_tab_sheet_names>.
CALL METHOD OF application 'Worksheets' = worksheet EXPORTING #1 = <w_tab_sheet_names>.
IF sy-subrc <> 0.
CONTINUE.
ENDIF.
CALL METHOD OF worksheet 'Activate'.
m_message.
**** ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
*** CALL METHOD OF worksheet 'Cells' = i_end_col
*** EXPORTING
*** #1 = 'Rows.Count'
*** #2 = 1
*** #3 = 'End(xlUp)'
*** #4 = 'Row'.
* mark whole spread sheet
CALL METHOD OF worksheet 'Cells' = h_cell
EXPORTING
#1 = i_begin_row
#2 = i_begin_col.
m_message.
CALL METHOD OF worksheet 'Cells' = h_cell1
EXPORTING
#1 = i_end_row
#2 = i_end_col.
m_message.
CALL METHOD OF worksheet 'RANGE' = range
EXPORTING
#1 = h_cell
#2 = h_cell1.
m_message.
CALL METHOD OF range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
CALL METHOD OF range 'COPY'.
m_message.
* read clipboard into ABAP
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = t_excel_tab
EXCEPTIONS
cntl_error = 1
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
CALL METHOD /fsc/cl_rw_tools=>excel_sep_to_intern_convert
EXPORTING
it_excel_tab = t_excel_tab
i_separator = ld_separator
IMPORTING
et_alsmex_tabline = t_alsmex_tabline.
IF NOT t_alsmex_tabline[] IS INITIAL.
* Insert excel table sheet name with index 0 0
INSERT INITIAL LINE INTO t_alsmex_tabline ASSIGNING <w_alsmex_tabline> INDEX 1.
<w_alsmex_tabline>-row = '0001'.
<w_alsmex_tabline>-col = '0000'.
<w_alsmex_tabline>-value = <w_tab_sheet_names>.
* append values of excel sheet to table with all sheet data
APPEND INITIAL LINE TO et_alsmex_tablines ASSIGNING <w_alsmex_tablines>.
<w_alsmex_tablines> = t_alsmex_tabline.
FREE t_alsmex_tabline.
ENDIF.
* clear clipboard
REFRESH t_excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = t_excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
ENDLOOP.
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT: h_cell,
h_cell1,
range,
worksheet,
workbook,
application.
* <<<<< End of change note 575877
ENDMETHOD.
Code: Alles auswählen.
FORM customer_excel_read.
TYPES:
BEGIN OF ltp_field_changes,
table_name TYPE tabname,
field_name TYPE string,
field_value TYPE string,
END OF ltp_field_changes.
DATA:
lt_excel_tablines TYPE zt_alsmex_tablines,
lv_filename TYPE localfile,
ls_string TYPE string.
FIELD-SYMBOLS:
<lw_col_value> TYPE ANY,
<lw_excel_tablines> TYPE zt_alsmex_tabline,
<lw_excel_tabline> TYPE alsmex_tabline.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = 10
text = text-p02.
lv_filename = p_file.
CALL METHOD /fsc/cl_rw_tools=>excel_all_sheets_to_itab
EXPORTING
i_filename = lv_filename
i_begin_col = 1
i_begin_row = 1
i_end_col = 40
i_end_row = 200
it_tab_sheet_names = t_tab_sheet_names
IMPORTING
et_alsmex_tablines = lt_excel_tablines
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.
LOOP AT lt_excel_tablines ASSIGNING <lw_excel_tablines>.
LOOP AT <lw_excel_tablines> ASSIGNING <lw_excel_tabline>.
AT NEW row.
APPEND INITIAL LINE TO t_input ASSIGNING <w_input>.
ENDAT.
CONCATENATE '<W_INPUT>-COL' <lw_excel_tabline>-col+2(2) INTO ls_string.
ASSIGN (ls_string) TO <lw_col_value>.
<lw_col_value> = <lw_excel_tabline>-value.
ENDLOOP.
ENDLOOP.
ENDFORM. " excel_file_read