Solved

How to read the Excel file

Posted on 2008-11-03
5
391 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 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