Read Excel with column cell name

Question

I am reading an Excel file and displaying in DataGrid. I need to display the column cell name along with the cell values. For ex., if I have any value in column A, the data table's header should display as A, B, C, …, AA, BB, … But here I am seeing F1, F2, …

This is the image i'm getting.

I want the format as below.

And here is my code.

string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
               ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';";

using (OleDbConnection con = new OleDbConnection(conn))
{                
    string selectQuery = "SELECT * FROM [Sheet2$G1:L20]";

    OleDbDataAdapter oleAdpt = new OleDbDataAdapter(selectQuery, con);
    oleAdpt.Fill(dtexcel);                       
}

Any help would be really appreciated.

You can change the header text with `myDataGrid.Columns[0].Header = "blah";` https://stackoverflow.com/questions/28477846/change-datagrid-column-header-text — Jaskier, Feb 05 '19 at 14:05
Personally, I would user [ExcelDataReader](https://github.com/ExcelDataReader/ExcelDataReader) or a similar, free NuGet package to read from an Excel file. — Uwe Keim, Feb 07 '19 at 12:31

score 1 · Answer 1 · answered Feb 07 '19 at 12:26

After your datatable is built, just alter the column names as such:

for (int i = 0; i < dtexcel.Columns.Count; i++)
    dtexcel.Columns[i].ColumnName = GetExcelColumnName(i + 7);

+7 is there because you started on column G. If you started on A, it would be +1.

The GetExcelColumnName method can be poached from here:

https://stackoverflow.com/a/182924/1278553

To give you some confidence this works, I tried it on a sample project:

Read Excel with column cell name

1 Answers1