ABAP – Drawing X-Y scatter graph in MS Excel using OLE

Introduction

This blog post describes how to draw a graph in an excel file from SAP for standard transaction. The transaction used here is ME1P- Purchase Order Price History. Object Linking and Embedding (OLE) is a technology developed by Microsoft that allows embedding and linking to documents and other objects. OLE allows an editing application to export part of a document to another editing application and then import it with additional content.

ABAP & OLE

Through its Open Object Interface, ABAP supports the OLE2 Automation technique. Desktop applications that provide their functionality in the form of an OLE2 Automation Server (such as Excel or WinWord) can thus be integrated into R/3. The following ABAP key words control the applications:

• CREATE OBJECT
  This ABAP key word generates an object of the class “class”. CREATE OBJECT H_EXCEL ‘EXCEL.APPLICATION’. To address an OLE Automation Server (e.g. EXCEL) from ABAP, the server must be registered with SAP. The CREATE statement generates the initial object of this class and this can be processed further with the related key words.
• SET PROPERTY
  This ABAP key word sets the property of the object according to the contents of the field.
  The object must be of type OLE2_OBJECT. SET PROPERTY OF H_EXCEL ‘Visible’ = 1
• GET PROPERTY
  This ABAP key word copies the property of the object to the field
  GET PROPERTY OF CELL ‘Font’ = BOOK_FONT.
• CALL METHOD
  This ABAP key word calls the method of the object.
  CALL METHOD OF H_EXCEL ‘Workbooks„ = WORKBOOKS.
  CALL METHOD OF H_EXCEL ‘Cells’ = CELL
  EXPORTING
  #1 = 1
  #2 = 1.
• FREE OBJECT
  This ABAP key word releases the storage space required for the object.
  The object can then no longer be processed.
  FREE OBJECT H_EXCEL.

When called from an ABAP program, the SAPGUI acts as OLE client, and the desktop application as the OLE server.

Macros

A macro is used to write a piece of code that can be used to perform a similar function in different scenarios. In Microsoft Office applications macros are written in VBA which enables developers to build user defined functions. These macros can be called via an ABAP program using OLE functionality provided by the Windows applications.

The macro defined here is used to outline the steps for creating the required graph in excel. Steps for creating a macro through ABAP program:

1. Define an internal table for the macrofile.

DATA: BEGIN OF MACROFILE OCCURS 0,
LINE(80) ,
END OF MACROFILE. “number of lines you require in your macro

2. Create a Sub..End Sub for the macro.

MACROFILE-LINE = ‘sub draw_graph()’.
APPEND MACROFILE. MACROFILE-LINE = ‘charts.add’.
APPEND MACROFILE. MACROFILE-LINE = ‘activechart.charttype = xllinemarkersStacked ‘.
…..
……
APPEND MACROFILE.
MACROFILE-LINE = ‘end sub ‘.
APPEND MACROFILE. “the Sub defines a function for the type of graph created

Sample Code

This is an example of a code sample to draw an excel graph via SAP transaction ME1P using enhancement.

ENHANCEMENT ZXCEL_GRAPH.
*&------------------------------------------------------------------------*
*&  Program       :  ZXCEL_GRAPH                                          *
*&  Created by    :  Sakshi Dogra                                         *
*&  Description   :  Downloading data in excel and creating a graph for it*
*                    based on different vendors                           *
*&------------------------------------------------------------------------*
“checking t-code
IF SY-TCODE = 'ME1P'.
"making use of macros to design an excel for displaying a graph
INCLUDE OLE2INCL.  “include for accessing OLE objects registered with SAP
DATA: H_EXCEL TYPE OLE2_OBJECT,
      WORKBOOKS TYPE OLE2_OBJECT,
      THIS_WORKBOOK TYPE OLE2_OBJECT,
      CELL TYPE OLE2_OBJECT,
      BOOK_FONT TYPE OLE2_OBJECT.
 "counter variable
 DATA: V_CTR TYPE I.
 "macrofile
 DATA: BEGIN OF MACROFILE OCCURS 0,
        LINE(80) ,
       END OF MACROFILE.
"inserting excel file path for download
  CALL METHOD OF THIS_WORKBOOK 'InsertFile'
    EXPORTING
      #1 = 'C:\temp\VBsource.tmp'.
"defining parameters for creating graph
MACROFILE-LINE = 'Sub draw_graph()'.
“defining variables to be used
APPEND MACROFILE. MACROFILE-LINE = 'Dim ThisSeries As String'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim lngRow As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim lngStartRow As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim objChart As Chart'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim objSeries As Series'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MinX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MaxX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MinAxisX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MaxAxisX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim NewDate As Date'.
"defining selection area in the generated excel
APPEND MACROFILE. MACROFILE-LINE = 'Range("A4").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Do While Not ActiveCell.Value = ""'.
“change in formula to get date in mm/dd/yyyy format
APPEND MACROFILE. MACROFILE-LINE = 'NewDate = DateValue(Mid(ActiveCell.Value, InStr(1, ActiveCell.Value, ".", _'.
APPEND MACROFILE. MACROFILE-LINE = 'vbTextCompare) + 1, 2) & "/" & Left(ActiveCell.Value, _'.
APPEND MACROFILE. MACROFILE-LINE = 'InStr(1, ActiveCell.Value, ".", _'.
APPEND MACROFILE. MACROFILE-LINE = 'vbTextCompare) - 1) & "/" & Right(ActiveCell.Value, Len(ActiveCell.Value) - _'.
APPEND MACROFILE. MACROFILE-LINE = 'InStrRev(ActiveCell.Value, ".", , vbTextCompare)))'.
APPEND MACROFILE. MACROFILE-LINE = 'ActiveCell.Value = NewDate'.
APPEND MACROFILE. MACROFILE-LINE = 'ActiveCell.Offset(1, 0).Activate'.
APPEND MACROFILE. MACROFILE-LINE = 'Loop'.
APPEND MACROFILE. MACROFILE-LINE = 'Range("A3").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Range(Selection, Selection.End(xlDown)).Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Selection.NumberFormat = "dd/mm/yyyy;@"'.
APPEND MACROFILE. MACROFILE-LINE = 'Range("A1").Activate'.
"defining graph properties
APPEND MACROFILE. MACROFILE-LINE = 'Set objChart = ActiveSheet.ChartObjects.Add( _'.
APPEND MACROFILE. MACROFILE-LINE = '190, 30, 700, 325).Chart'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.ChartType = xlXYScatterLines'.
APPEND MACROFILE. MACROFILE-LINE = 'With ActiveSheet'.
APPEND MACROFILE. MACROFILE-LINE = 'lngStartRow = 3'.
APPEND MACROFILE. MACROFILE-LINE = 'lngRow = 3'.
APPEND MACROFILE. MACROFILE-LINE = 'ThisSeries = .Cells(lngStartRow + 1, 3).Value'.
APPEND MACROFILE. MACROFILE-LINE = 'Do While Len(.Cells(lngRow, 1).Value) > 0'.
APPEND MACROFILE. MACROFILE-LINE = 'ThisSeries = .Cells(lngStartRow + 1, 3).Value'.
APPEND MACROFILE. MACROFILE-LINE = 'If Not .Cells(lngRow + 1, 3).Value = ThisSeries Then'.
APPEND MACROFILE. MACROFILE-LINE = 'Set objSeries = objChart.SeriesCollection.NewSeries'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.Name = .Cells(lngStartRow + 1, 3).Value'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.XValues = .Range("A" & lngStartRow + 1, "A" & lngRow)'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.Values = _'.
APPEND MACROFILE. MACROFILE-LINE = 'Range("B" & lngStartRow + 1, "B" & lngRow)'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.HasDataLabels =True'.
"adding labels to lines drawn in graph
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)'.
APPEND MACROFILE. MACROFILE-LINE = 'lngStartRow = lngRow'.
APPEND MACROFILE. MACROFILE-LINE = 'End If'.
APPEND MACROFILE. MACROFILE-LINE = 'lngRow = lngRow + 1'.
APPEND MACROFILE. MACROFILE-LINE = 'Loop'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.HasDataLabels =True'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)'.
APPEND MACROFILE. MACROFILE-LINE = 'End With'.
"adding title for the chart
APPEND MACROFILE. MACROFILE-LINE = 'objChart.HasTitle = True'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.ChartTitle.Characters.Text = " Commodity Price Graph "'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlValue, xlPrimary).HasTitle = True'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price(INR)"'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory, xlPrimary).HasTitle = True'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE"'.
“setting min and max values for date on x-axis
APPEND MACROFILE. MACROFILE-LINE = 'MinX = Application.Min(Range("A:A"))'.
APPEND MACROFILE. MACROFILE-LINE = 'MaxX = Application.Max(Range("A:A"))'.
APPEND MACROFILE. MACROFILE-LINE = 'MinAxisX = MinX - 30'.
APPEND MACROFILE. MACROFILE-LINE = 'MaxAxisX = MaxX + 30'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory).MaximumScale = MaxAxisX'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory).MinimumScale = MinAxisX'.
“fixing excel column width
APPEND MACROFILE. MACROFILE-LINE = 'Columns("A:A").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Selection.ColumnWidth = 11'.
APPEND MACROFILE. MACROFILE-LINE = 'Columns("B:B").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Selection.ColumnWidth = 13'.
APPEND MACROFILE. MACROFILE-LINE = 'End Sub'.
APPEND MACROFILE.
"downloading data into excel
    CALL FUNCTION 'WS_DOWNLOAD'
    EXPORTING
        FILENAME = 'c:\temp\VBsource.tmp' "directory with temporary file
        FILETYPE = 'ASC'
    TABLES
       DATA_TAB = MACROFILE
    EXCEPTIONS
        FILE_OPEN_ERROR = 1
        FILE_WRITE_ERROR = 2
        INVALID_FILESIZE = 3
        INVALID_TABLE_WIDTH = 4
        INVALID_TYPE = 5
        NO_BATCH = 6
        UNKNOWN_ERROR = 7
        OTHERS = 8.
"creating excel file
    CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
    SET PROPERTY OF H_EXCEL 'Visible' = 1.
    CALL METHOD OF H_EXCEL 'Workbooks' = WORKBOOKS.
    CALL METHOD OF WORKBOOKS 'Add' = THIS_WORKBOOK.
 "adding data to excel file
  " adding material heading
    CALL METHOD OF H_EXCEL 'Cells' = CELL
        EXPORTING
           #1 = 1
           #2 = 1.
     SET PROPERTY OF CELL 'Value' = 'MATERIAL' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
   " adding material
      LOOP AT XEINA.
        V_CTR = SY-TABIX.
        CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
               #1 = V_CTR
               #2 = 2.
       IF V_CTR = 1.
       SET PROPERTY OF CELL 'Value' = XEINA-MATNR .
       GET PROPERTY OF CELL 'Font' = BOOK_FONT.
       SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
       ENDIF.
     ENDLOOP.
      "    adding date heading
    CALL METHOD OF H_EXCEL 'Cells' = CELL
        EXPORTING
           #1 = 3
           #2 = 1 .
     SET PROPERTY OF CELL 'Value' = 'DATE' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
      "    adding price heading
      CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
             #1 = 3
             #2 = 2.
     SET PROPERTY OF CELL 'Value' = 'PRICE(INR)' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
  " adding vendor heading
    CALL METHOD OF H_EXCEL 'Cells' = CELL
        EXPORTING
           #1 = 3
           #2 = 3.
     SET PROPERTY OF CELL 'Value' = 'VENDOR' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
     SORT XEINA BY INFNR.
     SORT XEIPA BY INFNR.
      LOOP AT XEINA.
        LOOP AT XEIPA.
          IF xeipa-infnr = xeina-infnr.
        V_CTR = SY-TABIX + 3.
 " adding vendor
        CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
               #1 = V_CTR
               #2 = 3.
       SET PROPERTY OF CELL 'Value' = XEINA-LIFNR .
       GET PROPERTY OF CELL 'Font' = BOOK_FONT.
       SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
" adding date
       CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
               #1 = V_CTR
               #2 = 1.
     SET PROPERTY OF CELL 'Value' = XEIPA-BEDAT.
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
" adding price
       IF XEIPA-LPREI = ' '.
          CALL METHOD OF H_EXCEL 'Cells' = CELL
               EXPORTING
                   #1 = V_CTR
                   #2 = 2.
           SET PROPERTY OF CELL 'Value' = XEIPA-PREIS .
           GET PROPERTY OF CELL 'Font' = BOOK_FONT.
           SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
      ELSE.
            CALL METHOD OF H_EXCEL 'Cells' = CELL
                 EXPORTING
                      #1 = V_CTR
                      #2 = 2.
           SET PROPERTY OF CELL 'Value' = XEIPA-LPREI .
           GET PROPERTY OF CELL 'Font' = BOOK_FONT.
           SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
      ENDIF.
       endif.
       endloop.
    ENDLOOP.
"adding data sheet to created excel file
      CALL METHOD OF H_EXCEL 'Modules' = MODULE.
      CALL METHOD OF MODULE 'Add' = NEWMODULE.
      CALL METHOD OF NEWMODULE 'Activate'.
      CALL METHOD OF NEWMODULE 'InsertFile'
         EXPORTING #1 = 'C:\temp\VBsource.tmp'.
"creating graph
      CALL METHOD OF H_EXCEL 'Run'
         EXPORTING #1 = 'draw_graph'.
"deallocating memory
       FREE OBJECT H_EXCEL.
ENDIF.
ENDENHANCEMENT.