I’m playing around with code generation using Visual Studio 2008 T4 and I needed to pull in some data. Easiest way to create the data is using Excel and then pull it into a dataset. Using the right connection string this becomes very easy.
The code below shows how to select the data from a worksheet.
public class Excel2007Reader
{
private static string BuildExcelConnection( string filename )
{
return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0;HDR=YES;""";
}
public static DataSet Read( string filename, string worksheet, string range, string tableName )
{
DataSet result = new DataSet();
string connectionString = BuildExcelConnection( filename );
string select = @"SELECT * FROM [" + worksheet + "$" + range + "]";
using ( OleDbConnection conn = new OleDbConnection( connectionString ) )
{
conn.Open();
using ( OleDbCommand cmd = new OleDbCommand( select, conn ) )
{
OleDbDataAdapter da = new OleDbDataAdapter( cmd );
da.Fill( result, tableName );
}
conn.Close();
}
return result;
}
}