Query excel sheet in c#

c#, excel, ms-office

Solution

The Select-command should look like this if you want to read A1 to D1:

SELECT * FROM [SHEETNAME_HERE$A1:D1]

Whole Code:

OleDbConnection con = new OleDbConnection(
    "provider=Microsoft.Jet.OLEDB.4.0;data source="
    + XLS_FILE_NAME_AND_PATH_HERE
    + ";Extended Properties=Excel 8.0;");

StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [" + SHEETNAME_HERE + "$A1:D1]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);

DataView dvEmp = new DataView(dsXLS.Tables[0]);

dataGridView1.DataSource = dvEmp;

Problem

I want to read Excel file in c# using following code ``` string excelFileName = "Book2.xls"; string excelConnectString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Book2.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; //string excelConnectString = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + excelFileName + ";" + "Extended Properties = Excel 8.0; HDR=Yes;IMEX=1"; OleDbConnection objConn = new OleDbConnection(excelConnectString); OleDbCommand objCmd = new OleDbCommand("Select * From [Sheet1$]", objConn); OleDbDataAdapter objDatAdap = new OleDbDataAdapter(); objDatAdap.SelectCommand = objCmd; DataSet ds = new DataSet(); objDatAdap.Fill(ds); ``` Everything is working fine.Now my requirement is to read the excel file something like below ``` SELECT A,B,D From [Sheet1]; ```

Original source