The other day the Suit asked to do an Excel report. Piece of cake I think…Well…That wasn’t true…He wanted titles, background colors, bold font and some structure on the file.
We all know that when we download an Excel report usign ABAP, no matter what we do, we’re going to always have the same boring structure. No colors and no fancy stuff.
Of course, I think about Excel OLE…But haven’t work with it, so I browsed SCN to try to find something good to learn. I land to Manipulate Excel with OLE & ABAP written by my good friend Rich Heilman .
The blog is really great but lack of some important things that I needed for my report…The file needed to be available of the system and he wasn’t any colors of font enhancements…Sure, he was just learning how to use the tools at that time, so more than a critic, I think it’s really cool that he shared his knowledge while he was still learning.
Anyway…I keep browsing the forums and found all the needed info to finish my report. After that I decided to build a small and simple example, showing what you can do with Excel OLE and ABAP.
&—-
*& Report ZDUMMY_SANDBOX *
&—-
REPORT zdummy_sandbox.
&—-
-
INCLUDES *
&—-
INCLUDE ole2incl.
&—-
*& TYPES *
&—-
TYPES: BEGIN OF ty_spfli,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
countryfr TYPE spfli-countryfr,
cityfrom TYPE spfli-cityfrom,
airpfrom TYPE spfli-airpfrom,
countryto TYPE spfli-countryto,
cityto TYPE spfli-cityto,
airpto TYPE spfli-airpto,
END OF ty_spfli.
TYPES: BEGIN OF ty_titles,
title(20) TYPE c,
field(20) TYPE c,
END OF ty_titles.
&—-
*& INTERNAL TABLES *
&—-
DATA: t_spfli TYPE STANDARD TABLE OF ty_spfli,
t_titles TYPE STANDARD TABLE OF ty_titles.
&—-
*& FIELD-SYMBOLS *
&—-
FIELD-SYMBOLS: TYPE ANY.
&—-
*& VARIABLES *
&—-
DATA: w_tabix TYPE sy-tabix,
w_titles TYPE sy-tabix,
w_line TYPE sy-tabix,
w_field TYPE string,
filename TYPE string,
path TYPE string,
fullpath TYPE string.
DATA: data_titles TYPE REF TO data.
DATA: e_sheet TYPE ole2_object,
e_activesheet TYPE ole2_object,
e_newsheet TYPE ole2_object,
e_appl TYPE ole2_object,
e_work TYPE ole2_object,
e_cell TYPE ole2_object,
e_color TYPE ole2_object,
e_bold TYPE ole2_object.
&—-
*& SELECTION-SCREEN *
&—-
SELECTION-SCREEN BEGIN OF BLOCK b1.
PARAMETERS: p_file TYPE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK b1.
&—-
*& START-OF-SELECTION *
&—-
START-OF-SELECTION.
PERFORM get_titles.
PERFORM get_data.
PERFORM create_excel.
&—-
*& AT SELECTION-SCREEN *
&—-
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = ‘Select archivo’
default_extension = ‘xls’
file_filter = ‘*.xls’
CHANGING
filename = filename
path = path
fullpath = fullpath.
IF sy-subrc EQ 0.
p_file = fullpath.
ENDIF.
&—-
*& Form get_titles *
&—-
FORM get_titles.
CREATE DATA data_titles TYPE ty_titles.
ASSIGN data_titles->* TO TO t_titles.
ENDFORM. “get_titles
&—-
*& Form get_data *
&—-
FORM get_data.
SELECT carrid connid countryfr cityfrom
airpfrom countryto cityto airpto
INTO TABLE t_spfli
FROM spfli
WHERE carrid EQ ‘LH’.
ENDFORM. ” get_data
&—-
*& Form create_excel *
&—-
FORM create_excel.
w_line = 1.
CREATE OBJECT e_appl ‘EXCEL.APPLICATION’.
SET PROPERTY OF e_appl ‘VISIBLE’ = 1.
CALL METHOD OF e_appl ‘WORKBOOKS’ = e_work.
CALL METHOD OF e_work ‘Add’ = e_work.
GET PROPERTY OF e_appl ‘ActiveSheet’ = e_activesheet.
SET PROPERTY OF e_activesheet ‘Name’ = ‘Flights’.
LOOP AT t_spfli ASSIGNING
I’m so glad you like it -:) I now following on Twitter by the way -;)
Greetings,
Blag.
Sure, cell by cell got serious performance issues…But according to what the suit asked me to do, that was the best way to do it…Also, you’re not going to use this example for by say 4K rows, right? -;)
Greetings,
Blag.
Hola Blag,See you on twitter 😉
Thanks for posting, we are ABAP learning beginners and I showed your code to my buds and we think it is cool what you can do with SAP.
Thanks for sharing.
This is most weird comment I ever get…
Greetings,
Blag.
The same kind of formatted excels can be achieved with help of XML transformation.
Check this blog: https://weblogs.sdn.sap.com/pub/wlg/13092
Regards,
Naimesh Patel
Already read your blog and post a comment…Really cool stuff…I’m really excited about the fact that after Rich’s blog…I posted a blog, and then Michael post a blog and now you are doing your own…It’s just great to learn that we had so many options to achieve the same goal. -:)
Greetings,
Blag.
http://help.sap.com/saphelp_nw70/helpdata/EN/e9/0be775408e11d1893b0000e8323c4f/frameset.htm
I am doing a program by integrating ABAP with EXCEL, i need to write the following code in ABAP which is recorded by macro.
I need to change the Category Type of the AXIS from Default to Text.
“ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale”
Thanks,
Venky