1 *&---------------------------------------------------------------------*
  2 *& Report Z3426EXCEL002
  3 *&---------------------------------------------------------------------*
  4 *&
  5 *&---------------------------------------------------------------------*
  6 REPORT z3426excel005.
  7 
  8 DATA:gv_xcontent TYPE xstring.
  9 SELECT * INTO TABLE @DATA(lt_bp) FROM but000 UP TO 100 ROWS.
 10 PERFORM frm_tabconvxtr TABLES lt_bp[].
 11 PERFORM frm_upload_to_http.
 12 
 13 FORM frm_tabconvxtr TABLES pt_data.
 14   DATA:lw_tab_ref TYPE REF TO data.
 15   CREATE DATA lw_tab_ref LIKE LINE OF pt_data.
 16 
 17   DATA:ls_mime    TYPE w3mime,
 18        lt_mime    TYPE TABLE OF w3mime,
 19        filesize   TYPE string,
 20        filesizei  TYPE i,
 21        lv_xstring TYPE xstring,
 22        fileext    TYPE char20,
 23        ls_key     TYPE wwwdatatab.
 24 
 25   ls_key-relid = 'MI'.
 26   ls_key-objid = 'XXXXXX'.
 27   SELECT * FROM wwwparams INTO TABLE @DATA(lt_wwwparam)
 28    WHERE relid = @ls_key-relid
 29      AND objid = @ls_key-objid.
 30   TRY.
 31       LOOP AT lt_wwwparam INTO DATA(ls_wwwparam).
 32         CASE ls_wwwparam-name.
 33           WHEN 'filesize'.
 34             filesizei = filesize = ls_wwwparam-value.
 35             CONDENSE filesize NO-GAPS.
 36           WHEN 'fileextension'.
 37             fileext = ls_wwwparam-value.
 38         ENDCASE.
 39       ENDLOOP.
 40 **********************************************************************
 41 * 获取SMW0的数据
 42       CALL FUNCTION 'WWWDATA_IMPORT'
 43         EXPORTING
 44           key               = ls_key
 45         TABLES
 46           mime              = lt_mime
 47         EXCEPTIONS
 48           wrong_object_type = 1
 49           import_error      = 2
 50           OTHERS            = 99.
 51 
 52       CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
 53         EXPORTING
 54           input_length = filesizei
 55         IMPORTING
 56           buffer       = lv_xstring
 57         TABLES
 58           binary_tab   = lt_mime
 59         EXCEPTIONS
 60           failed       = 1
 61           OTHERS       = 2.
 62 **********************************************************************
 63       DATA(xlsx_handling) = cl_ehfnd_xlsx=>get_instance( )."xlsx 句柄
 64       DATA(xlsx_document) = xlsx_handling->load_doc( lv_xstring )."xlsx 文件
 65       DATA(xlsx_sheets) = xlsx_document->get_sheets( )."得到sheets
 66       DATA(first_xlsx_sheet) = xlsx_document->get_sheet_by_id( xlsx_sheets[ 1 ]-sheet_id )."得到sheet
 67       first_xlsx_sheet->change_sheet_name( 'Sheet_first' )."设置sheet的名称
 68       DO 2 TIMES.
 69         DATA(lv_row) = sy-index + 3."从第三行起，开始写数据
 70         DO 10 TIMES.
 71           DATA(lv_column) = sy-index.
 72           IF first_xlsx_sheet->has_cell_content( iv_row = lv_row iv_column = lv_column ).
 73           ELSE.
 74             first_xlsx_sheet->set_cell_content( iv_row = lv_row iv_column = lv_column iv_value = '测试数据!' ).
 75           ENDIF.
 76 
 77         ENDDO.
 78       ENDDO.
 79 
 80       gv_xcontent = xlsx_document->save( ).
 81     CATCH cx_openxml_format  INTO DATA(openxml_format_exception).
 82       MESSAGE e001(00) RAISING file_export_error
 83       WITH 'Error occurs when constructing excel file instance.'." cx_openxml_format.
 84     CATCH cx_openxml_not_found INTO DATA(openxml_not_found_exception).
 85       MESSAGE e001(00) RAISING file_export_error
 86       WITH ' Error occurs when constructing excel file instance.' "CX_OPENXML_NOT_FOUND |.
 87       .
 88     CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed_exception).
 89       MESSAGE e001(00) RAISING file_export_error
 90       WITH 'Error occurs when constructing excel file instance.'" CX_OPENXML_NOT_ALLOWED |.
 91       .
 92   ENDTRY.
 93 
 94   "cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = filecontent IMPORTING ex_xtab = pt_xtab[] ).
 95   "length = xstrlen( filecontent ).
 96   "pv_xtab[] = l_binary_tab[].
 97   "length = l_length.
 98 ENDFORM.
 99 
100 FORM frm_upload_to_http .
101   DATA:lo_response TYPE REF TO if_http_response,
102        lv_hostname TYPE string,
103        lv_port     TYPE string.
104 
105   CREATE OBJECT lo_response TYPE cl_http_response EXPORTING add_c_msg = 1.
106   lo_response->set_data( gv_xcontent ).
107   lo_response->set_header_field( name = if_http_header_fields=>content_type value = '.xlsx' ).
108   lo_response->set_status( code = 200 reason = 'OK' ).
109   lo_response->server_cache_expire_rel( expires_rel = 600 ).
110 
111   CALL FUNCTION 'TH_GET_VIRT_HOST_DATA'
112     EXPORTING
113       protocol = 0
114       virt_idx = 0
115     IMPORTING
116       hostname = lv_hostname
117       port     = lv_port.
118 
119   DATA(lv_guid) = cl_system_uuid=>create_uuid_x16_static( ).
120   DATA(lv_url) = |http://{ lv_hostname }:{ lv_port }/sap/public/{ lv_guid }.xlsx|.
121   cl_http_server=>server_cache_upload( url = lv_url response = lo_response ).
122   cl_gui_frontend_services=>execute( document = lv_url ).
123   FREE lo_response.
124 ENDFORM.