Solved

How to read the Excel file

Posted on 2008-11-03
5
394 Views
Last Modified: 2013-11-07
I want to read the Excel file from web application.
I used following process but its not working..

 string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

 OleDbConnection _conn = new OleDbConnection(connString);
            OleDbCommand _cmd = new OleDbCommand("Select Name,ID from [Details$] ", _conn);
            _conn.Open();
            OleDbDataReader _reader = _cmd.ExecuteReader();
            while (_reader.Read())
            {
                string Name = _reader["Name"].ToString();
                string ID = _reader["ID"].ToString();

            }

The Details is the Workbook Name..
I am getting the error like
"The Microsoft Jet database engine could not find the object 'Details$'.  Make sure the object exists and that you spell its name and the path name correctly..."
0
Comment
Question by:SwamyN
5 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 22866378
Can you check the names of your sheets in the excel-file.
Perhaps you should try: "Select Name,ID from [Details] "
0
 

Author Comment

by:SwamyN
ID: 22866412
I done that but getting same error..
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 22866425
Perphaps you can use this piece of code, to check the sheetnames you have in your object (to make sure you have the right file )

C# - Retrieve Excel Workbook Sheet Names.
http://www.codeproject.com/KB/aspnet/getsheetnames.aspx
private String[] GetExcelSheetNames(string excelFile)

{

  OleDbConnection objConn = null;

  System.Data.DataTable dt = null;
 

  try

  {

    // Connection String. Change the excel file to the file you

    // will search.

    String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 

        "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";

    // Create connection object by using the preceding connection string.

    objConn = new OleDbConnection(connString);

    // Open connection with the database.

    objConn.Open();

    // Get the data table containg the schema guid.

    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

 

    if(dt == null)

    {

      return null;

    }
 

    String[] excelSheets = new String[dt.Rows.Count];

    int i = 0;
 

    // Add the sheet name to the string array.

    foreach(DataRow row in dt.Rows)

    {

      excelSheets[i] = row["TABLE_NAME"].ToString();

      i++;

    }
 

    // Loop through all of the sheets if you want too...

    for(int j=0; j < excelSheets.Length; j++)

    {

      // Query each excel sheet.

    }
 

    return excelSheets;

  }

  catch(Exception ex)

  {

    return null;

  }

  finally

  {

    // Clean up.

    if(objConn != null)

    {

      objConn.Close();

      objConn.Dispose();

    }

    if(dt != null)

    {

      dt.Dispose();

    }

  }

}

Open in new window

0
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22866442
under Data Source=Test.xls .. please provide full path of excel
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22866443
Hi,
Has the "Details" workbook any filter / sorting applied? Because, behind the scenes, it changes the workbooks names.

Also, you can query the schema of the data source by calling  _reader.GetSchemaTable.
Have a look at the resulting DataTable and see if you can find something there.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now