Reading Excel 2010 file - cells in C#

Question

UPDATE 2

string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", EXCELFILENAME);

            string testCaseName = "test_case_2";

            string query = String.Format("SELECT * from [{0}$] WHERE columns={1}", workbookName, testCaseName);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);

            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet); //<<ERROR 

        DataTable myTable = dataSet.Tables[0]; 

ERROR: No value given for one or more required parameters.

UPDATE END

UPDATE:

string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", EXCELFILENAME);
            string query = String.Format("select * from [{0}$]", workbookName);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);               
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);

            DataTable myTable = dataSet.Tables[0];

How to read data from cells and store it in array/string. I have done reading worksheet but can't find any better of doing

so here is my excel sheet looks like:

I should be able to pass the column in my case test_case_1 or test_case_2 etc... and read the columns for that particular row....

Answer 1

I'd suggest you take a look at LinqToExcel. It makes querying Excel in .NET very easy.

var book = new LinqToExcel.ExcelQueryFactory(@"E:\Temporary\Workbook.xlsx");

var current_test_case = "Test_case_1";

var query =
    from row in book.Worksheet("Sheet1")
    let columns = row["columns"].Cast<string>()
    where columns == current_test_case
    select new
    {
        id = row["id"].Cast<int>(),
        name = row["name"].Cast<string>(),
        caption = row["caption"].Cast<string>(),
        date = row["date"].Cast<DateTime>(),
        success = row["success"].Cast<bool>(),
    };