Data interoperability with Microsoft office applications has become easier with ADO.net.
The Microsoft Office System exposes objects through COM objects. Microsoft released a suite of Primary Interop Assemblies (PIAs) that are optimized for accessing COM objects from .NET-based assemblies. These generally get installed into your system when you install MS office.
The data can be easily transferred from an Excel spreadsheet to an access database using ADO.net and suite of these Microsoft office interop assemblies. Here first we will make use of Microsoft Jet OLE DB provider to establish connection to an excel spreadsheet. The process to be followed is as under:
Create an Excel sheet you want to transfer data from. Let's assume the file is named Book.xls and the first sheet is the default sheet Sheet1.
Add reference to Microsoft Office Access Interop Assembly.
Right click on added reference's property to ensure that the Path of the assembly points to GAC.
e.g.:
C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Access\10.0.4504.0__31bf3856ad364e35\Microsoft.Office.Interop.Access.dll
Remove any previously created Access file and create a new one to import data into.
if (File.Exists(@"C:\Book.mdb"))
{
File.Delete(@"C:\ Book.mdb");
}
Access.Application _accessData;
_accessData = new Access.ApplicationClass();
_accessData.Visible = false;
_accessData.NewCurrentDatabase(@"C:\ Book.mdb");
_accessData.CloseCurrentDatabase();
_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
_accessData = null;
Now let's establish connection to our data source (Excel file) using Microsoft Jet OLE DB provider.
string _filename = @"C:\Book.xls";
string _conn;
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + _fileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection _connection = new OleDbConnection(_conn);
Use OledbCommand object to select all the data from sheet1 and execute a ExecuteNonQuery to import data into Book.mdb.
OleDbCommand _command = new OleDbCommand();
_command.Connection = _connection;
try
{
_command.CommandText = @"SELECT * INTO [MS Access;Database=C:\Book.mdb].[Sheet1] FROM [Sheet1$]";
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
MessageBox.Show("The import is complete!");
}
catch (Exception)
{
MessageBox.Show("Import Failed, correct Column name in the sheet!");
}
This will create a new mdb data file called Book.mdb on your disc containing Sheet1 data. You will notice that table Sheet1 in Book.mdb file has taken the first rows of Sheet1 as the column names. That means that first row of your sheet contains header row data. The attribute "HDR=yes;" in connection string specifies this.
The full Code is here:
//call this method by supplying it the Data Source file //name, which in the example is Book.xls
public static void CheckUpdateDBFile(string filename)
{
if (File.Exists(@"C:\Book.mdb"))
{
File.Delete(@"C:\Book.mdb");
}
Access.Application _accessData;
_accessData = new Access.ApplicationClass();
_accessData.Visible = false;
_accessData.NewCurrentDatabase(@"C:\Book.mdb");
_accessData.CloseCurrentDatabase();
_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
_accessData = null;
OleDbConnection _connection = MakeExcelConnection(filename);
FillAccessDatabase(_connection);
}
private static OleDbConnection MakeExcelConnection(string fileName)
{
string _conn;
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + fileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection _connection = new OleDbConnection(_conn);
return _connection;
}