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

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.

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.

 

 
上一篇:LQB2017A06 分巧克力


下一篇:Java - hello/hi的简单的网络聊天程序