Editable GridView Control in C# and .NET - Part IV Exporting the GridView to Excel

Listing 1 - Setting up the GridView columns in Excel

private void OpenSpreadsheet()
{
// Make Excel the Main Window Visible
ExcelApp.Visible = true;
object missing = System.Reflection.Missing.Value;
// Add a Worksheet to Excel
Excel.Workbook theWorkbook = ExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
TheSheet = (Excel.Worksheet)theWorkbook.Worksheets[1];
// Get the first row of cells to populate the header
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(0) + "1",
GetExcelIndexToLetter(NumberOfColumns - 1) + "1");
// draw the column headers in the excel spreadsheet from the GridView
for (int i = 0; i < this.NumberOfColumns; i++)
{
// Copy the text of the GridView Header into the Excel Cell
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).Value = listView1.Columns[i].Text;
// Set the width of the column in the Excel Spreadsheet to the Width of the GridView column
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).ColumnWidth =
listView1.Columns[i].Width/listView1.Font.SizeInPoints;
// Set the Excel Cell To Bold Font
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).Font.Bold = true;
}
}

The next step is to copy the cells of the GridView into the cells of the Excel Spreadsheet. The code for doing this looks similar to the previous listing accept in listing 2 we are accessing individual cells for our range inside of the Excel spreadsheet instead of a single row as in listing 1.

Listing 2 - Populating the cells in Excel from the GridView

private void WriteExcelData()
{
try
{
for (int i = 1; i < this.NumberOfRows; i++)
{
for (int j = 0; j < this.NumberOfColumns; j++)
{
// Get the current cell in Excel
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(j) + (i + 1).ToString(),GetExcelIndexToLetter(j) + (i + 1).ToString());
// Copy the GridView cell's Text Value, background color and text color into the Excel Cell
theRange.Value = GetCell(i, j+1);
theRange.Interior.Color = this.GetCellColor(i, j+1).ToArgb();
theRange.Font.Color = this.GetCellTextColor (i, j+1).ToArgb();
// Redraw the cell borders, they seem to disappear after coloring in the cell
theRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
theRange.Borders.Color = Color.LightGray.ToArgb();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

Unfortunately, the cell colors don't seem to map that well from .NET to Excel. Although the Excel documentation says to use RGB values, it seems that the color values that the Excel cell actually become when they are copied from the GridView are off a bit. This could be do to the small 56 color palette of an Excel Spreadsheet or it could be something I did wrong in the conversion. The grayscale, colors, however, do seem to map ok.

Summary

Thus ends our four part series. In this article we showed you how reference Microsoft Excel and export data from our GridView control into an Excel spreadsheet. Below are the links to the other three parts to the series if you haven't read them and wish to examine other aspects of the GridView.

An Editable GridView Control in C# and .NET - Part 1

An Editable GridView control in C# and .NET - Part II: Persisting in XML
Editable GridView Control in C# and .NET - Part III : Printing the GridView