Wednesday, December 2, 2015

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.