Tag Archive for 'EXCEL'

SAP: Export ALV to Excel File

Published
by
rs
on August 28, 2009
in ABAP, ALV, SAP and Technical
. 1 Comment


This tutorial will teach you how to export an ALV report to Microsoft Excel  file. This is intended for abap programmers who already have a knowledge in ALV reporting as I will not show here how to create an ALV.

Assuming you already have an ABAP program with ALV report, what you need to do is add a button or menu that will trigger the function that will export the ALV data to Excel file. but before that let’s start first with the declaration of types and variables that we’re going to use.

VARIABLE DECLARATION
 
TYPE-POOLS ole2.			 " we will use OLE objects
DATA: v_excel 	TYPE ole2_object,        " Excel object
      v_wrkbkl  TYPE ole2_object,        " list of workbooks
      v_wrkbk   TYPE ole2_object,        " workbook
      v_cell    TYPE ole2_object,        " cell
      v_font    TYPE ole2_object,        " font
      v_row	TYPE i.			 " row

Now we’re done with the variable declaration, let’s proceed to the main program. We will create 3 forms: One is for the preparation of the Excel file, second is for the data export from alv cells to excel cells, and third is for error handling.

The code below is for the preparation of excel application object.

EXPORT_TO_EXCEL
 
* it_main and wa_main is the tables used in your ALV
FORM export_to_excel TABLES p_it_main STRUCTURE wa_main.
 
* Create Excel object
  CREATE OBJECT v_excel 'EXCEL.APPLICATION'.
 
* Call Error_handler form
  PERFORM Error_Handler.
 
* Show excel window
  SET PROPERTY OF v_excel  'Visible' = 1.
 
* get list of workbooks, initially empty
  CALL METHOD OF v_excel 'Workbooks' = v_wrkbkl.
  PERFORM Error_Handler.
 
* add a new workbook to workbook list
  CALL METHOD OF v_wrkbkl 'Add' = v_wrkbk.
  PERFORM Error_Handler.
 
* export ALV column header to Excel
* PERFORM export_data USING rows columns boldfont value
  PERFORM export_data USING 1 1 1 'header_1'.
  PERFORM export_data USING 1 2 1 'header_2'.
  PERFORM export_data USING 1 3 1 'header_3'.
  PERFORM export_data USING 1 4 1 'header_4'.
  PERFORM export_data USING 1 5 1 'header_5'.
 
* Loop through ALV rows and fields and transfer data to Excel
  LOOP AT p_it_main INTO wa_main.
    v_row = sy-tabix + 1.
    PERFORM export_data USING v_row 1 0  wa_bill-field1.
    PERFORM export_data USING v_row 2 0  wa_bill-field2.
    PERFORM export_data USING v_row 3 0  wa_bill-field3.
    PERFORM export_data USING v_row 4 0  wa_bill-field4.
    PERFORM export_data USING v_row 5 0  wa_bill-field5.
  ENDLOOP.
  FREE OBJECT v_excel.
  PERFORM error_handler.
ENDFORM.

The next code is for the export_data form.

EXPORT_DATA
 
FORM export_data USING x y z val.
  CALL METHOD OF v_excel 'Cells' = v_cell
    exporting
    #1 = x
    #2 = y.
  PERFORM error_handler.
 
  SET PROPERTY OF v_cell 'Value' = val.
  PERFORM error_handler.
 
  GET PROPERTY OF v_cell 'Font' = v_font.
  PERFORM error_handler.
 
  SET PROPERTY OF v_font 'Bold' = z .
  PERFORM error_handler.
ENDFORM.

And for the code of error_handler form.

ERROR_HANDLER
 
FORM error_handler.
  IF sy-subrc  0.
    CALL METHOD OF v_excel 'QUIT'.
    FREE OBJECT v_excel.
    v_excel-handle = -1.
    MESSAGE: 'Failed to export to Excel.' TYPE 'E'.
  ENDIF.
ENDFORM.

Now that we’ve completed all the 3 forms, all you have to do now is call the export_to_excel form to initiate the export. In the example below, I call the export_to_excel form on user command ‘M_EXP’.

USER_COMMAND
 
FORM f_user_command USING p_ucomm LIKE sy-ucomm.
  CASE p_ucomm.
    WHEN 'M_EXP'.
      PERFORM export_to_excel TABLES it_main.
    WHEN 'CANCEL' OR 'BACK' OR 'EXIT'.
      LEAVE TO SCREEN 0.
  ENDCASE.
ENDFORM.

Creative Commons License