Create any graph in NAV using Excel

Variables
ExcelApp Automation ‘Microsoft Excel 15.0 Object Library’.Application
ExcelBook Automation ‘Microsoft Excel 15.0 Object Library’.Workbook
ExcelSheet Automation ‘Microsoft Excel 15.0 Object Library’.Worksheet
ExcelRange Automation ‘Microsoft Excel 15.0 Object Library’.Range
ExcelChart Automation ‘Microsoft Excel 15.0 Object Library’.Chart
GraphFile File
MemStream InStream
OStream OutStream
Customer Record Customer

CREATE(ExcelApp, FALSE, TRUE);

ExcelBook := ExcelApp.Workbooks.Add(-4167);
ExcelSheet := ExcelApp.ActiveSheet;
ExcelSheet.Name := ‘Sales customer’;

Customer.SETFILTER(“Date Filter”,’%1..%2′,010112D,311212D);
Customer.CALCFIELDS(“Sales (LCY)”);
ExcelSheet.Range(‘A1’).Value := ‘2012’;
ExcelSheet.Range(‘A2′).Value := Customer.”Sales (LCY)”;

Customer.SETFILTER(“Date Filter”,’%1..%2’,010113D,311213D);
Customer.CALCFIELDS(“Sales (LCY)”);
ExcelSheet.Range(‘B1’).Value := ‘2013’;
ExcelSheet.Range(‘B2′).Value := Customer.”Sales (LCY)”;

Customer.SETFILTER(“Date Filter”,’%1..%2’,010114D,311214D);
Customer.CALCFIELDS(“Sales (LCY)”);
ExcelSheet.Range(‘C1’).Value := ‘2014’;
ExcelSheet.Range(‘C2’).Value := Customer.”Sales (LCY)”;

ExcelRange := ExcelSheet.Range(‘A1:C2’);
ExcelChart := ExcelBook.Charts.Add;
ExcelChart.Name := ‘Customergraph’;

ExcelChart.ChartWizard(ExcelRange,60,70,1,1,0,0,’Sales (LCY) Customer Pardaan Inc.’);
ExcelChart.Export(TEMPORARYPATH+’graph.png’);

GraphFile.OPEN(TEMPORARYPATH+’graph.png’);
GraphFile.CREATEINSTREAM(MemStream);
CALCFIELDS(Graph);
Graph.CREATEOUTSTREAM(OStream);
COPYSTREAM(OStream,MemStream);
MODIFY;
GraphFile.CLOSE;

Download objects (NAV 2009 R2) : [download id=”133″]

Parameters ChartWizard Method
ChartType enumeration

graph