Programatically setting excel column/header names

This is a discussion on Programatically setting excel column/header names within the C# Programming forums, part of the General Programming Boards category; I have a program which needs to query from a database and export the resulting datatable to an excel sheet. ...

  1. #1
    Registered User
    Join Date
    Sep 2010
    Posts
    10

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

  2. #2
    Registered User
    Join Date
    Dec 2008
    Posts
    65
    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?

  3. #3
    Registered User
    Join Date
    Sep 2010
    Posts
    10
    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).

  4. #4
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,789
    Excel treats the top row as columns if it's formatting is different from the rest - so I usually set the first column as bold, then the excel sorting procedures will sort by those column names.

    To set the format you just select the first row as a Range and then access the Font. The VBA code would be

    Range("1:1").Font.Bold = True

    Doing this in C# shouldn't be too much hard work

  5. #5
    Registered User
    Join Date
    Sep 2010
    Posts
    10
    I believe that would work. I would love to test it but I'm getting a new and exciting error. Would anyone know why, after the above code runs and then saves with :

    Code:
    xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue,
        misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    , why it might give me the error:

    Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

    when I try to open it (by finding an clicking on it)?
    Last edited by cStudy; 11-01-2010 at 01:53 PM.

  6. #6
    Registered User C_ntua's Avatar
    Join Date
    Jun 2008
    Posts
    1,853
    Just a guess is that is is an xls nor xlsx.

    Maybe you need to Quit the excel instance from your code? (though I wouldn't bet this is the problem)

  7. #7
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,789
    What version of Excel do you have installed?

  8. #8
    Registered User
    Join Date
    Sep 2010
    Posts
    10
    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?

  9. #9
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,789
    Isnt that just the auto-filter feature in excel?

    Range("1:1").AutoFilter

    (More vba)

  10. #10
    Registered User
    Join Date
    Sep 2010
    Posts
    10
    Sounds about right. Thanks Fordy!

  11. #11
    Registered User
    Join Date
    Mar 2011
    Location
    Milano (Italy)
    Posts
    1
    Hi,
    has anyone found a way to show also the columns headers in the exported Excel file?

    Luigi

Popular pages Recent additions subscribe to a feed

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21