Project: Excel.OleDb

Get schema and data of an Excel worksheet using OleDb:
void Main()
{
	var stringBuilder = new OleDbConnectionStringBuilder();
	stringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
	stringBuilder.DataSource = @"file.xls";
	stringBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1");
	
	using (var connection = new OleDbConnection(stringBuilder.ToString().Dump()))
	{
		connection.Open();
		var schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
		var sheetName = schemaTable.AsEnumerable().Select(row => row.Field<string>("TABLE_NAME")).First();
		schemaTable.Dump();
	
		using (var command = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), connection))
		{
			var dataSet = new DataSet();
			command.Fill(dataSet, sheetName);
			dataSet.Dump();
		}
	}
}

Last edited May 14, 2012 at 11:46 AM by teoarch, version 2

Comments

bvisual Dec 17, 2015 at 9:51 AM 
I use a more upto data connection string:
var stringBuilder = new OleDbConnectionStringBuilder();
stringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
stringBuilder.DataSource = @"file.xlsx";
stringBuilder.Add("Extended Properties", "HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;");
stringBuilder.Add("Jet OLEDB:Engine Type","34");