Solved: Report to Excel Output

Former Member · ‎03-19-2009

Hi All,

I have a requirement where the Report Output should be displayed directly in Excel.

I am using OLE objects for this.

Could anyone provide me with all the Properties of Excel which can be used in the coding.

For example, Setting Row Height, formatting the row and setting a pattern for it.

Setting Borders for cells/columns/rows etc.

Any suggestions/ideas provided would be of great help.

Thanks in advance.

Regards,

Adapala M.

I355602 · ‎03-19-2009

Hi,

Use FM

FTBU_START_EXCEL

MS_EXCEL_OLE_STANDARD_DAT

Use:


DATA: w_file      TYPE  rlgrap-filename VALUE 'it_value'.

  CALL FUNCTION 'FTBU_START_EXCEL'
    EXPORTING
      data_name           = w_file
      data_path_flag      = 'T'
*---Start Excel and not Wait
      wait                = ' '
    TABLES
      data_tab            = <itab>
    EXCEPTIONS
      no_batch            = 1
      excel_not_installed = 2
      wrong_version       = 3
      internal_error      = 4
      invalid_type        = 5
      cancelled           = 6
      download_error      = 7
      OTHERS              = 8.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
           WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

Hope this helps you.

Regards,

Tarun

I355602 · ‎03-19-2009

Hi,

Use FM

FTBU_START_EXCEL

MS_EXCEL_OLE_STANDARD_DAT

Use:


DATA: w_file      TYPE  rlgrap-filename VALUE 'it_value'.

  CALL FUNCTION 'FTBU_START_EXCEL'
    EXPORTING
      data_name           = w_file
      data_path_flag      = 'T'
*---Start Excel and not Wait
      wait                = ' '
    TABLES
      data_tab            = <itab>
    EXCEPTIONS
      no_batch            = 1
      excel_not_installed = 2
      wrong_version       = 3
      internal_error      = 4
      invalid_type        = 5
      cancelled           = 6
      download_error      = 7
      OTHERS              = 8.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
           WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

Hope this helps you.

Regards,

Tarun

Former Member · ‎03-19-2009

Hi,

The following SAP tables are associated with OLE

● TOLE OLE Applications

● OLELOAD OLE type Information load

● SWOTOLE Workflow Object Types OLE Applications

● SWOTTOLE Workflow Object Types Texts OLE Applications

● TOLET Workflow Object Types Texts OLE Applications

The following ABAP key words control the applications:

● CREATE OBJECT

● SET PROPERTY

● GET PROPERTY

● CALL METHOD

● FREE OBJECT

Hope this helps you.

Regards,

Manoj Kumar P

Edited by: Manoj Kumar on Mar 19, 2009 11:32 AM

Former Member · ‎03-19-2009

Hi,

The following properties can be used using OLE for excel download:

Create EXCEL.

CREATE OBJECT ob_l_excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

SET PROPERTY OF ob_l_excel 'DisplayAlerts' = 0.

Create Workbook

CALL METHOD OF ob_l_excel 'WORKBOOKS' = ob_l_workbook .

SET PROPERTY OF ob_l_excel 'VISIBLE' = 1.

Create Worksheet

SET PROPERTY OF ob_l_excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF ob_l_workbook 'ADD'.

to set properties at the time of display.

CALL METHOD OF ob_l_excel 'cells' = ob_l_cell

EXPORTING

#1 = row

#2 = col.

SET PROPERTY OF ob_l_cell 'value' = value.

GET PROPERTY OF ob_l_cell 'font' = ob_l_font.

SET PROPERTY OF ob_l_font 'bold' = bold.

CALL METHOD OF ob_l_excel 'columns' = ob_l_column.

CALL METHOD OF ob_l_column 'autofit'.

Former Member · ‎03-19-2009

Hi Adap,

You can refer t-code DWDM, this is a set of demos and you can find excel OLE related beautifully explained in this.

SR

Former Member · ‎03-19-2009

Hi,

Kindly go through this sample code below:






INCLUDE ole2incl.                       "include used for providing classes used for using create object for creating application and worksheets
DATA: application TYPE ole2_object,
       workbook TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

*DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
*DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
*


TABLES: vbap,mara.


TYPES: BEGIN OF itab,
       lifnr TYPE lfa1-lifnr,
       land1 TYPE lfa1-land1,
*       name1 TYPE lfa1-name1,
*       ort01 TYPE lfa1-ort01,
       END OF itab.



DATA: BEGIN OF itab2 OCCURS 0,
matnr TYPE mara-matnr,
ersda TYPE mara-ersda,
ernam TYPE mara-ernam,
END OF itab2.
 data : v_row type sy-tabix.

DATA: itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.

DATA: IT_XLSTAB TYPE STANDARD TABLE OF ITAB ,
      WA_XLSTAB LIKE LINE OF IT_XLSTAB.

START-OF-SELECTION.

  SELECT lifnr land1 fROM lfa1  INTO CORRESPONDING FIELDS OF TABLE itab1 UP TO 5 ROWS.


  SELECT matnr
  ersda
  ernam
  FROM mara
  INTO CORRESPONDING FIELDS OF
  TABLE itab2 UP TO 5 ROWS.



************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.

  CREATE OBJECT application 'excel.application'.
  SET PROPERTY OF application 'visible' = 1.
  CALL METHOD OF application 'Workbooks' = workbook.
  CALL METHOD OF workbook 'Add'.

* Create first Excel Sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'Sheet1'.
  clear v_row.
  LOOP AT itab1.
     v_row = sy-tabix.
      perform fill_cell  using  v_row 1  itab1-lifnr.
      perform fill_cell  using  v_row 2 itab1-land1.
  ENDLOOP.

* Create second Excel sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 2.
  SET PROPERTY OF sheet 'Name' = 'Sheet2'.
  CALL METHOD OF sheet 'Activate'.
  clear v_row.
  LOOP AT itab2.
     v_row = sy-tabix.

      perform fill_cell using  v_row 1 itab2-matnr.
      perform fill_cell using  v_row 2 itab2-ersda.
      perform fill_cell using  v_row 3 itab2-ernam.

  ENDLOOP.


* Save excel speadsheet to particular filename
  CALL METHOD OF sheet 'SaveAs'
                  EXPORTING #1 = 'c:\temp\excelgeet.xls'     "filename
                            #2 = 1.                          "fileFormat

*  Closes excel window, data is lost if not saved
  SET PROPERTY OF application 'visible' = 0.

*  call method of sheet 'CLOSE'
*
*  EXPORTING #1 = 'YES'.


*&---------------------------------------------------------------------*
*& both the below coding closes the apllication permanently from the task manager also.
*&---------------------------------------------------------------------*

SET PROPERTY OF application 'DisplayAlerts' = 0.
   free OBJECT application.


**  call method of application 'QUIT'.
**
**  FREE OBJECT: APPLICATION,
**               SHEET.

*&---------------------------------------------------------------------*
*&      Form  fill_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_V_COL  text
*      -->P_ITAB1_LIFNR  text
*----------------------------------------------------------------------*
form fill_cell  using row  col val.

    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = row  #2 = col.
    SET PROPERTY OF cells 'Value' = val.


endform.                    " fill_cell

This is giving the two internal table data output in one excel files 2 sheets.

Hope it helps

Regards

Mansi

Former Member · ‎03-19-2009

Hi,

You can take help from this code.

Create OLE ojbect for Excel app

CREATE OBJECT gh_excel co_xl_app.

PERFORM err_handle.

Set invisalbe for the app

SET PROPERTY OF gh_excel co_xl_visible = 0.

PERFORM err_handle.

get list of workbooks, initially empty

CALL METHOD OF gh_excel co_xl_book = gh_mapl.

PERFORM err_handle.

add a new workbook

CALL METHOD OF gh_mapl co_xl_add = gh_map.

PERFORM err_handle.

Write Dealer Name label

PERFORM fill_cell

USING lv_row_idx lv_col_idx 1 text-t04.

*Set grid lines for the downloaded excel file

*the range start from A1 cell and end with N column and the total rows

CLEAR lv_str_temp.

lv_str_temp = gv_tot_rows.

CONDENSE lv_str_temp.

lv_range_begin = 'A3'.

lv_range_end = 'M'.

CONCATENATE lv_range_end lv_str_temp INTO lv_range_end.

CONDENSE lv_range_end.

CALL METHOD OF gh_excel co_xl_range = gh_range

NO FLUSH

EXPORTING #1 = lv_range_begin

#2 = lv_range_end.

PERFORM err_handle.

left edge border

CALL METHOD OF gh_range co_xl_borders = gh_borders

NO FLUSH

EXPORTING #1 = co_xl_edge_left.

PERFORM err_handle.