OLE : EXCEL.APPLICATION

[Former Member]

Hello All,

I am trying to download data to excel using EXCEL.APPLICATION.

But I am not able to find any place where I can see the all Attributes of Excel listed together.

Like if I want to format the cell of excel what Attribute is to be changed.

Please let me know in detail about EXCEL.APPLICATION, I have checked the threads but could not find any details on this.

Thanks a lot.

Ruchi

[balbino_soaresferreirafil]

Hi Ruchi,

try do it...

data: w_cell1 type ole2_object,

w_cell2 type ole2_object.

*Start of excel selection

call method of h_excel 'Cells' = w_cell1

EXPORTING

#1 = 1 "Line

#2 = 1. "Column

*End of excel selection

call method of h_excel 'Cells' = w_cell2

EXPORTING

#1 = 1 "Line

#2 = 50. "Column

call method of h_excel 'Range' = h_cell

EXPORTING

#1 = w_cell1

#2 = w_cell2.

*set property with you want.

get property of h_cell 'Font' = gs_font .

set property of gs_font 'Bold' = 1 .

Regards

Balbino

[Former Member]

Hi

See this code.. it has all the colors

<b>report  zkris_ole2.
 
type-pools ole2 .
data:  count type i,
       count_real type i,
       application type ole2_object,
       workbook type ole2_object,
       excel     type ole2_object,
       sheet type ole2_object,
       cells type ole2_object.
constants: row_max type i value 256.
data index type i.
 
 
data:
      h_cell        type ole2_object,        " cell
      h_f           type ole2_object,        " font
      h_int         type ole2_object,
      h_width       type ole2_object,
      h_columns     type ole2_object,
      h_rows        type ole2_object,
      h_font        type ole2_object,
      h_entirecol   type ole2_object.
.
 
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 .
 
set property of excel 'VISIBLE' = 1.
 
 
* creating workbook
set property of excel 'SheetsInNewWorkbook' = 1.
call method of workbook 'ADD'.
 
call method of excel 'WORKSHEETS' = sheet
  exporting
  #1 = 1.
 
set property of sheet 'NAME' = 'Color Palette'.
call method of sheet 'ACTIVATE'.
 
data: col type i value 1,
row type i value 2,
col1 type i value 2,
col_real type i value 1.
 
row = 1.
col = 2.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'No.'.
 
col = col + 1.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'Background'.
 
col = col + 1.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'Foreground with white background'.
 
col = col + 1.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'Foreground with black background'.
 
call method of excel 'Rows' = h_rows
  exporting
    #1 = '2:2'.
set property of h_rows 'WrapText' = 1.
 
col = 8.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'No.'.
 
col = col + 1.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'Background'.
 
col = col + 1.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'Foreground with white background'.
set property of h_cell 'Bold' = 1.
 
col = col + 1.
call method of excel 'Cells' = h_cell
  exporting
  #1 = row
  #2 = col.
set property of h_cell 'Value' = 'Foreground with black background'.
 
call method of excel 'Rows' = h_rows
  exporting
    #1 = '1:1'.
set property of h_rows 'WrapText' = 1.
get property of h_rows 'Font' = h_font.
set property of h_font 'Bold' = 1.
 
 
count = 1.
count_real = count.
row = 2.
col = 2.
do 56 times.
  perform write_num_and_color.
enddo.
 
 
* autofit
call method of excel 'Columns' = h_columns
  exporting
    #1 = 'B:K'.
get property of h_columns 'EntireColumn' = h_entirecol.
set property of h_entirecol 'Autofit' = 1.
 
 
 
*&---------------------------------------------------------------------*
*&      Form  write_num_and_color
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form write_num_and_color.
 
" write the color number
  index = row_max * ( row - 1 ) + col.
  call method of sheet 'Cells' = cells
    exporting
    #1 = index.
  set property of cells 'Value' = count_real.
 
" write the color as the background
  col = col + 1.
  call method of excel 'Cells' = h_cell
    exporting
    #1 = row
    #2 = col.
  get property of h_cell 'Interior'   = h_int.
  set property of h_int  'ColorIndex' = count_real.
 
" write the color as the foreground with a white background
  col = col + 1.
  call method of excel 'Cells' = h_cell
  exporting
    #1 = row
    #2 = col.
  set property of h_cell 'Value' = 'Sample Text'.
  get property of h_cell 'Font'    = h_f.
  set property of h_f 'ColorIndex' = count_real.
 
" write the color as the foreground with a black background
  col = col + 1.
  call method of excel 'Cells' = h_cell
  exporting
    #1 = row
    #2 = col.
  get property of h_cell 'Interior'   = h_int.
  set property of h_int  'ColorIndex' = 1.
  set property of h_cell 'Value' = 'Sample Text'.
  get property of h_cell 'Font'    = h_f.
  set property of h_f 'ColorIndex' = count_real.
 
  row = row + 1.
  col = col - 3.
  count = count + 1.
  if count = 29.
    count = 1.
    row = 2.
    col = col + 6.
  endif.
  count_real = count_real + 1.
 
endform.                    "write_num_and_color</b>

[Former Member]

Thanks a lot for above replies...but this is not exactly what I am looking for.

Basically I need to know whether there is some documentation available on EXCEL.APPLICATION where I can see the attributes available like 'Font', 'WrapText' , 'ColorIndex' etc. So that I can make use of them while downloading the data.

Thanks a lot.

[Former Member]

Hi, Ruchi !

As I understood, you mean Document Object Model help in Excel. Yuo can find it in Microsoft Excel Help (for example in MS Excel 2003) pressing F1, then Contents->Microsoft Excel Visual Basic Reference->Mcrosoft Excel Object Model. There you'll find a complete list of all the Classes, Collections with their methods and properties.

Regards,

Aleh Pratasenia