Can we generate output of a report in an Excel Sheet?

[Former Member]

Hi All,

Can anyone tell me how to generate output of a report in an Excel Sheet format?

Thanks in advance,

Jasmine.

[Former Member]

You can create EXcel from internal table using this code...

parameters: p_file like rlgrap-filename default 'c:tmptest.xls'.
data: itab like t001 occurs 0 with header line.
select * from t001 into table itab.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
  EXPORTING
    I_FILENAME                 = p_file
  TABLES
    I_TAB_SAP_DATA             = itab

.

[Former Member]

HI Jasmine,

WELCOME TO SDN...

Use GUI_DOWNLOAD , format - .xls ,give that Table - alv output internal table name

Regards,

Santosh

[Former Member]

hi,

try this sample...

data: begin of itab occurs 0,
      vbeln like vbak-vbeln,
      posnr like vbap-posnr,
      end of itab.
select vbeln
       posnr
       from vbap
       up to 20 rows
       into table itab.

* EXCEL sheet using OLE automation.
INCLUDE OLE2INCL.
* handles for OLE objects
DATA: H_EXCEL TYPE OLE2_OBJECT,        " Excel object
      H_WORK  TYPE OLE2_OBJECT,
      H_SHEET TYPE OLE2_OBJECT,
      H_CELL  TYPE OLE2_OBJECT,
      V_COL   LIKE SY-TABIX.     " column number of the cell
DATA:
  V_STEP(30),
  V_FILE LIKE RLGRAP-FILENAME.


* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Creating Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* start Excel
  V_STEP = 'Starting Excel'.
  CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
  PERFORM ERR_HDL.

  SET PROPERTY OF H_EXCEL  'Visible' = 1.

  CALL METHOD OF H_EXCEL 'APPEND'
    EXPORTING  #1 = 'D:SappdfABAP Trainingsheettr.xls'.

*  PERFORM ERR_HDL.
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Adding Data to Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* Get the list of workbooks
  V_STEP = 'Preaparing Excel'.
  CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_WORK.
  PERFORM ERR_HDL.

** Add new workbook (create a file)
  CALL METHOD OF H_WORK 'ADD'.
  PERFORM ERR_HDL.
* Get the created worksheet

************************Sheet Number

  CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_SHEET EXPORTING #1 = 3.

************************Sheet Number
  PERFORM ERR_HDL.
* Activate (select) the first sheet
  CALL METHOD OF H_SHEET 'ACTIVATE'.
  PERFORM ERR_HDL.


* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Adding Data to Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* output column headings to active Excel sheet
  V_STEP = 'Adding data to Excel'.
  LOOP AT ITAB.
    V_COL = SY-TABIX.
    PERFORM FILL_CELL USING 1 V_COL ITAB-vbeln.
    PERFORM FILL_CELL USING 2 V_COL ITAB-posnr.
  ENDLOOP.
  V_STEP = 'Releasing Excel'.
  FREE OBJECT H_EXCEL.
  PERFORM ERR_HDL.
  H_EXCEL-HANDLE = -1.

*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*----------------------------------------------------------------------*
FORM ERR_HDL.

  IF SY-SUBRC <> 0.
    WRITE: / 'Error in processing Excel File:', V_STEP.
    STOP.
  ENDIF.

ENDFORM.                    " ERR_HDL
*&---------------------------------------------------------------------*
*&      Form  FILL_CELL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_1      text
*      -->P_1      text
*----------------------------------------------------------------------*
FORM FILL_CELL USING  ROW COL VAL.
  CALL METHOD OF H_EXCEL 'Cells' = H_CELL
                 EXPORTING #1 = ROW #2 = COL.
  PERFORM ERR_HDL.
  SET PROPERTY OF H_CELL 'Value' = VAL .
  PERFORM ERR_HDL.
ENDFORM.                    " FILL_CELL

regards

satesh

[Former Member]

Hai Jasmine,

If at all you are using ALV you need to do this, you click on the export button, there you have option to save it as spread sheet.

If not,You can use GUI_DOWNLOAD and pass the final internal table to download to Excel format.

Simple way could be to use the output menu and use list download to desired format.

Regards,

Srikanth.

Reward points if helpful.

[vinod_gunaware2]

Hi

RH_START_EXCEL_WITH_DATA starts Excel with the contents of an internal table. This function finds Excel in the desktop registry. It also uses a local PC working directory to save the file (that's what the 'W' value for data path flag does). Very transparent to user!

SAP_CONVERT_TO_XLS_FORMAT Convert data to Microsoft Excel format.

WS_EXCEL Start EXCEL on the PC

Regards

vinod

[Former Member]

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = 'D:\abc.xls'

FILETYPE = 'ASC'

WRITE_FIELD_SEPARATOR = 'X'

tables

data_tab = itab

.

this will help u..

if want to display that excel sheet then use following function of a object

...

CALL METHOD CL_GUI_FRONTEND_SERVICES=>EXECUTE

EXPORTING

DOCUMENT = 'D:\abc.xls'

OPERATION = 'OPEN'

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

BAD_PARAMETER = 3

FILE_NOT_FOUND = 4

PATH_NOT_FOUND = 5

FILE_EXTENSION_UNKNOWN = 6

ERROR_EXECUTE_FAILED = 7

SYNCHRONOUS_FAILED = 8

NOT_SUPPORTED_BY_GUI = 9

OTHERS = 10.

IF SY-SUBRC <> 0.

ENDIF.

[Former Member]

Hi,

You can use the function module MS_EXCEL_OLE_STANDARD_DAT.

For example:

       CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'
           EXPORTING
                FILE_NAME                 = W_EXCEL_FNAME "Path and filename
                CREATE_PIVOT              = 0
                DATA_SHEET_NAME           = W_EXCEL_FNAME "Name of worksheet tab
           TABLES
                DATA_TAB                  = T_DATA "Internal table
                FIELDNAMES                = T_FIELDS "Column titles
           EXCEPTIONS
                FILE_NOT_EXIST            = 1
                FILENAME_EXPECTED         = 2
                COMMUNICATION_ERROR       = 3
                OLE_OBJECT_METHOD_ERROR   = 4
                OLE_OBJECT_PROPERTY_ERROR = 5
                INVALID_FILENAME          = 6
                INVALID_PIVOT_FIELDS      = 7
                DOWNLOAD_PROBLEM          = 8
                OTHERS                    = 9.
      IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE 'S' NUMBER SY-MSGNO
            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
        EXIT.
      ENDIF.

Here T_DATA is the internal table which contains the data to be sent to MS Excel. The titles of the column can be specified in table FIELDNAMES e.g.

DATA: BEGIN OF T_FIELDS OCCURS 0,
        FNAME(50) TYPE C,
      END OF T_FIELDS.

If you pass the tables parameter for FIELDNAMES, you can skip some columns by not specifying the heading.

Hope this helps.

Riyaz