Solved

How to read the Excel file

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
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…

734 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