*&---------------------------------------------------------------------*
*& Report Z_BARRY_EXCEL_TEST *
*& *
*&---------------------------------------------------------------------*
*& Excel OLE 测试 *
*&---------------------------------------------------------------------*
REPORT z_barry_excel_test .
INCLUDE ole2incl.
DATA: excel_obj TYPE ole2_object,
workbook_obj TYPE ole2_object,
sheet_obj TYPE ole2_object,
sheet_obj2 TYPE ole2_object,
cell_obj TYPE ole2_object,
column_obj TYPE ole2_object,
range_obj TYPE ole2_object,
borders_obj TYPE ole2_object,
button_obj TYPE ole2_object,
int_obj TYPE ole2_object,
font_obj TYPE ole2_object,
row_obj TYPE ole2_object.
DATA: celldata(40) TYPE c .
DATA: BEGIN OF itab OCCURS 0 ,
ff1(10) TYPE c,
ff2(10) TYPE c,
END OF itab.
START-OF-SELECTION.
itab-ff1 = 'A1'.
itab-ff2 = 'A2'.
APPEND itab.
itab-ff1 = 'B1'.
itab-ff2 = 'B2'.
APPEND itab.
itab-ff1 = 'C1'.
itab-ff2 = 'C2'.
APPEND itab.
itab-ff1 = 'D1'.
itab-ff2 = 'D2'.
APPEND itab.
itab-ff1 = 'E1'.
itab-ff2 = 'E2'.
APPEND itab.
PERFORM open_excel_file USING excel_obj
workbook_obj
sheet_obj
'C:\XLS_TMPLT.XLS'
'TestSheet'
'1' .
PERFORM excel_range_write USING excel_obj
'A1:B2'
'abcd'.
PERFORM excel_row_insert USING sheet_obj
'1'
'2'.
PERFORM excel_column_insert USING sheet_obj
'A'
'2'.
PERFORM rowheight USING excel_obj
'1:6'
'30'.
PERFORM columnwidth USING excel_obj
'A:C'
'20'.
PERFORM excel_cell_read USING excel_obj
'1'
'A'
celldata .
PERFORM excel_range_write USING excel_obj
'C1:D2'
celldata .
PERFORM excel_sheet_add USING workbook_obj
sheet_obj2
'Test02'.
PERFORM excel_range_write USING excel_obj
'C1:D2'
'aaaaa'.
PERFORM borderrange USING excel_obj
'A1:C3'.
PERFORM runmacro USING excel_obj
'Macro1'.
PERFORM fill_sheet_itab USING 2 3.
CALL METHOD OF sheet_obj 'Activate' .
PERFORM fill_sheet_itab USING 2 3.
PERFORM protect_sheet .
GET PROPERTY OF excel_obj 'ActiveWorkbook' = workbook_obj.
PERFORM excel_save USING excel_obj.
CALL METHOD OF workbook_obj 'CLOSE'.
CALL METHOD OF excel_obj 'QUIT'.
*&---------------------------------------------------------------------*
*& 创建Excel对象
*&---------------------------------------------------------------------*
FORM open_excel_file USING lcobj_excel
lcobj_workbook
lcobj_sheet
lc_filename
lc_sheetname
lc_visible.
CREATE OBJECT lcobj_excel 'Excel.Application'.
IF sy-subrc NE 0.
MESSAGE e796(f9) WITH '不能创建Excel对象'.
ENDIF.
CALL METHOD OF lcobj_excel 'Workbooks' = lcobj_workbook.
CALL METHOD OF lcobj_workbook 'Open' = lcobj_workbook
EXPORTING #1 = lc_filename.
IF sy-subrc NE 0.
MESSAGE e796(f9) WITH '打开文件错误'.
ENDIF.
SET PROPERTY OF lcobj_excel 'Visible' = lc_visible.
CALL METHOD OF lcobj_workbook 'Sheets' = lcobj_sheet
EXPORTING #1 = 1.
SET PROPERTY OF lcobj_sheet 'Name' = lc_sheetname.
ENDFORM. "excel_initialization
*&---------------------------------------------------------------------*
*& 读取单元格的数据
*&---------------------------------------------------------------------*
FORM excel_cell_read USING lcobj_excel
lc_row
lc_col
lc_value.
DATA: lc_cell TYPE ole2_object.
GET PROPERTY OF lcobj_excel 'Cells' = lc_cell
exporting #1 = lc_row
#2 = lc_col.
GET PROPERTY OF lc_cell 'Value' = lc_value.
ENDFORM. "excel_cell_read
*&---------------------------------------------------------------------*
*& 写数据至Range
*&---------------------------------------------------------------------*
FORM excel_range_write USING lcobj_excel
lc_range
lc_value.
DATA: lc_cell TYPE ole2_object.
CALL METHOD OF lcobj_excel 'RANGE' = lc_cell
EXPORTING
#1 = lc_range.
PERFORM font USING lc_cell 1 '30'.
SET PROPERTY OF lc_cell 'VALUE' = lc_value.
ENDFORM. "excel_cell_write
*&---------------------------------------------------------------------*
*& 插入N行
*&---------------------------------------------------------------------*
FORM excel_row_insert USING lcobj_sheet
lc_row
lc_count.
DATA lc_range TYPE ole2_object.
DO lc_count TIMES.
CALL METHOD OF lcobj_sheet 'Rows' = lc_range
EXPORTING #1 = lc_row.
CALL METHOD OF lc_range 'Copy'.
CALL METHOD OF lcobj_sheet 'Rows' = lc_range
EXPORTING #1 = lc_row.
CALL METHOD OF lc_range 'Insert'.
CALL METHOD OF lc_range 'ClearContents'. "是否需要清空Cell
ENDDO.
ENDFORM. "excel_row_insert
*&---------------------------------------------------------------------*
*& 插入N列
*&---------------------------------------------------------------------*
FORM excel_column_insert USING lcobj_sheet
lc_col
lc_count.
DATA lc_range TYPE ole2_object.
CALL METHOD OF lcobj_sheet 'COLUMNS' = lc_range
EXPORTING #1 = lc_col.
CALL METHOD OF lc_range 'Copy'.
CALL METHOD OF lcobj_sheet 'COLUMNS' = lc_range
EXPORTING #1 = lc_col.
DO lc_count TIMES.
CALL METHOD OF lc_range 'Insert'.
ENDDO.
CALL METHOD OF lc_range 'ClearContents'.
ENDFORM. "excel_column_insert
*&---------------------------------------------------------------------*
*& 增加一个Sheet
*&---------------------------------------------------------------------*
FORM excel_sheet_add USING lcobj_workbook
lcobj_sheet
lc_newsheetname.
DATA lc_newsheet TYPE ole2_object.
GET PROPERTY OF lcobj_workbook 'Sheets' = lcobj_sheet.
CALL METHOD OF lcobj_sheet 'Add' = lc_newsheet.
SET PROPERTY OF lc_newsheet 'Name' = lc_newsheetname.
ENDFORM. "excel_sheet_add
*&---------------------------------------------------------------------*
*& 增加一个Workbook
*&---------------------------------------------------------------------*
FORM excel_workbook_add USING lcobj_excel
lcobj_workbook.
DATA lc_newworkbook TYPE ole2_object.
GET PROPERTY OF lcobj_excel 'Workbooks' = lcobj_workbook.
CALL METHOD OF lcobj_workbook 'Add' = lc_newworkbook.
ENDFORM. "excel_workbook_add
*&---------------------------------------------------------------------*
*& 设置Cell的字体
*&---------------------------------------------------------------------*
FORM font USING lcobj_cell
bold
size.
DATA lc_font TYPE ole2_object.
CALL METHOD OF lcobj_cell 'FONT' = lc_font.
SET PROPERTY OF lc_font 'BOLD' = bold.
SET PROPERTY OF lc_font 'SIZE' = size.
FREE OBJECT lc_font.
ENDFORM. "font
*&---------------------------------------------------------------------*
*& 行高
*&---------------------------------------------------------------------*
FORM rowheight USING lcobj_excel
row
height .
DATA lc_row TYPE ole2_object .
CALL METHOD OF lcobj_excel 'ROWS' = lc_row
EXPORTING #1 = row.
SET PROPERTY OF lc_row 'RowHeight' = height .
FREE OBJECT lc_row.
ENDFORM . "rowheight
*&---------------------------------------------------------------------*
*& 列宽
*&---------------------------------------------------------------------*
FORM columnwidth USING lcobj_excel
column
width .
DATA ls_col TYPE ole2_object .
CALL METHOD OF lcobj_excel 'COLUMNS' = ls_col
EXPORTING
#1 = column.
SET PROPERTY OF ls_col 'columnwidth' = width .
FREE OBJECT ls_col.
ENDFORM . "columnwidth
*---------------------------------------------------------------------*
* 添加Range的边框 *
*---------------------------------------------------------------------*
FORM borderrange USING lcobj_excel
range .
DATA: lc_cell TYPE ole2_object ,
lc_borders TYPE ole2_object .
CALL METHOD OF lcobj_excel 'RANGE' = lc_cell
EXPORTING
#1 = range.
DO 4 TIMES .
CALL METHOD OF lc_cell 'BORDERS' = lc_borders
EXPORTING #1 = sy-index.
SET PROPERTY OF lc_borders 'LineStyle' = '1'.
SET PROPERTY OF lc_borders 'WEIGHT' = 2. "4=max
SET PROPERTY OF lc_borders 'ColorIndex' = '3'.
ENDDO.
FREE OBJECT lc_borders.
FREE OBJECT lc_cell.
ENDFORM. "borderrange
*---------------------------------------------------------------------*
* FORM fill_sheet_itab *
*---------------------------------------------------------------------*
* 把内表依次放入Cell *
*---------------------------------------------------------------------*
FORM fill_sheet_itab USING rowindex
colindex.
DATA: row TYPE i ,
col TYPE i .
DATA: lcobj_cell TYPE ole2_object .
FIELD-SYMBOLS: .
row = rowindex .
LOOP AT itab .
col = colindex .
DO 20 TIMES.
ASSIGN COMPONENT sy-index OF STRUCTURE itab TO .
IF sy-subrc <> 0 .
EXIT.
ENDIF.
CALL METHOD OF excel_obj 'Cells' = lcobj_cell
EXPORTING #1 = row
#2 = col.
SET PROPERTY OF lcobj_cell 'Value' = .
col = col + 1.
ENDDO.
row = row + 1.
ENDLOOP.
ENDFORM. "fill_sheet_itab
*---------------------------------------------------------------------*
* 执行Excel的宏 *
*---------------------------------------------------------------------*
FORM runmacro USING lcobj_excel
macroname.
CALL METHOD OF lcobj_excel 'RUN'
EXPORTING
#1 = macroname.
ENDFORM. "runmacro
*&---------------------------------------------------------------------*
* 保护工作表
*----------------------------------------------------------------------*
FORM protect_sheet .
CALL METHOD OF sheet_obj 'PROTECT'
EXPORTING
#1 = 'pass' " Password
#2 = 1 " Protect Drawing Objects
#3 = 1 " Protect Contents
#4 = 1." Protect Scenarios.
ENDFORM. " protect_sheet
*&---------------------------------------------------------------------*
*& 保存Excel
*&---------------------------------------------------------------------*
FORM excel_save USING lcobj_excel.
SET PROPERTY OF lcobj_excel 'DisplayAlerts' = 0.
CALL METHOD OF lcobj_excel 'Save'.
* CALL METHOD OF workbook_obj 'SAVEAS' "另存
* EXPORTING
* #1 = 'C:\Test.xls'
* #2 = 1.
ENDFORM. "excel_save
阅读(10437) | 评论(17) | 转发(1) |
