EziData Solutions

Web, NET and SQL Server

Read Excel 2000-2003 into Dataset

There are times when you need to deal with data stored in an Excel spreadsheet directly, without first importing it into your database of choice (Access or SQL Server for instance).

One easy way is to use the OleDB Data Provider in NET to retrieve the data from Excel and load it into a DataSet. If you have been using Access 2000-2003 as a data source in your .NET projects then you may have already come across the OleDB Namespace. According to Microsoft, the OleDb Namespace provides a number of classes used to access OLE data sources.

The basic steps required to connect to Excel using OleDB is as follows:

  1. Create a connection
  2. Create a command (such as a SELECT) to retrieve the data you want
  3. Create a data adapter using this command
  4. Fill a dataset with the results

    public DataSet OpenExcel(string FileName)

    {

        //read excel file using OleDB and return results in a DataSet

 

        DataSet ds = new DataSet();

 

        //FileName contains the full filepath

        string connstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0";

        OleDbConnection conn = new OleDbConnection(connstr);

 

        //select everything from the Worksheet named Merge Data

        string strSQL = "SELECT * FROM [Merge Data$]";

 

        OleDbCommand cmd = new OleDbCommand(strSQL, conn);

 

        //use the command to fill out DataSet

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        da.Fill(ds);

 

        return ds;

    }

In the code snippet above we are feeding in the full filename, including path into our function. The SELECT statement grabs everything from a Worksheet named Merge Data and returns the results in a DataSet.

Posted: Feb 28 2006, 07:22 by CameronM | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: ASP.NET | C#