SAP download 表格信息到本地 （四）

4、网上有一个开源的代码，用来生成xml的二進制文件，可以參考demo例子ZDEMO_EXCEL*

这个其实开源的代码都有，就不做特别多的介绍了，例子蛮多的，

下面介紹了一個簡單的例子，可以直接參考demo的例子，也可以參考下面的網站：

https://www.icode9.com/content-4-587262.html

REPORT ztestlian8.
DATA: BEGIN OF ls_itab,
  field_1(10) TYPE c,
  field_2(10) TYPE c,
  field_3(10) TYPE c,
  END OF ls_itab,
  lt_itab LIKE TABLE OF ls_itab.
DATA: i_contents_hex       LIKE solix   OCCURS 0 WITH HEADER LINE,
      filename TYPE string VALUE 'C:\Users\Lian.zhao\Desktop\TEST1.xlsx'.
ls_itab-field_1 = 'test1'.
ls_itab-field_2 = 'test2'.
ls_itab-field_3 = 'test3'.
APPEND ls_itab TO lt_itab.

DATA: lo_excel                TYPE REF TO zcl_excel,
      lo_excel_writer         TYPE REF TO zif_excel_writer,
      lo_worksheet            TYPE REF TO zcl_excel_worksheet,
      lo_exception            TYPE REF TO zcx_excel,
      t_rawdata               TYPE solix_tab WITH HEADER LINE,
      lv_file                 TYPE xstring,
      bytecount TYPE i.

DATA: ls_table_set TYPE zexcel_s_table_settings,
      lt_catelog TYPE zexcel_t_fieldcatalog.

FIELD-SYMBOLS: <fs_catalog> TYPE zexcel_s_fieldcatalog.

TRY.
    CREATE OBJECT lo_excel.
    " Get active sheet
    lo_worksheet = lo_excel->get_active_worksheet( ).
    lo_worksheet->set_title( ip_title = 'Sheet1' ).

    "设置内表样式
    ls_table_set-table_name = 'LT_ITAB'.
    ls_table_set-table_style = zcl_excel_table=>builtinstyle_medium2.
    "设置内表的启示行列
    ls_table_set-top_left_column = 'A'.
    ls_table_set-top_left_row = '2'.
    ls_table_set-bottom_right_column = 'C'.
*    ls_table_set-bottom_right_row
    "行条纹
    ls_table_set-show_row_stripes = abap_false.
    "列条纹
    ls_table_set-show_column_stripes = abap_true.
    "无过滤器
    ls_table_set-nofilters = abap_true.

    "获取内表字段
    lt_catelog = zcl_excel_common=>get_fieldcatalog( ip_table = lt_itab ).
    "设置field catalog
    "zexcel_s_fieldcatalog 的structure 结构解析
    "position：字段显示位置
    "dynpfld:字段是否显示，true显示，false隐藏
    "abap_type:字段对应ABAP类型
    "cond_style:可以添加zcl_excel_style_conditional，图标显示
    "totals_function:该列添加统计行，统计类型，
    "TOTALS_FUNCTION_AVERAGE:平均值
    "TOTALS_FUNCTION_COUNT:统计记录数
    "TOTALS_FUNCTION_MAX:最大值
    "TOTALS_FUNCTION_MIN:最小值
    "TOTALS_FUNCTION_SUM:合计

    LOOP AT lt_catelog ASSIGNING <fs_catalog>.
      CASE <fs_catalog>-fieldname.
        WHEN 'FIELD_1'.
          <fs_catalog>-position = 1.
          <fs_catalog>-dynpfld = abap_true.
          <fs_catalog>-scrtext_m = 'Fieldname1'.
          <fs_catalog>-abap_type = cl_abap_typedescr=>typekind_char.
        WHEN 'FIELD_2'.
          <fs_catalog>-position = 1.
          <fs_catalog>-dynpfld = abap_true.
          <fs_catalog>-scrtext_m = 'Fieldname2'.
        WHEN 'FIELD_3'.
          <fs_catalog>-position = 1.
          <fs_catalog>-dynpfld = abap_true.
          <fs_catalog>-scrtext_m = 'Fieldname3'.
      ENDCASE.
    ENDLOOP.

    lo_worksheet->bind_table(
      EXPORTING
        ip_table          = lt_itab
        it_field_catalog  = lt_catelog  " Table binding field catalog
        is_table_settings = ls_table_set  ).

    CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
    lv_file = lo_excel_writer->write_file( io_excel = lo_excel ).
    t_rawdata[] = cl_bcs_convert=>xstring_to_solix( iv_xstring  = lv_file ).
    bytecount = xstrlen( lv_file ).

    filename = 'C:\Users\Lian.zhao\Desktop\TEST1.xlsx'.

    cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = bytecount
                                              filename     = filename
                                              filetype     = 'BIN'
                                     CHANGING data_tab     = t_rawdata[] ).

  CATCH zcx_excel INTO lo_exception.
    DATA: lv_result TYPE string.
    lv_result = lo_exception->if_message~get_text( ).
    MESSAGE lv_result TYPE 'E'.

ENDTRY.

此处在添加几种我们会用到的几种单元格类型：

  " Create cell style for display only fields
  lo_style = lo_excel->add_new_style( ).
  lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
  lo_style->fill->fgcolor-rgb  = zcl_excel_style_color=>c_gray.
  lo_style->number_format->format_code = zcl_excel_style_number_format=>c_format_text.

  " sheet style (white background)
  lo_style = lo_excel->add_new_style( ).
  lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
  lo_style->fill->fgcolor-rgb  = zcl_excel_style_color=>c_white.
  lv_style_guid = lo_style->get_guid( ).

  " Create cell style for display only date field
  lo_style_date = lo_excel->add_new_style( ).
  lo_style_date->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
  lo_style_date->fill->fgcolor-rgb  = zcl_excel_style_color=>c_gray.
  lo_style_date->number_format->format_code = zcl_excel_style_number_format=>c_format_date_ddmmyyyy.

  " Create cell style for editable fields
  lo_style_editable = lo_excel->add_new_style( ).
  lo_style_editable->protection->locked = zcl_excel_style_protection=>c_protection_unlocked.

所有的背景填充颜色：  ZDEMO_EXCEL21