抱歉，您的浏览器无法访问本站
本页面需要浏览器支持（启用）JavaScript
了解详情 >

Excel 下载

ABAPExcel Donwload

发布于：2023年5月20日

次浏览

ABAP Excel下载三种方式的代码和技术说明，分别介绍了OLE、DOI和XML和ALV Tree下载的内容。

Excel 下载

一、Excel下载方式概览

1.OLE

  • 早期SAP解决与Office集成的技术。
  • OLE语法参照VBA，在ABAP中调用方法来操作Excel。
  • 语法的友好性差，另外将数据从ABAP内表写入到Excel速度特别慢。

2.DOI

  • DOI时Desktop Office Integration的缩写，是SAP提供的解决与Office集成的技术方案。
  • DOI可以看作OLE的替代品，用面向对象的方式实现。

3.XML

  • 使用Open Office技术，实现与ALV自带下载功能一致的下载效果。
  • 使用方法简单适合于下载ALV报表，可以直接使用ALV设置的FIELDCATALOG，LAYOUT，SORT等属性对下载数据设置格式，无需其他数据转换。

二、Excel 代码共通部分

1.全局变量的定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
*Excel File Name Variables
DATA:
  GV_TITLE			TYPE    STRING,
  GV_COL			TYPE	I,
  GV_ROW			TYPE	I.

TYPES:
  BEGIN OF TY_LONG,
    DATA			TYPE C LENGTH 8192,
  END   OF TY_LONG.

*Excel related variables OLE
DATA:
  GT_DOW_DATA		TYPE TABLE OF TY_LONG.
DATA:
  GO_EXCEL			TYPE 	OLE2_OBJECT,
  GO_BOOKS			TYPE    OLE2_OBJECT,
  GO_SHEET			TYPE    OLE2_OBJECT,
  GO_COLUMNS		TYPE    OLE2_OBJECT,
  GO_CELLINT		TYPE    OLE2_OBJECT,
  GO_RANGE			TYPE    OLE2_OBJECT,
  GO_BORDER			TYPE    OLE2_OBJECT.
  
*Excel related variables DOI
DATA:
  GO_CONTROL		TYPE REF TO I_OI_CONTAINER_CONTROL,
  GO_DOCU_PROXY		TYPE REF TO I_OI_DOCUMENT_PROXY,
  GO_DOISHEET		TYPE REF TO I_OI_SPREADSHEET,
  GO_ERROR			TYPE REF TO I_OI_ERROR.

2.获取下载文件的路径

（1）获取下载路径与检测文件存在与否模板
1
2
3
4
5
6
7
8
9
10
11
PERFORM GET_FILE_PATH CHANGING LV_FULLPATH.

FORM GET_FILE_PATH CHANGING VALUE(PV_FULLPATH) TYPE STRING.
  CLEAR: GV_MESSAGE.
  "Get download path
  PERFORM GET_DOWNLOAD_FILE CHANGING PV_FULLPATH.
  CHECK GV_MESSAGE IS INITIAL.
  
  "Check that download files exist,and if they do,delete
  PERFORM CHECK_FILE_EXIS	USING PV_FULLPATH.
ENDFORM.
（2）获取下载路径
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
FORM GET_DOWNLOAD_FILE CHANGING VALUE(PV_PATH) TYPE STRING.
*---------------------------Variables----------------------------*
  CONSTANTS:
    LC__        TYPE C LENGTH 1  VALUE '_'.
  DATA:
    LV_FNAME      TYPE STRING,
    LV_PATH       TYPE STRING,
    LV_FILENAME     TYPE STRING,
    LV_TYPENAME     TYPE STRING VALUE 'xlsx',
    LV_TITLE      TYPE STRING VALUE 'Excel Downolad'.
*-----------------------------Logic------------------------------*
  "Set Download file Name
  CONCATENATE GV_TITLE SY-DATUM SY-UZEIT INTO LV_FILENAME
    SEPARATED BY LC__.

  "Gets the current default path.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>DIRECTORY_GET_CURRENT
    CHANGING
      CURRENT_DIRECTORY    = PV_PATH.

  "Shell selection save path
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
    EXPORTING
      WINDOW_TITLE      = LV_TITLE
      DEFAULT_EXTENSION   = LV_TYPENAME
      DEFAULT_FILE_NAME   = LV_FILENAME
      FILE_FILTER           = CL_GUI_FRONTEND_SERVICES=>FILETYPE_ALL
      INITIAL_DIRECTORY     = PV_PATH
    CHANGING
      FILENAME        = LV_FNAME
      PATH          = LV_PATH
      FULLPATH        = PV_PATH
    EXCEPTIONS
      CNTL_ERROR      = 1
      ERROR_NO_GUI      = 2
      NOT_SUPPORTED_BY_GUI  = 3
      OTHERS        = 5.
  IF PV_PATH IS INITIAL.
    GV_MESSAGE = GC_X.
    "Download File Path Error
    MESSAGE TEXT-E01 TYPE GC_S DISPLAY LIKE GC_E.
    EXIT.
  ENDIF.
ENDFORM.
（3）检查文件存在与否
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
FORM CHECK_FILE_EXIS	USING VALUE(PV_PATH) TYPE STRING.
*---------------------------Variables----------------------------*
  CONSTANTS:
    LC_I        TYPE C LENGTH 1  VALUE 'I'.
  DATA:
    LV_RC       TYPE I,
    LV_RES        TYPE C.
*-----------------------------Logic------------------------------*
  "Does the verification file exist?
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_EXIST
    EXPORTING
      FILE                 = PV_PATH
    RECEIVING
      RESULT               = LV_RES
    EXCEPTIONS
      CNTL_ERROR           = 1
      ERROR_NO_GUI         = 2
      WRONG_PARAMETER      = 3
      NOT_SUPPORTED_BY_GUI = 4
      OTHERS               = 5.

  "Test results
  CHECK LV_RES EQ GC_X.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_DELETE
    EXPORTING
      FILENAME              = PV_PATH
    CHANGING
      RC                    = LV_RC
    EXCEPTIONS
      FILE_DELETE_FAILED    = 1
      CNTL_ERROR            = 2
      ERROR_NO_GUI          = 3
      FILE_NOT_FOUND        = 4
      ACCESS_DENIED         = 5
      UNKNOWN_ERROR         = 6
      NOT_SUPPORTED_BY_GUI  = 7
      WRONG_PARAMETER       = 8
      OTHERS                = 9.
  IF SY-SUBRC NE 0.
    GV_MESSAGE = GC_X.
    "File Opened
    MESSAGE TEXT-E02 TYPE GC_I DISPLAY LIKE GC_E.
  ENDIF.
ENDFORM.

三、下载SMW0中的模板

SMW0上传的模板，直接下载数据到本地。结合上面的获取下载路径，将获取到的下载路径填入变量 PV_PATH中即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CONSTANTS:
  LC_I					TYPE C LENGTH 1 VALUE 'I',
  LC_MI					TYPE C LENGTH 2 VALUE 'MI'.
DATA:
  LV_FNAME				TYPE RLGRAP-FILENAME,
  LV_KEY				TYPE WWWDATATAB,
  LV_RC					TYPE I,
  LV_OBJID				TYPE WWWDATA-OBJID VALUE '上传时的名称'.

SELECT SINGLE RELID
  FROM WWWDATA
  INTO @DATA(LV_RELID)
 WHERE RELID = @LC_MI
   AND OBJID = @LV_OBJID
   AND SRTF2 = 0.
IF SY-SUBRC <> 0.
  "Template file download faild.
  MESSAGE TEXT-E29 TYPE LC_I DISPLAY LIKE GC_E.
  EXIT.
ENDIF.

LV_FNAME = PV_PATH.
CONCATENATE LC_MI LV_OBJID INTO LV_KEY.

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
  EXPORTING
    KEY				= LV_KEY
    DESTINATION 	= LV_FNAME
  IMPORTING
    RC				= LV_RC.

四、OLE下载

1.技术要点

（1）方法说明
①创建实例
1
CREATE OBJECT name_obj 'app'.
②设置属性
1
SET PROPERTY OF name_obj 'XXX' = f.
③读取属性
1
GET PROPERTY OF name_obj 'XXX' = f.
④调用实例方法
1
2
3
4
CALL METHOD OF name_obj 
    'XXX' = f		"接收返回值
  EXPORTING
    #1	  = F1.		"调用name_obj的方法XXX 传入参数f1...fn
⑤释放实例
1
FREE OBJECT name_obj.
（2）打开Excel并激活工作表
①打开Excel
1
CREATE OBJECT GO_EXCEL 'Excel.Application'.
②设置Excel可见
1
SET PROPERTY OF GO_EXCEL 'Visible' = 1.
③创建空白工作簿
1
CALL METHOD OF GO_EXCEL 'Workbooks' = GO_BOOKS.
④创建工作表
1
CALL METHOD OF GO_BOOKS 'Add' = GO_BOOK.
⑤选择工作表
1
2
3
CALL METHOD OF GO_EXCEL 'Workwheets' = GO_BOOK.
  EXPORTING
    #1			= 1.
⑥设置工作表名
1
SET PROPERTY OF GO_BOOK 'Name' = 'Sheet1'.
⑦激活工作表
1
CALL METHOD OF GO_BOOK 'Activate'.
（3）选择单元格赋值

通过将数据复制到粘贴板进行数据插入

①清空单元格内容
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
  EXPORTING
    #1				= 1
    #2				= 2.
GET PROPERTY OF GO_EXCEL 'ActiveCell' = LOCELL2.

CALL METHOD OF LOCELL2 '' = LO_CELL3
  EXPORTING
    #1				= '11'. "xlLastCell
    
CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
  EXPORTING
    #1				= LO_CELL1
    #2				= LO_CELL3.

CALL METHOD OF GO_RANGE 'ClearContents'.
②将要下载的表格复制到剪贴板
1
2
3
4
5
6
7
8
9
10
CALL METHOD CL_GUI_FRONTEND_SERICES=>CLIPBOARD_EXPORT
  IMPORTING
    DATA			= LT_CLIPTAB[]
  CHANGING
    RC				= LV_RD
  EXCEPTIONS
    CNTL_ERROR				= 1
    ERROR_NO_GUI			= 2
    NOT_SUPPORTED_BY_GUI	= 3
    OTHER					= 4.
③粘贴数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
  EXPORTING
    #1				= 1
    #2				= 1.
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL2
  EXPORTING
    #1				= LV_ROW
    #2				= LV_COL.
CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
  EXPORTING
    #1				= LO_CELL1
    #2				= LO_CELL2.

CALL METHOD OF GO_RANGE 'Select'.
CALL METHOD OF GO_SHEET	'Paste'.
（4）设置单元格格式
①单元格公式
  • 行合计：PV_VALUE = ‘=SUM(RC[1]:RC[7])’ 合计第一列到第七列的数据
  • 列合计：PV_VALUE = ‘=SUM(R[1]:R[7]C)’ 合计第一行到第七行数据
②设置单个单元格数据
1
2
3
4
5
6
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL
  EXPORTING 
    #1				= PV_ROW
    #2				= PV_COL.
SET PROPERTY OF LO_CELL 'Value' = PV_VAL.
FREE OBJECT LO_CELL.
③设置数据类型

‘@’：常规

‘#，##0’：金额

1
SET PROPERTY OF GO_RANGE ‘NumberFormat’ = '@'.
③对齐方式
  • 2：左对齐
  • 3：居中
  • 4：右对齐
1
SET PROPERTY OF GO_RANGE 'HorizontalAlignment' = 2.
④颜色

色图编号图：

OLE 颜色图表 
1
2
CALL METHOD OF GO_RANGE 'Interior' = GO_CELLINT.
SET PROPERTY OF GO_CELLINT 'Colorindex' = PV_COLOR.
⑤边框
1
2
3
CALL METHOD OF GO_RANGE 'Borders' = GO_BORDER.		"实例化边框对象
SET PROPERTY OF GO_BORDER 'Weight' = 2.				"设置边框宽度
SET PORPERTY OF GO_BORDER 'Linestyle' = 1.			"设置边框类型（1实线）
⑥自适应宽度
1
2
CALL METHOD OF GO_EXCEL ' Columns' = GO_COLUMNS.
CALL METHOD OF GO_COLUMNS 'AutoFit'.
⑦执行宏
1
2
3
CALL METHOD OF GO_EXCEL 'RUN'
  EXPORTING 
    #1				= 'ZMAC'.
（5）OLE保存文件
①保存文件
1
CALL METHOD OF GO_BOOKS 'Save'.
②另存为
1
2
3
4
CALL METHOD OF GO_BOOKS 'SaveAs'
  EXPORTING
    #1				= '新的地址'
    #2				= 1.
③关闭工作区
1
CALL METHOD OF GO_BOOKS 'Colse'.
④退出Excel
1
CALL METHOD OF GO_EXCEL 'Quit'.

2.流程代码

OLE的下载流程主要可以概括为三个阶段：

  • 获取下载用的数据
  • 下载用于存放数据的Excel文件
  • 设置数据在Excel中的格式
1
2
3
4
5
6
7
8
9
10
FROM DOWNLOAD_EXCEL_OLE CHANGING VALUE(PV_FULLPATH) TYPE STRING.
  "Get Download data
  PERFORM GET_DOWNLOAD_DATA USING PV_FULLPATH.
  
  "Download EXCEL
  PERFORM DOWNLOAD_FILE     USING PV_FULLPATH.
  
  "Store data stream and download data
  PERFORM DOWNLOAD_DATA     USING PV_FULLPATH.
ENDFORM.

3.流程源代码

（1）获取下载数据(GET_DOWNLOAD_DATA)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
FORM GET_DOWNLOAD_DATA USING VALUE(PV_FULLPATH).
*---------------------------Variables----------------------------*
  CONSTANTS:
    LC_FU 				TYPE C LENGTH 1 VALUE '-',
    LC_D				TYPE C LENGTH 1 VALUE 'D',
    LC_DIAN				TYPE C LENGTH 1 VALUE ','.
  DATA:
    LV_INDEX			TYPE I VALUE 1,
    LV_DATA				TYPE STRING,
    LV_YEAR				TYPE C LENGTH 4,
    LV_MONTH			TYPE C LENGTH 2,
    LV_DAY				TYPE C LENGTH 2,
    LV_LENGTH			TYPE I,
    LS_DOW				TYPE TY_LONG,
    LT_FIELDCATALOG		LIKE GT_FIELDCATALOG,
    LS_FIELDCATALOG		LIKE LINE OF GT_FIELDCATALOG,
    LT_TABLE			LIKE GT_TABLE.
  FIELD-SYMBOLS:<FS_FIELDNAME>.
*-----------------------------Logic------------------------------*
  REFRESH: GT_DOW_DATA.
  
  "Set the column name of Excel
  APPEND LINES OF GT_FIELDCATALOG TO LT_FIELDCATALOG.
  DELETE LT_FIELDCATALOG WHERE TECH = GC_X.
  LOOP AT LT_FIELDCATALOG INTO LS_FIELDCATALOG.
  	IF SY-TABIX EQ 1.
  	  LS_DOW = LS_FIELDCATALOG-COLTEXT.
  	ELSE.
  	  CONCATENATE LS_DOW LS_FIELDCATALOG-COLTEXT INTO LS_DOW
  	    SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.
  	ENDIF.
  	CLEAR: LS_FIELDCATALOG.
  ENDLOOP.
  
  APPEND LS_DOW TO GT_DOW_DATA.
  
  "Fill in the data to be saved
  APPEND LINES OF GT_TABLE TO LT_TABLE.
  LOOP AT LT_TABLE INTO GS_TABLE.
    LOOP AT LT_FIELDCATALOG INTO LS_FIELDCATALOG.
      LV_INDEX = SY-TABIX.
      ASSIGN COMPONENT LS_FIELDCATALOG-FIELDNAME 
        OF STRUCTURE GS_TABLE TO <FV_VALUE>.
      IF SY-SUBRC EQ 0.
        LV_DATA = <FV_VALUE>.
        
        "Format date
        LV_LENGTH = STRLEN( LV_DATA ).
        IF LS_FIELDCATALOG-INTTYPE EQ LC_D AND LV_LENGTH EQ 8
          AND <FV_VALUE> IS NOT INITIAL.
		  LV_YEAR = LV_DATA+0(4).
		  LV_MONTH = LV_DATA+4(2).
		  LV_DAY = LV_DATA+6.
		  CONCATENATE LV_YEAR LV_MONTH LV_DAY INTO LV_DATA
		    SEPARATED BY LC_DIAN.
		ELSEIF LS_FIELDCATALOG-INTTYPE EQ LC_D AND 
		  <FV_VALUE> IS INITIAL.
		  CLEAR LV_DATA.
		ENDIF.
		
		IF LV_INDEX EQ 1.
		  LS_DOW = LV_DATA.
		ELSE.
		  CONCATENATE LS_DOW LV_DATA INTO LS_DOW
		    SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.
		ENDIF.
      ENDIF.
      
      CLEAR: LV_DATA,LS_FIELDCATALOG.
    ENDLOOP.
    
    APPEND LS_DOW TO GT_DOW_DATA.
    CLEAR: GS_TABLE,LS_DOW.
  ENDLOOP.
  
  "Set the total number of rows and columns
  GV_ROW = LINES( GT_DOW_DATA ).
  GV_COL = LINES( LT_FIELDCATALOG ).
  FREE: LT_FIELDCATALOG,LT_TABLE.
ENDFORM.
（2）获取下载的Excel文件(DOWNLOAD_FILE)

使用这个Function，他传入的参数GT_DOW_DATA不能为空，即必须有值才可以下载一个Excel。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
FROM DOWNLOAD_FILE USING VALUE(PV_FULLPATH).
*---------------------------Variables----------------------------*
  DATA:
    LV_FILE_NAME			TYPE RLGRAP-FILENAME.
*-----------------------------Logic------------------------------*
  LV_FILE_NAME = PV_FULLPATH.
  CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
    EXPORTING
      I_FILENAME			= LV_FILE_NAME
    TABLES
      I_TAB_SAP_DATA		= GT_DOW_DATA
    EXCEPTIONS
      CONVERSION_FAILED		= 1
      OTHER					= 2.
ENDFORM.

此处另外一种下载Excel文件的Function，这个Function是同事提供的，我自己暂时还没用过。

1
2
3
4
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
  EXPORTING
    KEY					= LS_KEY
    DESTINATION			= CONV RLGRAP-FILENAME( PV_FILENAME ).
（3）将数据下载到Excel(DOWNLOAD_DATA)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
FORM DOWNLOAD_DATA USING VALUE(PV_FULLPATH).
*---------------------------Variables----------------------------*
  DATA:
    LV_SHEETNAME			TYPE STRING.
*-----------------------------Logic------------------------------*
  "Create excel object
  CREATE OBJECT GO_EXCEL 'EXCEL.APPLICATION'.
  "Create unified file
  CALL METHOD OF GO_EXCEL 'WORKBOOKS' = GO_BOOKS.
  CALL METHOD OF GO_BOOKS 'OPEN' = GO_BOOKS
    EXPORTING #1 = PV_FULLPATH.
    
  "Table name / column name
  CALL METHOD OF GO_EXCEL 'WORKSHEETS' = GO_SHEET
  	EXPORTING #1 = 1.
  CALL METHOD OF GO_SHEET 'Activate'.
  
  GET PROPERTY OF GO_EXCEL 'ActiveWorkbook' = GO_BOOKS.
  
  "Excel data operation
  PERFORM EXCEL_MAIN_DATA.
  
  SET PROPERTY OF GO_EXCEL 'Visible' = 1.
  
  "Set Excel Sheet Name
  CONCATENATE GV_TITLE GC_TITLE INTO LV_SHEETNAME
    SEPARATED BY SPACE.
    
  SET PROPERTY OF GO_SHEET 'Name' = LV_SHEETNAME.
  CALL METHOD OF GO_BOOKS 'Save'.
  
  FREE OBJECT: GO_EXCEL,GO_BOOKS,GO_SHEET.
ENDFORM.
（4）将数据Copy到Excel中(EXCEL_MAIN_DATA)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
FORM EXCEL_MAIN_DATA.
*---------------------------Variables----------------------------*
  DATA:
    LV_RD				TYPE I,
    LO_CELL1			TYPE OLE2_OBJECT,
    LO_CELL2			TYPE OLE2_OBJECT,
    LO_CELL3			TYPE OLE2_OBJECT.
*-----------------------------Logic------------------------------*
	"Clear contents
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
	  EXPORTING
	    #1 = 1
	    #2 = 1.
	GET PROPERTY OF GO_EXCEL 'ActiveCell' = LO_CELL2.
	
	CALL METHOD OF LO_CELL2 'SpecialCells' = LO_CELL3
	  EXPORTING
	    #1 = '9'."xl Last Cell
	
	CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
	  EXPORTING
	    #1 = LO_CELL1
	    #2 = LO_CELL3.
	
	CALL METHOD OF GO_RANGE 'ClearContents'.
	FREE OBJECT: LO_CELL1, LO_CELL2, LOCELL3, GO_RANGE.
	
	"Copy to Clipboard
	CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
	  IMPORTING
	    DATA				= GT_DOW_DATA
	  CHANGING
	    RC					= LV_RD
	  EXCEPTIONS
	    CNTL_ERROR			 = 1
	    ERROR_NO_GUI		 = 2
	    NOT_SUPPORTED_BY_GUI = 3
		OTHERS				 =4.
		
	"Paste to excel
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
	  EXPORTING
	    #1 = 1
	    #2 = 1.
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL2
	  EXPORTING
	    #1 = GV_ROW
	    #2 = GV_COL.
	CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
	  EXPORTING
	    #1 = LO_CELL1
	    #2 = LO_CELL2.
	
	CALL METHOD OF GO_RANGE 'Select'.
	
	CALL METHOD OF GO_SHEET 'Paste'.
	
	"Center Alignment
	SET PROPERTY OF GO_RANGE 'VerticalAlignment' = 2.
	
	"Borders
	CALL METHOD OF GO_RANGE 'Borders' = GO_BORDER.
	SET PROPERTY OF GO_BORDER 'Weight' = 2.
	SET PROPERTY OF GO_BORDER 'Linestyle' = 1.
	
	"Auto adjust column width
	CALL METHOD OF GO_EXCEL 'Colimns' = GO_COLUMNS.
	CALL METHOD OF GO_COLUMNS 'AutoFit'.
	
	FREE OBJECT: LO_CELL1, LO_CELL2, GO_RANGE.
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
	  EXPORTING
	    #1 = 1
	    #2 = 1.
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL2
	  EXPORTING
	    #1 = 1
	    #2 = GV_COL.
	CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
	  EXPORTING
	    #1 = LO_CELL1
	    #2 = LO_CELL2.
	
	"Cell color: gray
	CALL METHOD OF GO_RANGE 'Interior' = GO_CELLINT.
	SET PROPERTY OF GO_CELLINT 'Colorindex' = '15'.
	
	"Set Data Alignment
	PERFORM SET_ALIGNMENT.
ENDFORM.
（5）根据字段目录设置Excel对齐方式(SET_ALIGNMENT)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
FORM SET_ALIGNMENT.
*---------------------------Variables----------------------------*
  CONSTANTS:
    LC_L				TYPE C LENGTH 1  VALUE 'L',
    LC_C				TYPE C LENGTH 1  VALUE 'C',
    LC_R				TYPE C LENGTH 1  VALUE 'R'.
  DATA:
    LO_CELL1			TYPE OLE2_OBJECT,
    LO_CELL2			TYPE OLE2_OBJECT.
  DATA:
    LV_JUST				TYPE I,
    LV_COL				TYPE I VALUE 1,
    LS_FIELDCATALOG		LIKE LINE OF GT_FIELDCATALOG.
*-----------------------------Logic------------------------------*
  LOOP GT_FIELDCATALOG INTO LS_FIELDCATALOG WHERE TECH <> GC_X.
    "Select Action Column
    FREE OBJECT: LO_CELL1, LO_CELL2, GO_RANGE.
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
	  EXPORTING
	    #1 = 1
	    #2 = LV_COL.
	CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL2
	  EXPORTING
	    #1 = GV_ROW
	    #2 = GV_COL.
	CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
	  EXPORTING
	    #1 = LO_CELL1
	    #2 = LO_CELL2.
    
    "Set alignment.
    CASE LS_FIELDCATALOG-JUST.
      WHEN LC_L.
        LV_JUST = 2.
      WHEN LC_C.
        LV_JUST = 3.
      WHEN OTHERS.
        LV_JUST = 4.
    ENDCASE.
    
    "Reflect alignment to Excel
  	SET PROPERTY OF GO_RANGE 'HorizontalAlignment' = LV_JUST.
  	
  	LV_COL = LV_COL + 1.
  	CLEAR: LS_FIELDCATALOG.
  ENDLOOP.
ENDFORM.

五、DOI下载

1.技术要点

（1）容器对象
①Container

存放Excel电子表格（Spreadsheet）的容器。操作这个对象就相当于操作Excel文件。

②Container Control

容器中用于创建和管理其他Office集成所需要的对象，Container Control是一个接口。

使用的Class类为：I_OI_CONTAINER_CONTROL

③Document Proxy

每一个document proxy的实例代表用Office Application打开的文档，可以是Excel，可以是Word，如果想打开多个文档，需要定义多个实例。document proxy是一个接口。

使用的Class类为：I_OI_DOCUMENT_PROXY

④Spreadsheet

Spreadsheet接口，代表最终要操作的Excel文档。

使用的Class类为：I_OI_SPREADSHEET

（2）通过上传模板下载Excel文件

下面的流程代码中使用的是无模板的形式创建一个Excel文件，所以此处介绍一下通过SMW0和OAOR的形式上传Excel模板然后下载Excel的方式。

①SMW0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
DATA:
  LT_DOC_TABLE				LIKE TABLE OF W3MIME,
  LV_DOC_SIZE				TYPE I,
  LV_DOC_TYPE(80)			VALUE SOI_DOCTYPE_EXCEL_SHEET,
  LV_DOC_FORMAT(80),
  LV_RETCODE				TYPE SOI_RET_STRING,
  LV_OBJID					TYPE WWWDATA-OBJID VALUE 'Z9050'."SMW0上传文件名称
  
CALL FUNCTION 'SAP_OI_LOAD_MIME_DATA'
  EXPORTING
    OBJECT_ID			= LV_OBJID
  IMPORTING
    DATA_SIZE			= LV_DOC_SIZE
    DOCUMENT_FORMAT		= LV_DOC_FORMAT
    DOCUMENT_TYPE		= LV_DOC_TYPE
  TABLES
    DATA_TABLE			= LT_DOC_TABLE
  EXCEPTIONS
    OBJECT_NOT_FOUND	= 1
    INTERNAL_ERROR		= 2
    OTHER				= 3.

GO_DOCU_PROXY->OPEN_DOCUMENT_FROM_TABLE(
  EXPORTING
    DOCUMENT_TABLE		= LT_DOC_TABLE[]
    DOCUMENT_SIZE		= LV_DOC_SIZE
    NO_FLUSH			= 'X'
  IMPORTING
    RETCODE				= LV_RETCODE ).

IF LV_RETCODE <> C_OI_ERRORS=>RET_OK.
  "Excel download template exception.
  MESSAGE I000 WITH TEXT-E01 DISPLAY LIKE 'E'.
ENDIF.
②OAOR
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
DATA:
  LO_BDS_DOCUMTNTS		TYPE REF TO CL_DBS_DOCUMENT_SET,
  LV_CLASSNAME			TYPE SBDST_CLASSNAME VALUE 'HRFPM_EXCEL_STANDARD',
  LV_CLASSTYPE			TYPE SBDST_CLASSTYPE VALUE 'OT',
  LV_OBJECTKEY			TYPE SBDST_OBJECT_KEY VALUE 'DOITEST',
  LV_DOC_COMPONENTS		TYPE SBDST_COMPONENTS,
  LV_DOC_SIGNATURE		TYPE SBDST_SIGNATURE.

DATA:
  LT_BDS_URIS			TYPE SBDST_URI,
  LS_BDS_URL			LIKE LINE OF LT_BDS_URIS,
  LV_URL				TYPE C LENGTH 256.
  
CREATE OBJECT LO_BDS_DOCUMENTS.

CL_BDS_DOCUMENT_SET=>GET_INFO(
  EXPORTING
    CLASSNAME			= LV_CLASSNAME
    CLASSTYPE			= LV_CLASSTYPE
    OBJECT_KEY			= LV_OBJECTKEY
  CHANGING
    COMPONENTS			= LV_DOC_COMPONENTS
    SIGNATURE			= LV_DOC_SIGNATURE ).

CL_BDS_DOCUMENT_SET=>GET_WITH_URL(
  EXPORTING
    CLASSNAME			= LV_CLASSNAME
    CLASSTYPE			= LV_CLASSTYPE
    OBJECT_KEY			= LV_OBJECTKEY
  CHANGING
    URIS				= LT_BDS_URIS
    SIGNATURE			= LV_DOC_SIGNATURE ).

FREE: LO_BDS_DOCUMENTS.

READ TABLE LT_BDS_URIS INTO LS_BDS_URL INDEX 1.
LV_URL = LS_BDS_URL-URI.

GO_DOCU_PROXY->OPEN_DOCUMENT(
  EXPORTING
    DOCUMENT_TITLE		= 'excel'
    DOCUMENT_URL		= LV_URL
    OPEN_INPLACE		= SPACE
  IMPORTING
    ERROR				= GO_ERROR ).
IF GO_ERROR->HAS_FAILED = 'X'.
  GO_ERROR->RAISE_MESSAGE( 
    EXPORTING
      TYPE				= 'E' ).
ENDIF.
（3）操作工作表
①获取所有工作表名
1
2
3
4
5
6
DATA:
  LT_SHEETS				TYPE SOI_SHEETS_TABLE.
GO_SHEET->GET_SHEETS(
  IMPORTING
    SHEETS			   	= LT_SHEETS
    ERROR				= GO_ERROR ).
②修改工作表名

修改Sheet1为Header

1
2
3
4
5
6
7
GO_SHEET->SET_SHEET_NAME(
  EXPORTING
    NEWNAME				= 'Header'
    OLDNAME				= 'Sheet1'
  IMPORTING
    ERROR				= GO_ERROR
).
③删除工作表

删除Sheet2

1
2
3
4
5
GO_SHEET->DELETE_SHEET(
  EXPORTING
    NAME				= 'Sheet2'
  IMPORTING
	ERROR				= GO_ERROR ).
④新增工作表

新增的Sheet会被放在当前激活Sheet前。

1
2
3
4
5
GO_SHEET->ADD_SHEET(
  EXPORTING
    NAME				= 'Item'
  IMPORTING
	ERROR				= GO_ERROR ).
⑤选择工作表

选择Header进行后续操作。

1
2
3
4
5
6
GO_SHEET->SELECT_SHEET(
  EXPORTING
    NAME				= 'Header'
    NO_FLUSH			= 'X'
  IMPORTING
	ERROR				= GO_ERROR ).
（4）设置单个单元格值

在下面的流程代码Form GET_DOWNLOAD_DATA_DOI 中使用的是直接根据内表的数据将内表整个更新到Excel中。而接下来介绍的这个方法是通过设置行、列和值的形式给单元格赋值。但是这种方式只适合数据量较少或未处理的数据，例如字段标题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
DATA:
  LV_COLUMN				TYPE I,
  LT_RANGES 			TYPE SOI_RANGE_LIST,
  LS_RANGES 			TYPE SOI_RANGE_ITEM,
  LT_TITLE				TYPE SOI_GENERIC_TABLE,
  LS_TITLE				TYPE SOI_GENERIC_ITEM.

DESCRIBE TABLE GT_FIELDCAT LIKES LV_COLUMN.

"Select range
REFRESH: LT_RANGES,LT_TITLE.
CLEAR LS_RANGES.
LS_RANGES-NAME		= 'TITLE'.
LS_RANGES-COLUMNS	= LV_COLUMN.
LS_RANGES-ROWS		= 1.
LS_RANGES-CODE		= 1.
APPEND LS_RANGES TO LT_RANGES.

LOOP AT GT_FIELDCAT INTO GS_FIELDCAT.
  LS_TITLE-ROW		= 1.
  LS_TITLE-COLUMN	= SY-TABIX.
  LS_TITLE-VALUE	= GS_FIELDCAT-COLTEXT.
  APPEND LS_TITLE TO LT_TITLE.
  CLEAR: LS_TITLE,GS_FIELDCAT.
ENDLOOP.

CALL METHOD GO_SHEET->SET_RANGES_DATA
  EXPORTING
    RANGES			= LT_RANGES
    CONTENTS		= LT_TITLE
  IMPORTING
    ERROR			= GO_ERROR.
（5）设置单元格格式
①单元格区分
1
2
3
4
5
6
7
8
9
10
11
DATA:
  LT_RANGES			TYPE SOI_FULL_RANGE_TABLE.
LT_RANGES[] = VALUE #(
	( NAME = 'TITLE' TOP = 1 LEFT = 1 ROWS = 1 COLUMNS = 标题个数 )
	( NAME = 'BODY'  TOP = 2 LEFT = 1 ROWS = 数据条数 COLUMNS = 标题个数 )
	( NAME = 'KEY'   TOP = 2 LEFT = 1 ROWS = 数据条数 COLUMNS = Key字段个数 )
).

"区分信息传入
GO_SHEET->INSERT_RANGES( EXPORTING RANGES = LT_RANGES
						 IMPORTING ERROR  = GO_ERROR ).
②字体设置
1
2
3
4
5
6
7
8
9
10
11
GO_SHEET->SET_FONT( 
  EXPORTING
    RANGENAME		= 'BODY'		"单元格区域，即字体样式生效区域
    FAMILY			= 'Arial'		"字体样式
    SIZE			= 20			"字体大小
    BOLD			= 1				"字体加粗  0 不加粗  1 加粗
	ITALIC			= 0				"字体斜体  0 非斜体  1 斜体
	ALIGN			= 2				"对齐方式  0 左对齐  1 居中  2 右对齐
  IMPORTING
    ERROR			= GO_ERROR
).
③单元格设置

设置Key区域的字体颜色为红色，背景色为黄色，字体加粗并添加绿色边框。

1
2
3
4
5
6
7
DATA:
  LT_FORMAT				TYPE SOI_FORMAT_TABLE.
LT_FORMAT = VALUE #( 
		( NAME = 'KEY' FRONT = 3 BACK = 6 BOLD = 1 FRAMETYP = 127 FRAMECOLOR = 4 )
 ).
GO_SHEET->SET_RANGES_FORMAT( EXPORTING FORMATTABLE = LT_FORMAT
							 IMPORTING ERRO 	   = GO_ERROR ).
④宽度自适应
1
2
GO_SHEET->FIT_WIDEST( EXPORTING NAME = 'TITLE'
					  IMPORTING ERROR = GO_ERROR ).
⑤单元格边框
1
2
3
4
5
6
7
8
GO_SHEET->SET_FRAME( 
	EXPORTING
	  RANGENAME		= 'TITLE'
	  TYP			= '127'
	  COLOR			= '1'
	IMPORTING
	  ERROR			= GO_ERROR
).
⑥执行宏
1
GO_DOCU_PROXY->EXECUTE_MACRO( EXPORTING MACRO_STRING = 宏名称 ).
（6）保存文件
①保存文档到本地
1
2
3
4
5
6
7
8
IF GO_DOCU_PROXY IS NOT INITIAL.
  GO_DOCU_PROXY->SAVE_AS(
    EXPORTING
      FILE_NAME			= PV_FILENAME
    IMPORTING
      ERROR				= GO_ERROR
  ).
ENDIF.
②保存文档到URL
1
2
3
4
5
6
7
8
GO_DOCU_PROXY->SAVE_DOCUMENT_TO_URL(
  EXPORTING
    URL					= LV_URL
  IMPORTING
    ERROR				= GO_ERROR
  CHANGING
    DOCUMENT_SIZE		= LV_SIZE
).
③保存文档到内表
1
2
3
4
5
6
7
GO_DOCU_PROXY->SAVE_DOCUMENT_TO_TABLE(
  EXPORTING
    ERROR				= GO_ERROR
  CHANGING
    DOCUMENT_SIZE		= LV_SIZE
    DOCUMENT_TABLE		= LT_TABLE
).

2.流程代码

DOI的下载流程步骤较多，但相应的对于Excel的操作相对于OLE也更加的灵活。

  • 创建DOI的容器对象
  • 创建一个空白的工作簿
  • 打开工作簿并实例化表对象
  • 设置Excel的抬头
  • 设置Excel中的内容
  • 设置Excel中内容的样式
  • 将数据保存到下载到本地的Excel文件中
  • 关闭文件并释放创建的DOI对象

全局变量定义

1
2
3
4
5
6
"Excel Object
DATA:
  GO_CONTROL        TYPE REF TO I_OI_CONTAINER_CONTROL,
  GO_ERROR          TYPE REF TO I_OI_ERROR,
  GO_DOCU_PROXY     TYPE REF TO I_OI_DOCUMENT_PROXY,
  GO_DOISHEET       TYPE REF TO I_OI_SPREADSHEET.

流程逻辑

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
FROM DOWNLOAD_EXCEL_DOI CHANGING VALUE(PV_FULLPATH) TYPE STRING.
  "Create Container
  PERFORM CREATE_DOI_CONTAINER.
  
  "Create a blank workbook
  PERFORM CREATE_BLANK_WORKBOOK.
  
  "Open the workbook and instantiate the table object.
  PERFORM OPEN_WORKBOOK.
  CHECK GV_MESSAGE IS INITIAL.
  
  "Set Excel Header Title
  PERFORM SET_HEADER_TITLE.
  
  "Set Excel Table Value
  PERFORM GET_DOWNLOAD_DATA_DOI.
  
  "Set Excel Style
  PERFORM SET_EXCEL_DOI_STYLE.
  
  "Save Data To Local File
  PERFORM SAVE_DATA_TO_FILE USING PV_FULLPATH.
  
  "Close File And Free Object
  PERFORM FREE_OBJECT.
ENDFORM.

3.流程源代码

（1）创建DOI的容器对象（CREATE_DOI_CONTAINER）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
FORM CREATE_DOI_CONTAINER.
*---------------------------Variables----------------------------*
  DATA: LV_DOC_TYPE(80) VALUE SOI_DOCTYPE_EXCEL_SHEET."Excel.sheet
*-----------------------------Logic------------------------------*
  C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL(
  	IMPORTING
  	  CONTROL			= GO_CONTROL
  	  ERROR             = GO_ERROR ).
  
  GO_ERROR->RAISE_MESSAGE( EXPORTING TYPE = GC_E ).
  
  GO_CONTROL->INIT_CONTROL(
  	EXPORTING
  	  R3_APPLICATION_NAME		= 'EXCEL CONTAINER'
  	  INPLACE_ENABLED			= SPACE
  	  INPLACE_RESIZE_DOCUMENTS  = GC_X
  	  INPLACE_SCROLL_DOCUMENTS  = GC_X
  	  INPLACE_SHOW_TOOLBARS		= GC_X
  	  PARENT					= CL_GUI_CONTAINER=>SCREEN0
  	  REGISTER_ON_CLOSE_EVENT   = SPACE
      REGISTER_ON_CUSTOM_EVENT  = SPACE
  	IMPORTING
  	  ERROR						= GO_ERROR ).
  GO_ERROR->RAISE_MESSAGE( EXPORTING TYPE = GC_E ).
  
  GO_CONTROL->GET_DOCUMENT_PROXY(
  	EXPORTING
  	  DOCUMENT_TYPE				= LV_DOC_TYPE
  	IMPORTING
  	  DOCUMENT_PROXY			= GO_DOCU_PROXY
  	  ERROR						= GO_ERROR ).
  
  GO_ERROR->RAISE_MESSAGE( EXPORTING TYPE = GC_E ).
ENDFORM.
（2）创建一个空白的工作簿（CREATE_BLANK_WORKBOOK）
1
2
3
4
5
6
7
8
9
10
11
FORM CREATE_BLANK_WORKBOOK.
  CHECK GO_DOCU_PROXY IS BOUND.
  GO_DOCU_PROXY->CREATE_DOCUMENT(
    EXPORTING
      DOCUMENT_TITLE  			= 'excel'
      OPEN_INPLACE				= GC_X		"是否要将Excel嵌入GUI中展示操作
      NO_FLUSH					= GC_X
    IMPORTING
      ERROR						= GO_ERROR ).
  GO_ERROR->RAISE_MESSAGE( EXPORTING TYPE = GC_E ).
ENDFORM.
（3）打开工作簿并实例化工作表对象（OPEN_WORKBOOK）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
FORM OPEN_WORKBOOK.
*---------------------------Variables----------------------------*
  DATA: LV_TEMP			TYPE I.
*-----------------------------Logic------------------------------*
  CLEAR: GV_MESSAGE.
  "Indicates Whether Interface Is Supported For This Doc. Type
  GO_DOCU_PROXY->HAS_SPREADSHEET_INTERFACE(
    IMPORTING
      IS_AVAILABLE			= LV_TEMP  ).
  
  IF LV_TEMP NE 1.
    GV_MESSAGE = GC_X.
    EXIT.
  ENDIF.
  
  "Returns an Interface Reference
  GO_DOCU_PROXY->GET_SPREADSHEET_INTERFACE(
  	IMPORTING
  	  ERROR					= GO_ERROR
  	  SHEET_INTERFACE		= GO_DOISHEET ).
  IF GO_ERROR->HAS_FAILED = GC_X.
    GO_ERROR->RAISE_MESSAGE( EXPORTING TYPE = GC_E ).
  ENDIF.
ENDFORM.
（4）设置Excel的抬头（SET_HEADER_TITLE）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
FORM SET_HEADER_TITLE.
*---------------------------Variables----------------------------*
  DATA:
    LT_RANGES				TYPE SOI_RANGE_LIST,
    LS_RANGES				TYPE SOI_RANGE_ITEM,
    LT_TITLE				TYPE SOI_GENERIC_TABLE,
    LS_TITLE				TYPE SOI_GENERIC_ITEM,
    LS_FIELDCATALOG			LIKE LINE OF GT_FIELDCATALOG.
*-----------------------------Logic------------------------------*
  CLEAR: GV_COL,GV_ROW.
  DELETE GT_FIELDCATALOG WHERE TECH EQ GC_X.
  DESCRIBE TABLE GT_FIELDCATALOG LINES GV_COL.
  
  "Select the operation area.
  GO_DOISHEET->INSERT_RANGE_DIM(
    EXPORTING
      NAME			= 'TITLE'
      TOP			= 1
      LEFT			= 1
      ROWS			= 1			"total row
      COLUMNS		= GV_COL	"total column
      UPDATING		= 0
    IMPORTING
      ERROR			= GO_ERROR ).
  
  "Set Range
  REFRESH: LT_RANGES,LT_TITLE.
  CLEAR LS_RANGES.
  LS_RANGES-NAME		= 'TITLE'.
  LS_RANGES-COLUMNS		= GV_COL.
  LS_RANGES-ROWS		= 1.
  LS_RANGES-CODE		= 1.
  APPEND LS_RANGES TO LT_RANGES.
  
  "Set Title
  LOOP AT GT_FIELDCATALOG INTO LS_FIELDCATALOG.
    LS_TITLE-ROW = 1.
    LS_TITLE-COLUMN = SY-TABIX.
    LS_TITLE-VALUE = LS_FIELDCATALOG-COLTEXT.
    APPEND LS_TITLE TO LT_TITLE.
    CLEAR: LS_TITLE,LS_FIELDCATALOG.
  ENDLOOP.
  
  CALL METHOD GO_DOISHEET->SET_RANGES_DATA
    EXPORTING
      RANGES			= LT_RANGES
      CONTENTS			= LT_TITLE
    IMPORTING
      ERROR				= GO_ERROR.
ENDFORM.
（5）设置Excel中的内容（GET_DOWNLOAD_DATA_DOI）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
FORM GET_DOWNLOAD_DATA_DOI.
*---------------------------Variables----------------------------*
  DATA: 
    LT_FIELDS			LIKE TABLE OF RFC_FIELDS.
*-----------------------------Logic------------------------------*
  GV_ROW = LINES( GT_TABLE ).
  GV_COL = LINES( GT_FIELDCATALOG ).
  
  "Insert space range
  GO_DOISHEET->INSERT_RANGE_DIM(
  	EXPORTING
  	  NAME			= 'DATA'
  	  TOP			= 2
  	  LEFT			= 1
  	  ROWS			= GV_ROW
  	  COLUMNS		= GV_COL
  	  UPDATING		= 0
  	IMPORTING
  	  ERROR			= GO_ERROR ).
  
  "Set Format
  CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE'
    TABLES
      DATA			= GT_TABLE
      FIELDS		= LT_FIELDS
    EXCEPTIONS
      DP_INVALID_TABLE	= 1
      OTHERS			= 2.
  
  "Insert a Table
  GO_DOISHEET->INSERT_ONE_TABLE(
  	EXPORTING
  	  DATA_TABLE		= GT_TABLE
  	  FIELDS_TABLE		= LT_FIELDS
  	  RANGENAME			= 'DATA'
  	  WHOLETABLE		= GC_X
  	  UPDATING			= 1 ).
ENDFORM.
（6）设置Excel中内容的样式（SET_EXCEL_DOI_STYLE）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
FORM SET_EXCEL_DOI_STYLE.
*---------------------------Variables----------------------------*
  CONSTANTS:
    LC_TITLE			TYPE C LENGTH 5 VALUE 'TITLE',
    LC_BODY				TYPE C LENGTH 4 VALUE 'BODY',
    LC_ALL				TYPE C LENGTH 3 VALUE 'ALL'.
  DATA: 
    LT_FORMAT			TYPE SOI_FORMAT_TABLE,
    LS_FORMAT			LIKE LINE OF LT_FORMAT,
    LT_RANGES			TYPE SOI_FULL_RANGE_TABLE WITH HEADER LINE.
*-----------------------------Logic------------------------------*
  "Set Area
  LT_RANGES-NAME	= LC_TITLE.
  LT_RANGES-TOP		= 1.
  LT_RANGES-LEFT	= 1.
  LT_RANGES-COLUMNS	= GV_COL.
  APPEND LT_RANGES.
  
  LT_RANGES-NAME	= LC_BODY.
  LT_RANGES-TOP		= 2.
  LT_RANGES-LEFT	= 1.
  LT_RANGES-ROWS	= GV_ROW + 1.
  LT_RANGES-COLUMNS	= GV_COL.
  APPEND LT_RANGES.
  
  LT_RANGES-NAME	= LC_ALL.
  LT_RANGES-TOP		= 1.
  LT_RANGES-LEFT	= 1.
  LT_RANGES-ROWS	= GV_ROW + 1.
  LT_RANGES-COLUMNS	= GV_COL.
  APPEND LT_RANGES.
  
  GO_DOISHEET->INSERT_RANGES(
	EXPORTING
	  RANGES		= LT_RANGES[]
	IMPORTING
	  ERRPR			= GO_ERROR ).

  "Set adaptive width
  CALL METHOD GO_DOISHEET->FIT_WIDEST(
  	EXPORTING
  	  NAME			= LC_ALL
  	IMPORTING
  	  ERROR			= GO_ERROR ).
  
  "Set Border
  GO_DOISHEET->SET_FRAME(
  	EXPORTING
  	  RANGENAME		= LC_ALL
  	  TYP			= '127'
  	  COLOR			= 1
  	IMPORTING
  	  ERROR			= GO_ERROR ).
  
  "Set Title Color
  LS_FORMAT-NAME	= LC_TITLE.
  "LS_FORMAT-BACK	= 5.
  "LS_FORMAT-FRONT	= 3.
  LS_FORMAT-BOLD	= 1.
  LS_FORMAT-FRAMETYP = 127.
  "LS_FORMAT-FRAMECOLOR = 4.
  APPEND LS_FORMAT TO LT_FORMAT.
  
  GO_DOISHEET->SET_RANGES_FORMAT(
  	EXPORTING
  	  FORMATTABLE	= LT_FORMAT
  	IMPORTING
  	  ERROR			= GO_ERROR ).
ENDFORM.
（7）将数据保存到下载到本地的Excel文件中（SAVE_DATA_TO_FILE）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
FORM SAVE_DATA_TO_FILE USING VALUE(PV_FULLPATH) TYPE STRING.
*---------------------------Variables----------------------------*
  DATA: 
    LV_FILENAME			TYPE C LENGTH 255.
*-----------------------------Logic------------------------------*
  LV_FILENAME = PV_FULLPATH.
  IF GO_DOCU_PROXY IS BOUND.
    GO_DOCU_PROXY->SAVE_AS(
      EXPORTING
        FILE_NAME		= LV_FILENAME
      IMPORTING
        ERROR			= GO_ERROR ).
  ENDIF.
ENDFORM.
（8）关闭文件并释放创建的DOI对象（FREE_OBJECT）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
FORM FREE_OBJECT.
*---------------------------Variables----------------------------*
  DATA: 
    LV_FILENAME			TYPE C LENGTH 255.
*-----------------------------Logic------------------------------*
  IF GO_DOCU_PROXY IS NOT INITIAL.
  	"关闭操作好的文档
    GO_DOCU_PROXY->CLOSE_DOCUMENT( ).
    FREE: GO_DOCU_PROXY.
  ENDIF.
  
  IF GO_CONTROL IS NOT INITIAL.
  	"释放资源
    GO_CONTROL->DESTROY_CONTROL( ).
    FREE: GO_CONTROL.
  ENDIF.
ENDFORM.

4.ALV_TREE下载

ALV Tree的下载，可以使用DOI的SET_HIERARCHY_TABLE方法设置Excel的层级结构。

需要设置层级开始行，层级包含的行数和层级结构使用的Sheet名。

多个层级时需要添加空行，否则连续层级会合并到一起。

1
2
3
4
5
6
7
8
9
10
11
12
DATA:
  BEGIN OF LS_HIERA,
    FIRSTLINE		TYPE I,
    LENGTH			TYPE I,
    SHEET			TYPE C LENGTH 128,
    MODE			TYPE I,
  END OF LS_HIERA,
  LT_HIERA			LIKE TABLE OF LS_HIERA.

CALL METHOD GO_SHEET->SET_HIERARCHY_TABLE
  EXPORTING
    TABLE 			= LT_HIERA.

六、XML下载

1.流程代码

（1）选择下载格式
1
2
3
4
5
6
7
8
9
10
11
12
13
DATA:
  LS_XML_CHOICE 			TYPE IF_SALV_BS_XML=>S_TYPE_XML_CHOICE,
  LT_XML_CHOICE				TYPE IF_SALV_BS_XML=>T_TYPE_XML_CHOICE,
  LV_XML					TYPE XSTRING.
  R_RESULT_DATA				TYPE REF TO CL_SALV_EX_RESULT_DATA_TABLE,
  LO_DREF					TYPE REF TO DATA,
  LV_FLAVOUR				TYPE STRING,
  LV_VERSION				TYPE STRING.

LT_XML_CHOICE = CL_SALV_EXPORT_XML_DIALOG=>GET_GUI_SPREADSHEET_FORMATS( ).

SORT LT_XML_CHOICE BY XML_TYPE.
READ TABLE LT_XML_CHOICE INTO LS_XML_CHOICE WITH KEY XML_TYPE = '10' BINARY SEARCH.
（2）根据ALV相关设置，设置数据格式
1
2
3
4
5
6
GET PEFERENCE OF GT_TABLE INTO LO_DREF.
R_RESULT_DATA = CL_SALV_EX_UTIL=>FACTORY_RESULT_DATA_TABLE(
	R_DATA			= LO_DREF
	S_LAYOUT		= GS_LAYOUT
	T_FIELDCATALOG	= GT_FIELDCAT
	T_SORT			= GT_SORT ).
（3）转化数据为XML格式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CASE CL_SALV_EX_BASE=>GET_VERSION( ).
  WHEN IF_SALV_BS_XML=>VERSION_25.
    LV_VERSION = IF_SALV_BS_XML=>VERSION_25.
  WHEN IF_SALV_BS_XML=>VERSION_26.
    LV_VERSION = IF_SALV_BS_XML=>VERSION_26.
ENDCASE.

LV_FLABOUR = IF_SALV_BS_C_TT=>C_TT_XML_FLAVOUR_EXPORT.
CALL METHOD CL_SALV_BS_TT_UTIL=>IF_SALV_BS_TT_UTIL~TRANSFORM
  EXPORTING
    XML_TYPE			= LS_XML_CHOICE-XML_TYPE
    XML_VERSION			= LV_VERSION
    R_RESULT_DATA		= R_RESULT_DATA
    XML_FLAVOUR			= IF_SALV_BS_C_TT=>C_TT_XML_FLAVOUR_EXPORT
  IMPORTING
    XML					= LV_XML.
（4）下载数据并打开
1
2
3
4
5
GET PEFERENCE OF GT_TABLE INTO LO_DREF.
R_RESULT_DATA = CL_SALV_EX_UTIL=>FACTORY_RESULT_DATA_TABLE(
	R_DATA			= LO_DREF
	S_LAYOUT		= GS_LAYOUT
	T_FIELDCATALOG	= GT_FIELDCAT ).

七、Excel下载注意事项

1.单位字段处理

例如金额，参照 货币转换为外部格式和勤奋为，并将负号提前。

1
2
WRITE GT_MAIN-PRICE TO LV_PRICE CURRENCY GT_MAIN-CURRENCY.
CONDENSE: LV_PRICE.

2.数值字段

以文本格式存储的数值，需要将负号提前，必要时增加千分位。提前负号使用函数CLOI_PUT_SIGN_IN_FRONT。

1
2
"千分位
WRITE LV_NUMBER TO LV_CHAR.

3.日期时间

格式适应外部格式

  • 使用函数转换日期格式：CONVERT_DATE_TO_EXTERNAL将日期格式转换为User Profile设置的日期格式
  • 使用掩码：WRITE LV_TIME TO LV_CHAR USING EDIT MASK ‘*:*:‘.

4.含内外码

根据字段的convexit属性转换成外码，例如N类型前导0，项目号去掉前导0等。

1
2
3
4
"去除前导0
SHIFT LV_CHAR LEFT DELETING LEADING 0.
"其他转换字段
WRITE LV_PROJ USING EDIT MASK '==ABPSN'.

八、参考资料

ABAP OLE COLOR

更新于：2024年7月24日

ABAP

评论