According to my experience most of the users are very happy about the MS Excel outputs (it is particularly useful if you want them to be able to change the output what would be a little uneasy for example with the Adobe form etc.).
You can understand the basics of "printing" to Excel using ABAP OLE automation from many better or worse documents, but for me the most valuable is the document by
Serdar SIMSEKLER called
An Easy Reference for OLE Automation . It covers not only Excel but also the Word output using ABAP OLE.
But the result from the provided codes was not as good as it could be if one spends some time tuning the output (no offense, author has done a great job!!). That is the reason to write this blog: give you the brief but more detailed guide for ABAP OLE Excel output.
Using this ABAP OLE Excel approach you have these two options:
- 1) Start with the Excel document from scratch
- 2) Use prepared template (created and edited in SAP transaction OAOR)
- (and two extra options of Excel output from SAP at the end)
General notes:
- You can only use this approach if MS Excel is installed, of course.
- You cannot use this approach if you don´t use the standard SAP GUI but for example Web GUI (that is understandable because how could the Excel catch any hook from the browser).
- This is a frontend feature!! You cannot do this in background.
Option 1: Start document from scratch
When you start the document from scratch, then as a result of your function module/ report the Excel is started on your client machine and the data are filled in. Most of the program structure you can get from the document mentioned above.
But you will probably need some more information about the OLE objects/ attributes/ values used in the code. And you will find there are not many SAP-related sources for this information. But don´t be sad. You can find pieces all around the SDN forums (ABAP general mostly) for example. I can recommend you to look not for SAP-related content but source codes for Perl.
You´ll find Perl code like:
$workbook = $excel -> Workbooks -> Add;
$sheet = $workbook -> Worksheets("Sheet1");
$sheet -> Activate;
$workbook = $excel -> Workbooks -> Open("$file_name");
$sheet = $workbook -> Worksheets(1) -> {Name};
$sheet = $workbook -> Worksheets($sheet);
$sheet -> Activate;
$excel -> Windows("Book1") -> Activate;
$workbook = $excel -> Activewindow;
$sheet = $workbook -> Activesheet;
This code was found on this page:
http://www.tek-tips.com/faqs.cfm?fid=6715
If you examine the ABAP OLE Excel source code you will find very similar structure. You only use GET PROPERTY and SET PROPERTY etc.
Compare with ABAP OLE Excel:
CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .
SET PROPERTY OF gs_excel 'Visible' = 1 .
GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .
GET PROPERTY OF gs_wbooklist 'Application' = gs_application .
SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1 .
CALL METHOD OF gs_wbooklist 'Add' = gs_wbook .
You will probably need few more simple things to make your Excel output really beautiful as I mentioned at the beginning (and you can find most of these in the above mentioned Perl code):
- Changing column width (e.g.: $sheet -> Range('A:A') -> {ColumnWidth} = 10;)
- Formatting the cell value (e.g.: $sheet -> Range("G7:H7") -> {NumberFormat} = "@";)
- Maybe formulas (e.g.: $sheet -> Range("A3") -> {FormulaR1C1} = "=SUM(R[-2]C:R[-1]C)";)
- (others, like graphs drawing, color filling, borders drawing etc. can be found all around the SDN and this is not a ABAP OLE Excel complete description)
You transfer this code like this (cell value format example):
CALL METHOD OF gs_excel 'Columns' = lv_columns
EXPORTING
#1 = 'A:A'.
SET PROPERTY OF lv_columns 'NumberFormat' = '@'. „format as plain text
Or change the format from '@' to for example '#,##0.00' for currency value or 'dd.mm.yyyy' for date.
I hope this shows step by step both how to find the Excel OLE objects, attributes and values and how to "transform" them into ABAP.
Option 2: Use the template
Note: In my opinion this option is good when you have a fixed layout (for example you have a paper form template, you want to redraw in Excel and fill with SAP ABAP data).
Start transaction OAOR.
Use classname = 'ALVLAYOUTTEMPLATES' and classtype = 'OT' for example.
Here you can create a new Excel template. The Excel window will be displayed on the right, where you can change the template: add texts, add pictures, format fields etc.
In you ABAP coding you ask the user where to save the file, next call the template (lv_object_name is your excel template name in OAOR) and then you fill the data into this template (sometimes it is useful to WAIT UP TO 3 SECONDS before filling the data):
DATA: lt_files TYPE sbdst_files.
CALL METHOD cl_bds_document_set=>get_with_files
EXPORTING
classname = 'ALVLAYOUTTEMPLATES'
classtype = 'OT'
object_key = lv_object_key
CHANGING
files = lt_files
EXCEPTIONS
nothing_found = 1
error_kpro = 2
internal_error = 3
parameter_error = 4
not_authorized = 5
not_allowed = 6
OTHERS = 7.
Use this code to open the template instead of creating a new one!!:
CREATE OBJECT lv_appl 'EXCEL.APPLICATION'.
SET PROPERTY OF lv_appl 'VISIBLE' = 1.
CALL METHOD OF lv_appl 'WORKBOOKS' = lv_workbook.
CALL METHOD OF lv_workbook 'OPEN'
EXPORTING
#1 = iv_file. „the filename you used to save the template
(extra): For lazy consultants
Search for the information about ALV Excel in-place. It lets the user to use standard functionality of SAP ALV: to view the data in Excel displayed in the ALV window and quickly copy out the displayed data. If this is not yet configured in your system, note it can be tricky and there are some SAP notes about it.
(extra): Cool pivot table (like for fancy analytics)
Run program (SE38): XXLFTEST. Adapt the provided code which is very straightforward.
Hope this helps the people to speed up their reporting to Excel and make the users happier.
Continue reading here:
Happy reporting with (XML) Excel II.:
Happy reporting with Excel II.
Happy reporting with Excel III: There and back.
Happy reporting with Excel III: There and back
