EXCEL 操作ABAP

来源：互联网发布：多臂机器人单片机编辑：程序博客网时间：2024/05/21 00:51

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