读取excel文件数据到内表
1.1 方法1 funtion: ZFI_INTERNAL_TAB_TO_EXCEL
FUNCTION zfi_internal_tab_to_excel.
*"----------------------------------------------------------------------
*"*"本地接口:
*" IMPORTING
*" REFERENCE(FILENAME) TYPE RLGRAP-FILENAME
*" REFERENCE(SHEET) TYPE I OPTIONAL
*" VALUE(SHEETNAME) OPTIONAL
*" VALUE(VISIBLE) TYPE I OPTIONAL
*" VALUE(I_BEGIN_COL) TYPE I DEFAULT 1
*" VALUE(I_BEGIN_ROW) TYPE I DEFAULT 2
*" VALUE(I_END_COL) TYPE I DEFAULT 20
*" VALUE(I_END_ROW) TYPE I DEFAULT 65536
*" VALUE(DATA_COLS) TYPE I DEFAULT 20
*" TABLES
*" DATA
*" EXCEPTIONS
*" INCORRECT_PARAMETERS
*" DOWNLOAD_ERROR
*"----------------------------------------------------------------------
TYPES:
* line type of sender table
BEGIN OF ty_s_senderline,
line(4096) TYPE c,
END OF ty_s_senderline,
* sender table
ty_t_sender TYPE ty_s_senderline OCCURS 0.
DATA: excel_tab TYPE ty_t_sender.
DATA: excel_line TYPE ty_s_senderline.
DATA: lv_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA: ld_rc TYPE i.
DATA: lv_cols TYPE i.
DATA: lv_count TYPE i.
FIELD-SYMBOLS <any> TYPE any.
FIELD-SYMBOLS <field> TYPE any.
"关闭Excel
DEFINE m_close.
CALL METHOD OF
workbook
'Close'.
CALL METHOD OF
application
'QUIT'.
END-OF-DEFINITION.
* Makro für Fehlerbehandlung der Methods
DEFINE m_message_o.
CASE sy-subrc.
WHEN 0.
WHEN 1.
m_close.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
RAISING download_error.
WHEN OTHERS.
m_close.
RAISE download_error.
ENDCASE.
END-OF-DEFINITION.
* check parameters
IF i_begin_row > i_end_row. RAISE incorrect_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE incorrect_parameters. ENDIF.
CHECK data[] IS NOT INITIAL.
* Get TAB-sign for separation of fields
CLASS cl_abap_char_utilities DEFINITION LOAD.
lv_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_o.
ENDIF.
SET PROPERTY OF application 'VISIBLE' = 0. "数据导出操作默认后台进行,如需显示,数据保存完成之后再显示
CALL METHOD OF
application
'Workbooks' = workbook.
m_message_o.
CALL METHOD OF
workbook
'Open'
EXPORTING
#1 = filename.
m_message_o.
IF sheet IS INITIAL."用默认模式
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message_o.
ELSE.
*-->可以实现读取多个sheet
CALL METHOD OF
application
'WORKSHEETS' = worksheet
EXPORTING
#1 = sheet.
CALL METHOD OF
worksheet
'Activate'.
m_message_o.
ENDIF.
IF sheetname is NOT INITIAL.
SET PROPERTY OF worksheet 'Name' = sheetname.
ENDIF.
"内表数据转换
LOOP AT data ASSIGNING <any>.
CLEAR excel_line.
DO data_cols TIMES.
ASSIGN COMPONENT sy-index OF STRUCTURE <any> TO <field>.
IF sy-subrc <> 0.
EXIT.
ENDIF.
IF sy-index = 1.
excel_line = <field>.
ELSE.
CONCATENATE excel_line <field> INTO excel_line
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDIF.
CLEAR <field>.
"计算内表有多少列
lv_count = lv_count + 1.
ENDDO.
IF lv_cols IS INITIAL.
lv_cols = lv_count.
ENDIF.
APPEND excel_line TO excel_tab.
CLEAR lv_count.
ENDLOOP.
REFRESH data.
*未指定Excel结束行列位置,需计算明确导出内容的结束行列位置,明确粘贴范围
*解决Excel导出提示问题:‘您粘贴的数据和选中的数据大小不一样,是否仍要粘贴’
IF i_end_row IS NOT SUPPLIED.
i_end_row = i_begin_row + lines( excel_tab[] ) - 1.
ENDIF.
IF i_end_col IS NOT SUPPLIED.
i_end_col = i_begin_col + lv_cols - 1.
ENDIF.
* mark whole spread sheet
CALL METHOD OF
worksheet
'Cells' = h_cell
EXPORTING
#1 = i_begin_row
#2 = i_begin_col.
m_message_o.
CALL METHOD OF
worksheet
'Cells' = h_cell1
EXPORTING
#1 = i_end_row
#2 = i_end_col.
m_message_o.
CALL METHOD OF
worksheet
'RANGE' = range
EXPORTING
#1 = h_cell
#2 = h_cell1.
m_message_o.
CALL METHOD OF
range
'SELECT'.
m_message_o.
* put ABAP into clipboard
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
m_message_o.
* paste Clippboard into marked area (whole spread sheet)
"尝试粘贴剪切板的数据到Excel(ABAP内表数据到剪切板需要时间)
DO 10 TIMES.
CALL METHOD OF
worksheet
'PASTE'.
IF sy-subrc = 0.
EXIT.
ELSE.
WAIT UP TO 1 SECONDS.
ENDIF.
ENDDO.
m_message_o.
* clear clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
m_message_o.
* save excel
GET PROPERTY OF application 'ActiveWorkbook' = workbook.
CALL METHOD OF
workbook
'SAVE'.
IF visible = 1.
SET PROPERTY OF application 'VISIBLE' = 1. "显示EXCEL
ELSE.
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF
workbook
'Close'.
CALL METHOD OF
application
'QUIT'.
m_message_o.
ENDIF.
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT h_cell. m_message_o.
FREE OBJECT h_cell1. m_message_o.
FREE OBJECT range. m_message_o.
FREE OBJECT worksheet. m_message_o.
FREE OBJECT workbook. m_message_o.
FREE OBJECT application. m_message_o.
ENDFUNCTION.
-
- 方法2 funtion: ZALSM_EXCEL_TO_INTERNAL_TABLE1
DATA:gt_data TYPE TABLE OF zalsmex_tabline.
DATA:gs_data TYPE zalsmex_tabline.
CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE1' "读取excel文件中的内容
EXPORTING
filename = p_file
i_begin_col = '1'
i_begin_row = '1'
i_end_col = '500'
i_end_row = '500000'
TABLES
intern = gt_data.
SORT gt_data BY row col value.
FUNCTION ZALSM_EXCEL_TO_INTERNAL_TABLE1 .
*"----------------------------------------------------------------------
*"*"本地接口:
*" IMPORTING
*" VALUE(FILENAME) LIKE RLGRAP-FILENAME
*" VALUE(I_BEGIN_COL) TYPE I
*" VALUE(I_BEGIN_ROW) TYPE I
*" VALUE(I_END_COL) TYPE I
*" VALUE(I_END_ROW) TYPE I
*" TABLES
*" INTERN STRUCTURE ZALSMEX_TABLINE
*" EXCEPTIONS
*" INCONSISTENT_PARAMETERS
*" UPLOAD_OLE
*"----------------------------------------------------------------------
DATA: excel_tab TYPE ty_t_sender.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA:
ld_rc TYPE i.
* Rückgabewert der Methode "clipboard_export "
* 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 = filename.
m_message.
* set property of application 'Visible' = 1.
* m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
* 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 = excel_tab
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
PERFORM separated_to_intern_convert TABLES excel_tab intern
USING ld_separator.
* clear clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
* 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. m_message.
FREE OBJECT h_cell1. m_message.
FREE OBJECT range. m_message.
FREE OBJECT worksheet. m_message.
FREE OBJECT workbook. m_message.
FREE OBJECT application. m_message.
* <<<<< End of change note 575877
ENDFUNCTION.
*&---------------------------------------------------------------------*
*& Form SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM separated_to_intern_convert TABLES i_tab TYPE ty_t_sender
i_intern type ty_t_itab
USING i_separator TYPE c.
DATA: l_sic_tabix LIKE sy-tabix,
l_sic_col TYPE kcd_ex_col.
DATA: l_fdpos LIKE sy-fdpos.
REFRESH i_intern.
LOOP AT i_tab.
l_sic_tabix = sy-tabix.
l_sic_col = 0.
WHILE i_tab CA i_separator.
l_fdpos = sy-fdpos.
l_sic_col = l_sic_col + 1.
PERFORM line_to_cell_separat TABLES i_intern
USING i_tab l_sic_tabix l_sic_col
i_separator l_fdpos.
ENDWHILE.
IF i_tab <> space.
CLEAR i_intern.
i_intern-row = l_sic_tabix.
i_intern-col = l_sic_col + 1.
i_intern-value = i_tab.
APPEND i_intern.
ENDIF.
ENDLOOP.
ENDFORM