ABAP学习(16):ABAP2XLSX,操作excel

Stella981
• 阅读 3224

ABAP2XLSX

    ABAP2XLS是一个操作EXCEL的开源项目,相比较OLE2和DOI方式操作excel更加方便。

    ABAP中搜索ZDEMO_EXCEL*搜索Program可以查看SAP提供的示例程序,实现多种Excel。

1.创建excel

    Excel类:zcl_excel类,代表一个excel文档;

    Worksheet类:zcl_excel_worksheet类,代表一个worksheet;

    异常类:zcx_excel类,异常类;

    超链接类:zcl_excel_hyperlink类,通过设置超链接可链接到指定sheet栏位或者打开网络链接;

    创建excel过程:

    1、定义zcl_excel类对象对应变量lo_excel;

    2、CREATE OBJECT lo_excel,创建zcl_excel类对象;

    3、通过调用zcl_excel的get_active_sheet方法,获取当前活动sheet,当创建zcl_excel对象后,默认有一个worksheet处于活动;

    4、调用add_new_worksheet方法,可以创建新的worksheet;

示例:

"excel文档类对象
DATA:lo_excel TYPE REF TO zcl_excel.
"excel worksheet类对象
DATA:lo_worksheet TYPE REF TO zcl_excel_worksheet.
"异常类
DATA:lf_cxexcel TYPE REF TO ZCX_EXCEL.

"创建excel
FORM creat_excel.
  "excel超链接
  DATA:lo_hyperlink TYPE REF TO zcl_excel_hyperlink.
  TRY.
      "创建excel对象
      CREATE OBJECT lo_excel.
      "获得当前worksheet
      lo_worksheet = lo_excel->get_active_worksheet( ).

      "设置单元格
      lo_worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = 'hello world!' ).
      lo_worksheet->set_cell( ip_column = 'A' ip_row = 2 ip_value = sy-datum ).
      lo_worksheet->set_cell( ip_column = 'A' ip_row = 3 ip_value = sy-uzeit ).

      "创建超链接
      "外部链接
      lo_hyperlink = zcl_excel_hyperlink=>create_external_link( iv_url = 'https://www.baidu.com' ).
      lo_worksheet->set_cell( ip_column = 'A' ip_row = 4 ip_value = '百度一下' ip_hyperlink = lo_hyperlink ).

      "内部链接到其他sheet
      lo_hyperlink = zcl_excel_hyperlink=>create_internal_link( iv_location = 'itab!A1' ).
      lo_worksheet->set_cell( ip_column = 'A' ip_row = 5 ip_value = 'itab:A1' ip_hyperlink = lo_hyperlink ).

    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

2.excel下载与显示

    Excel下载和显示需要将excel转换成xstring操作。

    通过类型zif_excel_writer的cl_writer对象,调用write_file方法,将zcl_excel对象转换成xstring。将xstring转换为solix_tab类型变量,方便显示和下载操作。

2.1将excel转换xstring

    使用zif_excel_writer类,如果需要下载成xlsx类型文档,创建实例化对象时类型为zcl_excel_writer_2007;通过write_file方法获取xstring类型数据;再将xstring类型data转换为solix_tab类型数据。

示例:

"下载显示excel转换
DATA:cl_writer TYPE REF TO zif_excel_writer.
DATA:xdata TYPE xstring.
DATA:t_rawdata TYPE solix_tab.
DATA:bytecount TYPE i.

"excel转换成xstring
FORM translate_excel.
  "实例化cl_writer对象
  CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
  xdata = cl_writer->write_file( lo_excel ).

*  "方式1:convert to binary
*  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
*    EXPORTING
*      buffer        = xdata
*    IMPORTING
*      output_length = bytecount
*    TABLES
*      binary_tab    = t_rawdata.

  "方式2:This method is only available on AS ABAP > 6.40
  "excel转换
  t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xdata ).
  "统计字节数
  bytecount = xstrlen( xdata ).
ENDFORM.

2.2GUI直接显示excel

示例:

"在ABAP显示
FORM display_excel.
  DATA:error TYPE REF TO i_oi_error.
  DATA:t_errors TYPE STANDARD TABLE OF REF TO i_oi_error WITH NON-UNIQUE DEFAULT KEY.
  DATA:cl_control  TYPE REF TO i_oi_container_control. "OIContainerCtrl
  DATA:cl_document TYPE REF TO i_oi_document_proxy.   "Office Dokument

  c_oi_container_control_creator=>get_container_control(
    IMPORTING
      control = cl_control
      error   = error
  ).
  APPEND error TO t_errors.

  cl_control->init_control(
     EXPORTING
       inplace_enabled     = 'X'
       no_flush            = 'X'
       r3_application_name = 'Demo Document Container'
       parent              = cl_gui_container=>screen0
     IMPORTING
       error               = error
     EXCEPTIONS
       OTHERS  = 2
    ).
  APPEND error TO t_errors.

  cl_control->get_document_proxy(
    EXPORTING
      document_type  = 'Excel.Sheet'                " EXCEL
      no_flush       = ' '
    IMPORTING
      document_proxy = cl_document
      error  = error
  ).
  APPEND error TO t_errors.

  cl_document->open_document_from_table(
     EXPORTING
       document_size    = bytecount
       document_table   = t_rawdata
       open_inplace     = 'X'
   ).
  WRITE: '.'.  " To create an output.  That way screen0 will exist
ENDFORM.

2.3导出excel到本地

    主要使用cl_gui_frontend_services=>gui_download方法下载。

示例:

"下载到本地
FORM download_excel.
  DATA:filepath TYPE String VALUE 'C:'.
  DATA:filename TYPE String VALUE 'abap2xlsx1'.

  "选择文件保存路径
  cl_gui_frontend_services=>directory_browse(
      EXPORTING
        window_title         = 'Select path to download EXCEL-file'
        initial_folder       = filepath
      CHANGING
        selected_folder      = filepath
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4
    ).

  filename = filepath && '\' && filename && '.XLSX'.
  "下载excel
  cl_gui_frontend_services=>gui_download(
    EXPORTING
       bin_filesize = bytecount
       filename     = filename
       filetype     = 'BIN'
    CHANGING
       data_tab     = t_rawdata
   ).
ENDFORM.

2.4将excel保存为csv文件

    使用zif_excel_writer类,如果需要下载成csv类型文档,创建实例化对象时类型为zcl_excel_writer_csv;调用write_file方法将zcl_excel转换为xstring;再将xstring类型数据转换为solix_tab类型数据。这个是单个sheet下载,使用zcl_excel_writer_csv类对象的set_active_sheet_index()设置要下载的worksheet。

示例:

"excel转换下载成csv
FORM translate_csv.
  "cl_writer对象
  CREATE OBJECT cl_writer TYPE zcl_excel_writer_csv.
  zcl_excel_writer_csv=>set_delimiter( ip_value = cl_abap_char_utilities=>horizontal_tab ).
  zcl_excel_writer_csv=>set_enclosure( ip_value = '''' ).
  zcl_excel_writer_csv=>set_endofline( ip_value = cl_abap_char_utilities=>cr_lf ).
  "单个sheet写入,set_active_sheet_index_by_name( )
  zcl_excel_writer_csv=>set_active_sheet_index( i_active_worksheet = 2 ).
  xdata = cl_writer->write_file( lo_excel ).
  "后续操作和excel一致,文件后缀.csv
ENDFORM.

2.5文件类型xlsm读写

    使用zif_excel_writer类,如果需要下载成xlsm类型文档,创建实例化对象时类型为zcl_excel_writer_xlsm.通过write_file方法获取xstring类型数据;再将xstring类型data转换为solix_tab类型数据。

    使用zif_excel_reader类,创建对象时类型为zcl_excel_reader_xlsm,通过类对象的load_file()方法加载xlsm类型文档。

示例:

"读写xlsm类型文档
FORM translate_xlsm.
  DATA:lo_excel_writer TYPE REF TO zif_excel_writer.
  DATA:lo_excel_reader TYPE REF TO zif_excel_reader.
 
  TRY .
      CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_xlsm.
      CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_xlsm.
      lo_excel = lo_excel_reader->load_file( '文件路径' ).
      xdata = lo_excel_writer->write_file( lo_excel ).

      "后续操作和excel一致,文件后缀.xlsm
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

2.6alv导出到excel

    使用zcl_excel_converter类导出alv到excel。

    1、创建zcl_excel_converter对象;

    2、调用convert方法,传入cl_salv_table和内表等参数;

    3、调用writer_file方法导出到excel;

示例1:

"使用zcl_excel_converter导出excel
FORM export_excel_conv.
  DATA:t_sflight TYPE TABLE OF sflight.
  DATA:lo_salv TYPE REF TO  cl_salv_table.

  SELECT * INTO TABLE t_sflight FROM sflight UP TO 10 ROWS.
  TRY.
      cl_salv_table=>factory(
        EXPORTING
          list_display = abap_false
        IMPORTING
          r_salv_table = lo_salv
        CHANGING
          t_table      = t_sflight[] ).
    CATCH cx_salv_msg .
  ENDTRY.

  "调用显示alv
*  lo_salv->display( ).
  DATA: lo_converter TYPE REF TO zcl_excel_converter.
  "创建zcl_excel_converter类
  CREATE OBJECT lo_converter.
  TRY .
      lo_converter->convert(
        EXPORTING
          io_alv        = lo_salv
          it_table      = t_sflight
          i_row_int     = 2
          i_column_int  = 2
          ).
    CATCH zcx_excel.
  ENDTRY.

  "调用write_file方法导出excel
  lo_converter->write_file( i_path = 'D:/test.xlsx' ).
ENDFORM.                    "EXPORT_TO_EXCEL_CONV

    使用zcl_excel_worksheet导出alv到excel。

    1、调用zcl_excel_worksheet对象的方法bind_alv(),将alv和worksheet绑定;

    2、然后zif_excel_writer接口实现对象,将lo_excel转换为xstring,然后再转换为solix_tab类型,最后调用cl_gui_frontend_services的gui_download方法导出excel。

示例2:

"使用worksheet的bind_alv方法
FORM export_excel_bind.
  DATA:t_sflight TYPE TABLE OF sflight.
  DATA:lo_salv TYPE REF TO  cl_salv_table.

  SELECT * INTO TABLE t_sflight FROM sflight UP TO 10 ROWS.
  TRY.
      cl_salv_table=>factory(
        EXPORTING
          list_display = abap_false
        IMPORTING
          r_salv_table = lo_salv
        CHANGING
          t_table      = t_sflight[] ).
    CATCH cx_salv_msg .
  ENDTRY.

  "调用显示alv
*  lo_salv->display( ).

  TRY .
      "将alv绑定到worksheet,
      "只是绑定,导出excel需要调用gui_download方法
      lo_worksheet->bind_alv(
          io_alv      = lo_salv
          it_table    = t_sflight
          i_top       = 2
          i_left      = 1
             ).
    CATCH zcx_excel.
  ENDTRY.

  "下载excel操作
  "zcl_excel_writer对象,实例化
  CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
  xdata = cl_writer->write_file( lo_excel ).
  t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xdata ).

  "统计字节数
  bytecount = xstrlen( xdata ).
  "下载excel
  cl_gui_frontend_services=>gui_download(
    EXPORTING
       bin_filesize = bytecount
       filename     = 'D:/test.xlsx'
       filetype     = 'BIN'
    CHANGING
       data_tab     = t_rawdata
    ).
ENDFORM.

  使用zcl_excel_worksheet的bind_alv_ole2方法直接导出。

    1、通过get_globals_from_slvc_fullscr方法获取屏幕的alv对象;

    2、直接调用bind_alv_ole2方法,就可以将alv导出到excel。

示例3:

"通过worksheet的bind_alv_ole2方法,将alv导出
FORM export_excel_bind1.
  "屏幕alv对象
  DATA:lo_alv TYPE REF TO cl_gui_alv_grid.
  "excel header
  DATA:gt_listheader TYPE slis_t_listheader.
  DATA:wa_listheader LIKE LINE OF gt_listheader.
  "excel保存路径
  DATA:l_path TYPE string VALUE 'C:temp/test.xlsx'.

  "获取屏幕alv对象
  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
    IMPORTING
      e_grid = lo_alv.

  wa_listheader-typ = 'H'.
  wa_listheader-info = 'header line'.
  APPEND wa_listheader TO gt_listheader.
  wa_listheader-typ = 'S'.
  wa_listheader-info = 'second line'.
  APPEND wa_listheader TO gt_listheader.
  wa_listheader-typ = 'A'.
  wa_listheader-info = '地址信息'.
  APPEND wa_listheader TO gt_listheader.

  "调用lo_worksheet的bind_alv_ole2方法,直接调用下载
  lo_worksheet->bind_alv_ole2(
    EXPORTING
*      I_DOCUMENT_URL          = SPACE " excel template
*      I_XLS                   = 'X' " create in xls format?
      i_save_path             = l_path
      io_alv                  = lo_alv
      it_listheader           = gt_listheader
      i_top                   = 2
      i_left                  = 1
*      I_COLUMNS_HEADER        = 'X'
*      I_COLUMNS_AUTOFIT       = 'X'
*      I_FORMAT_COL_HEADER     =
*      I_FORMAT_SUBTOTAL       =
*      I_FORMAT_TOTAL          =
    EXCEPTIONS
      miss_guide              = 1
      ex_transfer_kkblo_error = 2
      fatal_error             = 3
      inv_data_range          = 4
      dim_mismatch_vkey       = 5
      dim_mismatch_sema       = 6
      error_in_sema           = 7
      OTHERS                  = 8
          ).
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
ENDFORM.

2.7excel上传

    通过zcl_excel_reader_2007类实例化对象cl_reader,调用load_file方法,获取zcl_excel类对象lo_excel。通过lo_excel获取worksheet,然后通过worksheet获取上传excel单元格内容,将单元格内容保存到内表。

示例:

"读取excel

FORM read_excel.

  "excel文档类对象

  DATA:lo_excel TYPE REF TO zcl_excel.

  "excel worksheet类对象

  DATA:lo_worksheet TYPE REF TO zcl_excel_worksheet.

  "异常类

  DATA:lf_cxexcel TYPE REF TO ZCX_EXCEL.

  "上传excel

  DATA:cl_reader TYPE REF TO zif_excel_reader.

  "excel导入内表

  DATA:BEGIN OF t_excel OCCURS 0,

      str1 TYPE string,

      str2 TYPE string,

      str3 TYPE string,

      str4 TYPE string,

      END OF t_excel.

  "读取行列数

  DATA:col_count TYPE I.

  DATA:row_count TYPE I.

  DATA:col TYPE I.

  DATA:row TYPE I.

  

  TRY.

      CREATE OBJECT lo_excel.

      "cl_reader对象

      CREATE OBJECT cl_reader TYPE zcl_excel_reader_2007.

      lo_excel = cl_reader->load_file( i_filename = 'D:\test.xlsx' ).

      lo_worksheet = lo_excel->get_active_worksheet( ).

 

      "获取行数,列数

      row_count = lo_worksheet->get_highest_row( ).

      col_count = lo_worksheet->get_highest_column( ).

 

      DO row_count TIMES.

        row = sy-index.

        CLEAR t_excel.

        DO col_count TIMES.

          col = sy-index.

          CASE col.

            WHEN 1.

              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str1 ).

            WHEN 2.

              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str2 ).

            WHEN 3.

              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str3 ).

            WHEN 4.

              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str4 ).

          ENDCASE.

        ENDDO.

        APPEND t_excel.

      ENDDO.

 

      LOOP AT t_excel.

        WRITE:/ t_excel-str1,t_excel-str2,t_excel-str3,t_excel-str4.

      ENDLOOP.

    CATCH ZCX_EXCEL INTO lf_cxexcel.

      "获取错误信息

      DATA:result TYPE string.

      CALL METHOD lf_cxexcel->IF_MESSAGE~GET_TEXT

        RECEIVING

          result = result.

      MESSAGE result TYPE 'E'.

  ENDTRY.

ENDFORM.

3.设置workbook属性

    设置workbook保护,禁止编辑、新增worksheet。

示例:

"设置workbook级别protection,禁止编辑新建worksheet
FORM set_workbook.
  lo_excel->zif_excel_book_protection~protected = zif_excel_book_protection=>c_protected.
  lo_excel->zif_excel_book_protection~lockrevision = zif_excel_book_protection=>c_locked.
  lo_excel->zif_excel_book_protection~lockstructure = zif_excel_book_protection=>c_locked.
  lo_excel->zif_excel_book_protection~lockwindows = zif_excel_book_protection=>c_locked.
  lo_excel->zif_excel_book_protection~workbookpassword = zcl_excel_common=>encrypt_password( 'secret' ).
  lo_excel->zif_excel_book_protection~revisionspassword = zcl_excel_common=>encrypt_password( 'secret' ).
ENDFORM.

4.设置worksheet属性

    1、获取zcl_excel_worksheet类对象;

    通过zcl_excel对象调用add_new_worksheet方法创建类zcl_excel_worksheet实例对象lo_worksheet;

    通过lo_worksheet调用对应方法设置worksheet属性;

示例:

"设置worksheet
FORM set_worksheet.
  "sheet颜色
  DATA:ls_tabcolor TYPE ZEXCEL_S_TABCOLOR.
  "打印头和脚
  DATA:ls_header  TYPE zexcel_s_worksheet_head_foot.
  DATA:ls_footer  TYPE zexcel_s_worksheet_head_foot.

  TRY .
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'sheet2' ).
      lo_worksheet->zif_excel_sheet_properties~selected = zif_excel_sheet_properties=>c_selected.
      "设置sheet标签颜色
      ls_tabcolor-rgb = zcl_excel_style_color=>c_blue.
      lo_worksheet->set_tabcolor( iv_tabcolor = ls_tabcolor ).
      "设置sheet隐藏
*      lo_worksheet->zif_excel_sheet_properties~hidden = zif_excel_sheet_properties=>c_hidden.
      "设置隐藏0
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 1 ip_value = 'C2隐藏0:' ).
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 2 ip_value = 0 ).
      lo_worksheet->zif_excel_sheet_properties~show_zeros  = zif_excel_sheet_properties=>c_hidezero.

      "sheet打印设置,printing settings
      lo_worksheet->sheet_setup->set_page_margins( ip_header = '1' ip_footer = '1' ip_unit = 'cm' ).
      lo_worksheet->sheet_setup->black_and_white   = 'X'.
      lo_worksheet->sheet_setup->fit_to_page       = 'X'.  " you should turn this on to activate fit_to_height and fit_to_width
      lo_worksheet->sheet_setup->fit_to_height     = 0.    " used only if ip_fit_to_page = 'X'
      lo_worksheet->sheet_setup->fit_to_width      = 2.    " used only if ip_fit_to_page = 'X'
      lo_worksheet->sheet_setup->orientation       = zcl_excel_sheet_setup=>c_orientation_landscape.
      lo_worksheet->sheet_setup->page_order        = zcl_excel_sheet_setup=>c_ord_downthenover.
      lo_worksheet->sheet_setup->paper_size        = zcl_excel_sheet_setup=>c_papersize_a4.
      lo_worksheet->sheet_setup->scale             = 80.   " used only if ip_fit_to_page = SPACE

      "设置打印头和脚
      " Header and Footer
      ls_header-right_value = 'print date &D'.
      ls_header-right_font-size = 8.
      ls_header-right_font-name = zcl_excel_style_font=>c_name_arial.
      ls_footer-left_value = '&Z&F'. "Path / Filename
      ls_footer-left_font = ls_header-right_font.
      ls_footer-right_value = 'page &P of &N'. "page x of y
      ls_footer-right_font = ls_header-right_font.
      lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header
                                                    ip_odd_footer  = ls_footer ).

      "设置隐藏列
      lo_worksheet->zif_excel_sheet_properties~hide_columns_from = 'M'.
      "设置行收缩展开,lv_collapsed:true收缩,false展开
      lo_worksheet->set_row_outline( iv_row_from = 10
                               iv_row_to   = 16
                               iv_collapsed = abap_true ).  " collapsed

      "设置sheet保护
      DATA:lo_style TYPE REF TO zcl_excel_style.
      DATA:lv_style_guid TYPE zexcel_cell_style.
      lo_worksheet->zif_excel_sheet_protection~protected  = zif_excel_sheet_protection=>c_protected.
      "设置密码加密
      lo_worksheet->zif_excel_sheet_protection~password   = zcl_excel_common=>encrypt_password( 'secret' ).
      lo_worksheet->zif_excel_sheet_protection~sheet      = zif_excel_sheet_protection=>c_active.
      lo_worksheet->zif_excel_sheet_protection~objects    = zif_excel_sheet_protection=>c_active.
      lo_worksheet->zif_excel_sheet_protection~scenarios  = zif_excel_sheet_protection=>c_active.
      lo_style = lo_excel->add_new_style( ).
      "设置unlocked锁住样式
      lo_style->protection->locked = zcl_excel_style_protection=>c_protection_unlocked.
      lv_style_guid = lo_style->get_guid( ).
      "设置可编辑
      lo_worksheet->set_cell( ip_column = 'E' ip_row = 2 ip_value = 'unlocked' ip_style = lv_style_guid ).

      "设置单元格尺寸
      lo_worksheet->zif_excel_sheet_properties~zoomscale = 150.
*      lo_worksheet->zif_excel_sheet_properties~zoomscale_normal = 150.
*      lo_worksheet->zif_excel_sheet_properties~zoomscale_pagelayoutview = 150.
*      lo_worksheet->zif_excel_sheet_properties~zoomscale_sheetlayoutview = 150.

      "设置是否显示打印sheet网格线
      lo_worksheet->set_show_gridlines(  i_show_gridlines  = abap_false ).
      lo_worksheet->set_print_gridlines( i_print_gridlines = abap_false ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

   2. 获取设置当前活动worksheet,特别注意操作多个worksheet时,当前哪个worksheet处于活动状态。

示例:

"设置获取active worksheet
FORM set_active_sheet.
  "当创建lo_excle->add_new_worksheet(),当前创建worksheet处于active状态
  "get_active_sheet_index()方法,获取活动worksheet的index
  "get_active_worksheet()方法,获取活动worksheet
  "set_active_sheet_index()方法,通过index设置活动worksheet
  "set_active_sheet_index_by_name()方法,
ENDFORM.

5.设置单元格样式

    设置单元格样式:主要是zcl_excel_style类,使用zexcel_cell_style数据类型保存guid。

    1、通过excel对象lo_excel调用add_new_style,创建zcl_excel_style类对象;

    2、通过zcl_excel_style对象属性font,fill,borders,alignment,number_format等,设置显示style;

    3、通过zcl_excel_style对象的get_guid方法获取style对应的guid;

    4、zcl_excel_worksheet类lo_worksheet的set_cell方法输入参数ip_style等于对应获取的guid;

示例:

"设置单元格样式
FORM set_style.
  "style类
  DATA:lo_style TYPE REF TO zcl_excel_style.
  "style的guid
  DATA:lv_style_guid TYPE zexcel_cell_style.TRY.
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'sheet3' ).
    CATCH ZCX_EXCEL.
  ENDTRY.

  "字体设置,类:zcl_excel_style_font
  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "加粗
  lo_style->font->bold   = abap_true.
  "斜体
  lo_style->font->italic = abap_true.
  "下划线
  lo_style->font->underline = abap_true.
  "下划线mode,单下划线双下划线
  lo_style->font->underline_mode = zcl_excel_style_font=>c_underline_double.
  "字体family,C_FAMILY_XXX
  lo_style->font->family = zcl_excel_style_font=>c_family_roman.
  "字体名:c_name_XXX,直接设置字体名:'YouYuan'
*  lo_style->font->name   = zcl_excel_style_font=>c_name_arial.
  lo_style->font->name   = 'YouYuan'.
  "字体主题:C_SCHEME_XXX
  lo_style->font->scheme = zcl_excel_style_font=>c_scheme_none.
  "字体颜色,使用CSS颜色值:后面六位,前两位默认FF
*  lo_style->font->color-rgb  = zcl_excel_style_color=>c_red.
  lo_style->font->color-rgb = 'FF66FFCC'.
  "自定义color,设置红绿蓝;create_new_argb方法和create_new_argb_int方法,返回zecel_style_color_argb类型
*  zcl_excel_style_color=>create_new_argb( ip_red = 'FF' ip_green = '00' ip_blu = '00' ).
  "颜色对应结构体ZEXCEL_S_STYLE_COLOR
*  zexcel_s_style_color-rgb对应:ZEXCEL_STYLE_COLOR_ARGB类型
*  zexcel_s_style_color-indexed 对应:ZEXCEL_STYLE_COLOR_INDEXED类型
*  zexcel_s_style_color-theme 对应:ZEXCEL_STYLE_COLOR_THEME类型
*  zexcel_s_style_color-tint 对应:ZEXCEL_STYLE_COLOR_TINT类型
  "字体大小
  lo_style->font->size = 11.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 1 ip_style = lv_style_guid ip_value = 'style样式字体' ).
    CATCH ZCX_EXCEL.
  ENDTRY.

  "fill设置,类:zcl_excel_style_fill
  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "填充类型
  lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
  "前景色
  lo_style->fill->fgcolor-rgb = 'FF66FFCC'.
  "背景色
  lo_style->fill->bgcolor-rgb = 'FF000022'.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 2 ip_style = lv_style_guid ip_value = 'style样式fill' ).
    CATCH ZCX_EXCEL.
  ENDTRY.

  "设置边框,类:zcl_excel_style_borders  "border类
  DATA:lo_border TYPE REF TO zcl_excel_style_border.
  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "创建border对象
  CREATE OBJECT lo_border.
  "设置border样式
  lo_border->border_style = zcl_excel_style_border=>c_border_dashdot.
  lo_border->border_color-rgb = 'FFCC0066'.
  "设置所有border
  lo_style->borders->allborders = lo_border.
  "通过down,left,right,top设置单独边框
*  lo_style->borders->down = lo_border.
  "设置单元格中交叉线
*  lo_style->borders->diagonal = lo_border.
*  lo_style->borders->diagonal_mode = zcl_excel_style_borders=>c_diagonal_both.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 3 ip_style = lv_style_guid ip_value = 'style样式border' ).
    CATCH ZCX_EXCEL.
  ENDTRY.

 
  "设置alignment,类:zcl_excel_style_alignment
  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "设置水平方向,c_horizontal_xxxx:居中center,靠左left,靠右right,默认general
  lo_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_left.
  "设置竖直方向,c_vertical_xxxx:顶部top,底部bottom,中间center,排齐justify
  lo_style->alignment->vertical = zcl_excel_style_alignment=>c_vertical_top.
  "设置文本旋转
  lo_style->alignment->textrotation = 0.
  "设置缩小适应
  lo_style->alignment->shrinktofit = abap_true.
  "设置缩进字符
  lo_style->alignment->indent = 2.
  "换行
  lo_style->alignment->wraptext = abap_true.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 4 ip_style = lv_style_guid ip_value = 'style样式alignment' )
    CATCH ZCX_EXCEL.
  ENDTRY.

  "设置number_format格式,类:zcl_excel_style_number_format
  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "日期格式
  lo_style->number_format->format_code =  zcl_excel_style_number_format=>c_format_date_ddmmyyyy.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 5 ip_style = lv_style_guid ip_value = sy-datum ).
    CATCH ZCX_EXCEL.
  ENDTRY.

  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "文本格式
  lo_style->number_format->format_code =  zcl_excel_style_number_format=>c_format_text.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 6 ip_style = lv_style_guid ip_value = '01234' ).
    CATCH ZCX_EXCEL.
  ENDTRY.


  "设置保护protection,类:zcl_excel_style_protection
  "创建一个新style
  lo_style = lo_excel->add_new_style( ).
  "是否隐藏??
*  lo_style->protection->hidden = zcl_excel_style_protection=>c_protection_hidden.
  "是否锁住??
  lo_style->protection->locked = '1'.
  "获取style的编码uuid
  lv_style_guid = lo_style->get_guid( ).
  TRY .
      "设置style
      lo_worksheet->set_cell( ip_column = 'C' ip_row = 7 ip_style = lv_style_guid ip_value = 'style样式protection' ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

    设置单元格对应数据ABAP类型。

示例:

"设置单元格对应ABAP数据类型
FORM set_typedesc.
  TRY.
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'sheet6' ).
      "设置type desc
      "cl_abap_typedescr=>typekind_string,
      "cl_abap_typedescr=>typekind_packed,
      "cl_abap_typedescr=>typekind_num,
      "cl_abap_typedescr=>typekind_date,
      lo_worksheet->set_cell(
        ip_column = 'C'
        ip_row = 1
        ip_abap_type = cl_abap_typedescr=>typekind_string
        ip_value = 'string' ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

6.显示内表到excel

    通过worksheet的bind_table方法将一个内表显示到sheet中

示例:

"展示一个内表到excel
FORM show_itab.
  "内表定义
  DATA:t_sflight LIKE TABLE OF sflight.
  SELECT * INTO CORRESPONDING FIELDS OF TABLE t_sflight FROM sflight WHERE CARRID = 'AA'.

  "excel内表设置
  DATA: ls_table_settings TYPE zexcel_s_table_settings.
  "excel内表显示栏位,样式
  DATA: lt_field_catalog TYPE zexcel_t_fieldcatalog.
  "指针
  FIELD-SYMBOLS:<fs_catalog> TYPE zexcel_s_fieldcatalog.

  TRY .
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      "设置sheet标题
      lo_worksheet->set_title( ip_title = 'itab' ).
      "设置内表样式
      ls_table_settings-table_name = 'itab'.
      ls_table_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
      "设置内表起始列和行
      ls_table_settings-top_left_column = 'B'.
      ls_table_settings-top_left_row = 2.
*      ls_table_settings-bottom_right_column = 'F'.
*      ls_table_settings-bottom_right_row = 8.
      "行条纹
      ls_table_settings-show_row_stripes  = abap_false.
      "列条纹
      ls_table_settings-show_column_stripes  = abap_true.
      "无过滤器
      ls_table_settings-nofilters = abap_true.

      "iconset
      DATA: ls_iconset TYPE zexcel_conditional_iconset.
      "条件格式
      DATA: lo_style_conditional   TYPE REF TO zcl_excel_style_conditional.
      "设置显示图标类型c_iconset_XXXX,不同图标
      ls_iconset-iconset = zcl_excel_style_conditional=>c_iconset_5rating.
      "iconset_数字,数字是几设置几个cfvo,显示不同图标区间
      ls_iconset-cfvo1_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo1_value = '0'.
      ls_iconset-cfvo2_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo2_value = '20'.
      ls_iconset-cfvo3_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo3_value = '30'.
      ls_iconset-cfvo4_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo4_value = '40'.
      ls_iconset-cfvo5_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo5_value = '50'.

      "条件设置
      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
      "规则
      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_iconset.
      "优先级
      lo_style_conditional->priority = 1.
      lo_style_conditional->mode_iconset  = ls_iconset.
      "是否同时显示单元格值
      ls_iconset-showvalue = zcl_excel_style_conditional=>c_showvalue_true.

      "获取内表字段
      lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = t_sflight ).
      "设置field catalog
      "zexcel_s_fieldcatalog字段解析
      "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_field_catalog ASSIGNING <fs_catalog>.
        CASE <fs_catalog>-fieldname.
          WHEN 'CARRID'.
            <fs_catalog>-position = 1.
            <fs_catalog>-dynpfld = abap_true.
          WHEN 'CONNID'.
            <fs_catalog>-position = 2.
            <fs_catalog>-dynpfld = abap_true.
            <fs_catalog>-abap_type = cl_abap_typedescr=>typekind_int.
          WHEN 'FLDATE'.
            <fs_catalog>-position = 3.
            <fs_catalog>-dynpfld = abap_true.
            <fs_catalog>-totals_function = zcl_excel_table=>totals_function_count.
          WHEN 'PRICE'.
            <fs_catalog>-position = 4.
            <fs_catalog>-dynpfld = abap_true.
            <fs_catalog>-cond_style = lo_style_conditional.
            <fs_catalog>-totals_function = zcl_excel_table=>totals_function_sum.
          WHEN OTHERS.
            <fs_catalog>-dynpfld = abap_false.
        ENDCASE.
      ENDLOOP.

      "设置内表和worksheet绑定
      lo_worksheet->bind_table(
        ip_table = t_sflight
        is_table_settings = ls_table_settings
        it_field_catalog  = lt_field_catalog
        ).
      "设置固定行
      lo_worksheet->freeze_panes( ip_num_rows = 3 ).
 
      "设置活动的lo_worksheet,从1开始,
      "使用set_active_sheet_index_by_name方法,通过sheet名设置active sheet
      lo_excel->set_active_sheet_index( 1 ).
    CATCH ZCX_EXCEL INTO lf_cxexcel.
      "获取错误信息
      DATA:result TYPE String.
      CALL METHOD lf_cxexcel->IF_MESSAGE~GET_TEXT
        RECEIVING
          result = result.
      MESSAGE result TYPE 'E'.
  ENDTRY.
ENDFORM.

7.condition图标显示

  通过zcl_excel_style_conditional类,显示不同图标。

示例:

"图标显示
FORM conditional_format.
  "条件格式
  DATA: lo_style_conditional   TYPE REF TO zcl_excel_style_conditional.
  TRY.
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'sheet4' ).

      "iconset
      DATA: ls_iconset TYPE zexcel_conditional_iconset.
      "设置显示图标类型c_iconset_XXXX,不同图标
      ls_iconset-iconset = zcl_excel_style_conditional=>c_iconset_3trafficlights2.
      "iconset_数字,数字是几设置几个cfvo,显示不同图标区间
      ls_iconset-cfvo1_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo1_value = '0'.
      ls_iconset-cfvo2_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo2_value = '33'.
      ls_iconset-cfvo3_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
      ls_iconset-cfvo3_value = '66'.
      "是否同时显示单元格值
      ls_iconset-showvalue = zcl_excel_style_conditional=>c_showvalue_true.

      "条件设置
      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
      "规则
      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_iconset.
      "优先级
      lo_style_conditional->priority = 1.
      lo_style_conditional->mode_iconset  = ls_iconset.
      "设置范围range
      lo_style_conditional->set_range( ip_start_column  = 'C'
                                       ip_start_row     = 4
                                       ip_stop_column   = 'C'
                                       ip_stop_row      = 8 ).
      lo_worksheet->set_cell( ip_row = 4 ip_column = 'C' ip_value = 100 ).
      lo_worksheet->set_cell( ip_row = 5 ip_column = 'C' ip_value = 1000 ).
      lo_worksheet->set_cell( ip_row = 6 ip_column = 'C' ip_value = 150 ).
      lo_worksheet->set_cell( ip_row = 7 ip_column = 'C' ip_value = 10 ).
      lo_worksheet->set_cell( ip_row = 8 ip_column = 'C' ip_value = 500 ).

      "databar
      DATA:ls_databar TYPE zexcel_conditional_databar.
      ls_databar-cfvo1_type = zcl_excel_style_conditional=>c_cfvo_type_min.
      ls_databar-cfvo1_value = '0'.
      ls_databar-cfvo2_type = zcl_excel_style_conditional=>c_cfvo_type_max.
      ls_databar-cfvo2_value = '0'.
      ls_databar-colorrgb = 'FF638EC6'.

      "条件设置
      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
      "规则
      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_databar.
      "优先级
      lo_style_conditional->priority = 1.
      lo_style_conditional->mode_databar = ls_databar.
      "设置范围range
      lo_style_conditional->set_range( ip_start_column  = 'D'
                                       ip_start_row     = 4
                                       ip_stop_column   = 'D'
                                       ip_stop_row      = 8 ).
      lo_worksheet->set_cell( ip_row = 4 ip_column = 'D' ip_value = 100 ).
      lo_worksheet->set_cell( ip_row = 5 ip_column = 'D' ip_value = 200 ).
      lo_worksheet->set_cell( ip_row = 6 ip_column = 'D' ip_value = 300 ).
      lo_worksheet->set_cell( ip_row = 7 ip_column = 'D' ip_value = 400 ).
      lo_worksheet->set_cell( ip_row = 8 ip_column = 'D' ip_value = 500 ).

 
      "colorscale
      DATA:ls_colorscale TYPE zexcel_conditional_colorscale.
      ls_colorscale-cfvo1_type = zcl_excel_style_conditional=>c_cfvo_type_min.
      ls_colorscale-cfvo1_value = 0.
      ls_colorscale-cfvo2_type = zcl_excel_style_conditional=>c_cfvo_type_percentile.
      ls_colorscale-cfvo2_value = '50'.
      ls_colorscale-colorrgb1 = 'FFF8696B'.
      ls_colorscale-colorrgb2 = 'FF63BE7B'.

      "条件设置
      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
      "规则
      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_colorscale.
      "优先级
      lo_style_conditional->priority = 1.
      lo_style_conditional->mode_colorscale = ls_colorscale.
      "设置范围range
      lo_style_conditional->set_range( ip_start_column  = 'E'
                                       ip_start_row     = 4
                                       ip_stop_column   = 'E'
                                       ip_stop_row      = 8 ).
      lo_worksheet->set_cell( ip_row = 4 ip_column = 'E' ip_value = 10 ).
      lo_worksheet->set_cell( ip_row = 5 ip_column = 'E' ip_value = 20 ).
      lo_worksheet->set_cell( ip_row = 6 ip_column = 'E' ip_value = 30 ).
      lo_worksheet->set_cell( ip_row = 7 ip_column = 'E' ip_value = 40 ).
      lo_worksheet->set_cell( ip_row = 8 ip_column = 'E' ip_value = 50 ).

 
      "cellis
      "根据条件,设置单元格样式
      DATA:lo_style TYPE REF TO zcl_excel_style.
      DATA:lv_style_guid TYPE zexcel_cell_style.
      DATA:ls_cellis TYPE zexcel_conditional_cellis.
      lo_style = lo_excel->add_new_style( ).
      lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
      lo_style->fill->bgcolor-rgb = 'FF00CCFF'.
      lv_style_guid = lo_style->get_guid( ).
    "条件设置
      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
      lo_style_conditional->rule = zcl_excel_style_conditional=>c_rule_cellis.
      ls_cellis-formula     = '"hello"'.
      ls_cellis-operator    = zcl_excel_style_conditional=>c_operator_equal.
      ls_cellis-cell_style  = lv_style_guid.
      lo_style_conditional->mode_cellis = ls_cellis.
      lo_style_conditional->priority  = 1.
      lo_style_conditional->set_range( ip_start_column  = 'G'
                                   ip_start_row     = 2
                                   ip_stop_column   = 'G'
                                   ip_stop_row      = 2 ).
      lo_worksheet->set_cell( ip_row = 1 ip_column = 'G' ip_value = 'G2输入hello显示样式:' ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

8.设置单元格公式

    设置单元格公式:通过set_cell方法的ip_formula参数设置;

示例:

"formula,公式
FORM set_formula.
  TRY .
      lo_excel->set_active_sheet_index_by_name( 'sheet4' ).
      "获取worksheet,通过sheet名
      lo_worksheet = lo_excel->get_worksheet_by_name( 'sheet4' ).
      lo_worksheet->set_cell( ip_row = 9 ip_column = 'C' ip_value = '求和:' ).
      lo_worksheet->set_cell( ip_row = 10 ip_column = 'C' ip_formula = 'SUM(C4:C5)' ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

批量设置多个单元格公式:

    使用zcl_excel_commom的shift_formula方法批量获取动态公式。

示例:

"通过循环获取动态公式
      DO 2 TIMES.
        "获取转换的公式
        lv_formula = zcl_excel_common=>shift_formula( 
          iv_reference_formula = 'SUM(C4:C5)'
          iv_shift_cols        = 0    
          iv_shift_rows        = sy-index ). 
        lv_row = 10 + sy-index.  " Absolute row = sy-index rows below reference cell
        lo_worksheet->set_cell( ip_row = lv_row ip_column = 'C' ip_formula = lv_formula ).
      ENDDO.

9.定义range区域

    定义一个range区域,相当于Excel选择区域,进行批量单元格设置。

示例:

"range获取
FORM set_range.
  "range
  DATA:lo_range TYPE REF TO zcl_excel_range.
  "sheet名
  DATA: lv_title TYPE zexcel_sheet_title VALUE 'sheet4'.
  lo_range = lo_excel->add_new_range( ).
  lo_range->name = 'range'.

  "A4A5区域
  lo_range->set_value( ip_sheet_name    = lv_title
                       ip_start_column  = 'A'
                       ip_start_row     = 4
                       ip_stop_column   = 'A'
                       ip_stop_row      = 5 ).
ENDFORM.

10.通用zcl_excel_common类

    通用方法类zcl_excel_common,提供一些通用方法使用。

示例:

"ZCL_EXCEL_COMMON类
FORM common_excel.
  "ZCL_EXCEL_COMMON类提供一些通用方法
  "CONVERT_COLUMN2ALPHA:将列号转换对应字母
  "DATE_TO_EXCEL_STRING:将日期转换为string类型对应数字字符串
  "ENCRYPT_PASSWORD:加密密码字符串
  "EXCEL_STRING_TO_DATE:将日期数字字符串转换为日期
  "SHIFT_FORMULA:移动应用单元格公式
ENDFORM.

11.设置单元格验证

  1.通过zcl_excel_worksheet类对象lo_worksheet的add_new_worksheet方法获取zcl_excel_data_validation类;  

  2.通过zcl_excel_data_validation类设置验证规则;

示例:

"单元格验证
FORM set_validation.
  "range
  DATA:lo_range TYPE REF TO zcl_excel_range.
  "data validation验证规则
  DATA:lo_data_validation TYPE REF TO zcl_excel_data_validation.

  TRY.
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'data validation' ).
      "设置下拉选择值
      lo_worksheet->set_cell( ip_row = 1 ip_column = 'A' ip_value = '苹果' ).
      lo_worksheet->set_cell( ip_row = 2 ip_column = 'A' ip_value = '梨子' ).
      lo_worksheet->set_cell( ip_row = 3 ip_column = 'A' ip_value = '香蕉' ).
      "创建新range
      lo_range = lo_worksheet->add_new_range( ).
      lo_range->name = 'fruit'.
      "ip_sheet_name设置range数据所在sheet,设置range范围
      lo_range->set_value(
       ip_sheet_name = 'data validation'
       ip_start_column = 'A'
       ip_start_row = '1'
       ip_stop_column = 'A'
       ip_stop_row = '3'
       ).

      "创建验证规则
      lo_data_validation = lo_worksheet->add_new_data_validation( ).
      "验证类型:下拉选择框
      lo_data_validation->type = zcl_excel_data_validation=>c_type_list.
      lo_data_validation->formula1 = 'fruit'.
      "规则所在单元格
      lo_data_validation->cell_row = 4.
      lo_data_validation->cell_column = 'A'.
      lo_worksheet->set_cell( ip_row = 4 ip_column = 'A' ip_value = 'Select a value' ).

      "创建验证规则
      lo_data_validation = lo_worksheet->add_new_data_validation( ).
      "验证类型:文本长度
      lo_data_validation->type = zcl_excel_data_validation=>c_type_textlength.
      "操作符:小于等于
      lo_data_validation->operator = zcl_excel_data_validation=>c_operator_lessthanorequal.
      "公式
      lo_data_validation->formula1 = 10.
      "规则所在单元格
      lo_data_validation->cell_row = 2.
      lo_data_validation->cell_column = 'B'.
      "规则区域
*      lo_data_validation->cell_row_to = 3.
*      lo_data_validation->cell_column = 'D'.
      lo_worksheet->set_cell( ip_row = 1 ip_column = 'B' ip_value = 'text长度小于10:' ).

      "创建验证规则
      lo_data_validation = lo_worksheet->add_new_data_validation( ).
      "验证类型:
      lo_data_validation->type = zcl_excel_data_validation=>c_type_whole.
      "操作符:between
      lo_data_validation->operator    = zcl_excel_data_validation=>c_operator_between.
      "范围
      lo_data_validation->formula1    = 1.
      lo_data_validation->formula2    = 10.
      "选择单元格,弹出提示信息
      lo_data_validation->prompttitle = 'Range'.
      lo_data_validation->prompt      = 'Enter a value between 1 and 10'.
      "错误弹窗,显示错误信息
      lo_data_validation->errortitle  = 'Error'.
      lo_data_validation->error       = 'You have entered a wrong value. Please use only numbers between 1 and 10.'.
      "规则生效单元格
      lo_data_validation->cell_row    = 2.
      lo_data_validation->cell_column = 'C'.
      lo_worksheet->set_cell( ip_row = 1 ip_column = 'C' ip_value = '数字在1-10:' ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

12.设置单元格合并

    使用zcl_excel_worksheet类对象set_merge方法,合并单元格操作。

示例:

"单元格合并
FORM set_merge.
  TRY .
      lo_worksheet->set_cell( ip_row = 1 ip_column = 'F' ip_value = '单元格合并' ).
      "设置单元格合并
      lo_worksheet->set_merge(
      ip_column_start = 'F'
      ip_column_end = 'H'
      ip_row = 1
      ip_row_to = 2 ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

13.行列选择

    使用zcl_excel_worksheet_columndime类对象保存列信息,zcl_excel_worksheet_rowdimensi 类对象保存行信息。

    通过zcl_excel_worksheet类对象的get_column_dimension方法获取列对象;

    通过zcl_excel_worksheet类对象的get_row_dimension方法行对象;

    调用行列对象方法设置行列参数。

示例:

"行列选择
FORM select_area.
  "列选择对象
  DATA:column_dimension TYPE REF TO zcl_excel_worksheet_columndime.
  "行选择对象
  DATA:row_dimension TYPE REF TO zcl_excel_worksheet_rowdimensi.

  TRY .
      "列选择
      column_dimension = lo_worksheet->get_column_dimension( ip_column = 'A' ).
      "设置列属性
      "设置列宽
      column_dimension->set_width( ip_width = 6 ).
      "设置自动列宽
      column_dimension->set_auto_size( ip_auto_size = abap_true ).
      "设置是否可见
      column_dimension->set_visible( ip_visible = abap_true ).
      "设置列样式
      "style类
      DATA:lo_style TYPE REF TO zcl_excel_style.
      "style的guid
      DATA:lv_style_guid TYPE zexcel_cell_style.
      "创建一个新style
      lo_style = lo_excel->add_new_style( ).
      "文本格式
      lo_style->number_format->format_code =  zcl_excel_style_number_format=>c_format_text.
      "获取style的编码uuid
      lv_style_guid = lo_style->get_guid( ).
      column_dimension->set_column_style_by_guid( ip_style_guid = lv_style_guid ).
      "设置outline level,出现列收缩展开栏
      column_dimension->set_outline_level( ip_outline_level = 0 ).

      "行选择
      row_dimension = lo_worksheet->get_row_dimension( ip_row = 1 ).
      "设置行高
      row_dimension->set_row_height( ip_row_height = 32 ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

14.获取worksheet的行列数

    通过zcl_excel_worksheet类对象的ge_hightest_row方法获取当前worksheet中行数;

    通过zcl_excel_worksheet类对象的ge_hightest_column方法获取当前worksheet中列数;

示例:

"获取excel的列数和行数
FORM get_rc.
  "行数
  DATA:lv_rows TYPE I.
  "列数
  DATA:lv_columns TYPE I.
  "列数对应字母
  DATA:col_alpha TYPE zexcel_cell_column_alpha.

  TRY .
      "获取行列数
      lv_rows = lo_worksheet->get_highest_row( ).
      lv_columns = lo_worksheet->get_highest_column( ).
      "将列数转换成对应列字母
      col_alpha = zcl_excel_common=>convert_column2alpha( ip_column = 2 ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

15.excel显示图片

    通过zcl_excel_drawing类对象实现excel中绘制图片。

示例:

"显示图片
FORM set_drawing.
  "绘制图片对象
  DATA:lo_drawing TYPE REF TO zcl_excel_drawing.
  TRY .
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'drawing' ).

      "显示本地路径图片
      DATA:t_solix TYPE solix_tab.
      DATA:lv_len TYPE I.
      DATA:lv_xstring TYPE xstring.

      CALL METHOD cl_gui_frontend_services=>gui_upload
        EXPORTING
          filename   = 'D:\图片\back2.jpg'
          filetype   = 'BIN'
        IMPORTING
          filelength = lv_len
        CHANGING
          data_tab   = t_solix.
      IF sy-subrc <> 0 .
        MESSAGE '读取失败!' TYPE 'E'.
      ENDIF.

      CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
        EXPORTING
          input_length = lv_len
        IMPORTING
          buffer       = lv_xstring
        TABLES
          binary_tab   = t_solix
        EXCEPTIONS
          failed       = 1
          OTHERS       = 2.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
      "创建zcl_excel_drawing对象
      lo_drawing = lo_excel->add_new_drawing( ).
      lo_drawing->set_position( ip_from_row = 3 ip_from_col = 'B' ).
      lo_drawing->set_media( ip_media = lv_xstring
                             ip_media_type = zcl_excel_drawing=>c_media_type_bmp
                             ip_width = 83
                             ip_height = 160 ).
      "worksheet添加图片
      lo_worksheet->add_drawing( lo_drawing ).

      "加载tcode:SMW0图片
      DATA:ls_key TYPE wwwdatatab.
      ls_key-relid = 'MI'.
      "SMW0,通过object id可以查找到图片
      ls_key-objid = 'SAPLOGO.GIF'.
      lo_drawing = lo_excel->add_new_drawing( ).
      lo_drawing->set_position( ip_from_row = 16
                                ip_from_col = 'B' ).
      lo_drawing->set_media_www( ip_key = ls_key
                                 ip_width = 166
                                 ip_height = 75 ).
      "worksheet添加图片
      lo_worksheet->add_drawing( lo_drawing ).
      "Mime repository (by default Question mark in standard Web Dynpro WDT_QUIZ
      "使用lo_worksheet->set_media_mime方法
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.

16.设置过滤器filter

    通过zcl_excel_autofilter类对象实现过滤器filter。

示例:

"设置filter
FORM set_filter.
  DATA:t_sflight TYPE TABLE OF sflight.
  SELECT * FROM sflight INTO TABLE t_sflight UP TO 10 ROWS.
  "将alv或者内表绑定到excel,converter类
  DATA:lo_converter TYPE REF TO zcl_excel_converter.
  "自动过滤类
  DATA:lo_autofilter TYPE REF TO zcl_excel_autofilter.
  "过滤器范围
  DATA:ls_area TYPE zexcel_s_autofilter_area.
  "列单元格值
  DATA:lv_cell_value TYPE zexcel_cell_value.

  TRY .
      "创建一个新的worksheet
      lo_worksheet = lo_excel->add_new_worksheet( ).
      lo_worksheet->set_title( ip_title = 'filter' ).

      CREATE OBJECT lo_converter.
      lo_converter->convert(
        EXPORTING
          it_table = t_sflight
          i_row_int = 1
          i_column_int = 1
          io_worksheet = lo_worksheet
        CHANGING
          co_excel = lo_excel
      ).
    "当前worksheet,创建新的filter对象
    lo_autofilter = lo_excel->add_new_autofilter( io_sheet = lo_worksheet ) .
    ls_area-row_start = 1.
    ls_area-col_start = 1.
    ls_area-row_end = lo_worksheet->get_highest_row( ).
    ls_area-col_end = lo_worksheet->get_highest_column( ).
    lo_autofilter->set_filter_area( ls_area ).
    "设置第三列filter value
*    lo_worksheet->get_cell( EXPORTING
*                             ip_column    = 'C'
*                             ip_row       = 2
*                          IMPORTING
*                             ep_value     = lv_cell_value ).
    "设置列筛选值
*    lo_autofilter->set_value( i_column = 3 i_value  = lv_cell_value ).
    CATCH ZCX_EXCEL.
  ENDTRY.
ENDFORM.
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
PHP导入导出EXCELl,CSV
PHP导入导出Excel,CSVHTML<formaction"{:U('Admin/Unit/importcsv')}"method"post"name"myform"id"myform"enctype"multipart/formdata"<input
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这