Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Using OLE-ABAP Code , How to Display Multiple Chart's in One Excel Sheet.

 

Error rendering macro 'code': Invalid value specified for parameter 'lang'
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