Get Sheet Name from Imported Excel File 1

[theoryofben]

Hello All,

First off, thanks in advance. I am completely new to .net and I have been given a project at work for our intranet.

I need to import an excel file, do some queries on an SQL database and then output to a new excel file.

I have it all working great, except that my test file had a generic "Sheet1" worksheet name. But the actual files will not. They will be different. But there will only be one worksheet per file.

How do I either get the actual sheet name or is it possible to reference the sheet by number? My code is below.

Dim oleconn as OleDbConnection=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &_
Server.MapPath("/matchreport/uploaded/SampleBio.xls") & ";" & "Extended Properties=Excel 8.0;")

Dim selectCmd as OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oleconn)

Thanks.

Ben

"Insanity in individuals is something rare - but in groups, parties, nations and epochs, it is the rule."
Friedrich Nietzsche

 

[icrf]

You know, several weeks back, I ran into the same issue but never really looked at it, as my users should have been working off the same template excel document. A quick google did turn up this, from which I'll quote code:

http://www.codeproject.com/aspnet/getsheetnames.asp

/// <summary>
/// This mehtod retrieves the excel sheet names from 
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
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();
    }
  }
}

Definitely going to try that tomorrow, as the scope of my original work weeks ago has changed.

________________________________________
Andrew

I work for a gift card company!

 

[theoryofben]

Awesome, that's exactly what I need. I'll also try it tomorrow. I will be very relieved to complete this project.

Thanks a ton dude.

Ben

"Insanity in individuals is something rare - but in groups, parties, nations and epochs, it is the rule."
Friedrich Nietzsche

 

[theoryofben]

Ok, I'm using VB instead of C#, so I found a page on msdn that deals with this exact topic. Hope that it helps someone else.

http://support.microsoft.com/kb/318373/EN-US/

the code I used was:

Dim oleconn as OleDbConnection=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
		Server.MapPath("/matchreport/uploaded/current.xls") & ";" & "Extended Properties=Excel 8.0;")
	Dim dt as DataTable
	Dim tableName
	
	oleconn.Open
	
	dt = oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
	tableName=dt.Rows(0)("TABLE_NAME").ToString

Ben

"Insanity in individuals is something rare - but in groups, parties, nations and epochs, it is the rule."
Friedrich Nietzsche

 

[icrf]

Yeah, I saved the extra DT and went for more line density.

oleconn.Open
Dim tableName as String = oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString

I guess they still require the use of a sheet name because working off a sheet index wouldn't make it through the generalized ADO.NET SQL parser? Anyway, one line of code to get the sheet name, I'm content.

________________________________________
Andrew

I work for a gift card company!