SAP ABAP通过ABAP2XLSX上载读取excel数据到SAP
前言
在使用FM : ZALSM_EXCEL_TO_INTERNAL_TABLE时速度很慢,改用开源项目ABAP2XLSX去读取excel数据
一、代码
DATA:lt_filetab TYPE filetable,
ls_filetab TYPE file_table,
lv_rc TYPE i,
lv_title TYPE string,
* lv_filename TYPE rlgrap-filename.
lv_filename TYPE string.
DATA:lo_reader TYPE REF TO zif_excel_reader,
lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet,
lt_cell TYPE zexcel_t_cell_data.
lv_title = '选择文件'.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = lv_title " Title Of File Open Dialog
* default_extension = " Default Extension
* default_filename = " Default File Name
file_filter = 'All Files (*.*)|*.xlsx|*.xls' " File Extension Filter String
* with_encoding = " File Encoding
initial_directory = 'C:\' " Initial Directory
multiselection = abap_false " Multiple selections poss. 设置单选
CHANGING
file_table = lt_filetab " Table Holding Selected Files
rc = lv_rc " Return Code, Number of Files or -1 If Error Occurred
* user_action = " User Action (See Class Constants ACTION_OK, ACTION_CANCEL)
* file_encoding =
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
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_filetab INTO ls_filetab.
* 使用ABAP2XLSX读取excel
CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.
*lo_excel = lo_readeer->load( i_excel2007 = lv_mime_file ).
lv_filename = ls_filetab-filename.
TRY.
lo_excel = lo_reader->load_file(
i_filename = lv_filename"'C:\Users\CMCC-LAP\Documents\SAP\Download\EXCEL\公司间用平仓项目模板.XLSX'
* i_use_alternate_zip = SPACE
* i_from_applserver = SY-BATCH
* iv_zcl_excel_classname =
).
CATCH zcx_excel. "
ENDTRY.
CHECK lo_excel IS BOUND.
* 获取数据
CALL METHOD lo_excel->set_active_sheet_index_by_name
EXPORTING
i_worksheet_name = 'TESTSHEET'. " Worksheets name 你要读取的sheet名
lo_worksheet = lo_excel->get_active_worksheet( ).
IF lo_worksheet IS NOT INITIAL.
lt_cell = lo_worksheet->sheet_content.
PERFORM frm_get_data_from_excel USING lt_cell p_zbukrs CHANGING gt_data.
ENDIF.
ENDLOOP.
FORM frm_get_data_from_excel USING it_cell TYPE zexcel_t_cell_data
iv_bukrs TYPE bukrs
CHANGING ct_203i LIKE gt_data. "存放数据的内表
DATA:
ls_203i LIKE gs_203_i,
lv_cellrow TYPE zexcel_cell_row,
* lv_filename TYPE rlgrap-filename,
lt_tab_hq TYPE TABLE OF alsmex_tabline,
lv_data_f TYPE zexcel_number_format,
lv_date TYPE sy-datum,
lv_je TYPE bapicurr-bapicurr,
lv_wears TYPE waers.
DELETE it_cell WHERE cell_row = 1.
SELECT SINGLE waers INTO lv_wears
FROM t001
WHERE bukrs EQ iv_bukrs.
LOOP AT it_cell INTO DATA(ls_cell). "it_cell本身就是排序表 不需要再排序
CASE ls_cell-cell_column.
WHEN 1.
ls_203i-waers = ls_cell-cell_value.
WHEN 2.
lv_je = ls_cell-cell_value.
lv_je = abs( lv_je ).
CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_INTERNAL' "金额转换为内部格式
EXPORTING
currency = ls_203i-waers
amount_external = lv_je
max_number_of_digits = 18
IMPORTING
amount_internal = ls_203i-wrbtr
* RETURN =
.
WHEN 3.
lv_je = ls_cell-cell_value.
lv_je = abs( lv_je ).
CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_INTERNAL' "金额转换为内部格式
EXPORTING
currency = lv_wears
amount_external = lv_je
max_number_of_digits = 18
IMPORTING
amount_internal = ls_203i-dmbtr
* RETURN =
.
WHEN 4.
ls_203i-zuonr = ls_cell-cell_value.
WHEN 5.
ls_203i-sgtxt = ls_cell-cell_value.
WHEN 6.
ls_203i-xblnr = ls_cell-cell_value.
WHEN 7.
* lv_data_f = lo_worksheet->get_default_excel_date_format( ).
CALL METHOD zcl_excel_common=>excel_string_to_date "针对日期的处理
EXPORTING
ip_value = ls_cell-cell_value
RECEIVING
ep_value = ls_203i-budat.
* CATCH zcx_excel. "
WHEN OTHERS.
ENDCASE.
AT END OF cell_row.
APPEND ls_203i TO ct_203i.
CLEAR:ls_203i.
ENDAT.
ENDLOOP.
ENDFORM.
二、效果
截图
会先出现文件选择(设置了单选),确定后就会通过代码处理把数据放入内表GT_DATA
注意日期的处理:
读取到并不是excel中展示的日期格式,和ZALSM_EXCEL_TO_INTERNAL_TABLE有些不一样
需要通过方法转换一下就OK
关于日期格式读取不对的解释
总结
以上
post SAP-Garson
原文链接:https://blog.csdn.net/sinat_38119716/article/details/127054972文章来自于网络,如果侵犯了您的权益,请联系站长删除!