Excel: alle Tabellen über OleDb in DataSet lesen

/// <summary>
/// Holt die Daten aus einer Excel-Datei in ein DataSet
/// </summary>
/// <param name="excelFile">Pfad zur Excel-Datei</param>
/// <param name="headers">Haben die Tabellenspalten Überschriften</param>
/// <returns>DataSet</returns>
private DataSet GetExcelDataSet(string excelFile, bool headers)
{
	DataSet ds = new DataSet();

	OleDbConnection con = new OleDbConnection();
	con.ConnectionString = "Data Source=" + excelFile + 
		";Provider=Microsoft.Jet.OLEDB.4.0;";
	
	if (headers)
		con.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes""";
	else
		con.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=No""";
	
	con.Open();
	DataTable sheets = con.GetOleDbSchemaTable(
		OleDbSchemaGuid.Tables, 
		new object[] {null, null, null, "TABLE"});

	foreach (DataRow sheet in sheets.Rows)
	{
		string tableName = sheet["Table_Name"].ToString();
		string sql = "SELECT * FROM [" + tableName + "]";
		OleDbDataAdapter adap = new OleDbDataAdapter(sql, con);
		adap.Fill(ds, tableName);
	}
	
	con.Close();
	return ds;
}

/// <summary>
/// Holt die Daten aus einer Excel-Datei in ein DataSet
/// </summary>
/// <param name="excelFile">Pfad zur Excel-Datei</param>
/// <param name="headers">Haben die Tabellenspalten Überschriften</param>
/// <returns>DataSet</returns>
private DataSet GetExcelDataSet(string excelFile, bool headers)
{
	DataSet ds = new DataSet();

	OleDbConnection con = new OleDbConnection();
	con.ConnectionString = "Data Source=" + excelFile + 
		";Provider=Microsoft.Jet.OLEDB.4.0;";
	
	if (headers)
		con.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes""";
	else
		con.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=No""";
	
	con.Open();
	DataTable sheets = con.GetOleDbSchemaTable(
		OleDbSchemaGuid.Tables, 
		new object[] {null, null, null, "TABLE"});

	foreach (DataRow sheet in sheets.Rows)
	{
		string tableName = sheet["Table_Name"].ToString();
		string sql = "SELECT * FROM [" + tableName + "]";
		OleDbDataAdapter adap = new OleDbDataAdapter(sql, con);
		adap.Fill(ds, tableName);
	}
	
	con.Close();
	return ds;
}

Alte URL: /snippet/excel-alle-tabellen-ueber-oledb-in-dataset-lesen/637