TABLES sscrfields.
DATA:functxt TYPE smp_dyntxt,
lv_initfname TYPE string,
lv_w3objid TYPE w3objid,
gs_layout TYPE lvc_s_layo,
gt_fieldcat TYPE lvc_t_fcat.
SELECTION-SCREEN FUNCTION KEY 1.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS p_path TYPE localfile MODIF ID gp0.
SELECTION-SCREEN END OF BLOCK b1.
INITIALIZATION.
functxt-icon_id = icon_toggle_display_change.
functxt-icon_text ='下载模板'.
sscrfields-functxt_01 = functxt.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path.
p_path = zcl_batch_tool=>get_file_address( EXPORTING im_path = p_path ).
AT SELECTION-SCREEN.
CASE sscrfields-ucomm.
WHEN 'FC01'.
lv_w3objid ='ZPPR050'.
lv_initfname ='SOP销售预测模板'.
zcl_batch_tool=>download_template( EXPORTING im_filename = lv_w3objid im_init_filename = lv_initfname ).
ENDCASE.
START-OF-SELECTION.
perform upload.
perform display.
form upload.
zcl_batch_tool=>upload_excel_data( EXPORTING im_path = p_file
im_begin_col =1
im_begin_row =2
im_end_col =4
im_end_row =50000
IMPORTING ex_tab = gt_upload_data ).
endform.
form display.
gs_layout-zebra ='X'.
gs_layout-sel_mode ='D'.
PERFORM fill_fcat TABLES gt_fieldcat USING:'BUKRS''公司''''''''''''''''4'.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
i_callback_program = sy-repid
is_layout_lvc = gs_layout
it_fieldcat_lvc = gt_head_fieldcat[]
i_save ='A'
i_callback_user_command ='USER_COMMAND'
i_callback_pf_status_set ='SET_PF_STATUS'* is_variant = g_variant
* it_events = i_events
* it_sort_lvc = gt_sort[]* is_print_lvc = gs_print
* i_grid_title = l_title " 报表TITLE
TABLES
t_outtab = gt_head
EXCEPTIONS
program_error =1
OTHERS =2.
endform.
FORM set_pf_status USING rt_extab TYPE slis_t_extab.
SET PF-STATUS 'ALV_STATUS' EXCLUDING rt_extab.
ENDFORM.
FORM user_command USING r_ucomm LIKE sy-ucomm
rs_selfield TYPE slis_selfield.
DATA lr_grid TYPE REF TO cl_gui_alv_grid.
CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
IMPORTING
e_grid = lr_grid.
CALL METHOD lr_grid->check_changed_data.
CASE R_UCOMM.
WHEN ''.
ENDCASE.
rs_selfield-row_stable ='X'.
rs_selfield-col_stable ='X'.
rs_selfield-refresh ='X'.
ENDFORM.
FORM fill_fcat TABLES pt_fieldcat TYPE lvc_t_fcat
USING VALUE(ps1)VALUE(ps2)VALUE(ps3)VALUE(ps4)VALUE(ps5)VALUE(ps6)VALUE(ps7)VALUE(ps8)VALUE(ps9)VALUE(ps10).
DATA ls_fieldcat TYPE lvc_s_fcat.
ls_fieldcat-fieldname = ps1.
ls_fieldcat-scrtext_s = ps2.
ls_fieldcat-scrtext_m = ps2.
ls_fieldcat-scrtext_l = ps2.
ls_fieldcat-ref_field = ps3.
ls_fieldcat-ref_table = ps4.
ls_fieldcat-edit = ps5.
ls_fieldcat-convexit = ps6.
ls_fieldcat-checkbox = ps7.
ls_fieldcat-outputlen = ps10.
ls_fieldcat-no_zero = ps8.
ls_fieldcat-key = ps9.
APPEND ls_fieldcat TO pt_fieldcat.
ENDFORM.
zcl_batch_tool
CLASS zcl_batch_tool DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
TYPES:
ty_excel_table TYPE TABLE OF alsmex_tabline .
CLASS-METHODS download_template
IMPORTING
VALUE(im_filename) TYPE w3objid
VALUE(im_init_directory) TYPE string OPTIONAL
VALUE(im_init_filename) TYPE string OPTIONAL .
CLASS-METHODS get_file_address
IMPORTING
VALUE(im_path) TYPE localfile OPTIONAL
RETURNING
VALUE(ex_path) TYPE localfile .
CLASS-METHODS confirm_screen
IMPORTING
VALUE(im_title) TYPE char35 OPTIONAL
VALUE(im_textline) TYPE char35 OPTIONAL
RETURNING
VALUE(ex_flag) TYPE abap_bool .
CLASS-METHODS convert_date_to_internal
IMPORTING
VALUE(i_date_external) TYPE any
RETURNING
VALUE(o_date_internal) TYPE datum .
CLASS-METHODS upload_excel_data
IMPORTING
VALUE(im_path) TYPE localfile OPTIONAL
VALUE(im_begin_col) TYPE i
VALUE(im_begin_row) TYPE i
VALUE(im_end_col) TYPE i
VALUE(im_end_row) TYPE i
VALUE(im_convert) TYPE c OPTIONAL
EXPORTING
VALUE(ex_tab) TYPE data .
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_batch_tool IMPLEMENTATION.*<SIGNATURE>---------------------------------------------------------------------------------------+*| Static Public Method ZCL_BATCH_TOOL=>CONFIRM_SCREEN
*弹出确认框
*+-------------------------------------------------------------------------------------------------+*|[--->] IM_TITLE TYPE CHAR35(optional)*|[--->] IM_TEXTLINE TYPE CHAR35(optional)*|[<-()] EX_FLAG TYPE abap_bool
*+--------------------------------------------------------------------------------------</SIGNATURE>
METHOD confirm_screen.
DATA:lv_answer TYPE c.
CLEAR ex_flag.
CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
EXPORTING
titel = im_title
start_column =20 "设置开始的列
start_row =7
textline1 = im_textline
* cancel_display = abap_true
IMPORTING
answer = lv_answer.
CHECK lv_answer EQ 'J'.
ex_flag = abap_true.
ENDMETHOD.*<SIGNATURE>---------------------------------------------------------------------------------------+*| Static Public Method ZCL_BATCH_TOOL=>CONVERT_DATE_TO_INTERNAL
*外部日期格式转内部日期,如2023/4/11-->20240411*+-------------------------------------------------------------------------------------------------+*|[--->] I_DATE_EXTERNAL TYPE ANY
*|[<-()] O_DATE_INTERNAL TYPE DATUM
*+--------------------------------------------------------------------------------------</SIGNATURE>
METHOD convert_date_to_internal.
DATA:lt_dd07v TYPE STANDARD TABLE OF dd07v.
DATA:ls_dd07v TYPE dd07v.*核心代码参考:CL_ABAP_DATFM
**获取日期格式
CALL FUNCTION 'DD_DOMVALUES_GET'
EXPORTING
domname ='XUDATFM'
TABLES
dd07v_tab = lt_dd07v
EXCEPTIONS
wrong_textflag =1
OTHERS =2.
IF sy-subrc <>0.
RETURN.
ENDIF.
LOOP AT lt_dd07v INTO ls_dd07v.
CALL 'DATE_CONV_EXT_TO_INT'
ID 'DATINT' FIELD o_date_internal
ID 'DATEXT' FIELD i_date_external
ID 'DATFORMDES' FIELD ls_dd07v-domvalue_l.
IF sy-subrc =0.
EXIT.
ELSE.
CLEAR o_date_internal.
ENDIF.
ENDLOOP.
ENDMETHOD.*<SIGNATURE>---------------------------------------------------------------------------------------+*| Static Public Method ZCL_BATCH_TOOL=>DOWNLOAD_TEMPLATE
*下载批导模板
*+-------------------------------------------------------------------------------------------------+*|[--->] IM_FILENAME TYPE W3OBJID
*|[--->] IM_INIT_DIRECTORY TYPE STRING(optional)*|[--->] IM_INIT_FILENAME TYPE STRING(optional)*+--------------------------------------------------------------------------------------</SIGNATURE>
METHOD download_template.
DATA:lo_objdata TYPE wwwdatatab,
lo_mime TYPE w3mime,
ls_destination TYPE rlgrap-filename,
ls_objnam TYPE string,
li_rc TYPE sy-subrc,
ls_errtxt TYPE string.
DATA:p_objid TYPE wwwdatatab-objid,
p_dest TYPE sapb-sappfad.
DATA:l_path TYPE string,
l_fullpath TYPE string,
l_filename TYPE string,
gc_fullpath TYPE rlgrap-filename.
DATA:lv_init_directory TYPE string,
lv_init_filename TYPE string.
lv_init_directory ='D:/'&& im_init_directory.
lv_init_filename = im_init_filename &&'.xlsx'.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title ='Select directory'
initial_directory = lv_init_directory "'C:/'
default_file_name = lv_init_filename
file_filter ='.xlsx'
CHANGING
filename = l_filename
path = l_path
fullpath = l_fullpath
EXCEPTIONS
cntl_error =1
error_no_gui =2
not_supported_by_gui =3
OTHERS =4.
p_objid = im_filename.
CHECK l_fullpath IS NOT INITIAL.
IF sy-subrc =0.
CONCATENATE p_objid '.XLS' INTO ls_objnam.
CONDENSE ls_objnam NO-GAPS.
SELECT SINGLE relid objid
INTO CORRESPONDING FIELDS OF lo_objdata
FROM wwwdata
WHERE srtf2 =0
AND relid ='MI'
AND objid = p_objid.
IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
MESSAGE '模板不存在于系统' TYPE 'W'.
EXIT.
ENDIF.
CONCATENATE l_fullpath '.xls' INTO gc_fullpath.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = lo_objdata
destination = gc_fullpath
IMPORTING
rc = li_rc.
IF li_rc NE 0.
MESSAGE '模板下载失败!' TYPE 'W'.
ENDIF.
ENDIF.
ENDMETHOD.*<SIGNATURE>---------------------------------------------------------------------------------------+*| Static Public Method ZCL_BATCH_TOOL=>GET_FILE_ADDRESS
* 获取文件地址
*+-------------------------------------------------------------------------------------------------+*|[--->] IM_PATH TYPE LOCALFILE(optional)*|[<-()] EX_PATH TYPE LOCALFILE
*+--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_file_address.
DATA lv_filename TYPE rlgrap-filename.* 选择文件搜索帮助
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_path = im_path
* mask =',EXCEL FILE,*.XLS;*.XLSX;XML;' "',*.xlsx,*.XLSX,*.xls,*.XLS.'
mask =',*.XLS;*.XLSX;*.XML;' "',*.xlsx,*.XLSX,*.xls,*.XLS.'
mode ='0'
title ='导入文件'
IMPORTING
filename = lv_filename
EXCEPTIONS
inv_winsys =1
no_batch =2
selection_cancel =3
selection_error =4
OTHERS =5.
IF sy-subrc <>0 AND sy-subrc <>3.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.* 如果选择了文件
IF sy-subrc =0 AND lv_filename IS NOT INITIAL.
ex_path = lv_filename.
ENDIF.
ENDMETHOD.*<SIGNATURE>---------------------------------------------------------------------------------------+*| Static Public Method ZCL_BATCH_TOOL=>UPLOAD_EXCEL_DATA
* 处理导入的数据到abap内表
*+-------------------------------------------------------------------------------------------------+*|[--->] IM_PATH TYPE LOCALFILE(optional)*|[--->] IM_BEGIN_COL TYPE I
*|[--->] IM_BEGIN_ROW TYPE I
*|[--->] IM_END_COL TYPE I
*|[--->] IM_END_ROW TYPE I
*|[--->] IM_CONVERT TYPE C(optional)*|[<---] EX_TAB TYPE DATA
*+--------------------------------------------------------------------------------------</SIGNATURE>
METHOD upload_excel_data.
FIELD-SYMBOLS <fs_excel_value> TYPE any.
DATA:lt_excel TYPE TABLE OF alsmex_tabline,
ls_excel TYPE alsmex_tabline.
DATA:lv_conversion_no_number TYPE REF TO cx_sy_conversion_no_number.
DATA:ex_tab_cl TYPE REF TO data,
ex_tab_area TYPE REF TO data.
FIELD-SYMBOLS <fs_ex_tab> TYPE STANDARD TABLE.
DATA:BEGIN OF ls_convert,
col TYPE zkcd_ex_col_n,
component TYPE char30,
function TYPE char30.
DATA END OF ls_convert.
DATA lt_convert LIKE TABLE OF ls_convert.
DATA:cl_abap_elemdescr TYPE REF TO cl_abap_elemdescr,
cl_abap_typedescr TYPE REF TO cl_abap_typedescr.
IF im_path IS INITIAL.
im_path =get_file_address( 'C:\' ).
ENDIF.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = im_path
i_begin_col = im_begin_col
i_begin_row = im_begin_row
i_end_col = im_end_col
i_end_row = im_end_row
TABLES
intern = lt_excel[]
EXCEPTIONS
inconsistent_parameters =1
upload_ole =2
OTHERS =3.
IF sy-subrc <>0.
EXIT.
ELSE.***---获取内表结构
DATA:lr_table_type TYPE REF TO cl_abap_tabledescr,
lr_struct_type TYPE REF TO cl_abap_structdescr,
lt_comp_table TYPE cl_abap_structdescr=>component_table.
lr_table_type ?= cl_abap_tabledescr=>describe_by_data( ex_tab ).
lr_struct_type ?= lr_table_type->get_table_line_type().
lt_comp_table[]= lr_struct_type->get_components().
LOOP AT lt_comp_table INTO DATA(ls_comp_table).
cl_abap_elemdescr ?= ls_comp_table-type.
CHECK cl_abap_elemdescr->edit_mask IS NOT INITIAL.
ls_convert-component = cl_abap_elemdescr->absolute_name.
ls_convert-function =|CONVERSION_EXIT_{ cl_abap_elemdescr->edit_mask+2}_INPUT|.
APPEND ls_convert TO lt_convert.
ENDLOOP.
CLEAR ls_convert.
SORT lt_convert BY component ASCENDING.
CREATE DATA ex_tab_cl TYPE HANDLE lr_table_type.
ASSIGN ex_tab_cl->* TO <fs_ex_tab>.
CREATE DATA ex_tab_area TYPE HANDLE lr_struct_type.
ASSIGN ex_tab_area->* TO FIELD-SYMBOL(<fs_ex_tab_area>).
LOOP AT lt_excel INTO ls_excel.* IF skip_first_row EQ 'X' AND ls_excel-row =1.* CONTINUE.* ENDIF.** IF skip_first_col_int EQ 'X'.* ADD 1 TO ls_excel-col.* ENDIF.
ASSIGN COMPONENT ls_excel-col OF STRUCTURE <fs_ex_tab_area> TO <fs_excel_value>.
TRY .<fs_excel_value>= ls_excel-value.
IF im_convert EQ 'X'.
CLEAR ls_convert.
READ TABLE lt_convert INTO ls_convert WITH KEY col = ls_excel-col.
IF sy-subrc <>0.
cl_abap_typedescr = cl_abap_elemdescr=>describe_by_data(<fs_excel_value>).
READ TABLE lt_convert INTO ls_convert WITH KEY component = cl_abap_typedescr->absolute_name.
ls_convert-col = ls_excel-col.
APPEND ls_convert TO lt_convert.
ENDIF.
IF ls_convert-function IS NOT INITIAL.
CALL FUNCTION ls_convert-function
EXPORTING
input =<fs_excel_value>
IMPORTING
output =<fs_excel_value>
EXCEPTIONS
length_error =1
OTHERS =2.
ENDIF.
ENDIF.
CATCH cx_sy_conversion_no_number INTO lv_conversion_no_number.
ENDTRY.
AT END OF row.
APPEND <fs_ex_tab_area> TO <fs_ex_tab>.
CLEAR <fs_ex_tab_area>.
ENDAT.
ENDLOOP.
ex_tab =<fs_ex_tab>.
ENDIF.
ENDMETHOD.
ENDCLASS.