Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
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'";
            }
            else
            {
                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);
            data.Fill(dtExcel);
 
            int RowsCount = dtExcel.Rows.Count;
            if (RowsCount >
 

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
__PP__588
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

string query = "Select * from [Sheet2$]";
  Permalink  
Rate this: bad
good
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();
        i++;
    }
    // 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 Smile | :)
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 458
1 OriginalGriff 294
2 George Jonsson 258
3 CPallini 160
4 Shemeemsha RA 140
0 OriginalGriff 5,985
1 Sergey Alexandrovich Kryukov 5,341
2 CPallini 4,760
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 10 Oct 2012
Copyright © CodeProject, 1999-2014
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