Creating graph in Excel Using Automation Variables Simple Example for Beginners Like Me :)

Let's Create a table or you can use customer table or any others But for this example

Am create a Simple table  with only 3 fields.

fields     Name            datatype 

1              No.                code/text

2             Type              Option

3              Value            Integer

As we know  Option  field should be assigned with option string in properties.Just like below

Save the Table as ID: 50000  and Name is as Testtable.

Now Run the table and Insert some test value (i mean enter values) in table for testing

Now create a new CodeUnit for our example

Take following as global variables:

Name DataType     Subtype                                                       Length

xlApp Automation  'Microsoft Excel 15.0 Object Library'.Application

xlbook Automation 'Microsoft Excel 15.0 Object Library'.Workbook

xlsheet Automation 'Microsoft Excel 15.0 Object Library'.Worksheet

xlchart Automation 'Microsoft Excel 15.0 Object Library'.Chart

xlRange Automation 'Microsoft Excel 15.0 Object Library'.Range

Testtable   Record          Test table

value    Integer

CellNo1 Text

CellNo2 Text

In OnRun Trigger() write following code:

CREATE(xlApp,TRUE,TRUE);

xlbook := xlApp.Workbooks.Add(-4167);

{

In the preceding procedure, the Add method includes the attribute -4167. In the Microsoft Excel Visual Basic Help, the Add method as it applies to the Workbooks object takes one Template argument, which is of type VARIANT. The description in the Help says:

If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following: XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorkSheet.

For more information, see Workbooks.Add Method (Excel),  http://go.microsoft.com/fwlink/?LinkId=404331.

}

xlsheet:= xlApp.ActiveSheet;

// you use the ActiveSheet property  to ensure that what is done next affects the active sheet of new workbook.

xlsheet.Name := 'Test excel';

CellNo1 := 'A1';

//excel Cell No's

CellNo2 := 'B1';

IF Testtable.FINDSET THEN

REPEAT

CellNo1 := INCSTR(CellNo1);

xlsheet.Range(CellNo1).Value := FORMAT(Testtable.Type);//taking values of x axis

CellNo2 := INCSTR(CellNo2);

xlsheet.Range(CellNo2).Value := ABS(Testtable.Value);//taking values of Y axis

UNTIL Testtable.NEXT = 0;

xlRange := xlsheet.Range('A2:'+FORMAT(CellNo2));

xlchart := xlbook.Charts.Add;

xlchart.Name := ' Top 10 Customer - Graph';

xlchart.ChartWizard(xlRange,-4101,7,2,1,0,1,'Top 10 Customer');

//-4101 refers to chart type

//7            -The option number for the built-in auto formats

//2              - An integer specifying no of rows or Columns  within the source range that contains category labels.

CategoryLabels	An integer specifying the number of rows or columns within the source range that contains category labels.	1 – There is one row with category labels (the department names).
SeriesLabels	An integer specifying the number of rows or columns within the source range that contains series labels.	0 – There are no series labels in your data.
HasLegend	TRUE to include a legend.	0
Title	VARIANT with the title of the chart.	You pass a string such as ‘Personnel Expenses’.

xlApp.Visible := TRUE;

Save the code unit and Run it 

Thank You.

Keep Visiting.