SAP을 즐기는 사람들

REPORT ZS00_EXCEL_OLE_TEST .
* 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: sheetname(10) value 'TEST ',c_row type i,
      scnt type i,
      val(20), wb(2).

parameters: p_exvis as checkbox default 'X',
            p_workbk(2) type p default '01',
            p_wsheet(2) type p default '01'.

CONSTANTS: OK TYPE I VALUE 0.
INCLUDE OLE2INCL.
DATA: EXCEL     TYPE OLE2_OBJECT,
      WORKBOOK  TYPE OLE2_OBJECT,
      SHEET     TYPE OLE2_OBJECT,
      CELL      TYPE OLE2_OBJECT,
      CELL1     TYPE OLE2_OBJECT,
      COLUMN    TYPE OLE2_OBJECT,
      RANGE     TYPE OLE2_OBJECT,
      BORDERS   TYPE OLE2_OBJECT,
      button    TYPE OLE2_OBJECT,
      int      TYPE OLE2_OBJECT,
      FONT      TYPE OLE2_OBJECT,
      ROW       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.

* create sheets and save
  perform sheet.

  perform save_book.
enddo.
write: ' Done'.

*---------------------------------------------------------------------*
*       FORM create_excel                                             *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form create_excel.
  CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

  if sy-subrc ne 0.
     write: / 'No EXCEL creation possible'.
     stop.
  endif.

  set property of EXCEL 'DisplayAlerts' = 0.

  CALL METHOD  OF EXCEL 'WORKBOOKS' = WORKBOOK .
*  Put Excel in background
  if p_exvis eq 'X'.
    SET PROPERTY OF EXCEL 'VISIBLE' = 1.
  else.
    SET PROPERTY OF EXCEL 'VISIBLE' = 0.
  endif.

*  Create worksheet
  set property of excel 'SheetsInNewWorkbook' = 1.
  call method of workbook   'ADD'.
endform.

*---------------------------------------------------------------------*
*       FORM save_book                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form save_book.
  get property of excel 'ActiveSheet' = sheet.
  free object sheet.
  free object workbook.

  GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK.
  call method of workbook 'SAVEAS' exporting #1 = p_fname #2 = 1.
  call method of workbook 'CLOSE'.
  call method of excel 'QUIT'.

  free object sheet.
  free object workbook.
  free object excel.
endform.

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

  do p_wsheet times.
    unpack sy-index to sheetname+5(2).

    if sy-index gt 1.
      CALL METHOD  OF EXCEL 'WORKSHEETS' = sheet.
      call method of sheet  'ADD'.
      free object sheet.
    endif.
    scnt = sy-index.
    call method  of excel     'WORKSHEETS' = SHEET EXPORTING #1 = scnt.
    call method  of sheet     'ACTIVATE'.
    SET PROPERTY OF SHEET     'NAME'       = sheetname.
    free object sheet.   "OK

    perform fill_sheet.
*
    CALL METHOD OF EXCEL 'Columns' = COLUMN.
    CALL METHOD OF COLUMN 'Autofit'.
    free object COLUMN.
*
*    call method of sheet 'BUTTON' = button.
*    call method of button 'ADD'.
*    set property of button 'fmButtonStyle' = 0.
*      exporting #1 = '96.75' #2 = '372' #3 = '123.75' #4 = '12'.
*    set property of button 'Characters' = 'ButtonTest'.

    free object button.
    free object font.
    free object int.
    free object cell.
    free object: cell1.
    free object range.
    free object borders.
    free object: column, row.
  enddo.

  free object font.
  free object int.
  free object cell.
  free object cell1.
  free object range.
  free object borders.
  free object column.
  free object row.
  free object sheet.
endform.

*---------------------------------------------------------------------*
*       FORM border                                                   *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  we                                                            *
*---------------------------------------------------------------------*
form border using we.
*left
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.
  set property of borders 'LineStyle' = '1'.
  set property of borders 'WEIGHT' = we.                    "4=max
  free object borders.
* right
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.
  set property of borders 'LineStyle' = '2'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* top
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.
  set property of borders 'LineStyle' = '3'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* bottom
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.
  set property of borders 'LineStyle' = '4'.
  set property of borders 'WEIGHT' = we.
*    set property of borders 'ColorIndex' = 'xlAutomatic'.
  free object borders.
endform.

*---------------------------------------------------------------------*
*       FORM border2                                                  *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  we                                                            *
*---------------------------------------------------------------------*
form border2 using we.
*left
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.
  set property of borders 'LineStyle' = '5'.
  set property of borders 'WEIGHT' = we.                    "4=max
  free object borders.
* right
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.
  set property of borders 'LineStyle' = '6'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* top
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.
  set property of borders 'LineStyle' = '7'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* bottom
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.
  set property of borders 'LineStyle' = '8'.
  set property of borders 'WEIGHT' = we.
*    set property of borders 'ColorIndex' = 'xlAutomatic'.
  free object borders.
endform.

*---------------------------------------------------------------------*
*       FORM border3                                                  *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  we                                                            *
*---------------------------------------------------------------------*
form border3 using we.
*left
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.
  set property of borders 'LineStyle' = '9'.
  set property of borders 'WEIGHT' = we.                    "4=max
  free object borders.
* right
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.
  set property of borders 'LineStyle' = '10'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* top
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.
  set property of borders 'LineStyle' = '11'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* bottom
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.
  set property of borders 'LineStyle' = '12'.
  set property of borders 'WEIGHT' = we.
*    set property of borders 'ColorIndex' = 'xlAutomatic'.
  free object borders.
endform.

*---------------------------------------------------------------------*
*       FORM fill_cell                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  color                                                         *
*  -->  pattern                                                       *
*---------------------------------------------------------------------*
form fill_cell using color pattern.
  call method of cell 'INTERIOR' = int.
  set property of int 'ColorIndex' = color.
  set property of int 'Pattern' = pattern.
  free object int.
endform.

*---------------------------------------------------------------------*
*       FORM font                                                     *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  bold                                                          *
*  -->  size                                                          *
*---------------------------------------------------------------------*
form font using bold size.
  call method  of CELL 'FONT' = font.
  set property of font 'BOLD' = bold.
  set property of font 'SIZE' = size.
  free object font.
endform.

*---------------------------------------------------------------------*
*       FORM fill_sheet                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form fill_sheet.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'A1'.
  perform font          using 1 '14'.
  SET PROPERTY OF CELL    'VALUE' = 'Counter'.
  perform fill_cell     using '15' '1'.
  perform border        using '2'.
  free object cell.

  val = 'Workbook-Count'.
  move wb to val+16.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'B1'.
  SET PROPERTY OF CELL    'VALUE' = val.
  perform fill_cell using '14' '1'.
  perform border using '4'.
  free object cell.

  val = 'Sheet-Count'.
  unpack sy-index to val+12.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'C1'.
  SET PROPERTY OF CELL    'VALUE' = val.
  perform fill_cell using '12' '1'.
  perform border using '4'.
  free object cell.

  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E3'.
  perform border        using '1'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E5'.
  perform border        using '2'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E7'.
  perform border        using '3'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E9'.
  perform border        using '4'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F3'.
  perform border2       using '1'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F5'.
  perform border2       using '2'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F7'.
  perform border2       using '3'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F9'.
  perform border2       using '4'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G3'.
  perform border3       using '1'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G5'.
  perform border3       using '2'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G7'.
  perform border3       using '3'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G9'.
  perform border3       using '4'.
  free object cell.

  val = 'ROW-Count'.

  do 19 times.
    c_row = sy-index + 1.
    unpack c_row to val+12(4).
    CALL METHOD  OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.
    SET PROPERTY OF CELL1    'VALUE' = val.
    free object cell1.
    CALL METHOD  OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.
    SET PROPERTY OF CELL1    'VALUE' = val.
    free object cell1.
  enddo.

endform.


*----------------------------------------------------------------------*
* You find SAP OLE programs under development Class 'SOLE'             *
*                                                                      *
*  MSTAPPL  Table Maintenance APPL                                     *
*  RSOLEDOC Document list                                              *
*  RSOLEIN0 OLE Load Type Information                                  *
*  RSOLEINT Type Info Loaded                                           *
*  RSOLETI0 OLE Object Browser                                         *
*  RSOLETI1 OLE Object Browser                                         *
*  RSOLETI2 OLE Object Browser                                         *
*  RSOLETI3 F4 Help For OLE Objects                                    *
*  RSOLETT1 OLE 2.0 Automation Demo Program                            *
*                                                                      *
* Transactions:                                                        *
* SOLE                                                                 *
* SOLO  - List of OLE applcations with loaded type info                *
*                                                                      *
*                                                                      *
* You will find the decription of possible objects and methods in the  *
* windows help file for excel.                                         *
*----------------------------------------------------------------------*