关键代码DATA: excel TYPE ole2_object, " Excel object
worksheet TYPE ole2_object,
workbooks TYPE ole2_object, " Workbooks object
workbook TYPE ole2_object,
sheet TYPE ole2_object, " Worksheet object
cell TYPE ole2_object, " Cell object
range TYPE ole2_object.
DATA: ls_mime TYPE w3mime,
lt_mime TYPE TABLE OF w3mime,
l_fullpath TYPE string,
l_path TYPE string,
l_filename TYPE string,
l_default_name TYPE string,
l_filter TYPE string,
ls_key TYPE wwwdatatab,
l_objid TYPE wwwdata-objid.
DATA: xlsx_handling TYPE REF TO cl_ehfnd_xlsx,
xlsx_document TYPE REF TO if_ehfnd_xlsx_doc,
xlsx_sheets TYPE cl_ehfnd_xlsx=>gty_th_sheet_info,
first_xlsx_sheet TYPE REF TO if_ehfnd_xlsx_sheet.
DATA: lv_xstring TYPE xstring,
l_field_count TYPE i,
l_line TYPE i,
filesize TYPE i.
DATA: pt_xtab TYPE cpt_x255.
FIELD-SYMBOLS: TYPE any,
TYPE any.
l_objid = sy-repid.
*&**SMW0上传的方式
SELECT relid objid checkout checknew INTO ls_key UP TO 1 ROWS
FROM wwwdata
WHERE relid = 'MI'
AND objid = l_objid
ORDER BY srtf2 DESCENDING.
EXIT.
ENDSELECT.
* 获取SMW0的数据
CALL FUNCTION 'WWWDATA_IMPORT'
EXPORTING
key = ls_key
TABLES
mime = lt_mime
EXCEPTIONS
wrong_object_type = 1
import_error = 2
OTHERS = 99.
CALL METHOD cl_bcs_convert=>solix_to_xstring
EXPORTING
it_solix = lt_mime
* iv_size = iv_size
RECEIVING
ev_xstring = lv_xstring.
TRY.
xlsx_handling = cl_ehfnd_xlsx=>get_instance( )."xlsx 句柄
xlsx_document = xlsx_handling->load_doc( lv_xstring )."xlsx 文件
xlsx_sheets = xlsx_document->get_sheets( )."得到sheets
first_xlsx_sheet = xlsx_document->get_sheet_by_id( xlsx_sheets[ 1 ]-sheet_id )."得到sheet
* first_xlsx_sheet->change_sheet_name( 'Sheet1' )."设置sheet的名称
l_line = 1. "抬头行
DESCRIBE FIELD ls_excel TYPE ls_excel COMPONENTS l_field_count.
LOOP AT lt_excel_out ASSIGNING .
l_line = l_line + 1.
DO l_field_count TIMES.
ASSIGN COMPONENT sy-index OF STRUCTURE TO .
IF IS ASSIGNED.
* first_xlsx_sheet->has_cell_content( iv_row = l_line iv_column = sy-index ) "单元格有值
first_xlsx_sheet->set_cell_content( iv_row = l_line iv_column = sy-index iv_value = ).
ENDIF.
ENDDO.
ENDLOOP.
DATA(filecontent) = xlsx_document->save( ).
CATCH cx_openxml_format INTO DATA(openxml_format_exception).
MESSAGE e001(00) RAISING file_export_error
WITH 'Error occurs when constructing excel file instance.'." cx_openxml_format.
CATCH cx_openxml_not_found INTO DATA(openxml_not_found_exception).
MESSAGE e001(00) RAISING file_export_error
WITH ' Error occurs when constructing excel file instance.'. "CX_OPENXML_NOT_FOUND |.
CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed_exception).
MESSAGE e001(00) RAISING file_export_error
WITH 'Error occurs when constructing excel file instance.'." CX_OPENXML_NOT_ALLOWED |.
ENDTRY.
**&**下载模版
CLEAR: l_fullpath,l_path,l_filename,l_filter.",l_destination,ls_key.
l_filter = 'XLSX|*.XLSX'.
l_default_name = '工艺路线组件批量分配导入模板'.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_file_name = l_default_name
file_filter = l_filter
CHANGING
filename = l_filename
path = l_path
fullpath = l_fullpath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3.
IF l_fullpath IS NOT INITIAL.
IF strlen( l_fullpath ) GT 128.
MESSAGE TEXT-m02 TYPE 'E'.
ENDIF.
cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = filecontent
IMPORTING ex_xtab = pt_xtab[] ).
cl_gui_frontend_services=>gui_download(
EXPORTING
bin_filesize = xstrlen( filecontent )
filename = l_fullpath
filetype = 'BIN'
* confirm_overwrite = abap_true
CHANGING
data_tab = pt_xtab[]
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
not_supported_by_gui = 22
error_no_gui = 23
OTHERS = 24 ).
"打开Excel文件
CREATE OBJECT excel 'EXCEL.APPLICATION'."创建实例
SET PROPERTY OF excel 'Visible' = 1.
CALL METHOD OF
excel
'Workbooks' = workbooks.
CALL METHOD OF
workbooks
'Open' = workbooks
EXPORTING
#1 = l_fullpath.
CALL METHOD OF
workbooks
'Worksheets' = sheet
EXPORTING
#1 = 'Sheet1'.
CALL METHOD OF
sheet
'Activate'.
ENDIF.
- 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
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
