Retrieve Column By Header Name

Question

I am using OLEDB to read the data from an Excel spreadsheet.

var connectionString =
  string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "mySheet");
var data = ds.Tables["mySheet"].AsEnumerable();

foreach (var dataRow in data)
  { 
  Console.WriteLine(dataRow[0].ToString());                        
  }

Instead of passing an index to the DataRow to get the value of a column, is there anyway to retrieve the column by the name of the column header?

Answer 1

Modify your connection string to specify that you have headers in your excel file.

You can do this by setting the HDR value.

Refer this link to for various variations of connection string and build the one that suits your needs"

http://www.connectionstrings.com/excel/

Answer 2

Use a DataTable to have your data.

        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + **EXCEL FILE PATH** + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();            

        OleDbCommand cmd2 = new OleDbCommand("SELECT * FROM [**YOUR SHEET** $]", conn);
        cmd2.CommandType = CommandType.Text;

        DataTable outputTable2 = new DataTable("myDataTable");

        new OleDbDataAdapter(cmd2).Fill(outputTable2);

        foreach(Datarow row in outputTable2)
        {
             String s = row["yourcolumnheader"].ToString();
        }   

Answer 3

Try this code:

        var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0; HDR=YES", fileName);

        var adapter = new OleDbDataAdapter("SELECT * FROM [sheet1$]", connectionString);
        var ds = new DataSet();

        adapter.Fill(ds, "mySheet");
        var data = ds.Tables["mySheet"].AsEnumerable();

        foreach (DataRow dataRow in data)
        {
            Console.WriteLine(dataRow["MyColumnName"].ToString());    
            Console.WriteLine(dataRow.Field<string>("MyColumnName").ToString());
        }

I added in 2 ways to access the data in the row via column Name.

Hope this does the trick!!