ABAP将内表数据写入Excel 2003的通用方法

*&---------------------------------------------------------------------*
*&  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 .