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;
}
}
Search This Blog
Monday, January 08, 2007
Export Excel Chart To Image
Labels:
c#,
chart,
excel,
excel integration,
reports,
teamfoundationserver,
tfs
Subscribe to:
Post Comments (Atom)
2 comments:
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.
Just another example of why Mac is soooooo much better. Thanks for the quick fix.
Post a Comment