Let’s get into the A to Z of OLE Excel in ABAP 7.4. SAP clients, both business and end clients generally need to download the result of a report to bookkeeping sheet and do their investigation. The standard succeed yield from a report is exceptionally straightforward interaction however it is outdated and the bookkeeping sheet looks very exhausting. There is no default organizing and the clients need to do all the difficult work of changing the textual styles, shading the texts, denoting the boundaries and so forth.
Refreshed sixteenth Aug 2019 – Assuming you are working in non-ABAP 7.4 (beneath), there is finished reference program for you as well. Go to the furthest limit of this article. Much thanks to you Legxis (LeonievonK) for the offer. A to Z of OLE Excel in ABAP 7.4.
I recognize, anything I referenced above can be accomplished in numerous ways automatically. We can do it in the old customary ABAP way yet giving numerous tabs in the accounting sheet and arranging is very precarious with non OLE strategy. A to Z of OLE Excel in ABAP 7.4.
OLE = Item Connecting and Installing
The general plan of this article is to be the G.O.A.T. (kindly google it in the event that you don’t have a clue about the full type) of OLE Succeed Guide. We maintain that this one article should be the beacon of all ABAP engineers who necessities to work with OLE Succeed in their activities. A lot of chest pounding before the beginning. Correct?
We would utilize the new ABAP 7.4+ punctuations to made Succeed with OLE. We would design the bookkeeping sheet. Headers in strong, textual style tone would be different at various region, foundation would be blue or any shade of your decision. We would check the boundaries better and furthermore make various Tabs in the succeed accounting sheet.
We should begin our jump into the OLE approach of making Succeed.
Step 1 – Include OLE2INCL in the program
The OLE automation programs “OLE2INCL” include needs to be specified. It is kind of a library of OLE syntax.
*** Include objects
INCLUDE: ole2incl.Step 2 – Populate the internal tables with data
For our demo, we are bringing information from some standard SAP tables with the goal that everybody can utilize our code piece.
*** Popoluate tables
SELECT ebeln,
bukrs,
bstyp,
bsart
INTO TABLE @DATA(gt_ekko)
FROM ekko
UP TO 5 ROWS
WHERE ebeln LIKE '45%'.
IF gt_ekko[] IS NOT INITIAL.
SELECT ebeln,
ebelp,
matnr,
bukrs
INTO TABLE @DATA(gt_ekpo)
FROM ekpo
FOR ALL ENTRIES IN @gt_ekko
WHERE ebeln = @gt_ekko-ebeln.
IF gt_ekpo[] IS NOT INITIAL.
SELECT ebeln,
ebelp,
gjahr,
belnr
INTO TABLE @DATA(gt_ekbe)
FROM ekbe
FOR ALL ENTRIES IN @gt_ekpo
WHERE ebeln = @gt_ekpo-ebeln
AND ebelp = @gt_ekpo-ebelp.
ENDIF.
ENDIF.We have 3 inside tables viz GT_EKKO, GT_EKPO and GT_EKBE prepared.
Step 3 – Put the internal table in a string table separated by delimiter
We want to place each interior table information in various tabs of the succeed. So we would save the inside table information in a long person variable where the information would be isolated by delimiter ‘|’. The long factor would be utilized later to make a document at run-time and save in the tabs of the bookkeeping sheet.
For our clarification, we have made a Table Sort with 1500 long characters and involved it for putting away information isolated by ‘|’. I have characterized the delimiter variable as ‘store’ which contains ‘|’ esteem ( cl_abap_char_utilities=>horizontal_tab ).
Did you suppose shop was for food?
TYPES: data1(1500) TYPE c,
ty_data TYPE TABLE OF data1.
*** Variables
DATA: gt_1 TYPE ty_data WITH HEADER LINE,
gt_2 TYPE ty_data WITH HEADER LINE,
gt_3 TYPE ty_data WITH HEADER LINE,
deli(1) TYPE c.
"Delimeter
deli = cl_abap_char_utilities=>horizontal_tab.Step 4 – Put internal table data to respective data/file types
Loop through the inward tables and connect the information to the separate string factors (gt_1, gt_2, gt_3). If it’s not too much trouble, note, I have passed the headers also, which will behave like segment names in succeed sheets.
Actually take a look at the new punctuation for link. Assuming you are new to ABAP 7.4 punctuation, if it’s not too much trouble, take the Free Start to finish Video Seminar on ABAP 7.4 Grammar and New Highlights.
**Header for first sheet
gt_1 = |EBELN{ deli }BUKRS{ deli }BSTYP{ deli }BSART|.
APPEND gt_1.
CLEAR gt_1.
**Data for first sheet
LOOP AT gt_ekko INTO DATA(wa_ekko).
gt_1 = | { wa_ekko-ebeln } { deli } { wa_ekko-bukrs } { deli } { wa_ekko-bstyp } { deli } { wa_ekko-bsart } |.
APPEND gt_1.
CLEAR gt_1.
ENDLOOP.
**Header for second sheet
gt_2 = |EBELN{ deli }EBELP{ deli }MATNR{ deli }BUKRS|.
APPEND gt_2.
CLEAR gt_2.
**Data for second sheet
LOOP AT gt_ekpo INTO DATA(wa_ekpo).
gt_2 = | { wa_ekpo-ebeln } { deli } { wa_ekpo-ebelp } { deli } { wa_ekpo-matnr } { deli } { wa_ekpo-bukrs } |.
APPEND gt_2.
CLEAR gt_2.
ENDLOOP.
**Header for third sheet
gt_3 = |EBELN{ deli }EBELP{ deli }GJAHR{ deli }BELNR|.
APPEND gt_3.
CLEAR gt_3.
**Data for third sheet
LOOP AT gt_ekbe INTO DATA(wa_ekbe).
gt_3 = | { wa_ekbe-ebeln } { deli } { wa_ekbe-ebelp } { deli } { wa_ekbe-gjahr } { deli } { wa_ekbe-belnr } |.
APPEND gt_3.
CLEAR gt_3.
ENDLOOP.Step 5 – Time for OLE Application
Make an OLE object as displayed underneath.
* start Excel
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.Step 6 – Create Workbook and Worksheets
Assuming you are battling (I would like to think not) with the ideas of exercise manual and worksheets then this preview ought to help.
We will involve this multitude of functionalities in our OLE age report. Energized??
Let us create Workbook and Worksheets.
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_sheets.
SET PROPERTY OF h_excel 'Visible' = 1. “If ‘1’ – it opens excel application in frontend and if ‘0’ then excel will be created in backend mode.
CALL METHOD OF h_sheets 'Add' = h_sheet.All the objects of excel application must be declare with type ‘ole2_object’.
* Ole data Declarations
DATA: h_excel TYPE ole2_object, " Excel object
h_sheets TYPE ole2_object, " list of workbooks
h_sheet TYPE ole2_object, " workbook
h_cell TYPE ole2_object, " cell
worksheet TYPE ole2_object, "Worksheet
e_color TYPE ole2_object, "Color
range TYPE ole2_object, "Range
borders TYPE ole2_object, "Borders
h_sheet1 TYPE ole2_object, "First sheet
h_sheet2 TYPE ole2_object, "Second Sheet
h_sheet3 TYPE ole2_object, "Third Sheet
gs_font TYPE ole2_object. "FontStep 7 – Activate the current worksheet and name it
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = ’EKKO’. “Sheet nameStep 8 – Pass the data from string internal table to Excel file
There are two methods for passing the information in succeed:
I) individually
ii) duplicate glue technique
Here, we will duplicate entire information from inside table and glue it in the succeed. This approach saves time and builds the presentation of code. It’s just plain obvious, we uncovered a method for improving the code.
**Copy data in clipboard
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = gt1[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.The above piece is simple. It duplicates the information of interior table into clipboard.
Ctrl C is constantly trailed by Ctrl V.
Now paste the copied data from clipboard to spreadsheet.
For gluing the replicated information in succeed sheet, we want to choose the cells and need to make the reach, in which the information will be stuck.
**choose first cell.
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1 "Row
#2 = 1. "Column
**choose second cell.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1 "Row
#2 = 1. "Column
**Make range from selected cell
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.In our program, we have EBELN as our most memorable field in each table. Subsequent to duplicating that information into succeed sheet, we see EBELN in beneath design due to space requirements (less width of cell).
Change the width of particular column with property ‘Columnwidth’.
**Change width of column.
SET PROPERTY OF w_cell1 'Columnwidth' = 12.Now we need to select the range and paste it in excel worksheet.
CALL METHOD OF range 'Select'.
** Paste data from clipboard to worksheet.
CALL METHOD OF worksheet 'Paste'.Step 9 – Formatting of the Excel Spreadsheet in SAP ABAP
The above advances guarantee, we have the information in our succeed. Presently we have the intriguing position to do for example Arranging.
9.1 Create Borders
Anything information we will glue in succeed ought to contains borders. For accomplishing this, Succeed application has a property as ‘borders’.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 7. "7 for left side
SET PROPERTY OF borders 'LineStyle'= 1. "type of line.Above, 7 is indicating border for left side. Same way we have,
8 for right side, 9 for top side, etc.
**Logic to assign borders to fetched data in worksheet.
DATA(i) = 0.
LOOP AT it_sheet INTO DATA(ls_sheet).
i = i + 1.
DATA(first) = |A{ i }|. "Column from where you want to start providing borders.
DATA(second) = |D{ i }|. "Column up to which you want to provide the borders.
**Make range of selected columns.
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = first
#2 = second.
**Logic to assign border on left side.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 7. "7 for left side
SET PROPERTY OF borders 'LineStyle'= 1. "type of line.
**Logic to assign border on right side.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 8.
SET PROPERTY OF borders 'LineStyle'= 1.
**Logic to assign border on top side.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 9.
SET PROPERTY OF borders 'LineStyle'= 1.
**Logic to assign border on bottom side.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 10.
SET PROPERTY OF borders 'LineStyle'= 1.
**Logic to assign border on vertical side.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 11.
SET PROPERTY OF borders 'LineStyle'= 1.
**Logic to assign border on horizontal side.
CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 12.
SET PROPERTY OF borders 'LineStyle'= 1.
ENDLOOP.9.2 Change the Font and Color of Header cells
For our case, we have just 4 headers in each worksheet. In this manner we have hard coded for 4 headers. You ought to make it dynamic.
** Logic to change font and color of header data.
CLEAR i.
DO 4 TIMES.
i = i + 1.
CALL METHOD OF h_excel 'CELLS'= h_cell NO FLUSH
EXPORTING #1 = 1
#2 = i.
GET PROPERTY OF h_cell 'FONT' = gs_font NO FLUSH.
SET PROPERTY OF gs_font 'BOLD' = 1 NO FLUSH.
SET PROPERTY OF gs_font 'SIZE' = 12.
CALL METHOD OF h_cell 'INTERIOR' = range.
SET PROPERTY OF range 'ColorIndex' = 5.
SET PROPERTY OF range 'Pattern' = 1.
ENDDO.Above code is basic. Be that as it may, you might need to comprehend the property names. ? For various tones, we have various numbers in OLE as displayed underneath:
Choose your favourite color and you are done.
Check the output of the three Tabs.
Isn’t this cool? ? You can play with the succeed application and it’s properties to investigate more. Next time make your own succeed yield, design it with the eyes of a creator and flabbergast your client with the wonderful show. ? All things considered, SAP isn’t that exhausting as others grumble.
The total start to finish code bit is underneath toward the finish of the article. You might duplicate and past it in your ABAP manager. It ought to work all according to plan.
YOU MAY LIKE THIS
WhatsApp us