*&---------------------------------------------------------------------* *& Report YCX_021 *&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* REPORT YCX_021. INCLUDE OLE2INCL . *----------------------------------------------------------------------- * Global GUI Controls Variables / Structures *----------------------------------------------------------------------- *--Data Definition of OLE DATA: GS_EXCEL TYPE OLE2_OBJECT , GS_WBOOKLIST TYPE OLE2_OBJECT , GS_APPLICATION TYPE OLE2_OBJECT , GS_WBOOK TYPE OLE2_OBJECT , GS_ACTIVESHEET TYPE OLE2_OBJECT , GS_SHEETS TYPE OLE2_OBJECT , GS_NEWSHEET TYPE OLE2_OBJECT , GS_CELL1 TYPE OLE2_OBJECT , GS_CELL2 TYPE OLE2_OBJECT , GS_CELLS TYPE OLE2_OBJECT , GS_RANGE TYPE OLE2_OBJECT , GS_FONT TYPE OLE2_OBJECT , GS_INTERIOR TYPE OLE2_OBJECT , GS_COLUMNS TYPE OLE2_OBJECT , GS_CHARTS TYPE OLE2_OBJECT , GS_CHART TYPE OLE2_OBJECT , GS_CHARTTITLE TYPE OLE2_OBJECT , GS_CHARTTITLECHAR TYPE OLE2_OBJECT , GS_CHARTOBJECTS TYPE OLE2_OBJECT . DATA GV_SHEET_NAME(20) TYPE C . DATA GV_OUTER_INDEX LIKE SY-INDEX . DATA GV_INTEX(2) TYPE C . DATA GV_LINE_CNTR TYPE I . "line counter DATA GV_LINNO TYPE I . "line number DATA GV_COLNO TYPE I . "column number DATA GV_VALUE TYPE I . "data *$*$----------------------------------------------------------------$*$* *$*$ Selection Screen $*$* *$*$----------------------------------------------------------------$*$* *----------------------------------------------------------------------- * Selection Screen *----------------------------------------------------------------------- PARAMETERS: P_SHEETS TYPE I . *$*$----------------------------------------------------------------$*$* *$*$ Main Program $*$* *$*$----------------------------------------------------------------$*$* *--------- START-OF-SELECTION ---------- START-OF-SELECTION . DO P_SHEETS TIMES . *--Forming sheet name GV_INTEX = SY-INDEX . GV_OUTER_INDEX = SY-INDEX . CONCATENATE 'Excel Sheet #' GV_INTEX INTO GV_SHEET_NAME . *--For the first loop, Excel is initiated and one new sheet is added IF SY-INDEX = 1 . CREATE OBJECT GS_EXCEL 'EXCEL.APPLICATION' . SET PROPERTY OF GS_EXCEL 'Visible' = 1 . GET PROPERTY OF GS_EXCEL 'Workbooks' = GS_WBOOKLIST . GET PROPERTY OF GS_WBOOKLIST 'Application' = GS_APPLICATION . SET PROPERTY OF GS_APPLICATION 'SheetsInNewWorkbook' = 1 . CALL METHOD OF GS_WBOOKLIST 'Add' = GS_WBOOK. GET PROPERTY OF GS_APPLICATION 'ActiveSheet' = GS_ACTIVESHEET . SET PROPERTY OF GS_ACTIVESHEET 'Name' = GV_SHEET_NAME . *--For the rest of loops, other sheets are added ELSE . GET PROPERTY OF GS_WBOOK 'Sheets' = GS_SHEETS . CALL METHOD OF GS_SHEETS 'Add' = GS_NEWSHEET. SET PROPERTY OF GS_NEWSHEET 'Name' = GV_SHEET_NAME . ENDIF . GV_LINE_CNTR = 1 . "line counter *--Title *--Selecting cell area to be merged. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = 1 #2 = 1. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2 EXPORTING #1 = 1 #2 = 4. CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS EXPORTING #1 = GS_CELL1 #2 = GS_CELL2. CALL METHOD OF GS_CELLS 'Select'. *--Merging CALL METHOD OF GS_CELLS 'Merge'. *--Setting title data CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = 1. SET PROPERTY OF GS_CELL1 'Value' = 'TITLE' . *--Formatting the title GET PROPERTY OF GS_CELL1 'Font' = GS_FONT . SET PROPERTY OF GS_FONT 'Underline' = 2 . SET PROPERTY OF GS_FONT 'Bold' = 1 . SET PROPERTY OF GS_CELL1 'HorizontalAlignment' = -4108 . GET PROPERTY OF GS_CELL1 'Interior' = GS_INTERIOR . SET PROPERTY OF GS_INTERIOR 'ColorIndex' = 15 . SET PROPERTY OF GS_INTERIOR 'Pattern' = -4124 . SET PROPERTY OF GS_INTERIOR 'PatternColorIndex' = -4105 . GV_LINE_CNTR = GV_LINE_CNTR + 1 . *--Writing some additional data for the title CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = 1. SET PROPERTY OF GS_CELL1 'Value' = 'Sheet No' . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = 5. SET PROPERTY OF GS_CELL1 'Value' = ':' . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = 6. SET PROPERTY OF GS_CELL1 'Value' = GV_INTEX . *--Formatting the area of additional data 1 CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = 1 #2 = 1. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2 EXPORTING #1 = GV_LINE_CNTR #2 = 5. CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS EXPORTING #1 = GS_CELL1 #2 = GS_CELL2. CALL METHOD OF GS_CELLS 'Select'. GET PROPERTY OF GS_CELLS 'Font' = GS_FONT . SET PROPERTY OF GS_FONT 'Bold' = 1 . *--Formatting the area of additional data 2 CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = 1 #2 = 5. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2 EXPORTING #1 = GV_LINE_CNTR #2 = 5. CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS EXPORTING #1 = GS_CELL1 #2 = GS_CELL2. CALL METHOD OF GS_CELLS 'Select'. GET PROPERTY OF GS_CELLS 'Columns' = GS_COLUMNS . CALL METHOD OF GS_COLUMNS 'AutoFit'. *--Bordering title data area CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = 1 #2 = 1. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2 EXPORTING #1 = GV_LINE_CNTR #2 = 6. CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS EXPORTING #1 = GS_CELL1 #2 = GS_CELL2. CALL METHOD OF GS_CELLS 'Select'. CALL METHOD OF GS_CELLS 'BorderAround' EXPORTING #1 = 1 "continuous line #2 = 4. "thick *--Putting axis labels GV_COLNO = 2 . GV_LINE_CNTR = GV_LINE_CNTR + 5 . GV_LINNO = GV_LINE_CNTR - 1 . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINNO #2 = 1. SET PROPERTY OF GS_CELL1 'Value' = 'X' . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = 1. SET PROPERTY OF GS_CELL1 'Value' = 'Y' . *--Generating some data DO 3 TIMES . GV_VALUE = GV_OUTER_INDEX * SY-INDEX * 10 . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINNO #2 = GV_COLNO. SET PROPERTY OF GS_CELL1 'Value' = SY-INDEX . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = GV_COLNO. SET PROPERTY OF GS_CELL1 'Value' = GV_VALUE . GV_COLNO = GV_COLNO + 1 . ENDDO . *--Source data area GV_COLNO = GV_COLNO - 1 . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINNO #2 = 1. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2 EXPORTING #1 = GV_LINE_CNTR #2 = GV_COLNO. CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS EXPORTING #1 = GS_CELL1 #2 = GS_CELL2. CALL METHOD OF GS_CELLS 'Select'. GET PROPERTY OF GS_APPLICATION 'Charts' = GS_CHARTS . CALL METHOD OF GS_CHARTS 'Add' = GS_CHART. CALL METHOD OF GS_CHART 'Activate'. SET PROPERTY OF GS_CHART 'ChartType' = '51' . "Vertical bar graph CALL METHOD OF GS_CHART 'SetSourceData' EXPORTING #1 = GS_CELLS #2 = 1. SET PROPERTY OF GS_CHART 'HasTitle' = 1 . GET PROPERTY OF GS_CHART 'ChartTitle' = GS_CHARTTITLE . GET PROPERTY OF GS_CHARTTITLE 'Characters' = GS_CHARTTITLECHAR . SET PROPERTY OF GS_CHARTTITLECHAR 'Text' = 'Sample Graph' . *--Locate the chart onto the current worksheet *--Activate current sheet CALL METHOD OF GS_EXCEL 'WorkSheets' = GS_ACTIVESHEET EXPORTING #1 = GV_SHEET_NAME. CALL METHOD OF GS_ACTIVESHEET 'Activate'. CALL METHOD OF GS_CHART 'Location' EXPORTING #1 = 2 #2 = GV_SHEET_NAME. *--Reposition the chart on the worksheet (cut&paste) CALL METHOD OF GS_ACTIVESHEET 'ChartObjects' = GS_CHARTOBJECTS. CALL METHOD OF GS_CHARTOBJECTS 'Select'. CALL METHOD OF GS_CHARTOBJECTS 'Cut'. *--Select new area GV_LINE_CNTR = GV_LINE_CNTR + 2 . CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1 EXPORTING #1 = GV_LINE_CNTR #2 = 1. CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2 EXPORTING #1 = GV_LINE_CNTR #2 = 1. CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS EXPORTING #1 = GS_CELL1 #2 = GS_CELL2. CALL METHOD OF GS_CELLS 'Select'. CALL METHOD OF GS_ACTIVESHEET 'Paste'. ENDDO. *--Deallocating memory FREE: GS_EXCEL, GS_WBOOKLIST, GS_APPLICATION, GS_WBOOK, GS_ACTIVESHEET,GS_SHEETS, GS_NEWSHEET, GS_CELL1, GS_CELL2, GS_CELLS, GS_RANGE, GS_FONT, GS_INTERIOR, GS_COLUMNS, GS_CHARTS, GS_CHART, GS_CHARTTITLE, GS_CHARTTITLECHAR, GS_CHARTOBJECTS .