对于excel的操作 ,我所熟知的只有三种方式
1.直接下载到excel,适用于格式简单 ,对展现形式无太多要求的需求 ,对于的函数与class都是常见的
2.ole方式 ,这样方式看上去代码比较原始
3 DOI方式,主要还是封装了ole,算是一种进化吧
本实例选取项目上一个小的开发 ,主要介绍doi方式
首先创建一个函数
函数内容:
DATA: LT_SPOPLI TYPE STANDARD TABLE OF SPOPLI,
L_ANSWER TYPE CHAR1,
LWA_SPOPLI TYPE SPOPLI,
LW_TCTYP TYPE TB2BE,
L_TYPE TYPE C,
L_LINE TYPE SY-LILLI.
G_SBDST_CLASSNAME = 'ZGTM_CONT'.
G_SBDST_CLASSTYPE = 'OT'.
LWA_SPOPLI-SELFLAG = 1.
LWA_SPOPLI-VAROPTION = TEXT-001.
APPEND LWA_SPOPLI TO LT_SPOPLI.
LWA_SPOPLI-SELFLAG = 2.
LWA_SPOPLI-VAROPTION = TEXT-002.
APPEND LWA_SPOPLI TO LT_SPOPLI.
LWA_SPOPLI-SELFLAG = 3.
LWA_SPOPLI-VAROPTION = TEXT-003.
APPEND LWA_SPOPLI TO LT_SPOPLI.
LWA_SPOPLI-SELFLAG = 4.
LWA_SPOPLI-VAROPTION = TEXT-004.
APPEND LWA_SPOPLI TO LT_SPOPLI.
LWA_SPOPLI-SELFLAG = 5.
LWA_SPOPLI-VAROPTION = TEXT-005.
APPEND LWA_SPOPLI TO LT_SPOPLI.
LWA_SPOPLI-SELFLAG = 6.
LWA_SPOPLI-VAROPTION = TEXT-006.
APPEND LWA_SPOPLI TO LT_SPOPLI.
CALL FUNCTION 'POPUP_TO_DECIDE_LIST'
EXPORTING
CURSORLINE = L_LINE
START_COL = 10
START_ROW = 10
TEXTLINE1 = ''
TITEL = '选择操作'
IMPORTING
ANSWER = L_ANSWER
TABLES
T_SPOPLI = LT_SPOPLI.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CASE L_ANSWER .
WHEN '1' .
G_CPNAME = 'HXKL.XLS' .
G_SBDST_OBJECT_KEY = 'ZFM_PRINT'.
WHEN '2' .
G_CPNAME = 'HXFXG.XLS' .
G_SBDST_OBJECT_KEY = 'ZFM_PRINT'.
WHEN '3' .
G_CPNAME = 'HXXG.XLS' .
G_SBDST_OBJECT_KEY = 'ZFM_PRINT'.
WHEN '4'.
G_CPNAME = 'HXMZ.XLS' .
G_SBDST_OBJECT_KEY = 'ZFM_PRINT'.
WHEN '5' .
G_CPNAME = 'HXGJYX.XLS' .
G_SBDST_OBJECT_KEY = 'ZFM_PRINT'.
WHEN '6' .
G_CPNAME = 'HXJCK.XLS' .
G_SBDST_OBJECT_KEY = 'ZFM_PRINT'.
WHEN OTHERS .
EXIT .
ENDCASE .
G_MACRO = 'Module1.INSERT_ROWS'.
LV_TKONN = IS_TKONN .
PERFORM FRM_GETDATA . "获取打印数据
PERFORM FRM_UNION . "合并数据
CALL SCREEN 9000 .
其中G_SBDST_CLASSNAME 是我新建的 ,通过SBDSV1
当这个函数被调用的时候,会出现一个选择操作的窗口 可以选择不同的模块,适用于多单据的打印
PERFORM FRM_GETDATA . "获取打印数据
PERFORM FRM_UNION .
为取数逻辑 及合并逻辑
LOOP AT GT_OUT INTO WA_OUT .
PERFORM SET_DATA USING 3 2 WA_OUT-NAME1.
…………………………
ENDLOOP .
FORM SET_DATA USING VALUE(U_ROW) TYPE ANY
VALUE(U_COL) TYPE ANY
VALUE(U_VALUE) TYPE ANY.
CLEAR WA_PRNT.
WA_PRNT-ROW = U_ROW.
WA_PRNT-COL = U_COL.
WA_PRNT-VALUE = U_VALUE.
APPEND WA_PRNT TO IT_PRNT.
ENDFORM. " SET_DATA
然后这个9000屏幕作为一个container
PROCESS BEFORE OUTPUT.
MODULE STATUS_9000.
MODULE CREATE_OBJ .
MODULE PRINT_TO_EXCEL .
PROCESS AFTER INPUT.
MODULE USER_COMMAND_9000.
具体各module如下:
PBO MODULE 1
MODULE CREATE_OBJ OUTPUT.
CALL FUNCTION 'GUI_HAS_ACTIVEX'
IMPORTING
RETURN = G_RETURN.
IF G_RETURN IS INITIAL.
MESSAGE S007(DEMOOFFICEINTEGRATIO) DISPLAY LIKE 'E'.
* 在此程序中使用 Windows GUI
LEAVE TO SCREEN 0.
ENDIF.
* * 获取sap doi接口
CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL
IMPORTING
CONTROL = G_I_OI_CONTAINER_CONTROL
ERROR = G_I_OI_ERROR.
CALL METHOD G_I_OI_ERROR->RAISE_MESSAGE
EXPORTING
TYPE = 'E'
EXCEPTIONS
MESSAGE_RAISED = 1
FLUSH_FAILED = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
* 创建CONTAINER
CREATE OBJECT G_CL_GUI_SPLITTER_CONTAINER
EXPORTING
PARENT = CL_GUI_CONTAINER=>SCREEN0
ROWS = 1
COLUMNS = 1
EXCEPTIONS
CNTL_ERROR = 1
CNTL_SYSTEM_ERROR = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CALL METHOD G_CL_GUI_SPLITTER_CONTAINER->SET_BORDER
EXPORTING
BORDER = CL_GUI_CFW=>FALSE
EXCEPTIONS
CNTL_ERROR = 1
CNTL_SYSTEM_ERROR = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
G_CL_GUI_CONTAINER = G_CL_GUI_SPLITTER_CONTAINER->GET_CONTAINER( ROW = 1 COLUMN = 1 ).
* 填充CONTAINER
CALL METHOD G_I_OI_CONTAINER_CONTROL->INIT_CONTROL
EXPORTING
R3_APPLICATION_NAME = 'R/3 application'
PARENT = G_CL_GUI_CONTAINER
INPLACE_ENABLED = ' '
NO_FLUSH = ' '
IMPORTING
ERROR = G_I_OI_ERROR
* RETCODE = G_SOI_RET_STRING
EXCEPTIONS
JAVABEANNOTSUPPORTED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CALL METHOD CL_GUI_CFW=>DISPATCH.
CREATE OBJECT G_CL_BDS_DOCUMENT_SET.
CHECK SY-SUBRC = 0 .
REFRESH: IT_SBDST_COMPONENTS,
IT_SBDST_SIGNATURE.
CALL METHOD CL_BDS_DOCUMENT_SET=>GET_INFO
EXPORTING
* LOGICAL_SYSTEM =
CLASSNAME = G_SBDST_CLASSNAME
CLASSTYPE = G_SBDST_CLASSTYPE
* CLIENT =
OBJECT_KEY = G_SBDST_OBJECT_KEY
* CHECK_STATE = ' '
* IMPORTING
* CONNECTIONS =
* EXTENDED_COMPONENTS =
CHANGING
COMPONENTS = IT_SBDST_COMPONENTS
SIGNATURE = IT_SBDST_SIGNATURE
EXCEPTIONS
NOTHING_FOUND = 1
ERROR_KPRO = 2
INTERNAL_ERROR = 3
PARAMETER_ERROR = 4
NOT_AUTHORIZED = 5
NOT_ALLOWED = 6
OTHERS = 7.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CALL METHOD CL_BDS_DOCUMENT_SET=>GET_WITH_URL
EXPORTING
* LOGICAL_SYSTEM =
CLASSNAME = G_SBDST_CLASSNAME
CLASSTYPE = G_SBDST_CLASSTYPE
* CLIENT =
OBJECT_KEY = G_SBDST_OBJECT_KEY
* URL_LIFETIME =
CHANGING
URIS = IT_SBDST_URI
SIGNATURE = IT_SBDST_SIGNATURE
* COMPONENTS =
EXCEPTIONS
NOTHING_FOUND = 1
ERROR_KPRO = 2
INTERNAL_ERROR = 3
PARAMETER_ERROR = 4
NOT_AUTHORIZED = 5
NOT_ALLOWED = 6
OTHERS = 7.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CLEAR:TEXT, OREF.
CALL METHOD G_I_OI_CONTAINER_CONTROL->GET_DOCUMENT_PROXY
EXPORTING
* DOCUMENT_FORMAT = 'NATIVE'
DOCUMENT_TYPE = 'Excel.Sheet'
* NO_FLUSH = ' '
* REGISTER_CONTAINER = ' '
IMPORTING
DOCUMENT_PROXY = G_I_OI_DOCUMENT_PROXY
ERROR = G_I_OI_ERROR
RETCODE = G_SOI_RET_STRING.
CONCATENATE '*' G_CPNAME '*' INTO G_CPNAME.
LOOP AT IT_SBDST_URI INTO WA_SBDST_URI WHERE URI CP G_CPNAME.
G_URI = WA_SBDST_URI-URI.
EXIT.
ENDLOOP.
* CHECK G_URI IS NOT INITIAL.
IF SY-SUBRC <> 0.
MESSAGE S052(ZMGTM_SD) DISPLAY LIKE 'E'.
LEAVE TO SCREEN 0.
ENDIF.
CALL METHOD G_I_OI_DOCUMENT_PROXY->OPEN_DOCUMENT
EXPORTING
* DOCUMENT_TITLE = ' '
DOCUMENT_URL = G_URI
* no_flush = 'X'
OPEN_INPLACE = 'X'
* OPEN_READONLY = ' '
* PROTECT_DOCUMENT = ' '
* ONSAVE_MACRO = ' '
* STARTUP_MACRO = ''
* USER_INFO =
IMPORTING
ERROR = G_I_OI_ERROR
RETCODE = G_SOI_RET_STRING.
IF G_SOI_RET_STRING <> 'OK'.
MESSAGE S066(ZMGTM_SD) DISPLAY LIKE 'E'.
LEAVE TO SCREEN 0.
ENDIF.
CALL METHOD G_I_OI_DOCUMENT_PROXY->GET_SPREADSHEET_INTERFACE
EXPORTING
NO_FLUSH = 'X'
IMPORTING
ERROR = G_I_OI_ERROR
SHEET_INTERFACE = G_I_OI_SPREADSHEET.
* retcode = g_soi_ret_string.
CALL METHOD G_I_OI_SPREADSHEET->SELECT_SHEET
EXPORTING
NAME = 'PI'
NO_FLUSH = ''
IMPORTING
ERROR = G_I_OI_ERROR.
* RETCODE = G_SOI_RET_STRING.
LV_LINE = LV_LINE - 3.
CHECK LV_LINE > 0.
DO LV_LINE TIMES.
CALL METHOD G_I_OI_DOCUMENT_PROXY->EXECUTE_MACRO
EXPORTING
MACRO_STRING = G_MACRO
SCRIPT_NAME = ''
IMPORTING
ERROR = G_I_OI_ERROR.
ENDDO.
ENDMODULE. " CREATE_OBJ OUTPUT
PBO MODULE 2
MODULE PRINT_TO_EXCEL OUTPUT.
DATA: L_IT_PRNT TYPE STANDARD TABLE OF SOI_GENERIC_ITEM,
L_WA_PRNT TYPE SOI_GENERIC_ITEM.
LOOP AT IT_PRNT INTO WA_PRNT.
CALL METHOD G_I_OI_SPREADSHEET->INSERT_RANGE_DIM
EXPORTING
NO_FLUSH = 'X'
NAME = 'CELL'
LEFT = WA_PRNT-COL
TOP = WA_PRNT-ROW
ROWS = 1
COLUMNS = 1
IMPORTING
ERROR = G_I_OI_ERROR.
CLEAR WA_SOI_RANGE_ITEM.
REFRESH IT_SOI_RANGE_ITEM.
WA_SOI_RANGE_ITEM-NAME = 'CELL'.
WA_SOI_RANGE_ITEM-ROWS = 1.
WA_SOI_RANGE_ITEM-COLUMNS = 1.
WA_SOI_RANGE_ITEM-CODE = '4'.
APPEND WA_SOI_RANGE_ITEM TO IT_SOI_RANGE_ITEM.
REFRESH L_IT_PRNT.
CLEAR L_WA_PRNT.
WRITE WA_PRNT-ROW TO L_WA_PRNT-ROW NO-GAP LEFT-JUSTIFIED.
WRITE WA_PRNT-COL TO L_WA_PRNT-COLUMN NO-GAP LEFT-JUSTIFIED.
WRITE WA_PRNT-VALUE TO L_WA_PRNT-VALUE NO-GAP LEFT-JUSTIFIED.
APPEND L_WA_PRNT TO L_IT_PRNT.
CALL METHOD G_I_OI_SPREADSHEET->SET_RANGES_DATA
EXPORTING
* NO_FLUSH = ' '
RANGES = IT_SOI_RANGE_ITEM
CONTENTS = L_IT_PRNT
* UPDATING = -1
* RANGESDEF =
IMPORTING
ERROR = G_I_OI_ERROR.
* call method g_i_oi_spreadsheet->fit_widest
* exporting
* name = space
* no_flush = 'X'.
REFRESH: IT_SOI_RANGE_ITEM, EXCEL_INPUT.
CLEAR WA_PRNT.
ENDLOOP.
ENDMODULE. " PRINT_TO_EXCEL OUTPUT
PAI MODULE 1
MODULE USER_COMMAND_9000 INPUT.
CASE OKCODE .
WHEN 'BACK' OR 'EXIT' .
IF NOT G_I_OI_DOCUMENT_PROXY IS INITIAL.
CALL METHOD G_I_OI_DOCUMENT_PROXY->CLOSE_DOCUMENT.
FREE G_I_OI_DOCUMENT_PROXY.
ENDIF.
IF NOT G_I_OI_CONTAINER_CONTROL IS INITIAL.
CALL METHOD G_I_OI_CONTAINER_CONTROL->DESTROY_CONTROL.
FREE G_I_OI_CONTAINER_CONTROL.
ENDIF.
SET SCREEN 0 .
LEAVE SCREEN .
ENDCASE .
ENDMODULE. " USER_COMMAND_9000 INPUT
至此 ,这个打印函数已经完成