ABAP2XLSX常用功能汇总

此功能需将abap2xlsx项目导入系统方可参考

使用此项目导出数据到excel主要使用的类有三个，分别是excel构建zcl_excel、sheet构建zcl_excel_worksheet、输出数据zif_excel_writer。

1、zcl_excel

*主要三个操作
*1.获取初始化活动的sheet页
zcl_excel->get_active_worksheet( ).
*2.创建新的sheet页
zcl_excel->add_new_worksheet( ).
*3.定义格式
zcl_excel->add_new_style( ).
****** 如果某些格式你不知道对应的值是什么，可以在excel里设置好格式后将excel转为xml，这样看xml就能知道了
**  加粗 lc_excel_style->font->bold  值:abap_true/abap_false
**  字号 lc_excel_style->font->size  数字
**  字体 lc_excel_style->font->name  值:与excel字体列表一致
**  字体颜色 lc_excel_style->font->color-rgb 值: zcl_excel_style_color=>c_*
**  数字格式 lc_excel_style->number_format->format_code 值: zcl_excel_style_number_format=>c_format_number*
**  填充方式 lc_excel_style->fill->filltype  值:zcl_excel_style_fill=>c_fill_*
**  背景色   lc_excel_style->fill->fgcolor-rgb 值：zcl_excel_style_color=>c_*
**  水平对齐 lc_excel_style->alignment->horizontal 值:zcl_excel_style_alignment=>c_horizontal_*
**  垂直对齐 lc_excel_style->alignment->vertical 值:zcl_excel_style_alignment=>c_vertical_*
**  边框 lc_excel_style->borders->allborders 值:zcl_excel_style_border
**  边框颜色 lc_border_dark->border_color-rgb 值: zcl_excel_style_color=>c_*
**  边框样式 lc_border_dark->border_style 值:zcl_excel_style_border=>c_border_*
**  超链接zcl_excel_hyperlink=>create_internal_link( iv_location = im_location ) 值:可以为网址也可以为不同sheet页等.
***举例
	lc_excel_style = zcl_excel->add_new_style( ).
	lc_excel_style->font->bold   = abap_true. "加粗
    lc_excel_style->font->size = 14."字号14
    lc_excel_style->number_format->format_code = zcl_excel_style_number_format=>c_format_number_comma_sep1."数字格式
	CREATE OBJECT lc_border_dark.
    lc_border_dark->border_color-rgb = zcl_excel_style_color=>c_black.
    lc_border_dark->border_style = zcl_excel_style_border=>c_border_thin.
    lc_excel_style->borders->allborders = lc_border_dark."设置边框线
    lc_excel_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_center."水平居中
    lc_excel_style->alignment->vertical = zcl_excel_style_alignment=>c_vertical_center."垂直居中
    lc_excel_style->fill->filltype     = zcl_excel_style_fill=>c_fill_solid.
    lc_excel_style->fill->fgcolor-rgb = zcl_excel_style_color=>c_gray."背景色为灰色
    lc_excel_style->font->color-rgb = zcl_excel_style_color=>c_red."字体颜色为红
   zcl_excel会对此新增的样式分配一个guid(lc_excel_style->get_guid( ))，后续设置样式时也是传此guid 
******

2、zcl_excel_worksheet

*对于导出数据的操作主要是在此类种完成
*1.设置sheet页名称
zcl_excel_worksheet->set_title( ip_title = '资产负债' ).
*2.填充单元格属性
zcl_excel_worksheet->set_cell( ip_column = 'A' 
							   ip_row = ch_rows 
							   ip_value = |单元格内容|
							   ip_hyperlink = zcl_excel_hyperlink"超链接
							   ip_style = zexcel_cell_style->get_guid() "格式
							   ip_formula = formula "excel公式  )
*3.设置单元格公式
zcl_excel_worksheet->set_cell_formula( EXPORTING ip_column = col
                                                 ip_row = lv_row
                                                 ip_formula = lv_formula ).
*3.1.公式处理小技巧
**以下输出结果 为IF(ABS(C2)<0.01,"正确","错误")
cl_excel_common=>shift_formula( iv_reference_formula = 'IF(ABS(B1)<0.01,"正确","错误")'
                                iv_shift_cols        = 1 "横向偏移量
                                iv_shift_rows        = 1 "纵向偏移量).
                                
*4.设置单元格格式
zcl_excel_worksheet->set_cell_style( EXPORTING ip_column = col
                                        	   ip_row = row
                                        	   ip_style = zexcel_cell_style ).
*5.设置列宽
zcl_excel_worksheet->get_column( im_column )->set_width( im_length ).
*6.设置行高
zcl_excel_worksheet->get_row( im_row )->set_row_height( im_height ).
*7.合并单元格
zcl_excel_worksheet->set_merge( ip_row = lv_row ip_column_start = 'B' ip_column_end = 'K' ip_row_to = lv_row ip_style = get_style( 'TITLE-CENTER' ) ).
*8.针对区域设置格式/公式deng
zcl_excel_worksheet->set_area(...).
*9.单元格保护
zcl_excel_worksheet->zif_excel_sheet_protection~protected  = zif_excel_sheet_protection=>c_protected.
zcl_excel_worksheet->zif_excel_sheet_protection~password   = zcl_excel_common=>encrypt_password( 'coolye' ).
zcl_excel_worksheet->zif_excel_sheet_protection~sheet      = zif_excel_sheet_protection=>c_active.
zcl_excel_worksheet->zif_excel_sheet_protection~objects    = zif_excel_sheet_protection=>c_active.
zcl_excel_worksheet->zif_excel_sheet_protection~scenarios  = zif_excel_sheet_protection=>c_active.
*10.去掉网格线
zcl_excel_worksheet->set_show_gridlines(  i_show_gridlines  = abap_false ).
zcl_excel_worksheet->set_print_gridlines( i_print_gridlines = abap_false ).

zif_excel_writer

DATA:xdata            TYPE xstring,
     t_rawdata        TYPE solix_tab,
     bytecount        TYPE i.
*下载至本地     
xdata = zif_excel_writer->write_file( zcl_excel ) .
bytecount = xstrlen( xdata ).
t_rawdata = cl_bcs_convert=>xstring_to_solix( xdata ).
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = me->bytecount
                                                  filename     = lv_filename_download
                                                  filetype     = 'BIN'
                                        CHANGING  data_tab     = t_rawdata ).

注:颜色传值为FF+16进制RGB编码