ALV数据导出 - OLE
2017-12-01 本文已影响26人
Mmm_余安
DATA: LV_EXCEL TYPE OLE2_OBJECT, " EXCEL对象
LV_WORKBOOK TYPE OLE2_OBJECT, " WORKBOOK对象
LV_SHEET TYPE OLE2_OBJECT, " SHEET对象
LV_CELL TYPE OLE2_OBJECT. " CELL对象
DATA: LV_FILEPATH TYPE RLGRAP-FILENAME VALUE 'C'. "FILE PATCH
FORM FRM_OUTPUT_EXCEL .
DATA: LV_TEXT(50). " 编制单位的文本
DATA:LS_MONTH1 TYPE DATUM,
LS_MONTH2 TYPE DATUM.
*&---下载模版的子例程
PERFORM FRM_GET_EXECEL.
*&---打开模版的子例程
PERFORM FRM_OPEN_EXCEL.
*&---设置EXCEL模板单元格，设置报表时间
LS_MONTH1 = P_RYEAR && S_RPMAX-LOW+1(2) && '01'.
LS_MONTH2 = P_RYEAR && S_RPMAX-HIGH+1(2) && '01'.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT = LS_MONTH1
IMPORTING
OUTPUT = LS_MONTH1.
CALL FUNCTION 'LAST_DAY_OF_MONTHS'
EXPORTING
DAY_IN = LS_MONTH2
IMPORTING
LAST_DAY_OF_MONTH = LS_MONTH2
EXCEPTIONS
DAY_IN_NO_DATE = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT = LS_MONTH2
IMPORTING
OUTPUT = LS_MONTH2.
CONCATENATE '期间:' LS_MONTH1 '-' LS_MONTH2 INTO LV_TEXT.
PERFORM FRM_SET_CELL_VALUE USING 2 9 LV_TEXT.
CLEAR:LV_TEXT.
CONCATENATE '编制单位:' BUTXT INTO LV_TEXT.
*&---设置报表编制单位
PERFORM FRM_SET_CELL_VALUE USING 2 1 LV_TEXT.
*&---设置EXCEL模板单元格
PERFORM FRM_SET_CELL_VALUE USING 2 18 '单位:元'.
DATA: ROW TYPE I, " 行
COL TYPE I. " 列
DATA L_MOD2 TYPE I.
LOOP AT GT_TAB INTO GS_TAB.
CLEAR ROW.
ROW = SY-TABIX + 4.
CONDENSE GS_TAB-ZCPOR NO-GAPS.
PERFORM FRM_SET_CELL_VALUE USING ROW 1 GS_TAB-ZCPOR.
PERFORM FRM_SET_CELL_VALUE USING ROW 2 GS_TAB-ZCROT.
PERFORM FRM_SET_CELL_VALUE USING ROW 3 GS_TAB-B1HSL.
PERFORM FRM_SET_CELL_VALUE USING ROW 4 GS_TAB-B2HSL.
PERFORM FRM_SET_CELL_VALUE USING ROW 5 GS_TAB-B3HSL.
PERFORM FRM_SET_CELL_VALUE USING ROW 6 GS_TAB-B4HSL.
PERFORM FRM_SET_CELL_VALUE USING ROW 7 GS_TAB-B5HSL.
PERFORM FRM_SET_CELL_VALUE USING ROW 8 GS_TAB-B6HSL.
ENDLOOP.
PERFORM FRM_SAVE_EXCEL.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_GET_EXECEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_GET_EXECEL .
DATA : LV_OBJECT LIKE WWWDATATAB, " 表 WWWDATA的工作区
LV_RC TYPE SY-SUBRC. " 消息返回变量
*&---变量定义
DATA: LV_TEXT TYPE STRING, "MESSAGE
LV_FILENAME TYPE STRING, "DOWNLOAD FILE NAME
LV_PATH TYPE STRING, "DOWNLOAD FILE PATH
LS_FUNCTXT TYPE SMP_DYNTXT, "MENU NAME
LV_TITLE TYPE STRING , "TITLE
LV_TITLE_NAME TYPE STRING, "TITLE NAME
LV_MOD(20) TYPE C, "MODEL
LS_OBJECT TYPE WWWDATATAB, "OBJECT NAME\
LV_FULLPATH TYPE STRING. "FULL PATH
*&---调用OS 操作系统对话框
LV_TITLE = '保存'.
LV_TITLE_NAME = '所有者权益变动表'.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
WINDOW_TITLE = LV_TITLE " 保存'
DEFAULT_FILE_NAME = LV_TITLE_NAME " 客户合作伙伴导入模板
CHANGING
FILENAME = LV_FILENAME
PATH = LV_PATH
FULLPATH = LV_FULLPATH.
*&---操作系统文件路径
CONCATENATE LV_PATH
LV_FILENAME
'.XLS'
INTO LV_FILEPATH.
*&---根据服务器上的文件名获取WWW对象的信息
SELECT SINGLE RELID
OBJID
SRTF2
CHECKOUT
CHECKNEW
CHNAME
TDATE
TTIME
TEXT
CLUSTR
CLUSTD
FROM WWWDATA
INTO CORRESPONDING FIELDS OF LV_OBJECT
WHERE RELID = 'MI'
AND SRTF2 = 0
AND OBJID = 'ZFIR016'.
IF SY-SUBRC <> 0 OR LV_OBJECT-OBJID = SPACE.
MESSAGE '模版文件不存在，请用smw0进行加载' TYPE 'E'.
STOP.
ENDIF.
*&---调用函数下载模板
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LV_OBJECT
DESTINATION = LV_FILEPATH
IMPORTING
RC = LV_RC.
IF LV_RC <> 0.
MESSAGE '模版文件下载失败，请与开发人员联系' TYPE 'E'.
STOP.
ENDIF.
ENDFORM.
FORM FRM_OPEN_EXCEL .
CREATE OBJECT LV_EXCEL 'Excel.Application'.
IF SY-SUBRC <> 0.
MESSAGE 'excel初始化失败' TYPE 'E'.
STOP.
ENDIF.
SET PROPERTY OF LV_EXCEL 'VISIBLE' = 1.
CALL METHOD OF
LV_EXCEL
'Workbooks' = LV_WORKBOOK.
CALL METHOD OF
LV_WORKBOOK
'Open'
EXPORTING
#1 = LV_FILEPATH. "文件保存地址
CALL METHOD OF
LV_EXCEL
'Worksheets' = LV_SHEET
EXPORTING
#1 = '所有者权益变动表'. "工作表名称
CALL METHOD OF
LV_SHEET
'Activate'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SAVE_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_SAVE_EXCEL .
FREE LV_CELL.
FREE LV_SHEET.
FREE LV_WORKBOOK.
FREE LV_EXCEL.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SET_CELL_VALUE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_4 text
* -->P_5 text
* -->P_LV_TEXT text
*----------------------------------------------------------------------*
FORM FRM_SET_CELL_VALUE USING P_ROW P_COL P_VALUE .
*&---当p_value为空时，跳出子例程
IF P_VALUE IS INITIAL.
EXIT.
ENDIF.
CALL METHOD OF
LV_SHEET
'CELLS' = LV_CELL
EXPORTING
#1 = P_ROW
#2 = P_COL.
SET PROPERTY OF LV_CELL 'VALUE' = P_VALUE.
ENDFORM.
