1. 此处我用SFLIGHT表作为数据来源。
2. 上传的excel内容如下
3.代码片段讲解开始:
首先定义一些变量
DATA: gv_application TYPE ole2_object, "一些定义在TOP里的data
gv_sheet TYPE ole2_object,
gv_cell TYPE ole2_object,
gv_workbook TYPE ole2_object,
gv_columns TYPE ole2_object,
gv_rows TYPE ole2_object,
gv_file TYPE rlgrap-filename.
DATA: gt_sflight TYPE TABLE OF sflight,
gs_sflight TYPE sflight.
下载模板到本地的form:
FORM frm_download_excel_local .
DATA: lv_objdata LIKE wwwdatatab,
lv_obj_name LIKE wwwdatatab-objid VALUE 'ZFLIGHT_TEST', "这个是模板的obj name!
lv_file_name TYPE string VALUE '/ZFLIGHT_TEST', "这个是你要下载到本地的excel名字
lv_destination LIKE rlgrap-filename,
lv_subrc LIKE sy-subrc,
lv_file TYPE string,
lv_lines TYPE i,
lv_row TYPE i VALUE 2,
lv_date TYPE c LENGTH 6,
lv_answer TYPE c LENGTH 1.
lv_date = sy-datum.
CONCATENATE path lv_file_name lv_date '.xlsx' INTO gv_file. "给excel起个名,记住要加'.xlsx'
lv_file = gv_file.
PERFORM frm_check_if_file_exist USING lv_file CHANGING lv_answer. "检查是否本地已有同名文件,如果有,选择是否覆盖
IF lv_answer <> '1'.
EXIT.
ENDIF.
* download template
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "给个进度条信息
EXPORTING
text = 'Processing'.
SELECT SINGLE relid objid "固定用法
FROM wwwdata
INTO CORRESPONDING FIELDS OF lv_objdata
WHERE srtf2 = 0
AND relid = 'MI'
AND objid = lv_obj_name. "我也不知道问什么是0和MI,objid要填好
IF sy-subrc <> 0.
MESSAGE e555(bc400) WITH 'Error' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' "固定用法
EXPORTING
key = lv_objdata
destination = gv_file
IMPORTING
rc = lv_subrc.
IF lv_subrc <> 0.
MESSAGE e555(bc400) WITH 'Error' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
* Edit excel: add data
CREATE OBJECT gv_application 'Excel.Application'. "固定用法,从这开始是打开excel开始填数
PERFORM frm_open_excel_sheet USING gv_file 'Sheet1'. "打开excel,FORM是固定用法,直接拷贝
"interest info with detailed policy loan data
LOOP AT gt_sflight INTO gs_sflight. "逐行逐单元格进行填内容
PERFORM frm_fill_range USING lv_row 1 gs_sflight-carrid.
PERFORM frm_fill_range USING lv_row 2 gs_sflight-connid.
PERFORM frm_fill_range USING lv_row 3 gs_sflight-fldate.
PERFORM frm_fill_range USING lv_row 4 gs_sflight-price.
PERFORM frm_fill_range USING lv_row 5 gs_sflight-currency.
PERFORM frm_fill_range USING lv_row 6 gs_sflight-planetype.
PERFORM frm_fill_range USING lv_row 7 gs_sflight-seatsmax.
PERFORM frm_fill_range USING lv_row 8 gs_sflight-seatsocc.
PERFORM frm_fill_range USING lv_row 9 gs_sflight-paymentsum.
lv_row = lv_row + 1.
ENDLOOP.
PERFORM frm_free_object.
ENDFORM. "frm_download_excel_local
*&---------------------------------------------------------------------*
*& Form frm_check_if_file_exist
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_FILE text
*----------------------------------------------------------------------*
FORM frm_check_if_file_exist USING pv_file
CHANGING pv_answer.
DATA: lv_exist TYPE abap_bool,
lv_answer TYPE c LENGTH 1.
CALL METHOD cl_gui_frontend_services=>file_exist
EXPORTING
file = pv_file
RECEIVING
result = lv_exist
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
wrong_parameter = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc <> 0.
MESSAGE 'error' TYPE 'E'.
EXIT.
ENDIF.
IF lv_exist = abap_true.
CALL FUNCTION 'POPUP_TO_CONFIRM'
EXPORTING
text_question = '文件存在,覆盖?'
text_button_1 = '是'(001)
text_button_2 = '否'(002)
IMPORTING
answer = lv_answer "return value: 1, 2, A
EXCEPTIONS
text_not_found = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE 'error' TYPE 'E'.
ENDIF.
ELSE.
lv_answer = '1'.
ENDIF.
pv_answer = lv_answer.
ENDFORM. "frm_check_if_file_exist
*&---------------------------------------------------------------------*
*& Form frm_open_excel_sheet
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_FILE text
* -->PV_SHEET text
*----------------------------------------------------------------------*
FORM frm_open_excel_sheet USING pv_file pv_sheet. "固定用法,直接拷贝
CALL METHOD OF
gv_application
'Workbooks' = gv_workbook.
CALL METHOD OF
gv_workbook
'Open' = gv_workbook
EXPORTING
#1 = pv_file.
CALL METHOD OF
gv_workbook
'Worksheets' = gv_sheet
EXPORTING
#1 = pv_sheet.
CALL METHOD OF
gv_sheet
'Activate'.
ENDFORM. "frm_open_excel_sheet
" 这个FORM是直接填进去数,没有对数据进行操作,货币和数量的不适用,货币和数量的要进行转换。
*&---------------------------------------------------------------------*
*& Form frm_fill_range
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->VALUE text
* -->(PV_ROW) text
* -->VALUE text
* -->(PV_COL) text
* -->VALUE text
* -->(PV_VALUE) text
*----------------------------------------------------------------------*
FORM frm_fill_range USING value(pv_row)
value(pv_col)
value(pv_value).
DATA: lv_row TYPE i,
lv_col TYPE i.
CHECK pv_value IS NOT INITIAL.
lv_row = pv_row.
lv_col = pv_col.
CALL METHOD OF
gv_application
'CELLS' = gv_cell
EXPORTING
#1 = lv_row
#2 = lv_col.
SET PROPERTY OF gv_cell 'VALUE' = pv_value.
ENDFORM. "frm_fill_range
"这个FORM是填货币的数字的,有转换过程
*&---------------------------------------------------------------------*
*& Form frm_fill_range_value
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->VALUE text
* -->(PV_ROW) text
* -->VALUE text
* -->(PV_COL) text
* -->VALUE text
* -->(PV_VALUE) text
*----------------------------------------------------------------------*
FORM frm_fill_range_value USING value(pv_row)
value(pv_col)
value(pv_value) TYPE bzusage.
DATA: lv_row TYPE i,
lv_col TYPE i,
lv_val TYPE bapicurr_d.
CHECK pv_value IS NOT INITIAL.
lv_val = pv_value.
CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_EXTERNAL'
EXPORTING
currency = 'TWD' "我这里固定了货币是‘TWD’,FORM可以多一个货币类型参数
amount_internal = lv_val
IMPORTING
amount_external = lv_val.
lv_row = pv_row.
lv_col = pv_col.
CALL METHOD OF
gv_application
'CELLS' = gv_cell
EXPORTING
#1 = lv_row
#2 = lv_col.
SET PROPERTY OF gv_cell 'VALUE' = lv_val.
ENDFORM. "frm_fill_range_value
*&---------------------------------------------------------------------*
*& Form frm_free_object
*&---------------------------------------------------------------------*
* text "保存并关闭excel
*----------------------------------------------------------------------*
FORM frm_free_object.
CALL METHOD OF
gv_workbook
'Save'.
CALL METHOD OF
gv_workbook
'Close'
EXPORTING
#1 = 0.
CALL METHOD OF
gv_application
'Quit'.
FREE OBJECT gv_sheet.
FREE OBJECT gv_workbook.
FREE OBJECT gv_application.
ENDFORM. "frm_free_object