Solved

How to read the Excel file

Posted on 2008-11-03
5
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

627 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