Retrieving values of the first column from Excel sheet (file with .xlsm extension) with OleDB

Question

I am trying to retrieve values of the first column from the Excel sheet called "SP$".

I have the path:

string path = @"C:\Users\atsurkanu\Desktop" + @"\TemplateClientExtraction_IDEAFIMIT_Conero_QUARTER_20170127.xlsm";
string connectionString = string.Format(@"provider=Microsoft.ACE.OLEDB.12.0;data source={0};Extended Properties=Excel 12.0;", path);
string sheetName = "SP$";

and some code like this one:

using (OleDbConnection con = new OleDbConnection(connectionString))
        {
            try
            {                   
                var dataTable = new DataTable();

                con.Open();
                var tableschema = con.GetSchema("Tables");
                var firstsheet = tableschema.Rows[0]["SP$"].ToString();
                string name_query = "SELECT A4 FROM [" + firstsheet + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(name_query, con);
                da.Fill(dataTable);
                con.Close();

But it doesn't work. Please, tell me, how I cat retrieve the first column from Excel sheet.

UPDATE: I am not sure how it works, but it helps:

var dataTable = new DataTable();

                con.Open();
                var tableschema = con.GetSchema("Tables");

                // To get the first sheet name you use the first row and the column named TABLE_NAME
                var firstsheet = tableschema.Rows[0]["TABLE_NAME"].ToString();

                string name_query = "SELECT F1 FROM [" + "SP$" + "] WHERE F1 <> ''";
                OleDbDataAdapter da = new OleDbDataAdapter(name_query, con);
                da.Fill(dataTable);

                foreach (DataRow dataRow in dataTable.Rows)
                {
                    foreach (var item in dataRow.ItemArray)
                    {
                        listWithElementsFromSPfirstColumn.Add((string)item);
                    }
                }

Answer 1

You can't use "A4" and hope that OleDb understands the row/columns conventions of Excel. For OleDb the sheet is just a DataTable and you need to load it all or provide a WHERE condition to filter the rows you want to retrieve.

It is not clear if your sheet has HEADERs or not. You need to add to your connectionstring the key HDR=NO or HDR=YES. In case the headers are missing then OleDb assign automatically the column names with F1, F2, F3 and so on. So you can query your sheet with something like this or change the F1 to the header of the column A.

using (OleDbConnection con = new OleDbConnection(connectionString))
{
    try
    {                   
        var dataTable = new DataTable();

        con.Open();
        var tableschema = con.GetSchema("Tables");

        // To get the first sheet name you use the first row and the column named TABLE_NAME
        var firstsheet = tableschema.Rows[0]["TABLE_NAME"].ToString();

        string name_query = "SELECT F1 FROM [" + firstsheet + "] WHERE F1 <> ''";
        OleDbDataAdapter da = new OleDbDataAdapter(name_query, con);
        da.Fill(dataTable);
   }
   catch .....
}

Now it is up to you to read the datatable and use the content of the column F1 (the A column for Excel)