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