Thread: Programatically setting excel column/header names

I have a program which needs to query from a database and export the resulting datatable to an excel sheet. I already have code that is correctly doing these things. However, the resulting excel sheet doesn't have the column names that the datatable is. Thus, the excel sheet just has many columns of numbers without any lables, so it's fairly useless since the reader doesn't know what the information stands for.

My question is how would I set the column names in my excel sheet to be the the ones in my datatable. Here is the general relevant code.

Code:

// Create Excel File
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet
    = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

// This is the point I wish to add the column names. I was thinking that a for loop
// going through all the columns in Report and copying them to xlWorkSheet 
// would work, but I can't find anything (such as a property) of xlWorkSheet that 
// would allow me to do this.

// Transfer Data from Report to excel file
// Report is a DataGridView
for (i = 0; i <= Report.DataGridView.RowCount - 1; i++)
{
    for (j = 0; j <= Report.DataGridView.ColumnCount - 1; j++)
    {
        DataGridViewCell cell = Report.DataGridView[j, i];
        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
    }
}

Not 100% sure on this, without knowing how you populate the data in Report.DataGridView, but couldn't you make your column titles DataGridView[0, 0]? Or make a separate array just for the column titles, then write that information to the Excel file before writing your data?

Let me know if I get this wrong, but I think what you're saying is that I write my column headers to the top row of my excel sheet. This in theory is fine, except I want to make sure that these aren't treated as data. Thus, if the column is sorted it shouldn't put the column name somewhere within the data, or if the user scrolls down the column names should remain at the top or the view (scroll down with them).
So I'm looking for either:
1) A way to rename the columns from the usual (A, B, C, ...) to my column names
-OR-
2) A way to set the top row (row 1) of the excel sheet to act like column names. (For this option I would just write in the column names into the cells in this row).

I have excel 2007. I modified the problematic code so it's now saving as an xls and this has fixed that problem, so thanks C_ntua.

My column names I'm also writing to be the top row, so at least now the columns are labeled. Thanks Phyxashun for that idea.

The issue of the column names acting like the headers is mostly resolved, thanks to Fordy. I can now sort by the column names by values in the columns, which is very good. I wish to make this more robust because as it stands I don't think novic excel users would get how to sort the list. I've seen excel sheets where the column names had drop-down lists that had sort and filter options.

For example: the first column is part numbers and there are duplicates because part 1000 can have differences in other columns. Lets say I want to filter the list to only show rows with this part. The part column would have a drop down menu where I can select a checkbox for this part and no others. -OR- if I wished to sort this column ascending to descending order then the dropdown would have an option for that.
Does this make sense as I've explained it?
Does anyone know how I can do this programattically?

Sounds about right. Thanks Fordy!

Hi,
has anyone found a way to show also the columns headers in the exported Excel file?

Luigi