Automating GIF charts from Excel


Using the COM object model exposed by Excel, we can programmatically open files, generate graphs, and save them as stand-alone image files.


 


Handily, the functionality provided by Excel gives us a simple way to generate charts and graphs when you need something easy and automatable. This is great for creating visualizations for automated reports, web pages, and the like (especially when "offline" or when you have limited access to various other utilities). Unfortunately, the main drawback to using the COM interface is that documentation is often limited and hard to find (especially now that everything has been .NETized), to help remedy this I've linked some references from MSDN below.


 


This JavaScript sample shows how to create a typical line chart given a simple comma-delimited file, and save it out as a .gif file in two different sizes.


 


chart.js



var excel = WScript.CreateObject("Excel.Application");


 


// no we don't want to show the Excel window


excel.Visible = false;


 


// open the file


excel.Workbooks.Open("C:\\sample.csv");


var sheet = excel.Workbooks(1).Worksheets(1);


 


// select both rows of data


excel.Range("A1:B9").Select();


 


// create the chart and setup some options


var chart = excel.Charts.Add(sheet);


chart.ChartType = 4; // 2D line (XlChartType::xlLine)


chart.HasLegend = true;


chart.Axes(1).HasTitle = false;


chart.Axes(2).HasTitle = false;


 


// save the large chart


chart.Export("C:\\sample.gif", "GIF");


 


// move the chart onto the main sheet


var ptr = chart.Location(2, sheet.Name);


 


// save the smaller chart


ptr.Export("C:\\sample_small.gif", "GIF");


 


// close out the Excel spreadsheet and application


excel.ActiveWorkbook.Saved = true; // suppress the "save" dialog


excel.Quit();


 


sample.csv



ABC,DEF


1,2


2,2


3,2


4,3


5,3


6,3


7,2


8,2


 


MSDN References


Excel.Application


Excel.Chart


XlChartType enumeration


 


And FYI, the type library information is located in excel.exe (in Office 2007) so you can open that file in a viewer (such as oleview.exe) to see all the COM interfaces, enumerations, etc.

Skip to main content