SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

OLE Automation in ABAP



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Fri Apr 04, 2008 4:57 pm    Post subject: OLE Automation in ABAP Reply with quote

OLE Automation in ABAP
Original: http: //abaplovers.blogspot.com/2008/02/ole-automation-in-abap-part1.html
Transactions
IOLE
ISOLE

Run Transaction OLE the following screen will be displayed.
In the above transaction you can start and stop each application to check if it has been
registered.
Run transaction SOLE to get a list of all the OLE applications registered in your
system. You can maintain these applications here.
The above applications are stored in the table TOLE.
The following SAP tables are associated with OLE
ITOLE OLE Applications
IOLELOAD OLE type Information load
ISWOTOLE Workflow Object Types OLE Applications
ISWOTTOLE Workflow Object Types Texts OLE Applications
ITOLET Workflow Object Types Texts OLE Applications

The following ABAP key words control the applications:
ICREATE OBJECT
ISET PROPERTY
IGET PROPERTY
ICALL METHOD
IFREE OBJECT

The Desktop application serves as the OLE server to the calling ABAP program. For
example when the ABAP program makes calls to the OLE application the SAPGUI servers as the client.
The create statement generates the object of this class. The following return code values can be encountered.
SY-SUBRC = 0:
Object successfully generated.
SY-SUBRC = 1:
SAPGUI communication error.
SY-SUBRC = 2:
SAPGUI function call error. The frontend ports of SAPs OLE implementation modules are implemented only under Windows and Apple Macintosh.
SY-SUBRC = 3:
The OLE-API call resulted in an error - possibly a storage space problem.
SY-SUBRC = 4:
The object is not registered with SAP.
Note that for each OLE object there has to be a variable holding handle data for that object.
The type-pool ole2 defines the handle variable data of the type ole2_object.
For all the OLE automation programs <OLE2INCL> include should be used.
Please find below some examples of setting the properties of fonts, cell borders and colors.

Font Properties.
SET PROPERTY OF name_font 'Name' = 'Times New Roman' .
SET PROPERTY OF size_font 'Size' = '12' .
SET PROPERTY OF bold_font 'Bold' = '0' . "Not bold
SET PROPERTY OF Italic_font 'Italic' = '0' . "Not Italic
SET PROPERTY OF underline_font 'Underline' = '0' . "Not underlined

Paragraph Formatting
SET PROPERTY OF allignment_parformat 'Alignment' = '3' . "Justified
DATA: d_excel TYPE ole2_object ,
d_cell1 TYPE ole2_object ,
d_cell2 TYPE ole2_object ,
d_cells TYPE ole2_object ,
d_range TYPE ole2_object ,
d_font TYPE ole2_object ,
d_interior TYPE ole2_object ,
d_columns TYPE ole2_object ,
d_charts TYPE ole2_object ,
d_chart TYPE ole2_object ,
d_charttitle TYPE ole2_object ,
d_charttitlech TYPE ole2_object ,
d_chartob TYPE ole2_object .

Sample code
CREATE OBJECT d_excel 'EXCEL.APPLICATION' .
SET PROPERTY OF d_excel 'Visible' = 1 .
GET PROPERTY OF d_excel 'Workbooks' = gs_wbooklist .

Formatting the Excel Cells
GET PROPERTY OF d_cell1 'Font' = d_font .
SET PROPERTY OF d_font 'Underline' = 2 .
SET PROPERTY OF d_font 'Bold' = 1 .
SET PROPERTY OF d_cell1 'HorizontalAlignment' = -4108 .
GET PROPERTY OF d_cell1 'Interior' = d_interior .
SET PROPERTY OF d_interior 'ColorIndex' = 15 . >>>>>>>>>> Check in the diagram given below
SET PROPERTY OF d_interior 'Pattern' = -4124 .
SET PROPERTY OF d_interior 'PatternColorIndex' = -4105 .
Color code for ABAP is shown below, please use the numeric value as given in the figure below. For example if
you want the interior color of the Excel cell to be of the color Cyan then use the code 8.
Color code for ABAP is shown below, please use the numeric value as given in the figure below.
The following program transfers values from SAP to EXCEL with formating.
Code:

REPORT ZEXOLE2.
parameters: p_file like RLGRAP-FILENAME
            default 'C:\exceldata\Customerdata.xls'.
data: d_file like p_file,
      d_exsheet(10) value 'Customers',c_row type i,
      d_scnt type i,
      d_val(20),
      d_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,
      ole_book    TYPE ole2_object.
do p_workbk times.
  move p_file to d_file.
  unpack sy-index to d_wb.
  replace 'NN' with d_wb into d_file.
*
  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_file #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 d_exsheet+5(2).
    if sy-index gt 1.
      CALL METHOD  OF EXCEL 'WORKSHEETS' = sheet.
      call method of sheet  'ADD'.
      free object sheet.
    endif.
    d_scnt = sy-index.
    call method  of excel     'WORKSHEETS' = SHEET EXPORTING #1 = d_scnt
.
    call method  of sheet     'ACTIVATE'.
    SET PROPERTY OF SHEET     'NAME'       = d_exsheet.
    free object sheet.   "OK
    perform fill_sheet.
*
    CALL METHOD OF EXCEL 'Columns' = COLUMN.
    CALL METHOD OF COLUMN 'Autofit'.
    free object COLUMN.
*
    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' = '10'.
  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 '10'.
  SET PROPERTY OF CELL    'VALUE' = 'Counter'.
  perform fill_cell     using '20' '1'.
  perform border        using '2'.
  free object cell.
  d_val = 'Workbook-Count'.
  move d_wb to d_val+16.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'B1'.
  SET PROPERTY OF CELL    'VALUE' = d_val.
  perform fill_cell using '14' '1'.
  perform border using '4'.
  free object cell.
  d_val = 'Sheet-Count'.
  unpack sy-index to d_val+12.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'C1'.
  SET PROPERTY OF CELL    'VALUE' = d_val.
  perform fill_cell using '21' '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.
  d_val = 'ROW-Count'.
  do 19 times.
    c_row = sy-index + 1.
    unpack c_row to d_val+12(4).
    CALL METHOD  OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.
    SET PROPERTY OF CELL1    'VALUE' = d_val.
    free object cell1.
    CALL METHOD  OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.
    SET PROPERTY OF CELL1    'VALUE' = d_val.
    free object cell1.
  enddo.
endform.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.