Search This Blog

Monday, January 08, 2007

Export Excel Chart To Image

I'm working on a dashboard that will display TFS reports. I thought using "SQL Server Business Intelligence Development Studio" for creating reports but found it not so stable. Anyway, I think that using TFS Excel integration for creating reports is the best way (I will write about how to do it in a future post). Now I have some excel reports but I want them to be displayed in our dashboard automatically (soon will be displayed on a 42'' LCD...). The solution for this was to export the excel chart from the report to an image that will be displayed in the dashboard site.
Here's the code to export the image:


using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;
using System.Globalization;
using System.Threading;
using System.IO;

private static void ExportExcelChartToImage(string excelFile, string outputFile)
{

//Object to send in com methods instead of null
object missing = System.Reflection.Missing.Value;
//Create a new excel application
Excel.Application excelApplication = new Excel.ApplicationClass();
//Saving the old culture info
CultureInfo oldCultureInfo = Thread.CurrentThread.CurrentCulture;
try
{
//Setting new culture info is en-us is not default (Disable exception)
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-us");
//Open the excel document
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Open(excelFile,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing);
//Refresh the data from TFS
excelWorkbook.RefreshAll();
//Taking into consideration that there's only one sheet
Excel.Worksheet activeSheet = (Excel.Worksheet)excelWorkbook.ActiveSheet;
//Again, there's only one chart on the sheet
Excel.ChartObjects chartObjects = (Excel.ChartObjects)activeSheet.ChartObjects(missing);
Excel.ChartObject chartObject = (Excel.ChartObject)chartObjects.Item(1);
Excel.Chart chart = chartObject.Chart;
//Set the filter (bmp, jpg...)
string extension = Path.GetExtension(outputFile).Replace(".", "");
//Export the image
chart.Export(outputFile, extension, missing);
//Save and close the workbook
excelWorkbook.Save();
excelWorkbook.Close(false, excelFile, missing);
}
finally
{
//Set the old culture info
Thread.CurrentThread.CurrentCulture = oldCultureInfo;
//Close and free the excel application
excelApplication.Quit();
excelApplication = null;
}
}

2 comments:

growingneeds said...

I found an easier way to export the chart to GIF format: Instruct Excel to save worksheet in HTML format. A folder with the same name as the Excel worksheet appears. The exported charts would appear in those folders as GIF.

Anonymous said...

Just another example of why Mac is soooooo much better. Thanks for the quick fix.