Skip to Content
0
Apr 04, 2014 at 06:14 PM

ALV download to Excel onto desktop using OLE concept.

968 Views


Hi Experts,

I requriement is , through my se38 program i need to download report output to excel.

i did this using OLE concepts code and excel is being downloaded good.

But problem is all columns data is dumped into First column. But in my ALV i have 20 columns. So i except data in excel sheet to be in 20 columns.

Below is the OLE code i used

CREATE OBJECT wa_excel 'EXCEL.APPLICATION'. "Create object for Excel
SET PROPERTY OF wa_excel 'VISIBLE' = 1. "In background Mode
CALL METHOD OF wa_excel 'WORKBOOKS' = w_workbook.
CALL METHOD OF w_workbook 'ADD'. "Create a new Workbook
SET PROPERTY OF wa_excel 'SheetsInNewWorkbook' = 3. "No of sheets
* Downloading header details to first sheet
PERFORM download_sheet TABLES i_final USING 1 'Master Material Details'.
GET PROPERTY OF wa_excel 'ActiveSheet' = w_worksheet.
* Protect the first worksheet with a password
CALL METHOD OF w_worksheet 'PROTECT
EXPORTING #1 = 'infy@123'.
* Save the Excel file
GET PROPERTY OF wa_excel 'ActiveWorkbook' = w_workbook.
CALL METHOD OF w_workbook 'SAVEAS'
EXPORTING #1 = p_infile.
FREE OBJECT: w_worksheet, wa_excel.

FORM download_sheet TABLES p_tab USING p_sheet TYPE i p_name TYPE string.

CALL METHOD OF wa_excel 'WORKSHEETS' = w_worksheet
EXPORTING
#1 = p_sheet.

CALL METHOD OF w_worksheet 'ACTIVATE'.
SET PROPERTY OF w_worksheet 'NAME' = p_name.
CALL METHOD OF wa_excel 'Range' = w_range
EXPORTING
#1 = 'A1'
#2 = 'D1'.

CALL METHOD OF w_range 'INTERIOR' = w_int.
SET PROPERTY OF w_int 'ColorIndex' = 6.
SET PROPERTY OF w_int 'Pattern' = 1.
* Initially unlock all the columns( by default all the columns are locked )
CALL METHOD OF wa_excel 'Columns' = w_columns.
SET PROPERTY OF w_columns 'Locked' = 0.
* Locking and formatting first column
CALL METHOD OF wa_excel 'Columns' = w_columns
EXPORTING
#1 = 1.
* Locking and formatting second column
CALL METHOD OF wa_excel 'Columns' = w_columns
EXPORTING
#2 = 2.
SET PROPERTY OF w_columns 'Locked' = 2.
SET PROPERTY OF w_columns 'NumberFormat' = '@'.
* Export the contents in the internal table to the clipboard
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = p_tab[]
CHANGING
rc = w_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
* Paste the contents in the clipboard to the worksheet
CALL METHOD OF w_worksheet 'Paste'.
* Autofit the columns according to the contents
CALL METHOD OF wa_excel 'Columns' = w_columns.
CALL METHOD OF w_columns 'AutoFit'.
FREE OBJECT: w_columns, w_range.

Please help me if there is any SET Property of WA_EXCEL with which i can handle ALV data into Diffrenet columns.

Regards,

jayant.