Get 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 extremely basic cycle yet it is outdated and the calculation sheet looks very exhausting. There is no default arranging and the clients need to do all the difficult work of changing the textual styles, shading the texts, denoting the boundaries and so on. A to Z of OLE Excel in ABAP 7.4.
Refreshed sixteenth Aug 2019 – Assuming you are working in non-ABAP 7.4 (underneath), 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.
I recognize, anything I referenced above can be accomplished in numerous ways automatically. We can do it in the old conventional ABAP way however giving numerous tabs in the accounting sheet and organizing 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. (if it’s not too much trouble, google it on the off chance that you don’t have the foggiest idea about the full type) of OLE Succeed Guide. We maintain that this one article should be the beacon of all ABAP designers who necessities to work with OLE Succeed in their undertakings. A lot of chest pounding before the beginning. Correct?
We would utilize the new ABAP 7.4+ linguistic uses to made Succeed with OLE. We would organize the calculation sheet. Headers in strong, text style tone would be different at various region, foundation would be blue or any shade of your decision. We would check the lines better and furthermore make numerous Tabs in the succeed bookkeeping sheet.
How about we start our jump into the OLE approach of making Succeed.
Step 1 – Include OLE2INCL in the program
The OLE robotization programs “OLE2INCL” incorporate should be indicated. It is somewhat of a library of OLE language structure.
*** Include objects
INCLUDE: ole2incl.Step 2 – Populate the internal tables with data
For our demo, we are bringing information from some standard SAP tables so 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 internal tables viz GT_EKKO, GT_EKPO and GT_EKBE ready.
Step 3 – Put the internal table in a string table separated by delimiter
We want to place each inner table information in various tabs of the succeed. So we would save the interior 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 ‘shop’ which contains ‘|’ esteem ( cl_abap_char_utilities=>horizontal_tab ).
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
Circle through the inward tables and link the information to the particular string factors (gt_1, gt_2, gt_3). Kindly note, I have passed the headers too, which will carry on like segment names in succeed sheets.
Actually look at the new sentence structure for link. Assuming you are new to ABAP 7.4 language structure, kindly 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
Create an OLE object as shown below.
* start Excel
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.Step 6 – Create Workbook and Worksheets
Assuming that 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 large number 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.Every one of the objects of succeed application should be announce 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. "Font
Step 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 strategy
Here, we will duplicate entire information from inner table and glue it in the succeed. This approach saves time and expands the exhibition of code. It’s obvious, we uncovered a method for streamlining 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 bit is plain as day. It duplicates the information of inward table into clipboard.
Ctrl C is constantly trailed by Ctrl V.
Now paste the copied data from clipboard to spreadsheet.
For gluing the duplicated 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. In the wake of duplicating that information into succeed sheet, we see EBELN in beneath design in view of room imperatives (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 fascinating position to do for example Designing.
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.Over, 7 is demonstrating line for left side. Same way we have,
8 for right side, 9 for top side, and so on.
**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 straightforward. 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:
Pick your #1 variety and you are finished.
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 lovely show. ? All things considered, SAP isn’t that exhausting as others whine.
The total start to finish code scrap is underneath toward the finish of the article. You might duplicate and past it in your ABAP manager. It ought to work as planned.
YOU MAY LIKE THIS
Is SAP ABAP a High Paying Job?
WhatsApp us