How can I select different excel sheets?

Question

1.00/5 (1 vote)

See more:

I want to select diffrent sheets of a single excel file, but the next OleDbDataAdapter uses the previous sheet data (my excel file has two sheets namly Rv and NumOfmachine).

this is my code:
the DataNumOfmachine uses the Rv data sheet.

C#

OleDbConnection Amir = new OleDbConnection();
    OleDbCommand AmirCom = new OleDbCommand();
    OleDbDataAdapter DataRv, DataNumOfmachine = new OleDbDataAdapter();
    DataSet AmirDataSet = new DataSet();
    DataTable AmirDataTable = new DataTable();
    
    Amir.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DataSource.xlsx;Extended Properties='Excel 12.0;HDR=YES';";
    //Amir.Open();
    
    

    DataRv = new OleDbDataAdapter("select * from [Rv$]", Amir);
    //AmirData.Fill(AmirDataSet);
    DataRv.Fill(AmirDataTable);

    Double[,] Rv = new Double[G, O];
    for (g = 0; g < G; g++)
        for (o = 0; o < O; o++)
            Rv[g, o] = int.Parse(AmirDataTable.Rows[g][o]+ "");

    

 
    DataNumOfmachine = new OleDbDataAdapter("select * from [NumOfmachine$]", Amir);
    //DataNumOfmachine.Fill(AmirDataSet);
    DataNumOfmachine.Fill(AmirDataTable);
    
    int[] NumOfmachine = new int[S];
    for (s = 0; s < S; s++)
        NumOfmachine[s] = int.Parse(AmirDataTable.Rows[0][s] + "");

What I have tried:

How can I select different Excel sheets?

Posted 16-Jun-18 0:53am

Amir2018

Updated 17-Jun-18 22:30pm

User 4180254

v2

Add a Solution

Comments

Richard MacCutchan 16-Jun-18 10:05am

"select * from [NumOfmachine$]"
Just use the correct sheet name in your SQL statement.

Amir2018 16-Jun-18 17:20pm

how is the the correct sheet name?

[no name] 17-Jun-18 9:46am

Make sure to refer correct worksheet name in your select query for second work sheet. Also why are you dumping data in same table holding previously populated worksheet data, if you need to make sure to make it null before you assign second worksheet data or create seperate table for new worksheet.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-06-17T22:31:00

If you would like to enum Excel sheets via OleDb, you can use OleDbConnection.GetSchema Method (String) (System.Data.OleDb)[^]

Usage:

string sFileName = @"FullFileNmae.xlsx";
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName);

using (OleDbConnection connection = new OleDbConnection(sConStr))
{
    connection.Open();
    DataTable dt = new DataTable();
    dt = connection.GetSchema("TABLES");
    var sheets = dt.AsEnumerable()
        .Select(x=>x.Field<string>("TABLE_NAME"))
        .ToList();

}

Above code should return a list of sheet names, for example:

Sheet2$ 
Sheet3$ 
Sheet1$

Good luck!