ADO.net and Excel  
Author Message
mcrisf





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

I am trying to open an excel file from a asp.net page (C#) using ADO.net. all works good...but if the the excel worksheet has a name different from [Sheet1$] the code goes in error...

Any idea how to solve this problem... how can i discover the name of the worksheet

Here my code:

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";

sConnectionString += "Data Source=" + DirFileName + ";";

sConnectionString += "Extended Properties=\"Excel 8.0;HDR=YES;\";

OleDbConnection objConn = new OleDbConnection(sConnectionString);

try

{

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

OleDbDataAdapter objAdapter = new OleDbDataAdapter();

objAdapter.SelectCommand = objCmdSelect;

DataTable myExcelTable = new DataTable();

//DataSet myExcelDataSet = new DataSet();

//objAdapter.Fill(myExcelDataSet);

objAdapter.Fill(myExcelTable);

objConn.Close();

}



.NET Development3  
 
 
Figo Fei - MSFT





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

Hi, mcrisf

As you code:OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

So It read the [Sheet1$] of the excel file, of course.

If you change the name of the sheet in the file, you must change the corresponding code.

Thank you.



 
 
mcrisf





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

I know that i need to change the name of the worksheet...

My question was....is there a way to refer to the first worksheet (WITHOUT using the name of the worksheet) in a general way

I dont know what will be the name of the worksheet.it can change every time...so i need to use "something" (ex. a number ) to intercept the first worksheet...

any idea

thank you

mcrisf


 
 
Marlon





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

You could use Excel automation objects. The function below shows how you can get the name of the first sheet.

Some notes:

  • There's a problem, you need to have Excel installed on the server that'll run this code.
  • Add the reference to the Excel API in your project.
  • I don't remember if Workboos.Worksheets is a zero-based index or one-based . If get_Item(1) gets the second sheet, use 0.

public string GetWorkSheetName(string FileName)
{
Excel.Application excelApp = new Excel.ApplicationClass();
Excel.WorkbookClass wb = (Excel.WorkbookClass) excelApp.Workbooks.Open(
FileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet) wb.Worksheets.get_Item(1);
string sName= ws.Name;
wb.Close();
excelApp.Quit();
return sName;
}



 
 
mcrisf





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

thank you for the answer...but i dont want to use the excel API or library...I would like to avoid these solutions....any other idea thx

mcrisf


 
 
mcrisf





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

i found out the answer

read this article

http://davidhayden.com/blog/dave/archive/2006/05/31/2975.aspx


 
 
Figo Fei - MSFT





PostPosted: .NET Framework Data Access and Storage, ADO.net and Excel Top

Hi, mcrisf

You can use GetOleDbSchemaTable to get the sheet name first and then access the sheet data.

Following example show a method return the first sheet name of a specific excel file:



public string ExcelSheetNames(String excelFile)
{
DataTable dt;
string connString = ExcelExtendedConn(excelFile);
using (OleDbConnection objConn = new
OleDbConnection(connString))
{
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
string name = dt.Rows[0]["TABLE_NAME"].ToString();
if(name[0] == '\'')
{
//numeric sheetnames get single quotes around them in the schema.
//remove them here

{
name = name.Substring(1, name.Length - 2);
}
}
}
}


Thank you