SAP users, both business and end users always need to download the output of a report to spreadsheet and do their analytics. The standard excel output from a report is very simple process but it is old fashioned and the spreadsheet looks quite boring. There is no default formatting and the users have to do all the hard work of changing the fonts, coloring the texts, marking the borders etc.
I acknowledge, whatever I mentioned above can be achieved in many ways programmatically. We can do it in the old traditional ABAP way but providing multiple tabs in the spreadsheet and formatting is quite tricky with non OLE method.
OLE = Object Linking and Embedding
The high level agenda of this article is to be the G.O.A.T. (please google it if you do not know the full form) of OLE Excel Guide. We want this one article to be the light house of all ABAP developers who needs to work with OLE Excel in their projects. Too much of chest thumping before the start. Right?
We would use the new ABAP 7.4+ syntaxes to created Excel with OLE. We would format the spreadsheet. Headers in bold, font color would be different at different area, background would be blue or any color of your choice. We would mark the borders better and also create multiple Tabs in the excel spreadsheet.
Let’s start our dive into the OLE approach of creating Excel.
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.
<code>*** Include objects
INCLUDE: ole2incl.</code>Step 2 – Populate the internal tables with data
For our demo, we are fetching data from some standard SAP tables so that everyone can use our code snippet.
<code>*** 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.</code>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
Our goal is to put each internal table data in different tabs of the excel. So we would save the internal table data in a long character variable where the data would be separated by delimiter ‘|’. The long variable would be used later to create a file at run-time and save in the tabs of the spreadsheet.
For our explanation, we have created a Table Type with 1500 long characters and used it for storing data separated by ‘|’. I have defined the delimiter variable as ‘deli’ which contains ‘|’ value ( cl_abap_char_utilities=>horizontal_tab ).
Did you think deli was for food?
<code>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.</code>Step 4 – Put internal table data to respective data/file types
Loop through the internal tables and concatenate the data to the respective string variables (gt_1, gt_2, gt_3). Please note, I have passed the headers as well, which will act like column names in excel sheets.
<code>**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.</code>Step 5 – Time for OLE Application
Create an OLE object as shown below.
<code>* start Excel
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.</code>Step 6 – Create Workbook and Worksheets
If you are struggling (I hope not) with the concepts of workbook and worksheets then this snapshot should help.
We are going to use all these functionalities in our OLE generation report. Excited??
Let us create Workbook and Worksheets.
<code>*--- 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.</code>All the objects of excel application must be declare with type ‘ole2_object’.
<code>* 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</code>Step 7 – Activate the current worksheet and name it
<code>GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = ’EKKO’. “Sheet name</code>Step 8 – Pass the data from string internal table to Excel file
There are two ways to pass the data in excel:
i) one-by-one
ii) copy-paste method
Here, we am going to copy whole data from internal table and paste it in the excel. This approach saves time and increases the performance of code. See, we revealed a way to optimize the code.
<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.</code>The above snippet is self explanatory. It copies the data of internal table into clipboard.
Ctrl C is always followed by Ctrl V.
Now paste the copied data from clipboard to spreadsheet.
For pasting the copied data in excel sheet, we need to select the cells and need to make the range, in which the data will be pasted.
<code>**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.</code>In our program, we have EBELN as our first field in every table. After copying that data into excel sheet, we see EBELN in below format because of space constraints (less width of cell).
Change the width of particular column with property ‘Columnwidth’.
<code>**Change width of column.
SET PROPERTY OF w_cell1 'Columnwidth' = 12.</code>Now we need to select the range and paste it in excel worksheet.
<code> CALL METHOD OF range 'Select'.
** Paste data from clipboard to worksheet.
CALL METHOD OF worksheet 'Paste'.</code>Step 9 – Formatting of the Excel Spreadsheet in SAP ABAP
The above steps ensure, we have the data in our excel. Now we have the interesting job to do i.e. Formatting.
9.1 Create Borders
Whatever data we are going to paste in excel should contains borders. For achieving this, Excel application has a property as ‘borders’.
<code>CALL METHOD OF range 'Borders' = borders NO FLUSH
EXPORTING #1 = 7. "7 for left side
SET PROPERTY OF borders 'LineStyle'= 1. "type of line.</code>Above, 7 is indicating border for left side. Same way we have, 8 for right side, 9 for top side, etc.
<code>**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.</code>9.2 Change the Font and Color of Header cells
For our case, we have only 4 headers in every worksheet. Therefore we have hard coded for 4 headers. You should make it dynamic.
<code>** 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.</code>Above code is pretty simple. But you may want to understand the property names. For different colors, we have different numbers in OLE as shown below:
Choose your favourite color and you are done.
Check the output of the three Tabs.
Isn’t this cool? You can play with the excel application and it’s properties to explore more. Next time create your own excel output, format it with the eyes of a designer and amaze your client with the beautiful presentation. After all, SAP is not that boring as others complain.
The complete end to end code snippet is below at the end of the article. You may copy and past it in your ABAP editor. It should work without any hiccups.
Complete Reference Program for ABAP 7.4 +
<code>**&---------------------------------------------------------------------*
**& Report ZEXCEL_TAB
**&---------------------------------------------------------------------*
**&
**&---------------------------------------------------------------------*
REPORT zexcel_tab.
*&---------------------------------------------------------------------*
*& Include Z_EXCEL_DOWNLOAD_TOP Report Z_EXCEL_DOWNLOAD
*&---------------------------------------------------------------------*
*** Include objects
INCLUDE: ole2incl.
TYPES: data1(1500) TYPE c,
ty_data TYPE TABLE OF data1.
*** Variable Declarations
DATA: w_cell1 TYPE ole2_object,
w_cell2 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
*** 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,
gv_sheet_name(20) TYPE c.
DATA l_rc TYPE i.
"Delimeter
deli = cl_abap_char_utilities=>horizontal_tab.
*** 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.
**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.
* start Excel
IF h_excel-header = space OR h_excel-handle = -1.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_sheets.
SET PROPERTY OF h_excel 'Visible' = 1.
CALL METHOD OF h_sheets 'Add' = h_sheet.
PERFORM create_sheet TABLES gt_1
USING 'EKKO' h_sheet1.
PERFORM create_sheet TABLES gt_2
USING 'EKPO' h_sheet2.
PERFORM create_sheet TABLES gt_3
USING 'EKBE' h_sheet3.
* Free Excel objects
FREE OBJECT: h_cell,
h_sheets,
h_sheet,
h_excel.
*&---------------------------------------------------------------------*
*& Form CREATE_SHEET
*&---------------------------------------------------------------------*
FORM create_sheet TABLES it_sheet TYPE ty_data
USING iv_name
iv_sheet TYPE ole2_object.
DATA l_rc TYPE i.
gv_sheet_name = iv_name.
IF gv_sheet_name NE 'EKKO'.
GET PROPERTY OF h_excel 'Sheets' = iv_sheet .
CALL METHOD OF iv_sheet 'Add' = h_sheet.
SET PROPERTY OF h_sheet 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
ELSE.
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = gv_sheet_name .
ENDIF.
**Copy data in clipboard
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it_sheet[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
**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
**Change width of column.
SET PROPERTY OF w_cell1 'Columnwidth' = 12.
**Make range from selected cell
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.
CALL METHOD OF range 'Select'.
** Paste data from clipboard to worksheet.
CALL METHOD OF worksheet 'Paste'.
**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.
** 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.
ENDFORM. " CREATE_SHEET</code>Complete Reference Program for Below ABAP 7.4
<code>REPORT y_ole_test.
*&---------------------------------------------------------------------*
*& Include Z_EXCEL_DOWNLOAD_TOP Report Z_EXCEL_DOWNLOAD
*&---------------------------------------------------------------------*
*** Include objects
INCLUDE: ole2incl.
TYPES: data1(1500) TYPE c,
ty_data TYPE TABLE OF data1.
*** Variable Declarations
DATA: w_cell1 TYPE ole2_object,
w_cell2 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
*** 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,
gv_sheet_name(20) TYPE c.
DATA l_rc TYPE i.
*** EDIT: Extra Variables needed because of old system
DATA: gt_ekko TYPE STANDARD TABLE OF ekko,
gt_ekpo TYPE STANDARD TABLE OF ekpo,
gt_ekbe TYPE STANDARD TABLE OF ekbe,
wa_ekko TYPE ekko,
wa_ekpo TYPE ekpo,
wa_ekbe TYPE ekbe.
DATA gv_concat TYPE string.
"Delimeter
deli = cl_abap_char_utilities=>horizontal_tab.
*** Popoluate tables
*** EDIT: no commas in the SELECT, no @s & no inline declarations & INTO CORRESPONDING
SELECT ebeln bukrs bstyp bsart
INTO CORRESPONDING FIELDS OF TABLE gt_ekko
FROM ekko
UP TO 5 ROWS
WHERE ebeln LIKE '45%'.
IF gt_ekko[] IS NOT INITIAL.
SELECT ebeln ebelp matnr bukrs
FROM ekpo
INTO CORRESPONDING FIELDS OF TABLE gt_ekpo
FOR ALL ENTRIES IN gt_ekko
WHERE ebeln = gt_ekko-ebeln.
IF gt_ekpo[] IS NOT INITIAL.
SELECT ebeln ebelp gjahr belnr
FROM ekbe
INTO CORRESPONDING FIELDS OF TABLE gt_ekbe
FOR ALL ENTRIES IN gt_ekpo
WHERE ebeln = gt_ekpo-ebeln
AND ebelp = gt_ekpo-ebelp.
ENDIF.
ENDIF.
**Header for first sheet
*** EDIT: no inline declarations & CONCATENATE instead of using | to build the strings
CONCATENATE 'EBELN' deli 'BUKRS' deli 'BSTYP' deli 'BSART' INTO gt_1.
APPEND gt_1.
CLEAR gt_1.
**Data for first sheet
LOOP AT gt_ekko INTO wa_ekko.
CONCATENATE wa_ekko-ebeln deli wa_ekko-bukrs deli wa_ekko-bstyp deli wa_ekko-bsart INTO gt_1.
APPEND gt_1.
CLEAR gt_1.
ENDLOOP.
**Header for second sheet
CONCATENATE 'EBELN' deli 'EBELP' deli 'MATNR' deli 'BUKRS' INTO gt_2.
APPEND gt_2.
CLEAR gt_2.
**Data for second sheet
LOOP AT gt_ekpo INTO wa_ekpo.
CONCATENATE wa_ekpo-ebeln deli wa_ekpo-ebelp deli wa_ekpo-matnr deli wa_ekpo-bukrs INTO gt_2.
APPEND gt_2.
CLEAR gt_2.
ENDLOOP.
**Header for third sheet
CONCATENATE 'EBELN' deli 'EBELP' deli 'GJAHR' deli 'BELNR' INTO gt_3.
APPEND gt_3.
CLEAR gt_3.
**Data for third sheet
LOOP AT gt_ekbe INTO wa_ekbe.
CONCATENATE wa_ekbe-ebeln deli wa_ekbe-ebelp deli wa_ekbe-gjahr deli wa_ekbe-belnr INTO gt_3.
APPEND gt_3.
CLEAR gt_3.
ENDLOOP.
* start Excel
IF h_excel-header = space OR h_excel-handle = -1.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_sheets.
SET PROPERTY OF h_excel 'Visible' = 1.
CALL METHOD OF h_sheets 'Add' = h_sheet.
PERFORM create_sheet TABLES gt_1
USING 'EKKO' h_sheet1.
PERFORM create_sheet TABLES gt_2
USING 'EKPO' h_sheet2.
PERFORM create_sheet TABLES gt_3
USING 'EKBE' h_sheet3.
* Free Excel objects
FREE OBJECT: h_cell,
h_sheets,
h_sheet,
h_excel.
*&---------------------------------------------------------------------*
*& Form CREATE_SHEET
*&---------------------------------------------------------------------*
FORM create_sheet TABLES it_sheet TYPE ty_data
USING iv_name
iv_sheet TYPE ole2_object.
DATA l_rc TYPE i.
*** EDIT: Extra Variables needed because of old system
DATA i TYPE i.
DATA i_string TYPE string.
DATA first TYPE string.
DATA second TYPE string.
DATA ls_sheet TYPE data1.
gv_sheet_name = iv_name.
IF gv_sheet_name NE 'EKKO'.
GET PROPERTY OF h_excel 'Sheets' = iv_sheet .
CALL METHOD OF iv_sheet 'Add' = h_sheet.
SET PROPERTY OF h_sheet 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
ELSE.
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = gv_sheet_name .
ENDIF.
**Copy data in clipboard
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it_sheet[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
**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
**Change width of column.
SET PROPERTY OF w_cell1 'Columnwidth' = 12.
**Make range from selected cell
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.
CALL METHOD OF range 'Select'.
** Paste data from clipboard to worksheet.
CALL METHOD OF worksheet 'Paste'.
**Logic to assign borders to fetched data in worksheet.
*** EDIT: no inline declarations & CONCATENATE instead of using | to build the strings
i = 0.
LOOP AT it_sheet INTO ls_sheet.
i = i + 1.
i_string = i.
CONCATENATE 'A' i_string INTO first. "Column from where you want to start providing borders.
CONCATENATE 'D' i_string INTO second. "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.
** 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.
ENDFORM. " CREATE_SHEET</code>Rating: 0 / 5 (0 votes)
