Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to SELECT from any spreadsheet in Excel File using OleDbDataAdapter

I'm using OleDbDataAdapter to extract DataSet from excel file, but I have problems with SELECT statement inside

DataSet excelDataSet = new DataSet();
using (OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
{
     con.Open();
     OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [Name of spreadsheet]", con);
     cmd.Fill(excelDataSet);
     con.Close();
}

If you see I have "select * from [Name of spreadsheet]", but I need to get any spreadsheets, or for example 1st spreadsheet, but the name for this spreadsheet can be anything.

How to specify it? Is it any special characters like "select * from [%]"

like image 880
Bryuk Avatar asked Dec 16 '13 18:12

Bryuk


1 Answers

You need to know the name of the sheet to apply the select statement at it.
And you need to add the special char $ at the end of the name.

Supposing you have a sheet named MyFirstSheet then you can select rows from it with

 OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [MyFirstSheet$]", con);

In case you don't know the names of your sheets you could call

using (OleDbConnection con = new OleDbConnection(connectionString))
{
    con.Open();
    DataTable dt = con.GetSchema("Tables");
    string firstSheet = dt.Rows[0]["TABLE_NAME"].ToString();
    ...... work with the first sheet .....
}

This example should give you the name of the first sheet in the excel file (other sheets are available in the successive rows after the first)

like image 190
Steve Avatar answered Nov 09 '22 07:11

Steve