ABAP导入excel demo

&---------------------------------------------------------------------
*& Report ZTEST_YCY015
&---------------------------------------------------------------------
*&
&---------------------------------------------------------------------
REPORT ztest_ycy015.

TABLES:sscrfields.

**全局定义
TYPES: BEGIN OF tp_alv ,
mesg TYPE string,
incon TYPE char50,
matnr1 LIKE mara-matnr, "物料编码
matnr2 LIKE mara-matnr, "物料编码
werks LIKE eban-werks,
lgort1 TYPE eban-lgort, "短文本
lgort2 TYPE eban-lgort, "短文本
menge LIKE eban-menge, "
mblnr LIKE mkpf-mblnr,
mjahr LIKE mkpf-mjahr,
END OF tp_alv.

DATA:gt_alv TYPE TABLE OF tp_alv,
gs_alv TYPE tp_alv.

DATA: BEGIN OF itfile OCCURS 0 ,
matnr1 TYPE string,
matnr2 TYPE string,
werks TYPE string, "物料编码*
lgort1 TYPE string, "
lgort2 TYPE string, "
menge TYPE string, "

  END OF itfile.

DATA: gs_functxt TYPE smp_dyntxt. "功能代码文本
DATA: gt_fcat TYPE lvc_t_fcat, "字段目录
gs_fcat TYPE lvc_s_fcat,
gs_layout TYPE lvc_s_layo.

SELECTION-SCREEN: BEGIN OF BLOCK blkl WITH FRAME TITLE txt01.
PARAMETERS: p_pfile TYPE localfile.
SELECTION-SCREEN: END OF BLOCK blkl.

SELECTION-SCREEN: FUNCTION KEY 1.

**初始化
INITIALIZATION.
gs_functxt-icon_id = icon_export.
gs_functxt-quickinfo = ‘下载导入模版’.
gs_functxt-icon_text = ‘下载导入模版’.
sscrfields-functxt_01 = gs_functxt.

AT SELECTION-SCREEN.
IF sscrfields-ucomm = ‘FC01’.
PERFORM frm_download_template.
ENDIF.

AT SELECTION-SCREEN OUTPUT.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_pfile.
PERFORM frm_input_help CHANGING p_pfile.

START-OF-SELECTION.
PERFORM get_data USING p_pfile. "得到数据
PERFORM frm_change.

END-OF-SELECTION.
PERFORM frm_layout.
PERFORM frm_fcat.
PERFORM frm_show.
&---------------------------------------------------------------------
*& Form frm_download_template
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM frm_download_template .
DATA: lv_filepath TYPE string, " 存放路径
lv_filename TYPE string, "文件名称
lv_path TYPE string . " 下载后存放全路径
DATA: lv_objdata LIKE wwwdatatab,
lv_obj_name LIKE wwwdatatab-objid,
lt_mime LIKE w3mime OCCURS 10.
DATA: lv_ret TYPE abap_bool,
lv_answer.
DATA: lv_file TYPE string.

"设置 www object信息
lv_obj_name = ‘ZTEST01’.

"保存对话框
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = ‘下载模板’
default_extension = ‘xlsx’
default_file_name = ‘PR更新导入模板.xlsx’
file_filter = ‘EXCEL’
prompt_on_overwrite = ‘’
CHANGING
fullpath = lv_path
filename = lv_filename
path = lv_filepath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.

"检查文件是否存在
MOVE lv_path TO lv_file.
CALL METHOD cl_gui_frontend_services=>file_exist
EXPORTING
file = lv_file
RECEIVING
result = lv_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 lv_ret EQ ‘X’.

• 模版已存在，是否覆盖
  CALL FUNCTION ‘POPUP_TO_CONFIRM’
  EXPORTING
  text_question = ‘模版已存在，是否覆盖？’
  text_button_1 = ‘是’(005)
  text_button_2 = ‘否’(006)
  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.

  IF lv_path IS NOT INITIAL.
  " 装载至本地文件
  p_pfile = lv_path.
  CALL FUNCTION ‘DOWNLOAD_WEB_OBJECT’ "
  EXPORTING
  key = lv_objdata
  destination = p_pfile.
  ENDIF.

  ENDIF.
  ENDFORM.
  &---------------------------------------------------------------------
  *& Form frm_input_help
  &---------------------------------------------------------------------
  *& text
  &---------------------------------------------------------------------
  *& <-- P_PFILE
  &---------------------------------------------------------------------
  FORM frm_input_help CHANGING p_o_file.
  DATA:
  ltd_filetable TYPE filetable,
  lth_filetable TYPE LINE OF filetable,
  lw_rc TYPE i.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
  CHANGING
  file_table = ltd_filetable
  rc = lw_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.
  READ TABLE ltd_filetable INTO lth_filetable INDEX 1.
  p_o_file = lth_filetable-filename.
  ELSE.
  MESSAGE ID sy-msgid TYPE ‘I’ NUMBER sy-msgno
  DISPLAY LIKE ‘E’
  WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  LEAVE LIST-PROCESSING.
  ENDIF.
  ENDFORM.
  &---------------------------------------------------------------------
  *& Form get_data
  &---------------------------------------------------------------------
  *& text
  &---------------------------------------------------------------------
  *& --> P_PFILE
  &---------------------------------------------------------------------
  FORM get_data USING p_i_file.

  FIELD-SYMBOLS: TYPE any.
  DATA: l_intern TYPE zalsmex_tabline OCCURS 0 WITH HEADER LINE.

  DATA: c_row TYPE i VALUE 4, "读EXCEL第4行开始
  c_col TYPE i VALUE 1, "读EXCEL第1列开始
  l_index TYPE i,
  l_msg(60) TYPE c,
  g_mod TYPE d,
  l_row(5) TYPE n,
  li_len TYPE sy-tabix,
  l_cstr(4).

  CALL FUNCTION ‘ZALSM_EXCEL_TO_INTERNAL_TABLE’
  EXPORTING
  filename = p_pfile
  i_begin_col = ‘1’ "开始列
  i_begin_row = ‘2’ "开始行
  i_end_col = ‘7’ "结束列
  i_end_row = ‘80000’ "结束行

• I_END_ROW               = '100000'   "结束行
  

• I_SHEET_NO              =
  

  TABLES
  intern = l_intern
  EXCEPTIONS
  inconsistent_parameters = 1
  upload_ole = 2
  OTHERS = 3.
  IF sy-subrc <> 0.
  MESSAGE ‘导入数据文件失败！’ TYPE ‘S’.
  STOP.
  ENDIF.

  SORT l_intern BY row col.

  LOOP AT l_intern.
  ASSIGN COMPONENT l_intern-col OF STRUCTURE itfile TO .
  "动态方法将值传到相应的内表
  = l_intern-value.

  AT END OF row.
  MOVE-CORRESPONDING itfile TO gs_alv.

  APPEND gs_alv TO gt_alv.
  CLEAR: gs_alv,itfile.
  

  ENDAT.
  ENDLOOP.
  ENDFORM.
  &---------------------------------------------------------------------
  *& Form frm_change
  &---------------------------------------------------------------------
  *& text
  &---------------------------------------------------------------------
  *& --> p1 text
  *& <-- p2 text
  &---------------------------------------------------------------------
  FORM frm_change .

ENDFORM.
&---------------------------------------------------------------------
*& Form frm_layout
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM frm_layout .
CLEAR gs_layout.
gs_layout-zebra = ‘X’.
gs_layout-cwidth_opt = ‘X’.
gs_layout-sel_mode = ‘A’.
ENDFORM.
&---------------------------------------------------------------------
*& Form frm_fcat
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM frm_fcat .
REFRESH gt_fcat.
DEFINE set_fieldcat_alv.

CLEAR gs_fcat.

gs_fcat-fieldname = &1.
gs_fcat-coltext = &2.
gs_fcat-ref_field = &3.
gs_fcat-ref_table = &4.

IF &1 = ‘MATNR1’ .
gs_fcat-no_zero = ‘X’.
ENDIF.

APPEND gs_fcat TO gt_fcat.

END-OF-DEFINITION.
set_fieldcat_alv ‘INCON’ ‘图标’ ‘’ ‘’.
set_fieldcat_alv ‘MESG’ ‘消息’ ‘’ ‘’.
set_fieldcat_alv ‘MATNR1’ ‘物料A’ ‘’ ‘’.
set_fieldcat_alv ‘MATNR2’ ‘物料B’ ‘BNFPO’ ‘EBAN’.
set_fieldcat_alv ‘WERKS’ ‘工厂’ ‘’ ‘’.
set_fieldcat_alv ‘LGORT1’ ‘库位A’ ‘’ ‘’.
set_fieldcat_alv ‘LGORT2’ ‘库位B’ ‘’ ‘’.
set_fieldcat_alv ‘MENGE’ ‘数量’ ‘’ ‘’.
set_fieldcat_alv ‘MBLNR’ ‘物料凭证’ ‘’ ‘’.
set_fieldcat_alv ‘MJAHR’ ‘物料凭证年度’ ‘’ ‘’.
ENDFORM.
&---------------------------------------------------------------------
*& Form frm_show
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM frm_show .
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY_LVC’
EXPORTING
i_callback_program = sy-repid
is_layout_lvc = gs_layout
it_fieldcat_lvc = gt_fcat

• i_callback_pf_status_set = 'PF_STATUS'
  i_callback_user_command = 'USER_COMMAND'
  i_save                  = 'A'
  

  TABLES
  t_outtab = gt_alv
  EXCEPTIONS
  program_error = 1
  OTHERS = 2.
  ENDFORM.