用ABAP控制EXCEL的应用

* Marc Hoffmann HSE Consulting LLC, OrionFoodSystems LLC
* Written on 46B in 2003
*
* P_EXVIS Excel visible
* P_WORKBK Number of workbooks to create
* P_WSHEET Number of sheets per workbook
*
PARAMETERS: p_fname LIKE rlgrap-filename DEFAULT 'C:/temp/testNN.xls'.

DATA: fname LIKE p_fname,
      kn LIKE sy-repid.
DATA: cnt TYPE i VALUE 0.

DATA: itab LIKE TABLE OF kna1 WITH HEADER LINE .

DATA: sheetname(10) VALUE 'TEST ',c_row TYPE i,
      scnt TYPE i,
      val(20), wb(2).

PARAMETERS:
            p_workbk(2) TYPE p DEFAULT '01',
            p_wsheet(2) TYPE p DEFAULT '01',
            p_exvis AS CHECKBOX DEFAULT 'X' .

CONSTANTS: ok TYPE i VALUE 0.
INCLUDE ole2incl.
DATA: excel_obj     TYPE ole2_object,
      workbook_obj  TYPE ole2_object,
      sheet_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.

DATA: application TYPE ole2_object,
      book        TYPE ole2_object,
      books       TYPE ole2_object.
DATA: ole_book    TYPE ole2_object.

DO p_workbk TIMES.
  MOVE p_fname TO fname.
  UNPACK sy-index TO wb.
  REPLACE 'NN' WITH wb INTO fname.
*
  PERFORM create_excel_obj.

* create sheets and save
  PERFORM sheet.

  PERFORM save_book.
  perform open_workbook .
ENDDO.
WRITE: ' Done'.

*---------------------------------------------------------------------*
*       FORM create_Excel_Obj                                             *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM create_excel_obj.
  CREATE OBJECT excel_obj 'excel.APPLICATION'.
  IF sy-subrc NE 0.
    WRITE: / 'No Excel creation possible'.
    STOP.
  ENDIF.

  SET PROPERTY OF excel_obj 'DisplayAlerts' = 0.

  CALL METHOD OF excel_obj 'WORKBOOKS' = workbook_obj .
*  Put Excel in background
  IF p_exvis EQ 'X'.
    SET PROPERTY OF excel_obj 'VISIBLE' = 1.
  ELSE.
    SET PROPERTY OF excel_obj 'VISIBLE' = 0.
  ENDIF.

*  Create worksheet
  SET PROPERTY OF excel_obj 'SheetsInNewWorkbook' = 1.
  CALL METHOD OF workbook_obj 'ADD'.
ENDFORM.                    "create_Excel_Obj

*---------------------------------------------------------------------*
*       FORM save_book                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM save_book.
  GET PROPERTY OF excel_obj 'ActiveSheet' = sheet_obj.
  FREE OBJECT sheet_obj.
  FREE OBJECT workbook_obj.

  GET PROPERTY OF excel_obj 'ActiveWorkbook' = workbook_obj.
  CALL METHOD OF workbook_obj 'SAVEAS'
    EXPORTING
      #1 = fname
      #2 = 1.
  CALL METHOD OF workbook_obj 'CLOSE'.

*  CALL METHOD OF workbook_obj 'OPEN'
*    EXPORTING
*      #1 = 'c:/xl.xls' .

  CALL METHOD OF excel_obj 'QUIT'.

  FREE OBJECT sheet_obj.
  FREE OBJECT workbook_obj.
  FREE OBJECT excel_obj.
ENDFORM.                    "save_book

*---------------------------------------------------------------------*
*       FORM sheet                                                    *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM sheet.

  DO p_wsheet TIMES.
    UNPACK sy-index TO sheetname+5(2).

    IF sy-index GT 1.
      CALL METHOD OF excel_obj 'WORKSHEETS' = sheet_obj.
      CALL METHOD OF sheet_obj 'ADD'.
      FREE OBJECT sheet_obj.
    ENDIF.
    scnt = sy-index.
    CALL METHOD OF excel_obj 'WORKSHEETS' = sheet_obj
      EXPORTING
        #1 = scnt.
    CALL METHOD OF sheet_obj 'ACTIVATE'.
    SET PROPERTY OF sheet_obj 'NAME' = sheetname.
    FREE OBJECT sheet_obj.  "OK

    PERFORM fill_sheet.
*
    CALL METHOD OF excel_obj 'Columns