Así he conseguido hacerlo yo:
public String[] getHojas() {
String[] listadoHojas;
DbConnection connection;
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
try {
DataTable worksheets;
connection = factory.CreateConnection();
String connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ruta + ";Extended Properties=\"Excel 8.0;HDR=Yes\"";
connection.ConnectionString = connectionString;
connection.Open();
worksheets = connection.GetSchema("Tables");
listadoHojas = new String[worksheets.Rows.Count];
int i = 0;
foreach (DataRow dr in worksheets.Rows)
{
string hoja = dr["TABLE_NAME"].ToString();
//las siguientes líneas "limpian" el nombre
hoja= hoja.Substring(0,hoja.LastIndexOf("$"));
if (hoja.IndexOf("'") == 0)
hoja = hoja.Remove(0, 1);
listadoHojas[i++] = hoja;
}
connection.Close();
return listadoHojas;
}
catch (Exception e) {
throw new Exception(e.Message, e);
}
}
Y luego, para acceder a los datos de una hoja concreta:
DataSet dsMsExcel = new DataSet();
DbConnection connection;
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
try
{
if (hoja.Contains(" ") || hoja.Contains("-"))
hoja= "['" + hoja+ "$']";
else
hoja= "[" + hoja+ "$]";
DbDataAdapter adaptador = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT * FROM " + hoja;
connection = factory.CreateConnection();
String connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ruta + ";Extended Properties=\"Excel 8.0;HDR=Yes\"";
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adaptador.SelectCommand = selectCommand;
dsMsExcel.Tables.Clear();
adaptador.Fill(dsMsExcel);
return dsMsExcel;
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}