Dot Net Thoughts

In my web application, we used to import data from MS Excel file. Few days before one of the client representative posted an issue that he can’t import data from XLSX (MS Excel 2007 File Format) files. Previously we were using OLE DB provider to import data from Excel file (Checkout my previous post regarding How to Import / Export from C# : IMPORT / EXPORT DATA IN MS EXCEL USING C# ), but Microsoft worked on the Office 2007 file formats, and we are not able to connect to Excel using Microsoft.Jet.OLEDB. Yesterday I got a chance to work on this module, and thought of implementing XLSX support. After few searches I found a new provider from Microsoft to connect to Excel 2007 files, called Microsoft.ACE.OLEDB.12.0.

/// <summary>
/// Imports Data from Microsoft Excel File.
/// </summary>
/// <param name="FileName">Filename from which data need to import</param>
/// <returns>List of DataTables, based on the number of sheets</returns>
private List<DataTable> ImportExcel(string FileName)
{
    List<DataTable> _dataTables = new List<DataTable>();
    string _ConnectionString = string.Empty;
    string _Extension = Path.GetExtension(FileName);
    //Checking for the extentions, if XLS connect using Jet OleDB
    if (_Extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
    {
        _ConnectionString =
            "Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0";
    }
    //Use ACE OleDb
    else if (_Extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
    {
        _ConnectionString =
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
    }

    DataTable dataTable = null;

    using (OleDbConnection oleDbConnection =
        new OleDbConnection(string.Format(_ConnectionString, FileName)))
    {
        oleDbConnection.Open();
        //Getting the meta data information.
        //This DataTable will return the details of Sheets in the Excel File.
        DataTable dbSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
        foreach (DataRow item in dbSchema.Rows)
        {
            //reading data from excel to Data Table
            using (OleDbCommand oleDbCommand = new OleDbCommand())
            {
                oleDbCommand.Connection = oleDbConnection;
                oleDbCommand.CommandText = string.Format("SELECT * FROM [{0}]",
                    item["TABLE_NAME"].ToString());
                using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
                {
                    oleDbDataAdapter.SelectCommand = oleDbCommand;
                    dataTable = new DataTable(item["TABLE_NAME"].ToString());
                    oleDbDataAdapter.Fill(dataTable);
                    _dataTables.Add(dataTable);
                }
            }
        }
    }
    return _dataTables;
}

The connection string also supports HDR attribute, which decides, whether the Excel file first row is header or not. It can be either Yes or No. It can be used with both Excel 2003 and Excel 2007 files.

Update: You can download the Microsoft.ACE.OLEDB.12.0 provider from Microsoft : 2007 Office System Driver: Data Connectivity Components

Sometimes we may require to generate Excel file from our reports,read from excel files to import data etc. This can be achieved using Office Interop (Office Automation) assemblies, but Office Automation in Web servers,got some issues;(More details: http://support.microsoft.com/kb/257757). The alternative is using OleDb provider. You may need to add one more attribute to connection string to connect to the Excel file. And connection string will be

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"

Please note the “Extended Properties” attribute. This attribute helps us to query the excel file.

Exporting Data from Data Table to Excel File.

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"
// Establish a connection to the data source.
using(OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open()
//creating a new Sheet with name sample and three columns with Heading firstname, lastname and email
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "CREATE TABLE [Sample$](FirstName Char(255), LastName char(255), Email char(255))";
command.ExecuteNonQuery();
}
//Adding records to the Sample Worksheet
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "INSERT INTO TABLE [Sample$](FirstName,LastName,Email) VALUES('Anuraj','P','anuraj.p@example.com')";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO TABLE [Sample$](FirstName,LastName,Email) VALUES('sreekumar','vn','sreekumar.vn@example.com')";
command.ExecuteNonQuery();
}
}

Import the Data from Excel

DataTable dt;
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"
// Establish a connection to the data source.
using(OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open()
//reading data from excel to Data Table
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "SELECT * FROM [Sample]";
using(OleDbDataAdapter adapter =new OleDbDataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(dt);
}
}
}

You can get more information from Microsoft support site

http://support.microsoft.com/kb/306023 – How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET