(原创)不带模板的OLE输出EXCEL
目前作者已知的EXCEL输出方式有3种：
1.GUI_DOWNLOAD函数输出（适用于简单无格式要求的输出）。
2.OLE输出（适用于对EXCEL格式输出有特殊要求的，但是因其填充数据和设置格式是基于一个个单元格去操作来完成的，所以速度很慢，在下面的DEMO中，202行6列的数据输出需要用时2分钟左右）。
3.DOI输出（可以嵌入屏幕容器中直接输出EXCEL，且所有方法封装在类中，编写起来更加简单）。
个人感觉OLE的学习最好方式是设置EXCEL输出为前端显示模式，然后一句句弄懂每句OLE语句的意义，知道每一个OLE语句等同于在EXCEL中做了什么样的操作。
例如你写完
CREATE OBJECT V_EXCEL 'EXCEL.APPLICATION'. SET PROPERTY OF V_EXCEL 'VISIBLE' = 1.
你知道此时执行是打开了EXCEL应用，但是其中没有任何工作页，如下图：
当你在加上OLE语句
* "新建一个工作区WORKBOOK CALL METHOD OF V_EXCEL 'WORKBOOKS' = V_BOOK. CALL METHOD OF V_BOOK 'ADD'.
你知道此时创建了一个工作区，其中自动插入了一个工作页，且其为激活状态，如下图：
当你像这样一句句的做完一个DEMO之后，相信你应该能熟练应用OLE来输出EXCEL。
下面给出一个OLE的简单DEMO程序：
输出目标：
源程序：
主程序：Z15540_OLE1 *&---------------------------------------------------------------------* *& Report Z15540_0LE1 *&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* REPORT Z15540_OLE1. INCLUDE Z15540_OLE1_TOP. INCLUDE Z15540_OLE1_F01. START-OF-SELECTION. PERFORM FRM_GET_DATA. END-OF-SELECTION. IF GT_ALV IS NOT INITIAL. PERFORM FRM_SET_LAYOUT. PERFORM FRM_FILL_FIELD. PERFORM FRM_DISPLAY_ALV. ENDIF. 包含程序：Z15540_OLE1_TOP *&---------------------------------------------------------------------* *& 包含 Z15540_0LE1_TOP *&---------------------------------------------------------------------* TABLES:MARD,MARA,MAKT. TYPE-POOLS:SLIS,OLE2. DATA:BEGIN OF GS_ALV, MATNR TYPE MARD-MATNR, WERKS TYPE MARD-WERKS, LGORT TYPE MARD-LGORT, LABST TYPE MARD-LABST, MEINS TYPE MARA-MEINS, MAKTX TYPE MAKT-MAKTX, END OF GS_ALV. DATA GT_ALV LIKE TABLE OF GS_ALV. DATA:GS_LAYOUT TYPE LVC_S_LAYO, GS_FCAT TYPE LVC_S_FCAT, GT_FCAT TYPE LVC_T_FCAT. DATA:V_EXCEL TYPE OLE2_OBJECT, V_BOOK TYPE OLE2_OBJECT, V_SHEET TYPE OLE2_OBJECT, V_RANGE TYPE OLE2_OBJECT, V_CELL TYPE OLE2_OBJECT, V_FONT TYPE OLE2_OBJECT, V_COLOR TYPE OLE2_OBJECT, V_BORDER TYPE OLE2_OBJECT, V_COLUMN TYPE OLE2_OBJECT. DATA GV_FILENAME TYPE STRING."文件路径 SELECTION-SCREEN BEGIN OF BLOCK BLK1 WITH FRAME TITLE TEXT-001. SELECT-OPTIONS:S_WERKS FOR MARD-WERKS, S_LGORT FOR MARD-LGORT, S_MATNR FOR MARD-MATNR. SELECTION-SCREEN END OF BLOCK BLK1. 包含程序：Z15540_OLE1_F01 *&---------------------------------------------------------------------* *& 包含 Z15540_0LE1_F01 *&---------------------------------------------------------------------* *&---------------------------------------------------------------------* *& Form FRM_GET_DATA *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_GET_DATA . SELECT D~MATNR, D~WERKS, D~LGORT, D~LABST, A~MEINS, T~MAKTX INTO TABLE @GT_ALV UP TO 200 ROWS FROM MARD AS D INNER JOIN MARA AS A ON D~MATNR = A~MATNR LEFT OUTER JOIN MAKT AS T ON D~MATNR = T~MATNR WHERE D~WERKS IN @S_WERKS AND D~LGORT IN @S_LGORT AND D~MATNR IN @S_MATNR AND T~SPRAS = @SY-LANGU. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_DISPLAY_ALV *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_DISPLAY_ALV . CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC' EXPORTING * I_INTERFACE_CHECK = ' ' * I_BYPASSING_BUFFER = I_BYPASSING_BUFFER * I_BUFFER_ACTIVE = I_BUFFER_ACTIVE I_CALLBACK_PROGRAM = SY-REPID I_CALLBACK_PF_STATUS_SET = 'PF_STATUS_SET' I_CALLBACK_USER_COMMAND = 'USER_COMMAND' * I_CALLBACK_TOP_OF_PAGE = ' ' * I_CALLBACK_HTML_TOP_OF_PAGE = ' ' * I_CALLBACK_HTML_END_OF_LIST = ' ' * I_STRUCTURE_NAME = I_STRUCTURE_NAME * I_BACKGROUND_ID = ' ' * I_GRID_TITLE = I_GRID_TITLE * I_GRID_SETTINGS = I_GRID_SETTINGS IS_LAYOUT_LVC = GS_LAYOUT IT_FIELDCAT_LVC = GT_FCAT * IT_EXCLUDING = IT_EXCLUDING * IT_SPECIAL_GROUPS_LVC = IT_SPECIAL_GROUPS_LVC * IT_SORT_LVC = IT_SORT_LVC * IT_FILTER_LVC = IT_FILTER_LVC * IT_HYPERLINK = IT_HYPERLINK * IS_SEL_HIDE = IS_SEL_HIDE * I_DEFAULT = 'X' * I_SAVE = ' ' * IS_VARIANT = IS_VARIANT * IT_EVENTS = IT_EVENTS * IT_EVENT_EXIT = IT_EVENT_EXIT * IS_PRINT_LVC = IS_PRINT_LVC * IS_REPREP_ID_LVC = IS_REPREP_ID_LVC * I_SCREEN_START_COLUMN = 0 * I_SCREEN_START_LINE = 0 * I_SCREEN_END_COLUMN = 0 * I_SCREEN_END_LINE = 0 * I_HTML_HEIGHT_TOP = I_HTML_HEIGHT_TOP * I_HTML_HEIGHT_END = I_HTML_HEIGHT_END * IT_ALV_GRAPHICS = IT_ALV_GRAPHICS * IT_EXCEPT_QINFO_LVC = IT_EXCEPT_QINFO_LVC * IR_SALV_FULLSCREEN_ADAPTER = IR_SALV_FULLSCREEN_ADAPTER * IMPORTING * E_EXIT_CAUSED_BY_CALLER = E_EXIT_CAUSED_BY_CALLER * ES_EXIT_CAUSED_BY_USER = ES_EXIT_CAUSED_BY_USER TABLES T_OUTTAB = GT_ALV * EXCEPTIONS * PROGRAM_ERROR = 1 * OTHERS = 2 . IF SY-SUBRC <> 0. * Implement suitable error handling here ENDIF. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_SET_LAYOUT *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_SET_LAYOUT . CLEAR GS_LAYOUT. GS_LAYOUT-ZEBRA = 'X'. GS_LAYOUT-COL_OPT = 'X'. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_FILL_FIELD *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_FILL_FIELD . PERFORM FRM_FILLFIELD USING: 'MATNR' '物料号', 'MAKTX' '物料描述', 'WERKS' '工厂', 'LGORT' '仓库', 'LABST' '非限制使用库存', 'MEINS' '数量单位'. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_FILLFIELD *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> P_ *& --> P_ *&---------------------------------------------------------------------* FORM FRM_FILLFIELD USING P_FIELDNAME TYPE LVC_FNAME P_COLTEXT TYPE LVC_TXTCOL. CLEAR GS_FCAT. GS_FCAT-FIELDNAME = P_FIELDNAME. GS_FCAT-COLTEXT = P_COLTEXT. IF P_FIELDNAME = 'LABST'. GS_FCAT-QFIELDNAME = 'MEINS'. ENDIF. APPEND GS_FCAT TO GT_FCAT. ENDFORM. FORM PF_STATUS_SET USING RT_EXTAB TYPE SLIS_T_EXTAB. SET PF-STATUS 'STANDARD'. ENDFORM. FORM USER_COMMAND USING UCOMM LIKE SY-UCOMM "UCOMM获取返回的功能码 SELFIELD TYPE SLIS_SELFIELD."SELFIELD获取返回的行数据 CASE UCOMM. WHEN 'EXCEL'. "点击保存， PERFORM FRM_EXCEL. ENDCASE. ENDFORM. "USER_COMMAND *&------------------------------------------------------------------- *&---------------------------------------------------------------------* *& Form FRM_EXCEL *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_EXCEL . PERFORM FRM_FILE. "创建EXCEL对象 CREATE OBJECT V_EXCEL 'EXCEL.APPLICATION'. "设置EXCEL前台显示 SET PROPERTY OF V_EXCEL 'VISIBLE' = 1. * "新建一个工作页WORKBOOK CALL METHOD OF V_EXCEL 'WORKBOOKS' = V_BOOK. CALL METHOD OF V_BOOK 'ADD'. "获取当前SHEET页 GET PROPERTY OF V_EXCEL 'ACTIVESHEET' = V_SHEET. "选中SHEET页 CALL METHOD OF V_SHEET 'SELECT'. "更改当前SHEET页 名称 SET PROPERTY OF V_SHEET 'NAME' = '可用库存量清单SHEET1'. "填充标题和表头行 PERFORM FRM_HEADER. "填充主体部分 PERFORM FRM_BODY. "设置列宽自动优化(在数据填充完后调用) CALL METHOD OF V_EXCEL 'COLUMNS' = V_COLUMN. CALL METHOD OF V_COLUMN 'AUTOFIT'. "保存EXCEL PERFORM FRM_SAVE. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_CELL *&---------------------------------------------------------------------* *& text *&-------------------------------------------*& --> P_1 *& --> P_ *& --> P_2 *&---------------------------------------------------------------------* FORM FRM_CELL USING P_ROW "行号 P_COL "列号 P_VALUE "值 P_BOLD "加粗 P_SIZE "字体大小 P_UNDERLINE "下划线 P_FCOLOR "字体颜色 P_COLOR."背景颜色 CALL METHOD OF V_EXCEL 'CELLS' = V_CELL EXPORTING #1 = P_ROW #2 = P_COL. SET PROPERTY OF V_CELL 'VALUE' = P_VALUE. "生成字体对象 CALL METHOD OF V_CELL 'FONT' = V_FONT. SET PROPERTY OF V_FONT 'BOLD' = P_BOLD."加粗 SET PROPERTY OF V_FONT 'SIZE' = P_SIZE."字体大小 SET PROPERTY OF V_FONT 'UNDERLINE' = P_UNDERLINE."下划线 SET PROPERTY OF V_FONT 'COLORINDEX' = P_FCOLOR."字体颜色 "生成背景颜色对象 CALL METHOD OF V_CELL 'INTERIOR' = V_COLOR. SET PROPERTY OF V_COLOR 'COLORINDEX' = P_COLOR."背景颜色 FREE OBJECT V_CELL."用完对象后释放 ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_SAVE *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> P_ *&---------------------------------------------------------------------* FORM FRM_SAVE . CALL METHOD OF V_SHEET 'SAVEAS' EXPORTING #1 = GV_FILENAME #2 = 1. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_HEADER *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&------------------------------------------------------------------FORM FRM_HEADER . "标题 PERFORM FRM_CELL USING 1 1 '可用库存量清单' 1 18 2 3 8."行号 列号 值 加粗 字体大小 下划线 字体颜色（红） 背景颜色（蓝） PERFORM FRM_RANGE USING '1' '1' 1 -4108 1 2 1."左上角列号 右下角列号 合并 居中 边框格式 边框格式 边框粗细 边框颜色 "表头行 PERFORM FRM_CELL USING: 2 1 '工厂' 1 10 1 1 2,"行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） 2 2 '库存地点' 1 10 1 1 2, 2 3 '物料号' 1 10 1 1 2, 2 4 '物料描述' 1 10 1 1 2, 2 5 '可用非限制库存' 1 10 1 1 2, 2 6 '单位' 1 10 1 1 2. PERFORM FRM_RANGE USING '2' '2' 0 -4108 1 2 1."左上角列号 右下角列号 合并 居中 边框格式 边框格式 边框粗细 边框颜色 ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_RANGE *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_RANGE USING P_ZS TYPE CHAR5"左上角列号 P_YX TYPE CHAR5"右下角列号 P_MERGECELLS "合并 P_ALIGNMENT "居中 P_LINESTYLE "边框格式 P_WEIGHT "边框粗细 P_BCOLOR."边框颜色 CONCATENATE 'A' P_ZS INTO P_ZS. CONCATENATE 'F' P_YX INTO P_YX. CONDENSE P_ZS NO-GAPS. CONDENSE P_YX NO-GAPS. "创建RANGE对象 CALL METHOD OF V_EXCEL 'RANGE' = V_RANGE EXPORTING #1 = P_ZS #2 = P_YX. "选中RANGE CALL METHOD OF V_RANGE 'SELECT'. SET PROPERTY OF V_RANGE 'MERGECELLS' = P_MERGECELLS."0不合并 1合并 SET PROPERTY OF V_RANGE 'HORIZONTALALIGNMENT' = P_ALIGNMENT."居中 DO 4 TIMES. "创建BORDER对象 CALL METHOD OF V_RANGE 'BORDERS' = V_BORDER EXPORTING #1 = SY-INDEX. "设置线条格式，粗细，颜色 SET PROPERTY OF V_BORDER 'LINESTYLE' = P_LINESTYLE. SET PROPERTY OF V_BORDER 'WEIGHT' = P_WEIGHT. SET PROPERTY OF V_BORDER 'COLORINDEX' = P_BCOLOR. ENDDO. "用完释放对象 FREE OBJECT V_RANGE. FREE OBJECT V_BORDER. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_BODY *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_BODY . DATA LV_ZS TYPE CHAR5."左上 DATA LV_YX TYPE CHAR5."右下 DATA LV_LINE TYPE I."输出内表总行数 DATA LV_CURRENT TYPE I."当前填充的行号 DESCRIBE TABLE GT_ALV LINES LV_LINE. LV_ZS = 3."3 = 2(标题和表头行共计2行) + 1. LV_YX = 2 + LV_LINE."标题+表头+主体 CONDENSE LV_YX NO-GAPS."去除空格 "填充数据 LOOP AT GT_ALV INTO GS_ALV. LV_CURRENT = SY-TABIX + 2."从第3行开始填充主体数据 "每行需填充6列的数据 PERFORM FRM_CELL USING: LV_CURRENT 1 GS_ALV-WERKS 0 10 2 1 2,"行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） LV_CURRENT 2 GS_ALV-LGORT 0 10 2 1 2,"行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） LV_CURRENT 3 GS_ALV-MATNR 0 10 2 1 2,"行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） LV_CURRENT 4 GS_ALV-MAKTX 0 10 2 1 2,"行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） LV_CURRENT 5 GS_ALV-LABST 0 10 2 1 2,"行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） LV_CURRENT 6 GS_ALV-MEINS 0 10 2 1 2."行号 列号 值 加粗 字体大小 下划线 字体颜色（黑） 背景颜色（白） CLEAR GS_ALV. ENDLOOP. "加边框 PERFORM FRM_RANGE USING LV_ZS LV_YX 0 -4108 1 2 1."左上角列号 右下角列号 合并 居中 边框格式 边框格式 边框粗细 边框颜色 ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_FILE *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM FRM_FILE . *获取文件名 CALL FUNCTION 'WS_FILENAME_GET' EXPORTING DEF_FILENAME = '可用库存清单' "默认文件名 * DEF_PATH = ' ' MASK = ',EXCEL,*.XLS.' "文件类型 MODE = 'S' "S保存 O打开 TITLE = 'EXCEL保存' "弹出对话框标题 IMPORTING FILENAME = GV_FILENAME "输出的文件路径 * RC = RC EXCEPTIONS INV_WINSYS = 1 NO_BATCH = 2 SELECTION_CANCEL = 3 SELECTION_ERROR = 4. ENDFORM.
