*&---------------------------------------------------------------------*
*& Include ZOLEEXCEL *
*&---------------------------------------------------------------------*
*INCLUDE ole2incl.
*TABLES:rlgrap.
*
*DATA: sheetname(10) VALUE 'exp ',c_row TYPE i,
* scnt TYPE i,
* val(20), wb(2),
* msgtxt(50) type c .
*
*DATA: excel_obj TYPE ole2_object,
* workbook_obj TYPE ole2_object,
* workbook2_obj TYPE ole2_object,
* sheet_obj TYPE ole2_object,
* sheet2_obj TYPE ole2_object,
* cell_obj TYPE ole2_object,
* cell1_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,
* align_obj TYPE ole2_object.
*
*DATA: application TYPE ole2_object,
* book TYPE ole2_object,
* books TYPE ole2_object.
*DATA: ole_book TYPE ole2_object.
*&---------------------------------------------------------------------------------------------------------------------------------------------------------------*
*&
*& 该Include,由ZMB51(zmmr0039)试用,不可做轻易改动,如需要使用,直接调用方法便可(2009-03-31)
*& 调用该Include,在您的程序中必须满足上面变量的定义,然后在调用本Include
*&---------------------------------------------------------------------------------------------------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form create_excel_obj *
*& *
*&---------------------------------------------------------------------*
*& 创建一个excel应用程序 *
*&---------------------------------------------------------------------*
*& *
*&---------------------------------------------------------------------*
FORM create_excel_obj . "start create_excel_obj .
CREATE OBJECT excel_obj 'excel.APPLICATION'.
perform exception_excel using 'create excel application error' .
CALL METHOD OF excel_obj 'WORKBOOKS' = workbook_obj .
msgtxt = '正在创建excel文件' .
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
TEXT = msgtxt
EXCEPTIONS
OTHERS = 1.
CALL METHOD OF workbook_obj 'ADD'= workbook2_obj.
perform exception_excel using 'create excel workbook error' .
ENDFORM . " end create_obj
*&---------------------------------------------------------------------*
*& Form create_shheet_obj *
*& *
*&---------------------------------------------------------------------*
*& 创建工作表 *
*&---------------------------------------------------------------------*
*& *
*&---------------------------------------------------------------------*
FORM create_sheet_obj .
CALL METHOD OF excel_obj 'WORKSHEETS' = sheet_obj .
msgtxt = '正在为sheet命名' .
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
TEXT = msgtxt
EXCEPTIONS
OTHERS = 1.
CALL METHOD OF sheet_obj 'ADD' = sheet2_obj.
* CALL METHOD OF sheet_obj 'ACTIVATE'.
SET PROPERTY OF sheet2_obj 'NAME' = 'exp' .
perform exception_excel using '工作表命名出错' .
ENDFORM .
*&---------------------------------------------------------------------*
*& Form fill_cell *
*& *
*&---------------------------------------------------------------------*
*& 填充单元格内容 *
*&---------------------------------------------------------------------*
*& p1->text p2->text *
*& p3->text p4->text *
*&---------------------------------------------------------------------*
FORM fill_cell using i j bold val . "set value or set cell
CALL METHOD OF excel_obj 'Cells' = cell_obj EXPORTING #1 = I #2 = J.
* PERFORM exception_excel using 'read error,please export again'.
data : l_row(4) ,
l_col(4) .
l_row = i .
l_col = j .
CLEAR msgtxt .
CONCATENATE '正在写入单元格第' l_row '行,第' l_col '列' INTO msgtxt.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
TEXT = msgtxt
EXCEPTIONS
OTHERS = 1.
SET PROPERTY OF cell_obj 'Value' = VAL .
* PERFORM exception_excel using 'read error,please export again'.
GET PROPERTY OF cell_obj 'Font' = font_obj.
* PERFORM exception_excel using 'read error,please export again'.
SET PROPERTY OF font_obj 'Bold' = bold .
* PERFORM exception_excel using 'read error,please export again'.
ENDFORM. "end fill_cell
*&---------------------------------------------------------------------*
*& Form set_cell_style *
*& *
*&---------------------------------------------------------------------*
*& 设置单元格样式—宽度—对齐方式 *
*&---------------------------------------------------------------------*
*& p1->text p2->text *
*& p3->text p4->text *
*&---------------------------------------------------------------------*
FORM set_cell_style using i j width align.
CALL METHOD OF excel_obj 'Cells' = cell_obj EXPORTING #1 = I #2 = J.
* PERFORM exception_excel using 'set cell obj error'.
SET PROPERTY OF cell_obj 'ColumnWidth' = width .
* PERFORM exception_excel using 'set cell width error'.
SET PROPERTY OF cell_obj 'HorizontalAlignment' = align . "对齐方式取值:1保持原有格式,2左对齐,3居中,4右对齐
* PERFORM exception_excel using 'set cell alignment error'.
ENDFORM .
*&---------------------------------------------------------------------*
*& Form set_font_style *
*& *
*&---------------------------------------------------------------------*
*& 设置字体样式—大小—颜色 *
*&---------------------------------------------------------------------*
*& p1->text p2->text *
*& p3->text p4->text *
*&---------------------------------------------------------------------*
FORM set_font_style using i j size color . "set font-size color.颜色代码可通过录制宏得到
CALL METHOD OF excel_obj 'Cells' = cell_obj EXPORTING #1 = I #2 = J.
* PERFORM exception_excel using 'set cell error'.
CALL METHOD OF cell_obj 'Font' = font_obj .
SET PROPERTY OF font_obj 'SIZE' = size .
* PERFORM exception_excel using 'set font size error'.
SET PROPERTY OF font_obj 'Color' = color .
* PERFORM exception_excel using 'set font color error'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form set_font_style *
*& *
*&---------------------------------------------------------------------*
*& 设置单元格样式—边框线 *
*&---------------------------------------------------------------------*
*& p1->text p2->text *
*& p3->text *
*&---------------------------------------------------------------------*
FORM set_cell_border using i j w.
CALL METHOD OF excel_obj 'Cells' = cell_obj EXPORTING #1 = I #2 = J.
* PERFORM exception_excel using 'set cell obj error'.
CALL METHOD OF cell_obj 'BORDERS' = borders_obj "左边框
EXPORTING
#1 = '1'.
SET PROPERTY OF borders_obj 'LineStyle' = '1'. "线条样式
SET PROPERTY OF borders_obj 'WEIGHT' = w. "4=max 边框线的粗细程度
SET PROPERTY OF borders_obj 'ColorIndex' = '1'. "颜色
FREE OBJECT borders_obj.
CALL METHOD OF cell_obj 'BORDERS' = borders_obj "右边框
EXPORTING
#1 = '2'.
SET PROPERTY OF borders_obj 'LineStyle' = '1'.
SET PROPERTY OF borders_obj 'WEIGHT' = w.
SET PROPERTY OF borders_obj 'ColorIndex' = '1'.
FREE OBJECT borders_obj.
CALL METHOD OF cell_obj 'BORDERS' = borders_obj "上(顶)边框
EXPORTING
#1 = '3'.
SET PROPERTY OF borders_obj 'LineStyle' = '1'.
SET PROPERTY OF borders_obj 'WEIGHT' = w.
SET PROPERTY OF borders_obj 'ColorIndex' = '1'.
FREE OBJECT borders_obj.
CALL METHOD OF cell_obj 'BORDERS' = borders_obj "下(底)边框
EXPORTING
#1 = '4'.
SET PROPERTY OF borders_obj 'LineStyle' = '1'.
SET PROPERTY OF borders_obj 'WEIGHT' = w.
SET PROPERTY OF borders_obj 'ColorIndex' = '1'.
FREE OBJECT borders_obj.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form fill_error *
*& *
*&---------------------------------------------------------------------*
*& 错语处理 *
*&---------------------------------------------------------------------*
*& *
*&---------------------------------------------------------------------*
FORM fill_error.
msgtxt = '写入excel出错'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
TEXT = msgtxt
EXCEPTIONS
OTHERS = 1.
CALL METHOD OF workbook_obj 'CLOSE' .
CALL METHOD OF excel_obj 'QUIT' .
FREE OBJECT sheet_obj .
FREE OBJECT workbook_obj .
FREE OBJECT excel_obj .
ENDFORM.
*&---------------------------------------------------------------------*
*& Form exception_ *
*& *
*&---------------------------------------------------------------------*
*& 发生异常 *
*&---------------------------------------------------------------------*
*& *
*&---------------------------------------------------------------------*
FORM exception_excel using val. "throws exception
IF sy-subrc NE 0.
perform fill_error .
WRITE: / val.
STOP.
ENDIF.
ENDFORM.
*&
*&---------------------------------------------------------------------*
*INCLUDE
*TABLES:rlgrap.
*
*DATA:
*
*
*
*
*DATA:
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*DATA:
*
*
*DATA:
*&---------------------------------------------------------------------------------------------------------------------------------------------------------------*
*&
*&
*&
*&---------------------------------------------------------------------------------------------------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
FORM
ENDFORM
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
FORM
*
ENDFORM
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
FORM
*
*
*
*
ENDFORM.
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
FORM
*
*
*
ENDFORM
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
FORM
*
*
*
ENDFORM.
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
FORM
*
ENDFORM.
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
FORM
ENDFORM.
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
FORM
ENDFORM.