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.
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. " rowNow 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.
* 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.
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.
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’.
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.
Adding a button inside an ALV fields is a very simple task to do which needs only a few line of codes. what I always do is I create a field on ALV and populate that field with an Icon of my choice and declare it as a Hotspot so it will react on a single click and it works perfectly just like a button.
First Thing to do is to specify a field in our internal table that we will use to hold the Icon(refer to code #1).
TYPES: BEGIN OF t_alvbutton,
icon TYPE string, "icon field
kunnr LIKE kna1-kunnr,
name1 LIKE kna1-name1,
END OF t_alvbutton.
DATA: it_alvbutton TYPE STANDARD TABLE OF t_alvbutton,
wa_alvbutton TYPE t_alvbutton.Proceed to the data retrieval and populate the it_alvbutton. I assume you know how to retrieve data from database so there’s no need to show you the select statement for our example. After that, loop on every records of it_alvbutton to specify the Icon that we want to display(refer to code #2).
LOOP AT it_alvbutton INTO wa_alvbutton.
wa_alvbutton-icon = '@0X@'.
MODIFY it_alvbutton FROM wa_alvbutton.
ENDLOOP.
@0X@ is the code for print icon. For the complete list of icons, check it out here.
Now the important part is to define a field in ALV that will hold the icon. To show you how simple it is to do that, please refer to code #3.
DATA: ls_fieldcat TYPE slis_fieldcat_alv,
lt_fieldcat TYPE slis_t_fieldcat_alv.
DEFINE m_fieldcat.
add 1 to ls_fieldcat-col_pos.
ls_fieldcat-fieldname = &1.
ls_fieldcat-seltext_l = &2.
ls_fieldcat-outputlen = &3.
ls_fieldcat-hotspot = &4. "X to declare the field as a hotspot
ls_fieldcat-icon = &5. "X to declare the field as an icon
append ls_fieldcat to lt_fieldcat.
END-OF-DEFINITION.
m_fieldcat 'ICON' 'Print' '20' 'X' 'X'. "this specific field is a hotspot and an icon
m_fieldcat 'KUNNR' 'Customer No.' '10'.
m_fieldcat 'NAME1' 'Customer Name' '35'.
m_fieldcat . . .
m_fieldcat . . .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
. . .
. . .
The figure below shows an example of print icon as a button.
Now refer to code #4 to handle the command when a user clicks on the icon button.
FORM f_user_command USING ucomm LIKE sy-ucomm
v_selfld TYPE slis_selfield.
IF ucomm = '&IC1' AND v_selfld-fieldname = 'ICON'.
"code goes in here when user clicks on our print button
ENDIF.
Changing the color of an ALV row is very simple. First you have to add a field on your internal table that will holds the color attribute (Refer to the sample code below).
TYPES: BEGIN OF t_rowcolor,
column1 TYPE string,
column2 TYPE string,
column3 TYPE string,
rowcolor(4) TYPE c,
END OF t_rowcolor.
DATA: it_rowcolor TYPE STANDARD TABLE OF t_rowcolor,
wa_rowcolor TYPE t_rowcolor.
Next step is to Set the layout field for color attributes. In our case, that field is “rowcolor” (Refer to the sample code below).
DATA: gd_layout TYPE slis_layout_alv.
gd_layout-info_fieldname = ‘ROWCOLOR’.
Lastly, you have to populate the rowcolor field with the color attributes of your choice. You can do this when you loop at your internal table into your work area table (Refer to the sample code below).
LOOP AT it_row INTO wa_row.
wa_row-rowcolor = ‘C100′
MODIFY it_row FROM wa_row.
ENDLOOP.
In the code above, ‘C100′ is the color attribute. For the complete list of different color attributes, please refer to the figure below.
I created a simple ALV report displaying the data of my customized table through Function Module REUSE_ALV_GRID_DISPLAY. I also created a screen for the details view when you double click on a record from ALV. On this screen you could change the record details as you want and save it. When the user clicked on the save button, he will be automatically return to the ALV screen.
Now, what I want is when the user returns back from details view to ALV screen, I want the ALV to refresh automatically without clicking the refresh button on the application toolbar.
When I googled for the solution, I mostly came up with OOP(Object Oriented Programming) codes, wherein you declare an object with reference to the CL_GUI_ALV_GRID to create an ALV. In my case this is not applicable since I’ve already started my ALV code through function module. Fortunately I stumbled upon http://www.sap-basis-abap.com/abap/auto-refresh-alv-list.htm that provides a simple code for ALV auto refresh every 5 seconds.
In my case, I only have to add a a single line of codes in the form i used for the i_callback_user_command parameter of the REUSE_ALV_GRID_DISPLAY function module.
Below is my code for calling the REUSE_ALV_GRID_DISPLAY function module.
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
EXPORTING
i_callback_program = gd_repid
i_callback_pf_status_set = ‘F_STATUS’
i_callback_user_command = ‘F_USER_COMMAND’
is_layout = ls_layout
it_fieldcat = lt_fieldcat
i_callback_top_of_page = ‘TOP_OF_PAGE’
it_sort = lt_sort
TABLES
t_outtab = it_comm.
And below is the code for the form F_USER_COMMAND:
FORM f_user_command USING UCOMM LIKE SY-UCOMM v_selfld TYPE slis_selfield.
some codes here for processing the user command
PERFORM f_get_data. “codes for retrieving data for ALV
v_selfld-refresh = ‘X’. “codes I added for auto refresh
ENDFORM.
sdn blogger
sap blogger
Recent Comments