FORM frm_dowmload_file_r12 .
*OAOR related
DATA:
lv_def_name TYPE string VALUE '检查导入单价调整', " gc_file_name, "Default file name
lv_filename TYPE string, "The filename of the file to save
lv_path TYPE string, "File path saved
lv_fullpath TYPE string, "Full path (path + filename)
lv_user_action TYPE i,
lv_window_title TYPE string,
lv_file_filter TYPE string.
DATA: ls_excel TYPE ole2_object,
ls_sheet TYPE ole2_object,
ls_cell TYPE ole2_object,
ls_workbook TYPE ole2_object,
lv_xlsname TYPE string,
lv_line TYPE i VALUE 0. "Line number
TYPES:BEGIN OF tp_upload,
gjahr TYPE string,
co_perio TYPE string,
bukrs TYPE string,
prctr TYPE string,
reportid TYPE string,
ztemp TYPE string,
zrtype TYPE string,
zrowid TYPE string,
ztype TYPE string,
zitem TYPE string,
zvalue TYPE string,
werks TYPE string,
zproduct TYPE string,
plnbez TYPE string,
kostl TYPE string,
menge TYPE string,
matnr TYPE string,
maktx TYPE string,
menge2 TYPE string,
meins TYPE string,
zrate TYPE string,
salk3 TYPE string,
netpr TYPE string,
zxzh TYPE string,
ztzsl TYPE string,
zrate1 TYPE string,
ztzje TYPE string,
zbz TYPE string,
zbbje TYPE string,
END OF tp_upload.
DATA:lt_download TYPE TABLE OF tp_upload,
ls_download TYPE tp_upload.
CLEAR ls_download.
ls_download-gjahr = '会计年度'(t01) .
ls_download-co_perio = '期间'(t02).
ls_download-bukrs = '公司代码'(T03).
ls_download-prctr = '利润中心'(T04).
ls_download-reportid = '报表编码'(T05).
ls_download-ztemp = '报表模板'(T06).
ls_download-zrtype = '报表类型'(T07).
ls_download-zrowid = '行号'(T08).
ls_download-ztype = '报表项目类别'(T09).
ls_download-zitem = '报表项目'(T10).
ls_download-zvalue = '取值逻辑'(T11).
ls_download-werks = '工厂'(T12).
ls_download-zproduct = '产品'(T13).
ls_download-plnbez = '产品料号'(T14).
ls_download-kostl = '成本中心'(T15).
ls_download-menge = '折算系数'(T78).
ls_download-matnr = '物料号'(T79).
ls_download-maktx = '物料描述'(T80).
ls_download-menge2 = '当月消耗数量'(T81).
ls_download-meins = '单位'(T82).
ls_download-zrate = '税率'(T20).
ls_download-salk3 = '周期价格'(T86).
ls_download-netpr = '当月采购价格'(T83).
ls_download-zxzh = '新增行（是/否）'(T22).
ls_download-ztzsl = '调整数量'.
ls_download-zrate1 = '调整税率'(T84).
ls_download-ztzje = '调整单价（含税）'(T85).
ls_download-zbz = '备注（调整原因）'(T24).
ls_download-zbbje = '报表金额'(T25).
APPEND ls_download TO lt_download.
LOOP AT gt_alv12 INTO gs_alv12.
CLEAR ls_download.
ls_download-gjahr = gs_alv12-gjahr.
ls_download-co_perio = gs_alv12-co_perio.
ls_download-bukrs = gs_alv12-bukrs.
ls_download-prctr = gs_alv12-prctr.
ls_download-reportid = text-y01 && gs_alv12-reportid.
ls_download-ztemp = gs_alv12-ztemp.
ls_download-zrtype = gs_alv12-zrtype.
ls_download-zrowid = gs_alv12-zrowid.
ls_download-ztype = gs_alv12-ztype.
ls_download-zitem = gs_alv12-zitem.
ls_download-zvalue = gs_alv12-zvalue.
ls_download-werks = gs_alv12-werks.
ls_download-zproduct = gs_alv12-zproduct.
ls_download-plnbez = gs_alv12-plnbez.
ls_download-kostl = gs_alv12-kostl.
ls_download-menge = gs_alv12-menge.
ls_download-matnr = gs_alv12-matnr.
ls_download-maktx = gs_alv12-maktx.
ls_download-menge2 = gs_alv12-menge2.
ls_download-meins = gs_alv12-meins.
ls_download-zrate = gs_alv12-zrate.
ls_download-salk3 = gs_alv12-salk3.
ls_download-netpr = gs_alv12-netpr.
ls_download-zxzh = gs_alv12-zxzh.
ls_download-ztzsl = gs_alv12-ztzsl.
ls_download-zrate1 = gs_alv12-zrate1.
ls_download-ztzje = gs_alv12-ztzje.
ls_download-zbz = gs_alv12-zbz.
ls_download-zbbje = gs_alv12-zbbje.
APPEND ls_download TO lt_download.
ENDLOOP.
*Gets the pathname of the file to save
lv_window_title = '选择地址'.
lv_file_filter = 'xls'.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = lv_window_title
default_file_name = lv_def_name
file_filter = lv_file_filter
CHANGING
filename = lv_filename
path = lv_path
fullpath = lv_fullpath
user_action = lv_user_action
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
IF lv_fullpath IS NOT INITIAL.
lv_xlsname = lv_fullpath.
CREATE OBJECT ls_excel 'EXCEL.APPLICATION'."Excel startup
IF sy-subrc NE 0.
MESSAGE 'EXCEL启动错误' TYPE gc_e.
ENDIF.
CALL METHOD OF ls_excel 'WORKBOOKS' = ls_workbook.
SET PROPERTY OF ls_excel 'VISIBLE' = 1. " 1 / 0 Whether EXCEL is displayed or not
SET PROPERTY OF ls_excel 'SHEETSINNEWWORKBOOK' = 1.
CALL METHOD OF ls_workbook 'ADD'.
LOOP AT lt_download INTO ls_download.
lv_line = lv_line + 1. "The line number in Excel starts at 1
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 1. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-gjahr. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 2. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-co_perio. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 3. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-bukrs. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 4. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-prctr. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 5. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-reportid. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 6. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-ztemp. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 7. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zrtype. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 8. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zrowid. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 9. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-ztype. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 10. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zitem. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 11. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zvalue. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 12. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-werks. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 13. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zproduct. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 14. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-plnbez. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 15. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-kostl. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 16. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-menge. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 17. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-matnr. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 18. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-maktx. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 19. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-menge2. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 20. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-meins. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 21. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zrate. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 22. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-salk3. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 23. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-netpr. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 24. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zxzh.
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 25. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-ztzsl.
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 26. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zrate1. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 27. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-ztzje. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 28. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zbz. "Write the value
CALL METHOD OF ls_excel 'CELLS' = ls_cell EXPORTING #1 = lv_line #2 = 29. "Specify the cell,
SET PROPERTY OF ls_cell 'VALUE' = ls_download-zbbje. "Write the value
ENDLOOP.
DATA:lv_bod TYPE string.
CONCATENATE 'A1:AG' '1' INTO lv_bod.
PERFORM borderrange USING ls_excel lv_bod.
GET PROPERTY OF ls_excel 'ACTIVESHEET' = ls_sheet. "Activate workbook
GET PROPERTY OF ls_excel 'ACTIVEWORKBOOK' = ls_workbook. "Enable workspace
CALL METHOD OF ls_workbook 'SAVEAS' EXPORTING #1 = lv_xlsname #2 = 1. "Save the excel file
CALL METHOD OF ls_workbook 'CLOSE'. "Close workspace
CALL METHOD OF ls_excel 'QUIT'. "Exit excel
FREE OBJECT ls_sheet. "Release operation
FREE OBJECT ls_workbook.
FREE OBJECT ls_excel.
MESSAGE text-h17 TYPE gc_s."Template downloaded successfully
ENDIF.
ENDFORM.
FORM borderrange USING ps_excel TYPE ole2_object
pv_range TYPE string.
DATA: ls_cell TYPE ole2_object,
ls_borders TYPE ole2_object.
CALL METHOD OF ps_excel 'RANGE' = ls_cell
EXPORTING
#1 = pv_range.
DO 4 TIMES .
CALL METHOD OF ls_cell 'BORDERS' = ls_borders
EXPORTING #1 = sy-index.
SET PROPERTY OF ls_borders 'LineStyle' = '1'.
SET PROPERTY OF ls_borders 'WEIGHT' = 2. "4=max
SET PROPERTY OF ls_borders 'ColorIndex' = '1'.
ENDDO.
FREE OBJECT ls_borders.
FREE OBJECT ls_cell.
DATA:ls_int TYPE ole2_object .
CLEAR pv_range.
CONCATENATE 'A1:AG' '1' INTO pv_range.
CALL METHOD OF ps_excel 'RANGE' = ls_cell
EXPORTING
#1 = pv_range.
CALL METHOD OF ls_cell 'INTERIOR' = ls_int.
SET PROPERTY OF ls_int 'ColorIndex' = '32'.
SET PROPERTY OF ls_int 'Pattern' = '1'.
IF r_r12 IS NOT INITIAL.
CLEAR pv_range.
FREE OBJECT ls_int.
FREE OBJECT ls_cell.
pv_range = 'X1:Z1'.
CALL METHOD OF ps_excel 'RANGE' = ls_cell
EXPORTING
#1 = pv_range.
CALL METHOD OF ls_cell 'INTERIOR' = ls_int.
SET PROPERTY OF ls_int 'ColorIndex' = '6'.
SET PROPERTY OF ls_int 'Pattern' = '1'.
ENDIF.
FREE OBJECT ls_int.
FREE OBJECT ls_cell.
ENDFORM.