Using OLE-ABAP Code , How to Display Multiple Chart's in One Excel Sheet.
Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
REPORT YSCN_OLE.
*--------------------------------------------------------------------*
* INCLUDE *
*----------------------------------------------------------------------*
INCLUDE OLE2INCL.
*----------------------------------------------------------------------*
* VARIAVEIS *
*----------------------------------------------------------------------*
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 ,
GS_CHARTS2 TYPE OLE2_OBJECT ,
GS_CHART2 TYPE OLE2_OBJECT ,
GS_CHARTTITLE2 TYPE OLE2_OBJECT ,
GS_CHARTTITLECHAR2 TYPE OLE2_OBJECT ,
GS_ACTIVESHEET2 TYPE OLE2_OBJECT ,
GS_CHARTOBJECTS2 TYPE OLE2_OBJECT .
DATA: GV_SHEET_NAME(20) TYPE C ,
GV_OUTER_INDEX LIKE SY-INDEX ,
GV_INTEX(2) TYPE C ,
GV_LINE_CNTR TYPE I ,"line counter
GV_LINNO TYPE I ,"line number
GV_COLNO TYPE I ,"column number
GV_VALUE TYPE I ."data
*----------------------------------------------------------------------*
* INICIO - CRIAÇÃO DOC *
*----------------------------------------------------------------------*
START-OF-SELECTION .
*--Forming sheet name
GV_INTEX = SY-INDEX .
GV_OUTER_INDEX = SY-INDEX .
CONCATENATE 'Analise de Volume' GV_INTEX INTO GV_SHEET_NAME .
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 .
*GV_SHEET_NAME = 'TEST4'.
SET PROPERTY OF GS_ACTIVESHEET 'Name' = GV_SHEET_NAME .
GV_LINE_CNTR = 1 ."line counter
*----------------------------------------------------------------------*
* TITLE *
*----------------------------------------------------------------------*
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' = 'Análise de Volume' .
*--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 .
*----------------------------------------------------------------------*
* Inserção das colunas de dados *
*----------------------------------------------------------------------*
*--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 'Range' = GS_CELLS
EXPORTING
#1 = GS_CELL1.
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.
*----------------------------------------------------------------------*
* SELECIONA DADOS *
*----------------------------------------------------------------------*
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
*----------------------------------------------------------------------*
* Inserção das colunas de dados *
*----------------------------------------------------------------------*
*--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' = 'Toneladas' .
*--Generating some data
* Passa primeiro valor
GV_VALUE = 2 * 3 + GV_VALUE.
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
EXPORTING
#1 = GV_LINNO
#2 = GV_COLNO.
SET PROPERTY OF GS_CELL1 'Value' = '2'.
GV_COLNO = GV_COLNO + 1 .
* Passa segundo valor
GV_VALUE = 2 * 3 + GV_VALUE.
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
EXPORTING
#1 = GV_LINNO
#2 = GV_COLNO.
SET PROPERTY OF GS_CELL1 'Value' = '9'.
GV_COLNO = GV_COLNO + 1 .
*----------------------------------------------------------------------*
* Inserção das colunas de dados *
*----------------------------------------------------------------------*
*--Putting axis labels
GV_COLNO = 2 .
GV_LINE_CNTR = GV_LINE_CNTR + 30. "
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' = 'Impurezas' .
*--Generating some data
* Passa primeiro valor
GV_VALUE = 2 * 3 + GV_VALUE.
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
EXPORTING
#1 = GV_LINNO
#2 = GV_COLNO.
SET PROPERTY OF GS_CELL1 'Value' = '34'.
GV_COLNO = GV_COLNO + 1 .
* Passa segundo valor
GV_VALUE = 2 * 3 + GV_VALUE.
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
EXPORTING
#1 = GV_LINNO
#2 = GV_COLNO.
SET PROPERTY OF GS_CELL1 'Value' = '61'.
GV_COLNO = GV_COLNO + 1 .
*----------------------------------------------------------------------*
* DEFINE AREA DE SELEÇÃO *
*----------------------------------------------------------------------*
*--Source data area
GV_COLNO = GV_COLNO - 1 .
GV_LINNO = GV_LINNO - 30.
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_LINNO
#2 = GV_COLNO.
CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS
EXPORTING #1 = GS_CELL1
#2 = GS_CELL2.
CALL METHOD OF GS_CELLS 'Select' .
*----------------------------------------------------------------------*
* DEFINE TIPO/GRÁFICO *
*----------------------------------------------------------------------*
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 51
CALL METHOD OF GS_CHART 'SetSourceData'
EXPORTING #1 = GS_CELLS
#2 = 1.
*----------------------------------------------------------------------*
* DEFINE DADOS A SELEC. *
*----------------------------------------------------------------------*
*--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.
*----------------------------------------------------------------------*
* CHAMADA DO GRÁFICO *
*----------------------------------------------------------------------*
*--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' .
*----------------------------------------------------------------------*
* DEFINE AREA DE SELEÇÃO *
*----------------------------------------------------------------------*
*--Source data area
* gv_colno = gv_colno - 1 .
GV_LINNO = GV_LINE_CNTR - 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_LINNO
#2 = GV_COLNO.
CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS
EXPORTING #1 = GS_CELL1
#2 = GS_CELL2.
CALL METHOD OF GS_CELLS 'Select' .
*----------------------------------------------------------------------*
* DEFINE TIPO/GRÁFICO *
*----------------------------------------------------------------------*
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' = '98' ."Vertical bar graph 51
CALL METHOD OF GS_CHART 'SetSourceData'
EXPORTING #1 = GS_CELLS
#2 = 1.
*----------------------------------------------------------------------*
* DEFINE DADOS A SELEC. *
*----------------------------------------------------------------------*
*--Locate the chart onto the current worksheet
*--Activate current sheet
*GV_SHEET_NAME = 'TEST1'.
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.
*----------------------------------------------------------------------*
* CHAMADA DO GRÁFICO *
*----------------------------------------------------------------------*
*--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 + 20 .
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.
CALL METHOD OF GS_CELLS 'Select' .
CALL METHOD OF GS_ACTIVESHEET 'Paste' .
*----------------------------------------------------------------------*
* LIMPA VARIAVEIS *
*----------------------------------------------------------------------*
*--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 .===============================================================================================================================================
Out Put :
Screen Shot # 1
=====================================================================================================================
Below Screen Shot you can change 'Chart Type' value as per your requirement ,
'Chart Type' value list available below Screen Shot # 4 and # 5 .
Chart No: 1
Screen Shot # 2
Chart No: 2
Screen Shot # 3
=====================================================================================================================
List of Chart Type Based On MS-Office 2010
Chart Name Chart Value Chart Description
Screen Shot # 4
Screen Shot # 5