Regading Excel Creation using OLE

[Former Member]

hi friends,

i got a new requirement where i have to create the Excel document using OLE. one more thing is some cells has to be filled with some colour and in some cells the text has to come in Vertical manner, this text is used for Header purpose.

please help me urgently...

Suitable Answers will get more points..........

[Former Member]

Check this thread. U have sample program.

Regards,

Maha

[former_member189059]

Hello,

Also take a look at this code

*&---------------------------------------------------------------------*
*& Report  ZKRIS_OLE3_PALETTE
*&
*&---------------------------------------------------------------------*
*& Displays the full OLE color range in excel
*&
*&---------------------------------------------------------------------*

REPORT  ZKRIS_OLE3_PALETTE.

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. " change to 16384 for excel 2007
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.
DATA: h_range       TYPE ole2_object.
DATA: h_merge       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 = 3.
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 = 9.
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 = 3.
DO 56 TIMES.
  PERFORM write_num_and_color.
ENDDO.


* autofit
CALL METHOD OF excel 'Columns' = h_columns
  EXPORTING
    #1 = 'C:L'.
GET PROPERTY OF h_columns 'EntireColumn' = h_entirecol.
SET PROPERTY OF h_entirecol 'Autofit' = 1.


* write palette on lhs

*range
CALL METHOD OF excel 'Range' = h_range
  EXPORTING
    #1 = 'A2'
    #2 = 'A20'.

CALL METHOD OF h_range 'Merge' = h_merge .

CALL METHOD OF excel 'Cells' = h_cell
  EXPORTING
    #1 = 2
    #2 = 1.
SET PROPERTY OF h_cell 'Value' = 'Palette'.
SET PROPERTY OF h_cell 'Orientation' = 90.         "angled.
SET PROPERTY OF h_cell 'HorizontalAlignment' = 3.  "center align
GET PROPERTY OF h_cell 'Font'    = h_f.
SET PROPERTY OF h_f 'Bold' = 1.                    "bold
SET PROPERTY OF h_f 'Name' = 'Comic Sans MS'.
SET PROPERTY OF h_f 'Size' = '14'.
SET PROPERTY OF h_cell 'VerticalAlignment' = 2.  "center align

* autofit
CALL METHOD OF excel 'Columns' = h_columns
  EXPORTING
    #1 = 'A:A'.
GET PROPERTY OF h_columns 'EntireColumn' = h_entirecol.
SET PROPERTY OF h_columns 'ColumnWidth' = 4.


*&---------------------------------------------------------------------*
*&      Form  write_num_and_color
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM write_num_and_color.

  index = row_max * ( row - 1 ) + col.
  CALL METHOD OF sheet 'Cells' = cells
    EXPORTING
      #1 = index.
  SET PROPERTY OF cells 'Value' = count_real.

  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.

  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.

  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