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 you r.NET projects then youmay 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 followd:
- Create a connection
- Create a command specifying what data you want
- Use this command to fill a DataSet (via a DataAdapter)
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;
}