abap导出Excel

下载smw0 Excel模板

  METHOD get_excel_template.
    DATA:lt_mime_data TYPE TABLE OF w3mime,
         ls_id        TYPE wwwdataid,
         ls_key       TYPE wwwdatatab.
    DATA:lv_string       TYPE string,
         lv_input_length TYPE i.

    ls_key-relid ='MI'.
    ls_key-objid = iv_objid."模板ID

    CALL FUNCTION 'WWWDATA_IMPORT'
      EXPORTING
        key               = ls_key
      TABLES
*       HTML              =
        mime              = lt_mime_data
      EXCEPTIONS
        wrong_object_type = 1
        import_error      = 2
        OTHERS            = 3.
    IF sy-subrc <> 0.
* Implement suitable error handling here
    ENDIF.

    SELECT SINGLE value
    FROM  wwwparams
    WHERE  relid       EQ @ls_key-relid
    AND    objid       EQ @ls_key-objid
    AND name EQ 'filesize'
    INTO @DATA(lv_para_value).

    lv_input_length = lv_para_value.

    CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
      EXPORTING
        input_length = lv_input_length
*       FIRST_LINE   = 0
*       LAST_LINE    = 0
      IMPORTING
        buffer       = ev_file
      TABLES
        binary_tab   = lt_mime_data
      EXCEPTIONS
        failed       = 1
        OTHERS       = 2.
    IF sy-subrc <> 0.
* Implement suitable error handling here
    ENDIF.

  ENDMETHOD.

数据导入到Excel指定表格

METHOD set_data_to_excel.
  FIELD-SYMBOLS:<fv_value> TYPE any,
                <fs_data>  TYPE any.
  ASSIGN is_data TO <fs_data>.

  DATA: LV_VALUE TYPE VAL_TEXT.

  ASSIGN COMPONENT iv_fieldname  OF STRUCTURE IS_DATA TO <fv_value>.
  IF sy-subrc = 0.

    LV_VALUE = <fv_value>.

    CASE IV_FIELDNAME.
      WHEN 'PLSTA'.
        SELECT SINGLE DDTEXT INTO LV_VALUE FROM DD07T WHERE DOMNAME = 'ZD_PLSTA' AND DDLANGUAGE = SY-LANGU AND DOMVALUE_L = <fv_value>.

      WHEN OTHERS.
    ENDCASE.

    co_worksheet->set_cell( ip_column = iv_index_id
                            ip_row = iv_rowno
                            ip_value = CONV string( LV_VALUE )
                             ).

    IF iv_style IS NOT INITIAL.
      co_worksheet->set_cell_style( ip_column = iv_index_id
                        ip_row  = iv_rowno
                        ip_style  = iv_style ).
    ENDIF.
  ENDIF.
ENDMETHOD.

准备导出数据到Excel

  DATA:lo_excel     TYPE REF TO zcl_excel.
  DATA:
    lo_worksheet          TYPE REF TO zcl_excel_worksheet,
    lo_excel_reader       TYPE REF TO zif_excel_reader,
    lo_excel_writer       TYPE REF TO zif_excel_writer,
    lo_border             TYPE REF TO zcl_excel_style_border,
    lo_style              TYPE REF TO zcl_excel_style,
    lv_style_01           TYPE zexcel_cell_style,
    lv_style_02           TYPE zexcel_cell_style,
    lv_style_03           TYPE zexcel_cell_style,
    lv_style_04           TYPE zexcel_cell_style,
    lv_style_05           TYPE zexcel_cell_style,
    lv_style_06           TYPE zexcel_cell_style,
    lv_style_07           TYPE zexcel_cell_style,
    lv_style_08           TYPE zexcel_cell_style,
    lv_style_09           TYPE zexcel_cell_style,
    lv_style_10           TYPE zexcel_cell_style,
    lv_style_11           TYPE zexcel_cell_style,
    lv_style_title        TYPE zexcel_cell_style,
    lv_row                TYPE syindex,
    lv_column             TYPE syindex,
    lv_index              TYPE syindex,
    lv_init_total_formula TYPE string,
    lv_total_formula      TYPE string,
    lv_fname              TYPE fieldname,
    lv_numc2              TYPE numc2.


  DATA: lv_content      TYPE xstring,
        lv_filename     TYPE string,
        lv_version_name TYPE char20,
        lv_mimetype     TYPE string.

  DATA: lv_index_id  TYPE i.

  DATA: LT_FIELD_USAGE  TYPE FPMGB_T_FIELDUSAGE.
  
  zrre_cl_lib_plm_util=>get_excel_template( "获取excel模板
      EXPORTING
        iv_objid = 'ZRE_SMRY'
      IMPORTING
        ev_file  = DATA(lv_template)
    ).

  "设置EXCEL
  CREATE OBJECT lo_excel.

  CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_2007.

  lo_excel = lo_excel_reader->load( i_excel2007 = lv_template ).

***设置边框
  "边框样式——黑色细线条
  CREATE OBJECT lo_border.
  lo_border->border_color-rgb = zcl_excel_style_color=>c_black.
  lo_border->border_style     = zcl_excel_style_border=>c_border_thin.


***设置样式
  "设置样式1——
  lo_style                        = lo_excel->add_new_style( ).

  lo_style->fill->filltype        = zcl_excel_style_fill=>c_fill_pattern_darkgray.
  lo_style->font->bold            = abap_true.
  lo_style->borders->allborders            = lo_border.
  lo_style->font->size            = 11.
*  lo_style->font->name   = zcl_excel_style_font=>c_name_arial.
  lo_style->font->scheme = zcl_excel_style_font=>c_scheme_none.
*  lo_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_center.
  lo_style->alignment->vertical   = zcl_excel_style_alignment=>c_vertical_center.
  lv_style_title                     = lo_style->get_guid( ).


  lo_style                        = lo_excel->add_new_style( ).
*  lo_style->fill->filltype        = zcl_excel_style_fill=>c_fill_pattern_darkgray.
*  lo_style->font->bold            = abap_true.
  lo_style->borders->allborders            = lo_border.
  lo_style->font->size            = 11.
*  lo_style->font->name   = zcl_excel_style_font=>c_name_arial.
  lo_style->font->scheme = zcl_excel_style_font=>c_scheme_none.
*  lo_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_center.
  lo_style->alignment->vertical   = zcl_excel_style_alignment=>c_vertical_center.
  lv_style_01                     = lo_style->get_guid( ).

* 创建Excel Worksheet
*  " Second Worksheet
*  lo_worksheet = lo_excel->add_new_worksheet( ).
*  lo_worksheet->set_title( TEXT-042 ).
*
*  " Third Worksheet
*  lo_worksheet = lo_excel->add_new_worksheet( ).
*  lo_worksheet->set_title( TEXT-043 ).
*
*  " Third Worksheet
*  lo_worksheet = lo_excel->add_new_worksheet( ).
*  lo_worksheet->set_title( TEXT-044 ).

  IF lt_res23 IS NOT INITIAL.
    DATA: LV_PLTYP TYPE INT1.
    LV_PLTYP = 23.
    lv_row = 1.

    TRY.
        lo_excel->set_active_sheet_index( i_active_worksheet = LV_PLTYP  ).
        lo_worksheet        = lo_excel->get_worksheet_by_index( LV_PLTYP ).
      CATCH zcx_excel.
    ENDTRY.


    mo_fpm->mo_app_parameter->get_value(
      EXPORTING
        iv_key   = 'ZRRE_CL_PLAN_SMRY_23_ITEM_RPT'
      IMPORTING
        ev_value = lt_field_usage
     ).

    LOOP AT lt_res23 INTO DATA(LS_RES23).
      lv_row = lv_row + 1.
      LOOP AT lt_field_usage INTO DATA(LS_CONF).
        lv_index_id = SY-TABIX.
        me->set_data_to_excel(
                EXPORTING
                  iv_index_id = lv_index_id
                  iv_fieldname = CONV #( LS_CONF-NAME )                " 字段名
                  iv_style     = lv_style_01
                  iv_rowno     = lv_row
                  is_data      = LS_RES23
                CHANGING
                  co_worksheet = lo_worksheet
              ).

      ENDLOOP.

    ENDLOOP.

  ELSE.

    lo_excel->delete_worksheet_by_index( 23 ).

  ENDIF.


  " Create xlsx stream创建Excel文件流
  CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
  lv_content = lo_excel_writer->write_file( lo_excel ).



*    lo_worksheet->set_cell( ip_column = 'A' ip_row = lv_row ip_value = ls_conf-tvalue ip_style = lv_style_title ).

  lv_filename = |汇总_{ sy-datum }.xlsx|.
*    lv_filename = |上报{ sy-datum }.xlsx|.

  lv_mimetype = zrre_cl_ps_utility=>get_mimetype( 'xlsx' ).

  CALL METHOD cl_wd_runtime_services=>attach_file_to_response
    EXPORTING
      i_filename      = lv_filename
      i_content       = lv_content
      i_mime_type     = lv_mimetype
      i_in_new_window = abap_false
      i_inplace       = abap_false.
  DATA: lv_message TYPE string.
  lv_message = '正在下载，请稍侯...'.
  
  CALL METHOD mo_fpm->mo_message_manager->report_message
    EXPORTING
      iv_message_text = lv_message
      io_component    = me
      iv_severity     = if_fpm_message_manager=>gc_severity_success
	  
	  .