CODEWITHSUNDEEP
c#exceloledb
C N
C N

Reputation: 449

Get column name from excel worksheet

In C#, how do I get the column name from a worksheet in an Excel file?

Here is my code so far:

ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", "@"C:\file.xlsx"); 

objConn.Open(); 
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM ["xlWorksheet"$]", objConn); 

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); 

objAdapter1.SelectCommand = objCmdSelect; 

DataSet objDataset1 = new DataSet(); 

objAdapter1.Fill(objDataset1); 

objConn.Close();

Upvotes: 1

Views: 17079

Answers (1)

Kris Krause
Kris Krause

Reputation: 7326

Does you connection string include the HDR=YES ?:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Once you populate your DataTable or DataSet you can treat the usual way:

dt.Columns[0].ColumnName

Or:

// For each DataTable, print the ColumnName.
foreach(DataTable table in dataSet.Tables)
{
    foreach(DataColumn column in table.Columns)
    {
        Console.WriteLine(column.ColumnName);
    }
}

Also this does not look syntax correct:

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM ["xlWorksheet"$]", objConn);

Should be something like:

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + xlWorksheet + "$]", objConn);

Finally, if you time - investigate EPPlus (open source) for reading/writing Excel - http://epplus.codeplex.com as it works in both 32-bit and 64-bit environments.

Upvotes: 4

Related Questions