In this simple blog I’m going to explain within few
steps how we can connect to a MS Excel file and read the first sheet content
in a separated dataset…
Right ..this is a very useful strategy …where we really
take advantages from this in improving our software’s …
Connect
to excel …
We can connect to Excel file in the same way we connect
to any other data source such as MSSQLServer …in general all we need is a valid
connection-string and connection instance to connect …
Below code sample illustrate a connection-string
example to connect Excel file …
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + strPath + ";Extended
Properties=Excel 12.0;";
Note that the variable strPath
is the actually path for the excel file …now we can use the connection instance
to open the connection directly to the excel file…as illustrated below ….
OleDbConnection MyConnection = new OleDbConnection(connStr);
Import to Dataset …
Below code illustrate how we can read the first sheet in
the excel file and fill it into a dataset instance …
// Declarations
:
DataTable dtFullData;
OleDbDataAdapter MyAdapter;
OleDbCommand CmdSelect;
// Connect to
the excel file :
OleDbConnection MyConnection = new OleDbConnection(connStr);
// Prepare
Command :
CmdSelect = new
OleDbCommand(@"SELECT
* FROM [Sheet1$]", MyConnection);
// Fill DataSet
:
dtFullData = new DataTable("ExcelSheet1");
MyAdapter.FillSchema(dtFullData,
SchemaType.Source);
MyAdapter.Fill(dtFullData);
From the code above it’s a very simple …all we need is
to execute the command in the excel file (such as this command or query SELECT * FROM [Sheet1$] ) and fill the dataset
with the result using the adapter …
Hope this help …
