1 *&---------------------------------------------------------------------*
  2 *& Report Z3426EXCEL002
  3 *&---------------------------------------------------------------------*
  4 *&
  5 *&---------------------------------------------------------------------*
  6 REPORT z3426excel003.
  7 
  8 DATA:xtab     TYPE cpt_x255,
  9      length   TYPE so_att_len,
 10      lv_count TYPE i,
 11      filename TYPE string VALUE 'C:\Users\XXXX\Desktop\Test\SMW0数据.xlsx'.
 12 SELECT * INTO TABLE @DATA(lt_bp) FROM but000 UP TO 100 ROWS.
 13 PERFORM frm_tabconvxtr TABLES lt_bp[] xtab[] CHANGING length.
 14 
 15 lv_count = length.
 16 CALL METHOD cl_gui_frontend_services=>gui_download
 17   EXPORTING
 18     bin_filesize      = lv_count
 19     filename          = filename
 20     filetype          = 'BIN'
 21     confirm_overwrite = abap_true
 22   IMPORTING
 23     filelength        = DATA(filelength)
 24   CHANGING
 25     data_tab          = xtab.
 26 
 27 FORM frm_tabconvxtr TABLES pt_data pt_xtab CHANGING length.
 28   DATA:lw_tab_ref TYPE REF TO data.
 29   CREATE DATA lw_tab_ref LIKE LINE OF pt_data.
 30 
 31   DATA:ls_mime    TYPE w3mime,
 32        lt_mime    TYPE TABLE OF w3mime,
 33        filesize   TYPE string,
 34        filesizei  TYPE i,
 35        lv_xstring TYPE xstring,
 36        fileext    TYPE char20,
 37        ls_key     TYPE wwwdatatab.
 38 
 39   ls_key-relid = 'MI'.
 40   ls_key-objid = 'XXXXX'.
 41   SELECT * FROM wwwparams INTO TABLE @DATA(lt_wwwparam)
 42    WHERE relid = @ls_key-relid
 43      AND objid = @ls_key-objid.
 44   TRY.
 45       LOOP AT lt_wwwparam INTO DATA(ls_wwwparam).
 46         CASE ls_wwwparam-name.
 47           WHEN 'filesize'.
 48             filesizei = filesize = ls_wwwparam-value.
 49             CONDENSE filesize NO-GAPS.
 50           WHEN 'fileextension'.
 51             fileext = ls_wwwparam-value.
 52         ENDCASE.
 53       ENDLOOP.
 54 **********************************************************************
 55 * 获取SMW0的数据
 56       CALL FUNCTION 'WWWDATA_IMPORT'
 57         EXPORTING
 58           key               = ls_key
 59         TABLES
 60           mime              = lt_mime
 61         EXCEPTIONS
 62           wrong_object_type = 1
 63           import_error      = 2
 64           OTHERS            = 99.
 65 
 66       CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
 67         EXPORTING
 68           input_length = filesizei
 69         IMPORTING
 70           buffer       = lv_xstring
 71         TABLES
 72           binary_tab   = lt_mime
 73         EXCEPTIONS
 74           failed       = 1
 75           OTHERS       = 2.
 76 **********************************************************************
 77       DATA(xlsx_handling) = cl_ehfnd_xlsx=>get_instance( )."xlsx 句柄
 78       DATA(xlsx_document) = xlsx_handling->load_doc( lv_xstring )."xlsx 文件
 79       DATA(xlsx_sheets) = xlsx_document->get_sheets( )."得到sheets
 80       DATA(first_xlsx_sheet) = xlsx_document->get_sheet_by_id( xlsx_sheets[ 1 ]-sheet_id )."得到sheet
 81       first_xlsx_sheet->change_sheet_name( 'Sheet_first' )."设置sheet的名称
 82       DO 2 TIMES.
 83         DATA(lv_row) = sy-index + 3."从第三行起，开始写数据
 84         DO 10 TIMES.
 85           DATA(lv_column) = sy-index.
 86           IF first_xlsx_sheet->has_cell_content( iv_row = lv_row iv_column = lv_column ).
 87           ELSE.
 88             first_xlsx_sheet->set_cell_content( iv_row = lv_row iv_column = lv_column iv_value = '测试数据!' ).
 89           ENDIF.
 90 
 91         ENDDO.
 92       ENDDO.
 93 
 94       DATA(filecontent) = xlsx_document->save( ).
 95     CATCH cx_openxml_format  INTO DATA(openxml_format_exception).
 96       MESSAGE e001(00) RAISING file_export_error
 97       WITH 'Error occurs when constructing excel file instance.'." cx_openxml_format.
 98     CATCH cx_openxml_not_found INTO DATA(openxml_not_found_exception).
 99       MESSAGE e001(00) RAISING file_export_error
100       WITH ' Error occurs when constructing excel file instance.' "CX_OPENXML_NOT_FOUND |.
101       .
102     CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed_exception).
103       MESSAGE e001(00) RAISING file_export_error
104       WITH 'Error occurs when constructing excel file instance.'" CX_OPENXML_NOT_ALLOWED |.
105       .
106   ENDTRY.
107   cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = filecontent IMPORTING ex_xtab = pt_xtab[] ).
108   length = xstrlen( filecontent ).
109   "pv_xtab[] = l_binary_tab[].
110   "length = l_length.
111 ENDFORM.