Click here to Skip to main content
13,044,766 members (42,556 online)
Rate this:
Please Sign up or sign in to vote.
See more:
private void ReadExcelFile(int InstanceId, int ProjectID, string file, int NumberOfCrawlInstances)
            string fileType = Path.GetExtension(file);
            string SourceConstr;
            System.Data.DataTable dtExcel = new System.Data.DataTable();
            dtExcel.TableName = "MyExcelData";
            if (fileType == ".xls")
                SourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + file + "';;Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
                SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + file + "';;Extended Properties= 'Excel 12.0;HDR=Yes;IMEX=1'";
            OleDbConnection con = new OleDbConnection(SourceConstr);
           <big> string query = "Select * from [Sheet1$]";</big>
            OleDbDataAdapter data = new OleDbDataAdapter(query, con);
            int RowsCount = dtExcel.Rows.Count;
            if (RowsCount > 0)
                DataTable dt = ConvertInput(dtExcel, ProjectID);
                AttributesBulkCopy(dt, RowsCount, InstanceId);
                SplitInstances(InstanceId, NumberOfCrawlInstances);
                ManageInstance(InstanceId, RowsCount, 1);

This is console application...Here the Select querey is fetch data from sheet 1..i want to fetch even the data in sheet2 or sheet3 how can i change that code..pls help me
Posted 9-Oct-12 20:47pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Firstly you should get names of the workbook's sheets, them cycle through these names and do your selects
// OleDbConnection is built already
// Getting datatable with sheets names
System.Data.DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt != null){
    string[] esheets = new String[dt.Rows.Count];
    int i = 0;
    // Add the sheets to an array
    foreach(DataRow row in dt.Rows){
        esheets[i] = row["TABLE_NAME"].ToString();
    // Read all the sheets you wish to select from...
   for(int j=0; j < esheets.Length; j++){
        // Use System.Data.OleDb.OleDbParameter instead of this:
        string query = "Select * from " + esheets[j];

Good luck :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

string query = "Select * from [Sheet2$]";

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 10 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100