Read Excel Column using C#

Question

I am using inline query to load the excel data including header into a datatable.

      string Query;

      Query = string.Format("Select [Col1],[Col2],[Col3] FROM [{0}]", "Sheet1$");
      OleDbCommand Ecom = new OleDbCommand(Query, oleDbConn);
      oleDbConn.Open();

      DataSet ds = new DataSet();

      OleDbDataAdapter oda = new OleDbDataAdapter(Query, oleDbConn);

      oleDbConn.Close();
      oda.Fill(ds);
      DataTable Exceldt = ds.Tables[0];

Now instead of using this hardcoded line:

      Query = string.Format("Select [Col1],[Col2],[Col3] FROM [{0}]", "Sheet1$");

I want to make a class of columns headers and then use it in the SQL statement.

Can anyone please suggest how to achieve this approach ?

Answer 1

You can use LinqToExcel. https://code.google.com/p/linqtoexcel/

Then you can query with Linq and have typed Columns.

Like so:

var excel = new ExcelQueryFactory("excelFileName");
var ds = from c in excel.Worksheet<Sheet>()
                       select c;