Export Data to Excel Sheet

I have notice that there is a lot of traffic to my Blog because of a post I have about exporting Excel chart to an image. I thought that it would be good to share more stuff about Excel automation in C#. Below you can find a method to export data into excel sheet. This function uses a list view as the data source.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Globalization;
using System.Threading;
using System.Drawing;
using ExcelAutomation = Microsoft.Office.Interop.Excel;

namespace ExcelUtils
{
public static class Excel
{
private static void BorderAroundCell(ExcelAutomation.Range CellRange)
{

CellRange.BorderAround(ExcelAutomation.XlLineStyle.xlContinuous,
ExcelAutomation.XlBorderWeight.xlThin,
ExcelAutomation.XlColorIndex.xlColorIndexAutomatic,
Type.Missing);

}

public static void ReportFromListView(string reportName, ListView
listView)
{

ExcelAutomation.Application excelApp = new ExcelAutomation.ApplicationClass();
excelApp.UserControl = true;
CultureInfo oldCultureInfo = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelAutomation.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
ExcelAutomation.Worksheet worksheet = (ExcelAutomation.Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Name = reportName;
//Headers
foreach (ColumnHeader columnHeader in listView.Columns)
{
worksheet.Cells[1, columnHeader.Index + 1] = columnHeader.Text;
}
string[] letters = new string[26]{"A", "B", "C", "D", "E", "F", "G", "H",
"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
"W", "X", "Y", "Z"};
for (int i = 0; i < listView.Columns.Count; i++)

{
string headerCell = letters.GetValue(i) + "1";
worksheet.get_Range(headerCell, headerCell).Font.Bold = true;
BorderAroundCell(worksheet.get_Range(headerCell, headerCell));
worksheet.get_Range(headerCell, headerCell).Interior.ColorIndex = 36;
}
//Content
for (int i = 0; i < listView.Items.Count; i++)

{
for (int j = 0; j < listView.Columns.Count; j++)
{
string dataCell = letters.GetValue(j) + (i + 2).ToString();
worksheet.Cells[i + 2, j + 1] = listView.Items[i].SubItems[j].Text;
BorderAroundCell(worksheet.get_Range(dataCell, dataCell));
}
}
worksheet.Columns.AutoFit();
worksheet.Columns.HorizontalAlignment = ExcelAutomation.XlHAlign.xlHAlignLeft;
excelApp.Visible = true;
Thread.CurrentThread.CurrentCulture = oldCultureInfo;
}
}
}

Company Dashboard

We have installed a 42'' LCD Screen in our company headquarters. This LCD screen will be used for a dashboard displaying stats on our development process. Currently I'm using Excel reports created with TfsWarehouse OLAP cube (I have written about it before and I will post a guide in the future). The Excel reports are processed by a utility I wrote that extracts the charts from the Excel files to images in a directory that is displayed in a web site (I used javascript fade effect for it).

Here are some pictures of the LCD screen:

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;
}
}