1、屏幕设置
INITIALIZATION.
functxt-icon_id = icon_export.
functxt-quickinfo = '下载导入模版'.
functxt-icon_text = '下载导入模版'.
sscrfields-functxt_01 = functxt.2、下载模板
AT SELECTION-SCREEN.
CASE sscrfields-ucomm.
WHEN 'FC01'.
PERFORM frm_download_template.
ENDCASE.
FORM frm_download_template.
"定义OLE变量
DATA:g_excel TYPE ole2_object,
g_applica TYPE ole2_object,
g_sheet TYPE ole2_object,
g_cell TYPE ole2_object,
g_workbook TYPE ole2_object.
DATA:c_filepath TYPE string, " 存放路径
filename TYPE string, "文件名称
c_path TYPE string . " 下载后存放全路径
" www object
DATA: lv_objdata LIKE wwwdatatab,
lv_obj_name LIKE wwwdatatab-objid,
* lv_destination LIKE rlgrap-filename,
lt_mime LIKE w3mime OCCURS 10.
DATA:l_ret TYPE abap_bool,
lv_answer.
DATA:lv_file TYPE string.
"设置 www object信息
lv_objdata-objid = 'ZSD130_FILE'.
lv_obj_name = 'ZSD130_FILE'.
lv_objdata-text = 'XXXX.xls'."WWWDATA 对象的短文本
"保存对话框
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = '下载导入模板'
default_extension = 'xls'
default_file_name = 'XXXXXX模板.xls'
file_filter = 'EXCEL'
CHANGING
fullpath = c_path
filename = filename
path = c_filepath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
"检查文件是否存在
MOVE c_path TO lv_file.
CALL METHOD cl_gui_frontend_services=>file_exist
EXPORTING
file = lv_file
RECEIVING
result = l_ret
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
wrong_parameter = 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.
EXIT.
ENDIF.
IF l_ret EQ 'X'.
* 模版已存在，是否覆盖
CALL FUNCTION 'POPUP_TO_CONFIRM'
EXPORTING
text_question = '模版已存在，是否覆盖？'
text_button_1 = '是'(001)
text_button_2 = '否'(002)
IMPORTING
answer = lv_answer
EXCEPTIONS
text_not_found = 1
OTHERS = 2.
IF sy-subrc <> 0.
ENDIF.
IF lv_answer EQ 'A'. "取消
EXIT.
ELSEIF lv_answer NE '1'. "否
EXIT.
ENDIF.
ELSE.
lv_answer = '1'.
ENDIF.
IF lv_answer EQ '1'.
SELECT relid objid
FROM wwwdata
INTO CORRESPONDING FIELDS OF lv_objdata
UP TO 1 ROWS
WHERE srtf2 = 0 AND relid = 'MI'
AND objid = lv_obj_name.
ENDSELECT.
" 下载 wwwdata 至 内存中
CALL FUNCTION 'WWWDATA_IMPORT' "#EC *
EXPORTING
key = lv_objdata
TABLES
mime = lt_mime
EXCEPTIONS
wrong_object_type = 1
import_error = 2
OTHERS = 3.
IF c_path IS NOT INITIAL.
" 装载至本地文件
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = c_path
filetype = 'BIN'
TABLES
data_tab = lt_mime.
ENDIF.
ENDIF.
p_file = c_path.
ENDFORM.3、选择Excel POV事件
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM get_excel. " 获取excel文件名
FORM get_excel.
DATA: l_rc TYPE i, "L_RC 是否成功打开
l_filetable TYPE filetable. "文件列表
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = '选择数据文件'
multiselection = space
file_filter = 'Excel Files(*.xls)|*.xls;*.XLS;*.xlsx;*.XLSX'
CHANGING
file_table = l_filetable
rc = l_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc = 0 AND l_rc = 1. "判断是否成功打开
READ TABLE l_filetable INTO p_file INDEX 1.
ENDIF.
ENDFORM.或者
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_path = 'C:/'
mask = 'Excel(*.xls;*.xlsx)|*.XLSX;*.XLS;'
title = '选择Excel文件'
IMPORTING
filename = p_file
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
IF sy-subrc <> 0 AND sy-subrc <> 3.
MESSAGE '选择文件出错' TYPE 'I' DISPLAY LIKE 'E'.
ENDIF.4、将Excel转成内表
4.1 直接转换，如果碰到数据类型问题出错 会DUMP
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = lt_raw
i_filename = p_file
TABLES
i_tab_converted_data = gt_excel
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
4.2 将Excel以单元格为单位转到内表
DATA: typedescr_ref TYPE REF TO cl_abap_typedescr.
DATA: BEGIN OF lt_excel OCCURS 0. "excel上载内表
INCLUDE STRUCTURE zalsmex_tabline.
DATA: END OF lt_excel.
DATA: lv_true TYPE c,
lv_flag TYPE c.
FIELD-SYMBOLS: <fs_excel> LIKE LINE OF lt_excel,
<fs_value>.
CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 2
i_end_col = 10
i_end_row = 65000
TABLES
intern = lt_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
SORT lt_excel BY row col.
-----------------------------------------------------------------------------------------
"将excel内表循环加入内表
LOOP AT lt_excel ASSIGNING <fs_excel>.
"将值动态分配
ASSIGN COMPONENT <fs_excel>-col OF STRUCTURE gs_create_excel TO <fs_value>.
IF sy-subrc EQ 0.
"通过值获取描述来对数量进行校验
typedescr_ref = cl_abap_typedescr=>describe_by_data( <fs_value> ).
"注意这里需要指明的是数据元素 而不是字段名!
IF typedescr_ref->absolute_name = '/TYPE=BAMNG'.
CALL FUNCTION 'ZNUMERIC_CHECK'
EXPORTING
input = <fs_excel>-value
i_decimals = 3
IMPORTING
boolean = lv_true
EXCEPTIONS
wrong_number = 1
OTHERS = 2.
IF lv_true = 'X'.
<fs_value> = <fs_excel>-value.
ELSE.
gs_create_alv-iconid = icon_led_red.
"拼接错误信息
gs_create_alv-mesg = gs_create_alv-mesg && <fs_excel>-value && '数字格式错误；'.
ENDIF.
ELSE.
IF <fs_excel>-value = '必填' OR <fs_excel>-value = '可填'.
<fs_value> = ''.
ELSE.
<fs_value> = <fs_excel>-value.
ENDIF.
ENDIF.
ENDIF.
"在行变化的时候 将每行的数据添加到内表
AT END OF row.
MOVE-CORRESPONDING gs_create_excel TO gs_create_alv.
APPEND gs_create_alv TO gt_create_alv.
CLEAR: gs_create_alv,gs_create_excel.
gs_create_alv-iconid = icon_space.
ENDAT.
ENDLOOP.原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/17984.html