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 Chart
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.
1 Comment
