1 *&---------------------------------------------------------------------*
2 *& Report Z3426EXCEL004
3 *&---------------------------------------------------------------------*
4 *&
5 *&---------------------------------------------------------------------*
6 REPORT z3426excel004.
7
8 DATA:BEGIN OF gt_bukrs OCCURS 0,
9 bukrs TYPE t001-bukrs,
10 butxt TYPE t001-butxt,
11 sheetname TYPE string,
12 END OF gt_bukrs.
13 DATA: BEGIN OF gt_file OCCURS 0, "下载到本地格式
14 bukrs(4) TYPE c,
15 field1(22) TYPE c,
16 field2(22) TYPE c,
17 field3(22) TYPE c,
18 field4(22) TYPE c,
19 field5(22) TYPE c,
20 field6(22) TYPE c,
21 END OF gt_file.
22
23
24 gt_bukrs[] = VALUE #( ( bukrs = '1000' butxt = '测试公司0')
25 ( bukrs = '1001' butxt = '测试公司1')
26 ( bukrs = '1002' butxt = '测试公司2')
27 ( bukrs = '1003' butxt = '测试公司3')
28 ( bukrs = '1004' butxt = '测试公司4')
29 ( bukrs = '1005' butxt = '测试公司5') ).
30
31 gt_file[] = VALUE #( ( bukrs = '1000' field1 = '测试' field2 = '测试' field3 = '测试' )
32 ( bukrs = '1001' field1 = '测试' field2 = '测试' field3 = '测试' )
33 ( bukrs = '1002' field1 = '测试' field2 = '测试' field3 = '测试' )
34 ( bukrs = '1003' field1 = '测试' field2 = '测试' field3 = '测试' )
35 ( bukrs = '1004' field1 = '测试' field2 = '测试' field3 = '测试' )
36 ( bukrs = '1005' field1 = '测试' field2 = '测试' field3 = '测试' )
37 ).
38
39 DATA(filename) = 'C:\Users\XXXX\Desktop\Test\测试数据.xls'.
40 PERFORM download_file.
41
42 FORM download_file.
43 INCLUDE ole2incl.
44 DATA:lv_str TYPE string.
45 DATA: excel TYPE ole2_object,
46 tempsheet TYPE ole2_object,
47 sheet TYPE ole2_object,
48 cell TYPE ole2_object,
49 range TYPE ole2_object,
50 workbook TYPE ole2_object,
51 xlsname TYPE string VALUE 'D:\测试数据.xls',
52 line TYPE i VALUE 0.
53
54 CREATE OBJECT excel 'excel.application'.
55 SET PROPERTY OF excel 'visible' = 0.
56 CALL METHOD OF excel 'workbooks' = workbook.
57 CALL METHOD OF workbook 'add'.
58 CALL METHOD OF excel 'sheets' = sheet.
59 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = 1 #2 = 1.
60 SET PROPERTY OF cell 'value' = '公司代码'.
61 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = 1 #2 = 2.
62 SET PROPERTY OF cell 'value' = '公司代码描述'.
63
64 LOOP AT gt_bukrs.
65 line = sy-tabix + 1.
66 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
67 SET PROPERTY OF cell 'value' = gt_bukrs-bukrs.
68 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
69 SET PROPERTY OF cell 'value' = gt_bukrs-butxt.
70 ENDLOOP.
71
72 LOOP AT gt_bukrs.
73 CALL METHOD OF excel 'sheets' = sheet.
74 CALL METHOD OF sheet 'add'.
75 CALL METHOD OF excel 'worksheets' = sheet EXPORTING #1 = gt_bukrs-sheetname.
76 CALL METHOD OF sheet 'activate '.
77 SET PROPERTY OF sheet 'name' = gt_bukrs-bukrs.
78 CLEAR:line.
79
80 line = line + 1.
81 lv_str = '表头区域'.
82 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
83 SET PROPERTY OF cell 'value' = lv_str.
84 CALL METHOD OF excel 'Range' = range
85 EXPORTING
86 #1 = 'A1'
87 #2 = 'F1'.
88 CALL METHOD OF range 'SELECT'.
89 CALL METHOD OF range 'MERGE'.
90 SET PROPERTY OF range 'MergeCells' = 1.
91 SET PROPERTY OF range 'HorizontalAlignment' = 3. "横向对齐
92 SET PROPERTY OF range 'VerticalAlignment' = 2.
93
94 line = line + 1.
95 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
96 SET PROPERTY OF cell 'value' = '编制单位'.
97
98 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
99 SET PROPERTY OF cell 'value' = gt_bukrs-butxt.
100
101 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 3.
102 SET PROPERTY OF cell 'value' = `期间`.
103
104 lv_str = sy-datum+0(4) && `年` && sy-datum+4(2) && `月`.
105 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 4.
106 SET PROPERTY OF cell 'value' = lv_str.
107
108 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 5.
109 SET PROPERTY OF cell 'value' = '单位'.
110
111 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 6.
112 SET PROPERTY OF cell 'value' = '人民币元'.
113
114 line = line + 1.
115 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
116 SET PROPERTY OF cell 'value' = '项目'.
117
118 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
119 SET PROPERTY OF cell 'value' = '行次'.
120
121 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 3.
122 SET PROPERTY OF cell 'value' = '本月'.
123
124 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 4.
125 SET PROPERTY OF cell 'value' = '本季'.
126
127 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 5.
128 SET PROPERTY OF cell 'value' = '本年'.
129
130 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 6.
131 SET PROPERTY OF cell 'value' = '上年'.
132
133 LOOP AT gt_file WHERE bukrs = gt_bukrs-bukrs.
134 line = line + 1.
135
136 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
137 SET PROPERTY OF cell 'value' = gt_file-field1.
138
139 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
140 SET PROPERTY OF cell 'value' = gt_file-field2.
141
142 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 3.
143 SET PROPERTY OF cell 'value' = gt_file-field3.
144
145 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 4.
146 SET PROPERTY OF cell 'value' = gt_file-field4.
147
148 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 5.
149 SET PROPERTY OF cell 'value' = gt_file-field5.
150
151 CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 6.
152 SET PROPERTY OF cell 'value' = gt_file-field6.
153 ENDLOOP.
154
155 ENDLOOP.
156 xlsname = filename.
157 GET PROPERTY OF excel 'ActiveWorkbook ' = workbook.
158 CALL METHOD OF workbook 'saveas' EXPORTING #1 = xlsname #2 = 1.
159 CALL METHOD OF workbook 'close'.
160 CALL METHOD OF excel 'quit'.
161 FREE OBJECT workbook.
162 FREE OBJECT excel.
163 MESSAGE s001(00) WITH '文件已下载完成!'.
164 ENDFORM.