Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

How to read the Excel file

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
SwamyN
Asked:
SwamyN
1 Solution
 
DhaestCommented:
Can you check the names of your sheets in the excel-file.
Perhaps you should try: "Select Name,ID from [Details] "
0
 
SwamyNAuthor Commented:
I done that but getting same error..
0
 
DhaestCommented:
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
 
Gyanendra SinghArchitectCommented:
under Data Source=Test.xls .. please provide full path of excel
0
 
Juan_BarreraCommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now