Get column names from excel file of a specific sheet using c# with OleDbConnection
19,612
Solution 1
You did not include sheet name in your code.
You can try below code:
var adapter = new OleDbDataAdapter("SELECT * FROM [" +sheetName + "$]", excelConnection);
var ds = new DataSet();
adapter.Fill(ds, "myTable");
DataTable data = ds.Tables["myTable"];
foreach(DataColumn dc in data.Columns){
...
}
Solution 2
What about using such snippet:
var adapter = new OleDbDataAdapter("SELECT * FROM [" +sheetName + "$A1:Z1]", excelConnection);
var table = new DataTable();
adapter.Fill(table);
For connection string with "HDR=Yes" it will read first row and the destination table will have all columns but no data.
Author by
user2881691
Updated on June 28, 2022Comments
-
user2881691 almost 2 years
So far I have managed to get the column names of the whole excel file, but what I would like to do is to get the column names of the excel file of a given table (sheet). How could I modify the code to achieve this. I have been trying for a while now with no positive results, any help much appreciated.
public static List<String> ReadSpecificTableColumns(string filePath, string sheetName) { var columnList = new List<string>(); try { var excelConnection = new OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 12.0;IMEX=1'"); excelConnection.Open(); var columns = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null); excelConnection.Close(); if (columns != null) { columnList.AddRange(from DataRow column in columns.Rows select column["Column_name"].ToString()); } } catch (Exception exception) { Console.WriteLine(exception.Message); } return columnList; }