abap

[Former Member]

how to print an output in excel format

[former_member189059]

Use this

INCLUDE ole2incl.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cells TYPE ole2_object,
gs_chart TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.



CREATE OBJECT application 'excel.application'.
SET PROPERTY OF application 'visible' = 1.
CALL METHOD OF application 'Workbooks' = workbook.
*CALL METHOD OF workbook 'Add'. "if you want to create a file
CALL METHOD OF workbook 'Open'
EXPORTING
#1 = p_file.
* #1 = 'C:TempXXX0099_Support_20XX.xls'. "your excel file
** name here






* Create second Excel sheet "Timesheets
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING
#1 = 2.
SET PROPERTY OF sheet 'Name' = 'Timesheets'.
CALL METHOD OF sheet 'Activate'.
CLEAR: w_index.
SORT itab BY budat hiden.
LOOP AT itab.
w_index = sy-tabix + w_lin.



CALL METHOD OF application 'Cells' = cells
EXPORTING
#1 = w_index " line
#2 = 1. " column
SET PROPERTY OF cells 'Value' = itab-hiden . "need to be changed


* Save excel spreadsheet to particular filename
CALL METHOD OF sheet 'Save' "'Save'
EXPORTING
* #1 = p_file

#1 = p_file
#2 = 1."filename
"fileFormat

* Closes excel window, data is lost if not saved
* SET PROPERTY OF application 'visible' = 0.

[former_member189059]

For uploading excel values to your internal table, use <b>ALSM_EXCEL_TO_INTERNAL_TABLE</b>

check this example:

REPORT ZSR_BDC_XL
NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES : LFA1,RF02K.

DATA : BEGIN OF ITAB OCCURS 0,
LIFNR LIKE RF02K-LIFNR,
KTOKK LIKE RF02K-KTOKK,
NAME1 LIKE LFA1-NAME1,
SORTL LIKE LFA1-SORTL,
LAND1 LIKE LFA1-LAND1,
SPRAS LIKE LFA1-SPRAS,
END OF ITAB.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA : B1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

B2 TYPE I VALUE 10,

C2 TYPE I VALUE 99.

START-OF-SELECTION.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = 'C:xl.XLS'
I_BEGIN_COL = B1
I_BEGIN_ROW = C1
I_END_COL = B2
I_END_ROW = C2
TABLES
INTERN = ITAB1
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
PERFORM ORGANIZE_UPLOADED_DATA.
FORM ORGANIZE_UPLOADED_DATA .

SORT ITAB1 BY ROW
COL.

LOOP AT ITAB1.

CASE ITAB1-COL.
* ....................................................
WHEN 1.
ITAB-LIFNR = ITAB1-VALUE.
WHEN 2.
ITAB-KTOKK = ITAB1-VALUE.
WHEN 3.
ITAB-NAME1 = ITAB1-VALUE.
WHEN 4.
ITAB-SORTL = ITAB1-VALUE.
WHEN 5.
ITAB-LAND1 = ITAB1-VALUE.
WHEN 6.
ITAB-SPRAS = ITAB1-VALUE.
* ....................................................
ENDCASE.


AT END OF ROW.
APPEND ITAB.
CLEAR ITAB.
ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA  

Message was edited by:

Kris Donald

[Former Member]

hi,

U first execute ur list report..go to menu bar....system >>list>>save>>as spredsheet

or if it is an ALV report then simply click the { Ctrl + SHIFT + F7 } or the excel sheet button at the top.

reward if useful,

anju

[Former Member]

Hi,

OLE automation is setting the orientation(portrait/landscape) as well as the margins.

report zrich_0003.

include ole2incl.

data: e_sheet type ole2_object.

data: e_appl type ole2_object.

data: e_work type ole2_object.

data: e_pgst type ole2_object.

data: field_value(30) type c.

parameters: p_file type localfile default 'C:\Test.xls'.

start-of-selection.

• Start the application

create object e_appl 'EXCEL.APPLICATION'.

set property of e_appl 'VISIBLE' = 1.

• Open the file

call method of e_appl 'WORKBOOKS' = e_work.

call method of e_work 'OPEN'

exporting

#1 = p_file.

<b> get property of e_appl 'ActiveSheet' = e_sheet .

get property of e_sheet 'PageSetup' = e_pgst.

• Lanscape = 2 or Portrait = 1

set property of e_pgst 'Orientation' = '2'.

• Print grid lines.... checked = 1

set property of e_pgst 'PrintGridlines' = '1' .

• Set the margins

set property of e_pgst 'LeftMargin' = '5.00'.

set property of e_pgst 'RightMargin' = '2.00'.

set property of e_pgst 'TopMargin' = '2.00'.

set property of e_pgst 'BottomMargin'= '2.00'.</b>

get property of e_appl 'ACTIVEWORKBOOK' = e_work.

call method of e_work 'PRINTOUT'.

• Close the file

call method of e_work 'close'.

• Quit the file

call method of e_appl 'QUIT'.

Regards