ABAP OLE开发模板(含进度条)
FIELD-SYMBOLS:<f1>.
"""" 如下参数定义为操作EXCEL所用
INCLUDE: ole2incl,
zfir090_excel.
DATA: i_excel_control TYPE REF TO excel_control,
i_excel_destination TYPE REF TO excel_destination.
DATA: i_rows_table TYPE rows_tab.
DATA: BEGIN OF excel_column OCCURS 0,
s_index LIKE sy-tabix,
s_id TYPE c,
END OF excel_column.
*&---------------------------------------------------------------------*
*& Form OLE_FOM
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM ole_fom .
DATA: g_msg TYPE string.
DATA: tl TYPE string.
DATA: objid TYPE string.
DATA: dst TYPE string.
DATA: stn TYPE string.
REFRESH excel_column.
**********************************************************************************
**********************************************************************************
******************* 做一个EXCEL的列号控制,以下内容固定.
excel_column-s_index = 1. excel_column-s_id = 'A'. APPEND excel_column.
excel_column-s_index = 2. excel_column-s_id = 'B'. APPEND excel_column.
excel_column-s_index = 3. excel_column-s_id = 'C'. APPEND excel_column.
excel_column-s_index = 4. excel_column-s_id = 'D'. APPEND excel_column.
excel_column-s_index = 5. excel_column-s_id = 'E'. APPEND excel_column.
excel_column-s_index = 6. excel_column-s_id = 'F'. APPEND excel_column.
excel_column-s_index = 7. excel_column-s_id = 'G'. APPEND excel_column.
excel_column-s_index = 8. excel_column-s_id = 'H'. APPEND excel_column.
excel_column-s_index = 9. excel_column-s_id = 'I'. APPEND excel_column.
excel_column-s_index = 10. excel_column-s_id = 'J'. APPEND excel_column.
excel_column-s_index = 11. excel_column-s_id = 'K'. APPEND excel_column.
excel_column-s_index = 12. excel_column-s_id = 'L'. APPEND excel_column.
excel_column-s_index = 13. excel_column-s_id = 'M'. APPEND excel_column.
excel_column-s_index = 14. excel_column-s_id = 'N'. APPEND excel_column.
excel_column-s_index = 15. excel_column-s_id = 'O'. APPEND excel_column.
excel_column-s_index = 16. excel_column-s_id = 'P'. APPEND excel_column.
excel_column-s_index = 17. excel_column-s_id = 'Q'. APPEND excel_column.
excel_column-s_index = 18. excel_column-s_id = 'R'. APPEND excel_column.
excel_column-s_index = 19. excel_column-s_id = 'S'. APPEND excel_column.
excel_column-s_index = 20. excel_column-s_id = 'T'. APPEND excel_column.
excel_column-s_index = 21. excel_column-s_id = 'U'. APPEND excel_column.
excel_column-s_index = 22. excel_column-s_id = 'V'. APPEND excel_column.
excel_column-s_index = 23. excel_column-s_id = 'W'. APPEND excel_column.
excel_column-s_index = 24. excel_column-s_id = 'X'. APPEND excel_column.
excel_column-s_index = 25. excel_column-s_id = 'Y'. APPEND excel_column.
excel_column-s_index = 26. excel_column-s_id = 'Z'. APPEND excel_column.
***********************************************************************************
DATA: column_index TYPE sy-tabix.
DATA row_index(5) .
DATA: column_index1 TYPE sy-tabix.
DATA zdouble TYPE n. "双线
DATA zbold. "粗体
DATA value(40).
DATA : yy(3).
FIELD-SYMBOLS <fs_f1>.
DATA field(30).
DATA : t1(20) TYPE c .
DATA : t2(20) TYPE c .
DATA : t3(20) TYPE c .
DATA : t4(20) TYPE c .
DATA : t5(20) TYPE c .
DATA : t6(20) TYPE c .
DATA : t7(20) TYPE c .
DATA : t8(20) TYPE c .
"创建对象
CREATE OBJECT i_excel_control.
objid = 'ZFIR090'.
tl = '分析表'.
dst = 'D:\'.
stn = '分析表'.
"下载相关的Excel模板
CALL METHOD i_excel_control->get_excel_templet
EXPORTING
i_objid = objid
i_filename = tl
is_back_run = ''
CHANGING
c_destination = dst
EXCEPTIONS
templet_is_not_fount = 1
can_not_save_file = 2
templet_download_failure = 3
OTHERS = 4.
IF sy-subrc NE 0.
IF sy-subrc EQ 1.
MESSAGE '没有找到相应的模板!' TYPE 'I'.
STOP.
ELSEIF sy-subrc EQ 2.
STOP.
ELSEIF sy-subrc EQ 3.
MESSAGE '模板下载失败!' TYPE 'I'.
STOP.
ELSE.
MESSAGE '取得模板错误!' TYPE 'I'.
STOP.
ENDIF.
ENDIF.
* "显示导出信息
* CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
* EXPORTING
* text = '正在导出Excel,导出时间可能比较长,请耐心等待...'.
"创建Excel对象,并打开
CREATE OBJECT i_excel_destination
EXPORTING
i_destination = dst
i_sheetname = stn
is_visible = ''
EXCEPTIONS
error_create_excel_object = 1
error_open_destination_object = 2
OTHERS = 3.
IF sy-subrc NE 0.
IF sy-subrc EQ 1.
MESSAGE '创建Excel对象失败!' TYPE 'I'.
STOP.
ELSEIF sy-subrc EQ 2.
CONCATENATE '打开文件' dst '失败!' INTO g_msg.
MESSAGE g_msg TYPE 'I'.
STOP.
ELSE.
MESSAGE '打开目标文件失败!' TYPE 'I'.
STOP.
ENDIF.
ENDIF.
"""开始操作EXCEL
REFRESH i_rows_table.
CLEAR :cells,rows.
""" 开始写入抬头标题
PERFORM frm_filling_cells USING '3' 'B' out_01 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '4' 'B' out_02 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '5' 'B' out_03 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '6' 'B' out_04 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '8' 'B' out_05 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '8' 'C' out_06 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '8' 'D' out_07 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '8' 'E' out_08 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '8' 'F' out_09 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '8' 'G' out_10 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '9' 'B' out_11 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '9' 'C' out_12 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '9' 'D' out_13 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '9' 'E' out_14 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '9' 'F' out_15 ' ' '' '' '' '' '' .
PERFORM frm_filling_cells USING '9' 'G' out_16 ' ' '' '' '' '' '' .
""" 开始写入表内容
row_index = '12' .
LOOP AT gt_data.
IF gt_data-msl < 0 .
CLEAR t1 .
t1 = gt_data-msl .
CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
CHANGING
value = t1.
ENDIF.
IF gt_data-netsr < 0 .
CLEAR t2 .
t2 = gt_data-netsr .
CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
CHANGING
value = t2.
ENDIF.
IF gt_data-hsl < 0 .
CLEAR t3 .
t3 = gt_data-hsl .
CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
CHANGING
value = t3.
ENDIF.
"""""""""""""" 行号 列号 内容 粗体 上边框 下边框 左边框 右边框 双线
SHIFT row_index LEFT DELETING LEADING ''.
PERFORM frm_filling_cells USING row_index 'A' gt_data-kunnr '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'B' gt_data-name1 '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'C' gt_data-matnr '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'D' gt_data-zuonr_f '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'E' gt_data-zuonr_b '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'F' gt_data-wadat_ist '' '' '' '' '' '' .
IF gt_data-msl < 0.
PERFORM frm_filling_cells USING row_index 'G' t1 '' '' '' '' '' '' .
ELSE.
PERFORM frm_filling_cells USING row_index 'G' gt_data-msl '' '' '' '' '' '' .
ENDIF.
PERFORM frm_filling_cells USING row_index 'H' gt_data-runit '' '' '' '' '' '' .
IF gt_data-netsr < 0.
PERFORM frm_filling_cells USING row_index 'I' t2 '' '' '' '' '' '' .
ELSE .
PERFORM frm_filling_cells USING row_index 'I' gt_data-netsr '' '' '' '' '' '' .
ENDIF.
IF gt_data-hsl < 0 .
PERFORM frm_filling_cells USING row_index 'J' t3 '' '' '' '' '' '' .
ELSE .
PERFORM frm_filling_cells USING row_index 'J' gt_data-hsl '' '' '' '' '' '' .
ENDIF.
row_index = row_index + 1 .
CLEAR gt_data .
ENDLOOP.
SHIFT row_index LEFT DELETING LEADING ''.
PERFORM frm_filling_cells USING row_index 'A' '合计' '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'G' out_17 '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'I' out_05 '' '' '' '' '' '' .
PERFORM frm_filling_cells USING row_index 'J' out_11 '' '' '' '' '' '' .
"加载数据
CALL METHOD i_excel_destination->load_data
EXPORTING
rows_tab = i_rows_table.
"处理完数据后保存一下
CALL METHOD i_excel_destination->set_cells_autofit. "自动列宽
CALL METHOD i_excel_destination->save.
* IF s_excel NE 'X'.
CALL METHOD i_excel_destination->quit.
* ENDIF.
CALL METHOD i_excel_destination->recycle.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_FILLING_CELLS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->ROW_NUM text
* -->COLUMN_NAME text
* -->VALUE text
* -->IS_BOLD text
* -->TOP text
* -->BOTTOM text
* -->LEFT text
* -->RIGHT text
* -->BORDER_STYLE text
*----------------------------------------------------------------------*
FORM frm_filling_cells USING row_num "行号
column_name "列号
value "内容
is_bold "粗体
top "上边框
bottom "下边框
left "左边框
right "右边框
border_style . "双线
CLEAR cells. CLEAR rows.
rows-row_num = row_num .
cells-column_name = column_name.
cells-value = value.
cells-is_bold = is_bold.
cells-show_border_top = top.
cells-show_border_bottom = bottom.
cells-show_border_left = left.
cells-show_border_right = right .
cells-border_style = border_style.
APPEND cells TO rows-cells_tab.
APPEND rows TO i_rows_table.
ENDFORM . "FRM_FILLING_CELLS
ZFIR090_EXCEL-----------------------------------------------------------------
*&---------------------------------------------------------------------*
*& Include ZFIR090_EXCEL
*& 以下为Excel封装的对象,及相关的操作方法
*&---------------------------------------------------------------------*
"Excel Border相关属性常量
CONSTANTS: xledgetop TYPE string VALUE '8',
xledgeright TYPE string VALUE '10',
xledgebottom TYPE string VALUE '9',
xledgeleft TYPE string VALUE '7'.
"Border_Style常量,线条的样式,是单线还是双线
CONSTANTS: xl_double(1) TYPE n VALUE '9', "双线
xl_continuous(1) TYPE n VALUE '1'. "单线
"线条的样式
CONSTANTS: xl_medium TYPE string VALUE '-4138', "中等细线条
xl_thin TYPE string VALUE '2', "细线条
xl_thick TYPE string VALUE '4', "粗的
xl_hairline TYPE string VALUE '1'. "极线的线条
"单元格值样式
CONSTANTS: negative_include TYPE n VALUE '1'.
"单元格结构类型,供写入数据用
DATA: BEGIN OF cells,
column_name(3) TYPE c,
value TYPE string,
format(1) TYPE n,
is_bold(1) TYPE c,
font TYPE string,
fontsize(2) TYPE c,
color TYPE string,
show_border_top(1) TYPE c,
show_border_right(1) TYPE c,
show_border_bottom(1) TYPE c,
show_border_left(1) TYPE c,
is_border_weight(1) TYPE c,
border_style(1) TYPE n,
END OF cells.
"定义单元格表类型,供行数据类型使用
TYPES cells_tab LIKE STANDARD TABLE OF cells.
"Excel行数据类型,供写入数据用
DATA: BEGIN OF rows,
row_num(5) TYPE c,
is_add(1) TYPE c,
cells_tab TYPE cells_tab,
END OF rows.
"定义一个行表类型,供接口里面使用
TYPES rows_tab LIKE STANDARD TABLE OF rows.
*----------------------------------------------------------------------*
* CLASS excel_control DEFINITION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS excel_control DEFINITION.
PUBLIC SECTION.
METHODS: get_excel_templet IMPORTING i_objid TYPE string
i_filename TYPE string
is_back_run TYPE c OPTIONAL
CHANGING c_destination TYPE string
EXCEPTIONS templet_is_not_fount
can_not_save_file
templet_download_failure.
ENDCLASS. "excel_session DEFINITION
*----------------------------------------------------------------------*
* CLASS excel_control IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS excel_control IMPLEMENTATION.
METHOD get_excel_templet.
DATA: l_objdata TYPE wwwdatatab, "查找Web资源对象关键字
l_rc LIKE sy-subrc,
lc_filename_temp TYPE string,
ls_destination TYPE rlgrap-filename,
lc_fullpath TYPE string VALUE '',
lc_path TYPE string VALUE ''.
"取得对应的模板,并判断是否存在
SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF l_objdata WHERE srtf2 = 0 AND relid = 'MI' AND objid = i_objid.
IF sy-subrc NE 0 AND l_objdata-objid EQ space.
RAISE templet_is_not_fount.
ENDIF.
IF is_back_run = 'X'.
CONCATENATE c_destination '\' i_filename '.xls' INTO lc_fullpath.
ELSE.
"用户选择对应的路径
lc_filename_temp = i_filename.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = 'xls'
default_file_name = i_filename
initial_directory = c_destination
CHANGING
filename = lc_filename_temp
path = lc_path
fullpath = lc_fullpath.
IF lc_fullpath EQ ''.
RAISE can_not_save_file.
ENDIF.
ENDIF.
"如果模板存在,则下载到用户电脑本地
ls_destination = lc_fullpath.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = l_objdata
destination = ls_destination
IMPORTING
rc = l_rc.
"当l_rc不为0的时候表示对象不存在
IF l_rc NE 0.
RAISE templet_download_failure.
ENDIF.
"回写生成的路径
c_destination = lc_fullpath.
ENDMETHOD. "get_excel_templet_object
ENDCLASS. "excel_control IMPLEMENTATION
*----------------------------------------------------------------------*
* CLASS excel_destination IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS excel_destination DEFINITION.
PUBLIC SECTION.
METHODS: constructor IMPORTING i_destination TYPE string
i_sheetname TYPE string OPTIONAL "需要打开的Sheet名字(目前不起作用)
is_visible TYPE c DEFAULT space
n_sheetname TYPE string OPTIONAL "需要更改的Sheet名字
EXCEPTIONS error_create_excel_object
error_open_destination_object,
set_column_width IMPORTING column_name TYPE c
column_width TYPE p,
set_row_height IMPORTING row_num TYPE n
row_height TYPE p,
set_group IMPORTING start_row_num TYPE n
end_row_num TYPE n,
set_merge IMPORTING range TYPE string,
copy_insert_columns IMPORTING copy_range TYPE string
insert_range TYPE string,
copy_insert_rows IMPORTING copy_range TYPE string
insert_range TYPE string,
delete_columns IMPORTING range TYPE string,
set_cells_autofit,
save,
quit,
recycle,
load_data IMPORTING rows_tab TYPE rows_tab.
PRIVATE SECTION.
DATA: excel_ole_object TYPE ole2_object,
workbooks_ole_object TYPE ole2_object,
worksheets_ole_object TYPE ole2_object,
sheets_ole_object TYPE ole2_object.
ENDCLASS. "excel_destination IMPLEMENTATION
*----------------------------------------------------------------------*
* CLASS excel_destination IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS excel_destination IMPLEMENTATION.
METHOD constructor.
CREATE OBJECT excel_ole_object 'Excel.Application'.
IF sy-subrc NE 0.
RAISE error_create_excel_object.
ENDIF.
"处理显示Excel或后台处理
DATA is_visible_flag(1) TYPE c.
IF is_visible EQ 'X'.
is_visible_flag = '1'.
ELSE.
is_visible_flag = '0'.
ENDIF.
CALL METHOD OF excel_ole_object 'Workbooks' = workbooks_ole_object.
SET PROPERTY OF excel_ole_object 'Visible' = is_visible_flag.
CALL METHOD OF workbooks_ole_object 'Open' = workbooks_ole_object
EXPORTING
#1 = i_destination.
IF sy-subrc NE 0.
RAISE error_open_destination_object.
ENDIF.
"取得对应的Sheet
CALL METHOD OF excel_ole_object 'WORKSHEETS' = sheets_ole_object
EXPORTING
#1 = 1.
"更改Sheet的名字
IF NOT n_sheetname IS INITIAL.
SET PROPERTY OF sheets_ole_object 'Name' = n_sheetname.
ENDIF.
ENDMETHOD. "open
"设定指定列的宽度
METHOD set_column_width.
DATA column_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Columns' = column_ole_object
EXPORTING
#1 = column_name.
SET PROPERTY OF column_ole_object 'ColumnWidth' = column_width.
FREE OBJECT column_ole_object.
ENDMETHOD. "set_column_width
"设计指定行的高度
METHOD set_row_height.
DATA row_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Rows' = row_ole_object
EXPORTING
#1 = row_num.
SET PROPERTY OF row_ole_object 'RowHeight' = row_height.
FREE OBJECT row_ole_object.
ENDMETHOD. "set_row_height
"指过指定起始行及结束行,对期间的行范围进行分组
METHOD set_group.
DATA: rows_ole_object TYPE ole2_object,
selection_ole_object TYPE ole2_object,
selection_rows_ole_object TYPE ole2_object,
rows_range TYPE string.
CONCATENATE start_row_num ':' end_row_num INTO rows_range.
CALL METHOD OF excel_ole_object 'Rows' = rows_ole_object
EXPORTING
#1 = rows_range.
CALL METHOD OF rows_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Rows' = selection_rows_ole_object.
CALL METHOD OF selection_rows_ole_object 'Group'.
FREE OBJECT: rows_ole_object,
selection_ole_object,
selection_rows_ole_object.
ENDMETHOD. "set_group
"复制插入列
METHOD copy_insert_columns.
DATA: columns_ole_object TYPE ole2_object,
selection_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Columns' = columns_ole_object
EXPORTING
#1 = copy_range.
CALL METHOD OF columns_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Copy'.
CALL METHOD OF excel_ole_object 'Columns' = columns_ole_object
EXPORTING
#1 = insert_range.
CALL METHOD OF columns_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Insert'.
ENDMETHOD. "copy_insert_columns
"复制插入行
METHOD copy_insert_rows.
DATA: rows_ole_object TYPE ole2_object,
selection_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Rows' = rows_ole_object
EXPORTING
#1 = copy_range.
CALL METHOD OF rows_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Copy'.
CALL METHOD OF excel_ole_object 'Rows' = rows_ole_object
EXPORTING
#1 = insert_range.
CALL METHOD OF rows_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Insert'.
ENDMETHOD. "copy_insert_rows
"删除列
METHOD delete_columns.
DATA: columns_ole_object TYPE ole2_object,
selection_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Columns' = columns_ole_object
EXPORTING
#1 = range.
CALL METHOD OF columns_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Delete'.
ENDMETHOD. "copy_insert_columns
"自动设置单元格自适应宽度
METHOD set_cells_autofit.
DATA: cells_ole_object TYPE ole2_object,
entirecolumn_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Cells' = cells_ole_object.
CALL METHOD OF cells_ole_object 'Select'.
CALL METHOD OF cells_ole_object 'EntireColumn' = entirecolumn_ole_object.
CALL METHOD OF entirecolumn_ole_object 'AutoFit'.
ENDMETHOD. "set_cells_autofit
"合并指定矩形范围单元格
METHOD set_merge.
DATA: range_ole_object TYPE ole2_object,
selection_ole_object TYPE ole2_object.
CALL METHOD OF excel_ole_object 'Range' = range_ole_object
EXPORTING
#1 = range.
CALL METHOD OF range_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Merge'.
FREE OBJECT: range_ole_object,
selection_ole_object.
ENDMETHOD. "set_merge
"加载数据
METHOD load_data.
DATA: rows_ole_object TYPE ole2_object,
selection_ole_object TYPE ole2_object,
range_ole_object TYPE ole2_object,
ac_ole_object TYPE ole2_object,
font_ole_object TYPE ole2_object,
borders_ole_object TYPE ole2_object,
xledge_ole_object TYPE ole2_object,
linestyle_ole_object TYPE ole2_object,
xlthin_ole_object TYPE ole2_object,
range TYPE string,
rows_tab_c TYPE rows_tab ,
n TYPE i .
rows_tab_c[] = rows_tab[] .
SORT rows_tab_c[] BY row_num DESCENDING.
DELETE ADJACENT DUPLICATES FROM rows_tab_c[] COMPARING row_num .
DESCRIBE TABLE rows_tab_c[] LINES n.
DATA: perc TYPE i,
cnt TYPE i .
DATA: stxt TYPE string.
DATA: sperc(3) TYPE c.
perc = 0. cnt = 1000.
LOOP AT rows_tab INTO rows.
"新增行标志,当该标志为X时,表示新插入一行到指定的位置,否则为替换相应行的数据
IF rows-is_add = 'X'.
"执行在指定的位置插入一行
CALL METHOD OF excel_ole_object 'Rows' = rows_ole_object
EXPORTING
#1 = rows-row_num.
CALL METHOD OF rows_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Insert'.
"清空OLE对象
CLEAR: rows_ole_object,
selection_ole_object.
"开始写入Cell数据
LOOP AT rows-cells_tab INTO cells.
"组合Ragen,如行为6,列名为B,则Range:B6,当前的数据值及属性将写入到B6单元格里面,其它类似
CONCATENATE cells-column_name rows-row_num INTO range.
CALL METHOD OF excel_ole_object 'Range' = range_ole_object
EXPORTING
#1 = range.
CALL METHOD OF range_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'ActiveCell' = ac_ole_object.
SET PROPERTY OF ac_ole_object 'FormulaR1C1' = cells-value.
CALL METHOD OF range_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Font' = font_ole_object.
"当标志为粗体的时候则设置
IF cells-is_bold EQ 'X'.
SET PROPERTY OF font_ole_object 'Bold' = 'True'.
ELSE.
SET PROPERTY OF font_ole_object 'Bold' = 'False'.
ENDIF.
"设置字体
IF NOT cells-font IS INITIAL.
SET PROPERTY OF font_ole_object 'Name' = cells-font.
ENDIF.
"设置字体大小
IF NOT cells-fontsize IS INITIAL.
SET PROPERTY OF font_ole_object 'Size' = cells-fontsize.
ENDIF.
"设置字体的颜色
"设置单元格格式
IF cells-format EQ '1'.
SET PROPERTY OF selection_ole_object 'NumberFormatLocal' = '0.00_);(0.00)'.
ENDIF.
"设置单元格是否有边框
IF NOT cells-border_style IS INITIAL.
"Set Cell's Top Border
IF cells-show_border_top EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgetop.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
"Set Cell's Right Border
IF cells-show_border_right EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgeright.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
"Set Cell's Bottom Border
IF cells-show_border_bottom EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgebottom.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
"Set Cell's Left Border
IF cells-show_border_left EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgeleft.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
ENDIF.
"释放对象
FREE OBJECT range_ole_object.
FREE OBJECT ac_ole_object.
FREE OBJECT selection_ole_object.
ENDLOOP.
ELSE.
"开始写入Cell数据
LOOP AT rows-cells_tab INTO cells.
"组合Ragen,如行为6,列名为B,则Range:B6,当前的数据值及属性将写入到B6单元格里面,其它类似
CONCATENATE cells-column_name rows-row_num INTO range.
CALL METHOD OF excel_ole_object 'Range' = range_ole_object
EXPORTING
#1 = range.
CALL METHOD OF range_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'ActiveCell' = ac_ole_object.
SET PROPERTY OF ac_ole_object 'FormulaR1C1' = cells-value.
CALL METHOD OF range_ole_object 'Select'.
CALL METHOD OF excel_ole_object 'Selection' = selection_ole_object.
CALL METHOD OF selection_ole_object 'Font' = font_ole_object.
"当标志为粗体的时候则设置
IF cells-is_bold EQ 'X'.
SET PROPERTY OF font_ole_object 'Bold' = 'True'.
ELSE.
SET PROPERTY OF font_ole_object 'Bold' = 'False'.
ENDIF.
"设置字体
IF NOT cells-font IS INITIAL.
SET PROPERTY OF font_ole_object 'Name' = cells-font.
ENDIF.
"设置字体大小
IF NOT cells-fontsize IS INITIAL.
SET PROPERTY OF font_ole_object 'Size' = cells-fontsize.
ENDIF.
"设置字体的颜色
"设置单元格格式
IF cells-format EQ '1'.
SET PROPERTY OF selection_ole_object 'NumberFormatLocal' = '0.00_);(0.00)'.
ENDIF.
"设置单元格是否有边框
IF NOT cells-border_style IS INITIAL.
"Set Cell's Top Border
IF cells-show_border_top EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgetop.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
"Set Cell's Right Border
IF cells-show_border_right EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgeright.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
"Set Cell's Bottom Border
IF cells-show_border_bottom EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgebottom.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
"Set Cell's Left Border
IF cells-show_border_left EQ 'X'.
CALL METHOD OF selection_ole_object 'Borders' = borders_ole_object
EXPORTING
#1 = xledgeleft.
SET PROPERTY OF borders_ole_object 'LineStyle' = cells-border_style.
IF cells-is_border_weight EQ 'X'.
SET PROPERTY OF borders_ole_object 'Weight' = xl_medium.
ENDIF.
FREE OBJECT borders_ole_object.
ENDIF.
ENDIF.
"释放对象
FREE OBJECT range_ole_object.
FREE OBJECT ac_ole_object.
FREE OBJECT selection_ole_object.
ENDLOOP.
ENDIF.
" 进度条
stxt = ''.
perc = rows-row_num * 100 / n .
sperc = perc.
IF sperc > 100.
sperc = 100 .
ENDIF.
CONCATENATE 'EXCEL已下载 :_' sperc '% …… ' INTO stxt.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = perc "百分比的数值,用来控制秒表的指针。
text = stxt. "状态栏显示的文本。
ENDLOOP.
ENDMETHOD. "load_data
METHOD save.
CALL METHOD OF workbooks_ole_object 'save'.
ENDMETHOD. "save
METHOD quit.
CALL METHOD OF workbooks_ole_object 'close'.
CALL METHOD OF excel_ole_object 'quit'.
ENDMETHOD. "quit
METHOD recycle.
IF NOT sheets_ole_object IS INITIAL.
FREE OBJECT sheets_ole_object.
ENDIF.
IF NOT workbooks_ole_object IS INITIAL.
FREE OBJECT workbooks_ole_object.
ENDIF.
IF NOT excel_ole_object IS INITIAL.
FREE OBJECT excel_ole_object.
ENDIF.
ENDMETHOD. "recycle
ENDCLASS. "excel_destination IMPLEMENTATION