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;

示例：

1"excel文档类对象
2DATA:lo_excel TYPE REF TO zcl_excel.
3"excel worksheet类对象
4DATA:lo_worksheet TYPE REF TO zcl_excel_worksheet.
5"异常类
6DATA:lf_cxexcel TYPE REF TO ZCX_EXCEL.
7
8"创建excel
9FORM creat_excel.
10  "excel超链接
11  DATA:lo_hyperlink TYPE REF TO zcl_excel_hyperlink.
12  TRY.
13      "创建excel对象
14      CREATE OBJECT lo_excel.
15      "获得当前worksheet
16      lo_worksheet = lo_excel->get_active_worksheet( ).
17
18      "设置单元格
19      lo_worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = 'hello world!' ).
20      lo_worksheet->set_cell( ip_column = 'A' ip_row = 2 ip_value = sy-datum ).
21      lo_worksheet->set_cell( ip_column = 'A' ip_row = 3 ip_value = sy-uzeit ).
22
23      "创建超链接
24      "外部链接
25      lo_hyperlink = zcl_excel_hyperlink=>create_external_link( iv_url = 'https://www.baidu.com' ).
26      lo_worksheet->set_cell( ip_column = 'A' ip_row = 4 ip_value = '百度一下' ip_hyperlink = lo_hyperlink ).
27
28      "内部链接到其他sheet
29      lo_hyperlink = zcl_excel_hyperlink=>create_internal_link( iv_location = 'itab!A1' ).
30      lo_worksheet->set_cell( ip_column = 'A' ip_row = 5 ip_value = 'itab:A1' ip_hyperlink = lo_hyperlink ).
31
32    CATCH ZCX_EXCEL.
33  ENDTRY.
34ENDFORM.

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类型数据。

示例：

1"下载显示excel转换
2DATA:cl_writer TYPE REF TO zif_excel_writer.
3DATA:xdata TYPE xstring.
4DATA:t_rawdata TYPE solix_tab.
5DATA:bytecount TYPE i.
6
7"excel转换成xstring
8FORM translate_excel.
9  "实例化cl_writer对象
10  CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
11  xdata = cl_writer->write_file( lo_excel ).
12
13*  "方式1：convert to binary
14*  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
15*    EXPORTING
16*      buffer        = xdata
17*    IMPORTING
18*      output_length = bytecount
19*    TABLES
20*      binary_tab    = t_rawdata.
21
22  "方式2：This method is only available on AS ABAP > 6.40
23  "excel转换
24  t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xdata ).
25  "统计字节数
26  bytecount = xstrlen( xdata ).
27ENDFORM.

2.2GUI直接显示excel

示例：

1"在ABAP显示
2FORM display_excel.
3  DATA:error TYPE REF TO i_oi_error.
4  DATA:t_errors TYPE STANDARD TABLE OF REF TO i_oi_error WITH NON-UNIQUE DEFAULT KEY.
5  DATA:cl_control  TYPE REF TO i_oi_container_control. "OIContainerCtrl
6  DATA:cl_document TYPE REF TO i_oi_document_proxy.   "Office Dokument
7
8  c_oi_container_control_creator=>get_container_control(
9    IMPORTING
10      control = cl_control
11      error   = error
12  ).
13  APPEND error TO t_errors.
14
15  cl_control->init_control(
16     EXPORTING
17       inplace_enabled     = 'X'
18       no_flush            = 'X'
19       r3_application_name = 'Demo Document Container'
20       parent              = cl_gui_container=>screen0
21     IMPORTING
22       error               = error
23     EXCEPTIONS
24       OTHERS  = 2
25    ).
26  APPEND error TO t_errors.
27
28  cl_control->get_document_proxy(
29    EXPORTING
30      document_type  = 'Excel.Sheet'                " EXCEL
31      no_flush       = ' '
32    IMPORTING
33      document_proxy = cl_document
34      error  = error
35  ).
36  APPEND error TO t_errors.
37
38  cl_document->open_document_from_table(
39     EXPORTING
40       document_size    = bytecount
41       document_table   = t_rawdata
42       open_inplace     = 'X'
43   ).
44  WRITE: '.'.  " To create an output.  That way screen0 will exist
45ENDFORM.

2.3导出excel到本地

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

示例：

1"下载到本地
2FORM download_excel.
3  DATA:filepath TYPE String VALUE 'C:'.
4  DATA:filename TYPE String VALUE 'abap2xlsx1'.
5
6  "选择文件保存路径
7  cl_gui_frontend_services=>directory_browse(
8      EXPORTING
9        window_title         = 'Select path to download EXCEL-file'
10        initial_folder       = filepath
11      CHANGING
12        selected_folder      = filepath
13      EXCEPTIONS
14        cntl_error           = 1
15        error_no_gui         = 2
16        not_supported_by_gui = 3
17        OTHERS               = 4
18    ).
19
20  filename = filepath && '\' && filename && '.XLSX'.
21  "下载excel
22  cl_gui_frontend_services=>gui_download(
23    EXPORTING
24       bin_filesize = bytecount
25       filename     = filename
26       filetype     = 'BIN'
27    CHANGING
28       data_tab     = t_rawdata
29   ).
30ENDFORM.

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。

示例：

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

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类型文档。

示例：

1"读写xlsm类型文档
2FORM translate_xlsm.
3  DATA:lo_excel_writer TYPE REF TO zif_excel_writer.
4  DATA:lo_excel_reader TYPE REF TO zif_excel_reader.
5 
6  TRY .
7      CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_xlsm.
8      CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_xlsm.
9      lo_excel = lo_excel_reader->load_file( '文件路径' ).
10      xdata = lo_excel_writer->write_file( lo_excel ).
11
12      "后续操作和excel一致，文件后缀.xlsm
13    CATCH ZCX_EXCEL.
14  ENDTRY.
15ENDFORM.

2.6alv导出到excel

    使用zcl_excel_converter类导出alv到excel。

    1、创建zcl_excel_converter对象；

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

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

示例1：

1"使用zcl_excel_converter导出excel
2FORM export_excel_conv.
3  DATA:t_sflight TYPE TABLE OF sflight.
4  DATA:lo_salv TYPE REF TO  cl_salv_table.
5
6  SELECT * INTO TABLE t_sflight FROM sflight UP TO 10 ROWS.
7  TRY.
8      cl_salv_table=>factory(
9        EXPORTING
10          list_display = abap_false
11        IMPORTING
12          r_salv_table = lo_salv
13        CHANGING
14          t_table      = t_sflight[] ).
15    CATCH cx_salv_msg .
16  ENDTRY.
17
18  "调用显示alv
19*  lo_salv->display( ).
20  DATA: lo_converter TYPE REF TO zcl_excel_converter.
21  "创建zcl_excel_converter类
22  CREATE OBJECT lo_converter.
23  TRY .
24      lo_converter->convert(
25        EXPORTING
26          io_alv        = lo_salv
27          it_table      = t_sflight
28          i_row_int     = 2
29          i_column_int  = 2
30          ).
31    CATCH zcx_excel.
32  ENDTRY.
33
34  "调用write_file方法导出excel
35  lo_converter->write_file( i_path = 'D:/test.xlsx' ).
36ENDFORM.                    "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：

1"使用worksheet的bind_alv方法
2FORM export_excel_bind.
3  DATA:t_sflight TYPE TABLE OF sflight.
4  DATA:lo_salv TYPE REF TO  cl_salv_table.
5
6  SELECT * INTO TABLE t_sflight FROM sflight UP TO 10 ROWS.
7  TRY.
8      cl_salv_table=>factory(
9        EXPORTING
10          list_display = abap_false
11        IMPORTING
12          r_salv_table = lo_salv
13        CHANGING
14          t_table      = t_sflight[] ).
15    CATCH cx_salv_msg .
16  ENDTRY.
17
18  "调用显示alv
19*  lo_salv->display( ).
20
21  TRY .
22      "将alv绑定到worksheet，
23      "只是绑定,导出excel需要调用gui_download方法
24      lo_worksheet->bind_alv(
25          io_alv      = lo_salv
26          it_table    = t_sflight
27          i_top       = 2
28          i_left      = 1
29             ).
30    CATCH zcx_excel.
31  ENDTRY.
32
33  "下载excel操作
34  "zcl_excel_writer对象,实例化
35  CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
36  xdata = cl_writer->write_file( lo_excel ).
37  t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xdata ).
38
39  "统计字节数
40  bytecount = xstrlen( xdata ).
41  "下载excel
42  cl_gui_frontend_services=>gui_download(
43    EXPORTING
44       bin_filesize = bytecount
45       filename     = 'D:/test.xlsx'
46       filetype     = 'BIN'
47    CHANGING
48       data_tab     = t_rawdata
49    ).
50ENDFORM.

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

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

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

示例3：

1"通过worksheet的bind_alv_ole2方法，将alv导出
2FORM export_excel_bind1.
3  "屏幕alv对象
4  DATA:lo_alv TYPE REF TO cl_gui_alv_grid.
5  "excel header
6  DATA:gt_listheader TYPE slis_t_listheader.
7  DATA:wa_listheader LIKE LINE OF gt_listheader.
8  "excel保存路径
9  DATA:l_path TYPE string VALUE 'C:temp/test.xlsx'.
10
11  "获取屏幕alv对象
12  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
13    IMPORTING
14      e_grid = lo_alv.
15
16  wa_listheader-typ = 'H'.
17  wa_listheader-info = 'header line'.
18  APPEND wa_listheader TO gt_listheader.
19  wa_listheader-typ = 'S'.
20  wa_listheader-info = 'second line'.
21  APPEND wa_listheader TO gt_listheader.
22  wa_listheader-typ = 'A'.
23  wa_listheader-info = '地址信息'.
24  APPEND wa_listheader TO gt_listheader.
25
26  "调用lo_worksheet的bind_alv_ole2方法,直接调用下载
27  lo_worksheet->bind_alv_ole2(
28    EXPORTING
29*      I_DOCUMENT_URL          = SPACE " excel template
30*      I_XLS                   = 'X' " create in xls format?
31      i_save_path             = l_path
32      io_alv                  = lo_alv
33      it_listheader           = gt_listheader
34      i_top                   = 2
35      i_left                  = 1
36*      I_COLUMNS_HEADER        = 'X'
37*      I_COLUMNS_AUTOFIT       = 'X'
38*      I_FORMAT_COL_HEADER     =
39*      I_FORMAT_SUBTOTAL       =
40*      I_FORMAT_TOTAL          =
41    EXCEPTIONS
42      miss_guide              = 1
43      ex_transfer_kkblo_error = 2
44      fatal_error             = 3
45      inv_data_range          = 4
46      dim_mismatch_vkey       = 5
47      dim_mismatch_sema       = 6
48      error_in_sema           = 7
49      OTHERS                  = 8
50          ).
51  IF sy-subrc <> 0.
52    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
53               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
54  ENDIF.
55ENDFORM.

2.7excel上传

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

示例：

1"读取excel
2
3FORM read_excel.
4
5  "excel文档类对象
6
7  DATA:lo_excel TYPE REF TO zcl_excel.
8
9  "excel worksheet类对象
10
11  DATA:lo_worksheet TYPE REF TO zcl_excel_worksheet.
12
13  "异常类
14
15  DATA:lf_cxexcel TYPE REF TO ZCX_EXCEL.
16
17  "上传excel
18
19  DATA:cl_reader TYPE REF TO zif_excel_reader.
20
21  "excel导入内表
22
23  DATA:BEGIN OF t_excel OCCURS 0,
24
25      str1 TYPE string,
26
27      str2 TYPE string,
28
29      str3 TYPE string,
30
31      str4 TYPE string,
32
33      END OF t_excel.
34
35  "读取行列数
36
37  DATA:col_count TYPE I.
38
39  DATA:row_count TYPE I.
40
41  DATA:col TYPE I.
42
43  DATA:row TYPE I.
44
45  
46
47  TRY.
48
49      CREATE OBJECT lo_excel.
50
51      "cl_reader对象
52
53      CREATE OBJECT cl_reader TYPE zcl_excel_reader_2007.
54
55      lo_excel = cl_reader->load_file( i_filename = 'D:\test.xlsx' ).
56
57      lo_worksheet = lo_excel->get_active_worksheet( ).
58
59 
60
61      "获取行数，列数
62
63      row_count = lo_worksheet->get_highest_row( ).
64
65      col_count = lo_worksheet->get_highest_column( ).
66
67 
68
69      DO row_count TIMES.
70
71        row = sy-index.
72
73        CLEAR t_excel.
74
75        DO col_count TIMES.
76
77          col = sy-index.
78
79          CASE col.
80
81            WHEN 1.
82
83              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str1 ).
84
85            WHEN 2.
86
87              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str2 ).
88
89            WHEN 3.
90
91              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str3 ).
92
93            WHEN 4.
94
95              lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = t_excel-str4 ).
96
97          ENDCASE.
98
99        ENDDO.
100
101        APPEND t_excel.
102
103      ENDDO.
104
105 
106
107      LOOP AT t_excel.
108
109        WRITE:/ t_excel-str1,t_excel-str2,t_excel-str3,t_excel-str4.
110
111      ENDLOOP.
112
113    CATCH ZCX_EXCEL INTO lf_cxexcel.
114
115      "获取错误信息
116
117      DATA:result TYPE string.
118
119      CALL METHOD lf_cxexcel->IF_MESSAGE~GET_TEXT
120
121        RECEIVING
122
123          result = result.
124
125      MESSAGE result TYPE 'E'.
126
127  ENDTRY.
128
129ENDFORM.

3.设置workbook属性

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

示例：

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

4.设置worksheet属性

    1、获取zcl_excel_worksheet类对象；

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

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

示例：

1"设置worksheet
2FORM set_worksheet.
3  "sheet颜色
4  DATA:ls_tabcolor TYPE ZEXCEL_S_TABCOLOR.
5  "打印头和脚
6  DATA:ls_header  TYPE zexcel_s_worksheet_head_foot.
7  DATA:ls_footer  TYPE zexcel_s_worksheet_head_foot.
8
9  TRY .
10      "创建一个新的worksheet
11      lo_worksheet = lo_excel->add_new_worksheet( ).
12      lo_worksheet->set_title( ip_title = 'sheet2' ).
13      lo_worksheet->zif_excel_sheet_properties~selected = zif_excel_sheet_properties=>c_selected.
14      "设置sheet标签颜色
15      ls_tabcolor-rgb = zcl_excel_style_color=>c_blue.
16      lo_worksheet->set_tabcolor( iv_tabcolor = ls_tabcolor ).
17      "设置sheet隐藏
18*      lo_worksheet->zif_excel_sheet_properties~hidden = zif_excel_sheet_properties=>c_hidden.
19      "设置隐藏0
20      lo_worksheet->set_cell( ip_column = 'C' ip_row = 1 ip_value = 'C2隐藏0：' ).
21      lo_worksheet->set_cell( ip_column = 'C' ip_row = 2 ip_value = 0 ).
22      lo_worksheet->zif_excel_sheet_properties~show_zeros  = zif_excel_sheet_properties=>c_hidezero.
23
24      "sheet打印设置，printing settings
25      lo_worksheet->sheet_setup->set_page_margins( ip_header = '1' ip_footer = '1' ip_unit = 'cm' ).
26      lo_worksheet->sheet_setup->black_and_white   = 'X'.
27      lo_worksheet->sheet_setup->fit_to_page       = 'X'.  " you should turn this on to activate fit_to_height and fit_to_width
28      lo_worksheet->sheet_setup->fit_to_height     = 0.    " used only if ip_fit_to_page = 'X'
29      lo_worksheet->sheet_setup->fit_to_width      = 2.    " used only if ip_fit_to_page = 'X'
30      lo_worksheet->sheet_setup->orientation       = zcl_excel_sheet_setup=>c_orientation_landscape.
31      lo_worksheet->sheet_setup->page_order        = zcl_excel_sheet_setup=>c_ord_downthenover.
32      lo_worksheet->sheet_setup->paper_size        = zcl_excel_sheet_setup=>c_papersize_a4.
33      lo_worksheet->sheet_setup->scale             = 80.   " used only if ip_fit_to_page = SPACE
34
35      "设置打印头和脚
36      " Header and Footer
37      ls_header-right_value = 'print date &D'.
38      ls_header-right_font-size = 8.
39      ls_header-right_font-name = zcl_excel_style_font=>c_name_arial.
40      ls_footer-left_value = '&Z&F'. "Path / Filename
41      ls_footer-left_font = ls_header-right_font.
42      ls_footer-right_value = 'page &P of &N'. "page x of y
43      ls_footer-right_font = ls_header-right_font.
44      lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header
45                                                    ip_odd_footer  = ls_footer ).
46
47      "设置隐藏列
48      lo_worksheet->zif_excel_sheet_properties~hide_columns_from = 'M'.
49      "设置行收缩展开，lv_collapsed:true收缩，false展开
50      lo_worksheet->set_row_outline( iv_row_from = 10
51                               iv_row_to   = 16
52                               iv_collapsed = abap_true ).  " collapsed
53
54      "设置sheet保护
55      DATA:lo_style TYPE REF TO zcl_excel_style.
56      DATA:lv_style_guid TYPE zexcel_cell_style.
57      lo_worksheet->zif_excel_sheet_protection~protected  = zif_excel_sheet_protection=>c_protected.
58      "设置密码加密
59      lo_worksheet->zif_excel_sheet_protection~password   = zcl_excel_common=>encrypt_password( 'secret' ).
60      lo_worksheet->zif_excel_sheet_protection~sheet      = zif_excel_sheet_protection=>c_active.
61      lo_worksheet->zif_excel_sheet_protection~objects    = zif_excel_sheet_protection=>c_active.
62      lo_worksheet->zif_excel_sheet_protection~scenarios  = zif_excel_sheet_protection=>c_active.
63      lo_style = lo_excel->add_new_style( ).
64      "设置unlocked锁住样式
65      lo_style->protection->locked = zcl_excel_style_protection=>c_protection_unlocked.
66      lv_style_guid = lo_style->get_guid( ).
67      "设置可编辑
68      lo_worksheet->set_cell( ip_column = 'E' ip_row = 2 ip_value = 'unlocked' ip_style = lv_style_guid ).
69
70      "设置单元格尺寸
71      lo_worksheet->zif_excel_sheet_properties~zoomscale = 150.
72*      lo_worksheet->zif_excel_sheet_properties~zoomscale_normal = 150.
73*      lo_worksheet->zif_excel_sheet_properties~zoomscale_pagelayoutview = 150.
74*      lo_worksheet->zif_excel_sheet_properties~zoomscale_sheetlayoutview = 150.
75
76      "设置是否显示打印sheet网格线
77      lo_worksheet->set_show_gridlines(  i_show_gridlines  = abap_false ).
78      lo_worksheet->set_print_gridlines( i_print_gridlines = abap_false ).
79    CATCH ZCX_EXCEL.
80  ENDTRY.
81ENDFORM.

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

示例：

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

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；

示例：

1"设置单元格样式
2FORM set_style.
3  "style类
4  DATA:lo_style TYPE REF TO zcl_excel_style.
5  "style的guid
6  DATA:lv_style_guid TYPE zexcel_cell_style.TRY.
7      "创建一个新的worksheet
8      lo_worksheet = lo_excel->add_new_worksheet( ).
9      lo_worksheet->set_title( ip_title = 'sheet3' ).
10    CATCH ZCX_EXCEL.
11  ENDTRY.
12
13  "字体设置,类：zcl_excel_style_font
14  "创建一个新style
15  lo_style = lo_excel->add_new_style( ).
16  "加粗
17  lo_style->font->bold   = abap_true.
18  "斜体
19  lo_style->font->italic = abap_true.
20  "下划线
21  lo_style->font->underline = abap_true.
22  "下划线mode,单下划线双下划线
23  lo_style->font->underline_mode = zcl_excel_style_font=>c_underline_double.
24  "字体family,C_FAMILY_XXX
25  lo_style->font->family = zcl_excel_style_font=>c_family_roman.
26  "字体名:c_name_XXX,直接设置字体名:'YouYuan'
27*  lo_style->font->name   = zcl_excel_style_font=>c_name_arial.
28  lo_style->font->name   = 'YouYuan'.
29  "字体主题：C_SCHEME_XXX
30  lo_style->font->scheme = zcl_excel_style_font=>c_scheme_none.
31  "字体颜色,使用CSS颜色值：后面六位，前两位默认FF
32*  lo_style->font->color-rgb  = zcl_excel_style_color=>c_red.
33  lo_style->font->color-rgb = 'FF66FFCC'.
34  "自定义color,设置红绿蓝；create_new_argb方法和create_new_argb_int方法，返回zecel_style_color_argb类型
35*  zcl_excel_style_color=>create_new_argb( ip_red = 'FF' ip_green = '00' ip_blu = '00' ).
36  "颜色对应结构体ZEXCEL_S_STYLE_COLOR
37*  zexcel_s_style_color-rgb对应：ZEXCEL_STYLE_COLOR_ARGB类型
38*  zexcel_s_style_color-indexed 对应：ZEXCEL_STYLE_COLOR_INDEXED类型
39*  zexcel_s_style_color-theme 对应：ZEXCEL_STYLE_COLOR_THEME类型
40*  zexcel_s_style_color-tint 对应：ZEXCEL_STYLE_COLOR_TINT类型
41  "字体大小
42  lo_style->font->size = 11.
43  "获取style的编码uuid
44  lv_style_guid = lo_style->get_guid( ).
45  TRY .
46      "设置style
47      lo_worksheet->set_cell( ip_column = 'C' ip_row = 1 ip_style = lv_style_guid ip_value = 'style样式字体' ).
48    CATCH ZCX_EXCEL.
49  ENDTRY.
50
51  "fill设置，类：zcl_excel_style_fill
52  "创建一个新style
53  lo_style = lo_excel->add_new_style( ).
54  "填充类型
55  lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
56  "前景色
57  lo_style->fill->fgcolor-rgb = 'FF66FFCC'.
58  "背景色
59  lo_style->fill->bgcolor-rgb = 'FF000022'.
60  "获取style的编码uuid
61  lv_style_guid = lo_style->get_guid( ).
62  TRY .
63      "设置style
64      lo_worksheet->set_cell( ip_column = 'C' ip_row = 2 ip_style = lv_style_guid ip_value = 'style样式fill' ).
65    CATCH ZCX_EXCEL.
66  ENDTRY.
67
68  "设置边框,类：zcl_excel_style_borders  "border类
69  DATA:lo_border TYPE REF TO zcl_excel_style_border.
70  "创建一个新style
71  lo_style = lo_excel->add_new_style( ).
72  "创建border对象
73  CREATE OBJECT lo_border.
74  "设置border样式
75  lo_border->border_style = zcl_excel_style_border=>c_border_dashdot.
76  lo_border->border_color-rgb = 'FFCC0066'.
77  "设置所有border
78  lo_style->borders->allborders = lo_border.
79  "通过down,left,right,top设置单独边框
80*  lo_style->borders->down = lo_border.
81  "设置单元格中交叉线
82*  lo_style->borders->diagonal = lo_border.
83*  lo_style->borders->diagonal_mode = zcl_excel_style_borders=>c_diagonal_both.
84  "获取style的编码uuid
85  lv_style_guid = lo_style->get_guid( ).
86  TRY .
87      "设置style
88      lo_worksheet->set_cell( ip_column = 'C' ip_row = 3 ip_style = lv_style_guid ip_value = 'style样式border' ).
89    CATCH ZCX_EXCEL.
90  ENDTRY.
91
92 
93  "设置alignment,类：zcl_excel_style_alignment
94  "创建一个新style
95  lo_style = lo_excel->add_new_style( ).
96  "设置水平方向,c_horizontal_xxxx:居中center，靠左left，靠右right,默认general
97  lo_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_left.
98  "设置竖直方向,c_vertical_xxxx:顶部top，底部bottom,中间center,排齐justify
99  lo_style->alignment->vertical = zcl_excel_style_alignment=>c_vertical_top.
100  "设置文本旋转
101  lo_style->alignment->textrotation = 0.
102  "设置缩小适应
103  lo_style->alignment->shrinktofit = abap_true.
104  "设置缩进字符
105  lo_style->alignment->indent = 2.
106  "换行
107  lo_style->alignment->wraptext = abap_true.
108  "获取style的编码uuid
109  lv_style_guid = lo_style->get_guid( ).
110  TRY .
111      "设置style
112      lo_worksheet->set_cell( ip_column = 'C' ip_row = 4 ip_style = lv_style_guid ip_value = 'style样式alignment' )
113    CATCH ZCX_EXCEL.
114  ENDTRY.
115
116  "设置number_format格式,类：zcl_excel_style_number_format
117  "创建一个新style
118  lo_style = lo_excel->add_new_style( ).
119  "日期格式
120  lo_style->number_format->format_code =  zcl_excel_style_number_format=>c_format_date_ddmmyyyy.
121  "获取style的编码uuid
122  lv_style_guid = lo_style->get_guid( ).
123  TRY .
124      "设置style
125      lo_worksheet->set_cell( ip_column = 'C' ip_row = 5 ip_style = lv_style_guid ip_value = sy-datum ).
126    CATCH ZCX_EXCEL.
127  ENDTRY.
128
129  "创建一个新style
130  lo_style = lo_excel->add_new_style( ).
131  "文本格式
132  lo_style->number_format->format_code =  zcl_excel_style_number_format=>c_format_text.
133  "获取style的编码uuid
134  lv_style_guid = lo_style->get_guid( ).
135  TRY .
136      "设置style
137      lo_worksheet->set_cell( ip_column = 'C' ip_row = 6 ip_style = lv_style_guid ip_value = '01234' ).
138    CATCH ZCX_EXCEL.
139  ENDTRY.
140
141
142  "设置保护protection,类：zcl_excel_style_protection
143  "创建一个新style
144  lo_style = lo_excel->add_new_style( ).
145  "是否隐藏??
146*  lo_style->protection->hidden = zcl_excel_style_protection=>c_protection_hidden.
147  "是否锁住??
148  lo_style->protection->locked = '1'.
149  "获取style的编码uuid
150  lv_style_guid = lo_style->get_guid( ).
151  TRY .
152      "设置style
153      lo_worksheet->set_cell( ip_column = 'C' ip_row = 7 ip_style = lv_style_guid ip_value = 'style样式protection' ).
154    CATCH ZCX_EXCEL.
155  ENDTRY.
156ENDFORM.

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

示例：

1"设置单元格对应ABAP数据类型
2FORM set_typedesc.
3  TRY.
4      "创建一个新的worksheet
5      lo_worksheet = lo_excel->add_new_worksheet( ).
6      lo_worksheet->set_title( ip_title = 'sheet6' ).
7      "设置type desc
8      "cl_abap_typedescr=>typekind_string,
9      "cl_abap_typedescr=>typekind_packed,
10      "cl_abap_typedescr=>typekind_num,
11      "cl_abap_typedescr=>typekind_date,
12      lo_worksheet->set_cell(
13        ip_column = 'C'
14        ip_row = 1
15        ip_abap_type = cl_abap_typedescr=>typekind_string
16        ip_value = 'string' ).
17    CATCH ZCX_EXCEL.
18  ENDTRY.
19ENDFORM.

6.显示内表到excel

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

示例：

1"展示一个内表到excel
2FORM show_itab.
3  "内表定义
4  DATA:t_sflight LIKE TABLE OF sflight.
5  SELECT * INTO CORRESPONDING FIELDS OF TABLE t_sflight FROM sflight WHERE CARRID = 'AA'.
6
7  "excel内表设置
8  DATA: ls_table_settings TYPE zexcel_s_table_settings.
9  "excel内表显示栏位，样式
10  DATA: lt_field_catalog TYPE zexcel_t_fieldcatalog.
11  "指针
12  FIELD-SYMBOLS:<fs_catalog> TYPE zexcel_s_fieldcatalog.
13
14  TRY .
15      "创建一个新的worksheet
16      lo_worksheet = lo_excel->add_new_worksheet( ).
17      "设置sheet标题
18      lo_worksheet->set_title( ip_title = 'itab' ).
19      "设置内表样式
20      ls_table_settings-table_name = 'itab'.
21      ls_table_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
22      "设置内表起始列和行
23      ls_table_settings-top_left_column = 'B'.
24      ls_table_settings-top_left_row = 2.
25*      ls_table_settings-bottom_right_column = 'F'.
26*      ls_table_settings-bottom_right_row = 8.
27      "行条纹
28      ls_table_settings-show_row_stripes  = abap_false.
29      "列条纹
30      ls_table_settings-show_column_stripes  = abap_true.
31      "无过滤器
32      ls_table_settings-nofilters = abap_true.
33
34      "iconset
35      DATA: ls_iconset TYPE zexcel_conditional_iconset.
36      "条件格式
37      DATA: lo_style_conditional   TYPE REF TO zcl_excel_style_conditional.
38      "设置显示图标类型c_iconset_XXXX，不同图标
39      ls_iconset-iconset = zcl_excel_style_conditional=>c_iconset_5rating.
40      "iconset_数字，数字是几设置几个cfvo，显示不同图标区间
41      ls_iconset-cfvo1_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
42      ls_iconset-cfvo1_value = '0'.
43      ls_iconset-cfvo2_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
44      ls_iconset-cfvo2_value = '20'.
45      ls_iconset-cfvo3_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
46      ls_iconset-cfvo3_value = '30'.
47      ls_iconset-cfvo4_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
48      ls_iconset-cfvo4_value = '40'.
49      ls_iconset-cfvo5_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
50      ls_iconset-cfvo5_value = '50'.
51
52      "条件设置
53      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
54      "规则
55      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_iconset.
56      "优先级
57      lo_style_conditional->priority = 1.
58      lo_style_conditional->mode_iconset  = ls_iconset.
59      "是否同时显示单元格值
60      ls_iconset-showvalue = zcl_excel_style_conditional=>c_showvalue_true.
61
62      "获取内表字段
63      lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = t_sflight ).
64      "设置field catalog
65      "zexcel_s_fieldcatalog字段解析
66      "position：字段显示位置
67      "dynpfld:字段是否显示，true显示，false隐藏
68      "abap_type:字段对应ABAP类型
69      "cond_style:可以添加zcl_excel_style_conditional，图标显示
70      "totals_function:该列添加统计行，统计类型，
71      "TOTALS_FUNCTION_AVERAGE:平均值
72      "TOTALS_FUNCTION_COUNT:统计记录数
73      "TOTALS_FUNCTION_MAX:最大值
74      "TOTALS_FUNCTION_MIN:最小值
75      "TOTALS_FUNCTION_SUM:合计
76      LOOP AT lt_field_catalog ASSIGNING <fs_catalog>.
77        CASE <fs_catalog>-fieldname.
78          WHEN 'CARRID'.
79            <fs_catalog>-position = 1.
80            <fs_catalog>-dynpfld = abap_true.
81          WHEN 'CONNID'.
82            <fs_catalog>-position = 2.
83            <fs_catalog>-dynpfld = abap_true.
84            <fs_catalog>-abap_type = cl_abap_typedescr=>typekind_int.
85          WHEN 'FLDATE'.
86            <fs_catalog>-position = 3.
87            <fs_catalog>-dynpfld = abap_true.
88            <fs_catalog>-totals_function = zcl_excel_table=>totals_function_count.
89          WHEN 'PRICE'.
90            <fs_catalog>-position = 4.
91            <fs_catalog>-dynpfld = abap_true.
92            <fs_catalog>-cond_style = lo_style_conditional.
93            <fs_catalog>-totals_function = zcl_excel_table=>totals_function_sum.
94          WHEN OTHERS.
95            <fs_catalog>-dynpfld = abap_false.
96        ENDCASE.
97      ENDLOOP.
98
99      "设置内表和worksheet绑定
100      lo_worksheet->bind_table(
101        ip_table = t_sflight
102        is_table_settings = ls_table_settings
103        it_field_catalog  = lt_field_catalog
104        ).
105      "设置固定行
106      lo_worksheet->freeze_panes( ip_num_rows = 3 ).
107 
108      "设置活动的lo_worksheet,从1开始，
109      "使用set_active_sheet_index_by_name方法，通过sheet名设置active sheet
110      lo_excel->set_active_sheet_index( 1 ).
111    CATCH ZCX_EXCEL INTO lf_cxexcel.
112      "获取错误信息
113      DATA:result TYPE String.
114      CALL METHOD lf_cxexcel->IF_MESSAGE~GET_TEXT
115        RECEIVING
116          result = result.
117      MESSAGE result TYPE 'E'.
118  ENDTRY.
119ENDFORM.

7.condition图标显示

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

示例：

1"图标显示
2FORM conditional_format.
3  "条件格式
4  DATA: lo_style_conditional   TYPE REF TO zcl_excel_style_conditional.
5  TRY.
6      "创建一个新的worksheet
7      lo_worksheet = lo_excel->add_new_worksheet( ).
8      lo_worksheet->set_title( ip_title = 'sheet4' ).
9
10      "iconset
11      DATA: ls_iconset TYPE zexcel_conditional_iconset.
12      "设置显示图标类型c_iconset_XXXX，不同图标
13      ls_iconset-iconset = zcl_excel_style_conditional=>c_iconset_3trafficlights2.
14      "iconset_数字，数字是几设置几个cfvo，显示不同图标区间
15      ls_iconset-cfvo1_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
16      ls_iconset-cfvo1_value = '0'.
17      ls_iconset-cfvo2_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
18      ls_iconset-cfvo2_value = '33'.
19      ls_iconset-cfvo3_type  = zcl_excel_style_conditional=>c_cfvo_type_percent.
20      ls_iconset-cfvo3_value = '66'.
21      "是否同时显示单元格值
22      ls_iconset-showvalue = zcl_excel_style_conditional=>c_showvalue_true.
23
24      "条件设置
25      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
26      "规则
27      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_iconset.
28      "优先级
29      lo_style_conditional->priority = 1.
30      lo_style_conditional->mode_iconset  = ls_iconset.
31      "设置范围range
32      lo_style_conditional->set_range( ip_start_column  = 'C'
33                                       ip_start_row     = 4
34                                       ip_stop_column   = 'C'
35                                       ip_stop_row      = 8 ).
36      lo_worksheet->set_cell( ip_row = 4 ip_column = 'C' ip_value = 100 ).
37      lo_worksheet->set_cell( ip_row = 5 ip_column = 'C' ip_value = 1000 ).
38      lo_worksheet->set_cell( ip_row = 6 ip_column = 'C' ip_value = 150 ).
39      lo_worksheet->set_cell( ip_row = 7 ip_column = 'C' ip_value = 10 ).
40      lo_worksheet->set_cell( ip_row = 8 ip_column = 'C' ip_value = 500 ).
41
42      "databar
43      DATA:ls_databar TYPE zexcel_conditional_databar.
44      ls_databar-cfvo1_type = zcl_excel_style_conditional=>c_cfvo_type_min.
45      ls_databar-cfvo1_value = '0'.
46      ls_databar-cfvo2_type = zcl_excel_style_conditional=>c_cfvo_type_max.
47      ls_databar-cfvo2_value = '0'.
48      ls_databar-colorrgb = 'FF638EC6'.
49
50      "条件设置
51      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
52      "规则
53      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_databar.
54      "优先级
55      lo_style_conditional->priority = 1.
56      lo_style_conditional->mode_databar = ls_databar.
57      "设置范围range
58      lo_style_conditional->set_range( ip_start_column  = 'D'
59                                       ip_start_row     = 4
60                                       ip_stop_column   = 'D'
61                                       ip_stop_row      = 8 ).
62      lo_worksheet->set_cell( ip_row = 4 ip_column = 'D' ip_value = 100 ).
63      lo_worksheet->set_cell( ip_row = 5 ip_column = 'D' ip_value = 200 ).
64      lo_worksheet->set_cell( ip_row = 6 ip_column = 'D' ip_value = 300 ).
65      lo_worksheet->set_cell( ip_row = 7 ip_column = 'D' ip_value = 400 ).
66      lo_worksheet->set_cell( ip_row = 8 ip_column = 'D' ip_value = 500 ).
67
68 
69      "colorscale
70      DATA:ls_colorscale TYPE zexcel_conditional_colorscale.
71      ls_colorscale-cfvo1_type = zcl_excel_style_conditional=>c_cfvo_type_min.
72      ls_colorscale-cfvo1_value = 0.
73      ls_colorscale-cfvo2_type = zcl_excel_style_conditional=>c_cfvo_type_percentile.
74      ls_colorscale-cfvo2_value = '50'.
75      ls_colorscale-colorrgb1 = 'FFF8696B'.
76      ls_colorscale-colorrgb2 = 'FF63BE7B'.
77
78      "条件设置
79      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
80      "规则
81      lo_style_conditional->rule  = zcl_excel_style_conditional=>c_rule_colorscale.
82      "优先级
83      lo_style_conditional->priority = 1.
84      lo_style_conditional->mode_colorscale = ls_colorscale.
85      "设置范围range
86      lo_style_conditional->set_range( ip_start_column  = 'E'
87                                       ip_start_row     = 4
88                                       ip_stop_column   = 'E'
89                                       ip_stop_row      = 8 ).
90      lo_worksheet->set_cell( ip_row = 4 ip_column = 'E' ip_value = 10 ).
91      lo_worksheet->set_cell( ip_row = 5 ip_column = 'E' ip_value = 20 ).
92      lo_worksheet->set_cell( ip_row = 6 ip_column = 'E' ip_value = 30 ).
93      lo_worksheet->set_cell( ip_row = 7 ip_column = 'E' ip_value = 40 ).
94      lo_worksheet->set_cell( ip_row = 8 ip_column = 'E' ip_value = 50 ).
95
96 
97      "cellis
98      "根据条件,设置单元格样式
99      DATA:lo_style TYPE REF TO zcl_excel_style.
100      DATA:lv_style_guid TYPE zexcel_cell_style.
101      DATA:ls_cellis TYPE zexcel_conditional_cellis.
102      lo_style = lo_excel->add_new_style( ).
103      lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
104      lo_style->fill->bgcolor-rgb = 'FF00CCFF'.
105      lv_style_guid = lo_style->get_guid( ).
106　　　　"条件设置
107      lo_style_conditional = lo_worksheet->add_new_conditional_style( ).
108      lo_style_conditional->rule = zcl_excel_style_conditional=>c_rule_cellis.
109      ls_cellis-formula     = '"hello"'.
110      ls_cellis-operator    = zcl_excel_style_conditional=>c_operator_equal.
111      ls_cellis-cell_style  = lv_style_guid.
112      lo_style_conditional->mode_cellis = ls_cellis.
113      lo_style_conditional->priority  = 1.
114      lo_style_conditional->set_range( ip_start_column  = 'G'
115                                   ip_start_row     = 2
116                                   ip_stop_column   = 'G'
117                                   ip_stop_row      = 2 ).
118      lo_worksheet->set_cell( ip_row = 1 ip_column = 'G' ip_value = 'G2输入hello显示样式:' ).
119    CATCH ZCX_EXCEL.
120  ENDTRY.
121ENDFORM.

8.设置单元格公式

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

示例：

1"formula，公式
2FORM set_formula.
3  TRY .
4      lo_excel->set_active_sheet_index_by_name( 'sheet4' ).
5      "获取worksheet，通过sheet名
6      lo_worksheet = lo_excel->get_worksheet_by_name( 'sheet4' ).
7      lo_worksheet->set_cell( ip_row = 9 ip_column = 'C' ip_value = '求和：' ).
8      lo_worksheet->set_cell( ip_row = 10 ip_column = 'C' ip_formula = 'SUM(C4:C5)' ).
9    CATCH ZCX_EXCEL.
10  ENDTRY.
11ENDFORM.

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

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

示例：

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

9.定义range区域

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

示例：

1"range获取
2FORM set_range.
3  "range
4  DATA:lo_range TYPE REF TO zcl_excel_range.
5  "sheet名
6  DATA: lv_title TYPE zexcel_sheet_title VALUE 'sheet4'.
7  lo_range = lo_excel->add_new_range( ).
8  lo_range->name = 'range'.
9
10  "A4A5区域
11  lo_range->set_value( ip_sheet_name    = lv_title
12                       ip_start_column  = 'A'
13                       ip_start_row     = 4
14                       ip_stop_column   = 'A'
15                       ip_stop_row      = 5 ).
16ENDFORM.

10.通用zcl_excel_common类

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

示例：

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

11.设置单元格验证

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

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

示例：

1"单元格验证
2FORM set_validation.
3  "range
4  DATA:lo_range TYPE REF TO zcl_excel_range.
5  "data validation验证规则
6  DATA:lo_data_validation TYPE REF TO zcl_excel_data_validation.
7
8  TRY.
9      "创建一个新的worksheet
10      lo_worksheet = lo_excel->add_new_worksheet( ).
11      lo_worksheet->set_title( ip_title = 'data validation' ).
12      "设置下拉选择值
13      lo_worksheet->set_cell( ip_row = 1 ip_column = 'A' ip_value = '苹果' ).
14      lo_worksheet->set_cell( ip_row = 2 ip_column = 'A' ip_value = '梨子' ).
15      lo_worksheet->set_cell( ip_row = 3 ip_column = 'A' ip_value = '香蕉' ).
16      "创建新range
17      lo_range = lo_worksheet->add_new_range( ).
18      lo_range->name = 'fruit'.
19      "ip_sheet_name设置range数据所在sheet,设置range范围
20      lo_range->set_value(
21       ip_sheet_name = 'data validation'
22       ip_start_column = 'A'
23       ip_start_row = '1'
24       ip_stop_column = 'A'
25       ip_stop_row = '3'
26       ).
27
28      "创建验证规则
29      lo_data_validation = lo_worksheet->add_new_data_validation( ).
30      "验证类型:下拉选择框
31      lo_data_validation->type = zcl_excel_data_validation=>c_type_list.
32      lo_data_validation->formula1 = 'fruit'.
33      "规则所在单元格
34      lo_data_validation->cell_row = 4.
35      lo_data_validation->cell_column = 'A'.
36      lo_worksheet->set_cell( ip_row = 4 ip_column = 'A' ip_value = 'Select a value' ).
37
38      "创建验证规则
39      lo_data_validation = lo_worksheet->add_new_data_validation( ).
40      "验证类型：文本长度
41      lo_data_validation->type = zcl_excel_data_validation=>c_type_textlength.
42      "操作符：小于等于
43      lo_data_validation->operator = zcl_excel_data_validation=>c_operator_lessthanorequal.
44      "公式
45      lo_data_validation->formula1 = 10.
46      "规则所在单元格
47      lo_data_validation->cell_row = 2.
48      lo_data_validation->cell_column = 'B'.
49      "规则区域
50*      lo_data_validation->cell_row_to = 3.
51*      lo_data_validation->cell_column = 'D'.
52      lo_worksheet->set_cell( ip_row = 1 ip_column = 'B' ip_value = 'text长度小于10：' ).
53
54      "创建验证规则
55      lo_data_validation = lo_worksheet->add_new_data_validation( ).
56      "验证类型：
57      lo_data_validation->type = zcl_excel_data_validation=>c_type_whole.
58      "操作符：between
59      lo_data_validation->operator    = zcl_excel_data_validation=>c_operator_between.
60      "范围
61      lo_data_validation->formula1    = 1.
62      lo_data_validation->formula2    = 10.
63      "选择单元格，弹出提示信息
64      lo_data_validation->prompttitle = 'Range'.
65      lo_data_validation->prompt      = 'Enter a value between 1 and 10'.
66      "错误弹窗，显示错误信息
67      lo_data_validation->errortitle  = 'Error'.
68      lo_data_validation->error       = 'You have entered a wrong value. Please use only numbers between 1 and 10.'.
69      "规则生效单元格
70      lo_data_validation->cell_row    = 2.
71      lo_data_validation->cell_column = 'C'.
72      lo_worksheet->set_cell( ip_row = 1 ip_column = 'C' ip_value = '数字在1-10：' ).
73    CATCH ZCX_EXCEL.
74  ENDTRY.
75ENDFORM.