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.