Loading data from Excel 2007

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;

    }

}