Exporting data to Excel

In this article we will see how we can export data to an Excel file in a web application where the Interop library might no be available. We saw a similar approach in a previous article but that approach generates a file that is not a proper Excel file, rather it was a text file containing an HTML table, something that Excel can open.

In this article we will make use of the ISAM engine that allows us to see an Excel file as a DataSource. That allows us to insert data with SQL queries, but there are limitations. For example, you cannot run a DELETE query.

Load data to an Excel file

The worksheet in the Excel file is seen as an SQL table, each column as a column of an SQL table. The entries in the first row act as names of the columns. Let’s assume that we have a table named Customers with data for First name, Last name, Email and Phone. Here is what our Excel template file should look like.

The following snippet shows us how we can load data to out excel template file. Note the $ character that follows the name of the worksheet.

using System.Data.OleDb;
 
string path = "C:\path\to\excel\template.xls";
OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;");
myConn.Open();
 
// Load your data to a List
 
foreach (Customer customer in customers)
{
    OleDbCommand myComm = new OleDbCommand("INSERT INTO [Customers$] ([First name],[Last name],[Email],[Phone]) VALUES (@v1,@v2,@v3,@v4)", myConn);
 
    myComm.Parameters.Add("@v1", OleDbType.VarWChar).Value = customer.firstName;
    myComm.Parameters.Add("@v2", OleDbType.VarWChar).Value = customer.lastName;
    myComm.Parameters.Add("@v3", OleDbType.VarWChar).Value = (!string.IsNullOrEmpty(customer.email) ? customer.email: "-");
    myComm.Parameters.Add("@v4", OleDbType.VarWChar).Value = customer.phone;
 
    myComm.ExecuteNonQuery();
    myComm.Dispose();
}
myConn.Close();

There are two things to keep in mind:

1. You cannot add NULL values. If any of the values assigned to a parameter is NULL, you will get an exception, so you could make a check and pass some value, as there is in the above example for the email value
2. You cannot insert text bigger than 255 characters. If you try to insert a value that is more than 255 characters long you will also get an exception. Later in the article, we will see how we can overcome this

Create the template structure at runtime

The above approach works with the limitations mentioned, but usually having to maintain the structure of the Excel template file might be cumbersome. In the following snippet we will see how we can create a new Worksheet with the desired structure. You only need an empty Excel file as a template, that will contain at least one empty Worksheet.

using System.Data.OleDb;
 
string path = "C:\path\to\excel\template.xls";
OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;");
myConn.Open();
 
OleDbCommand myComm = new System.Data.OleDb.OleDbCommand("CREATE TABLE [Customers] ([First name],[Last name],[Email],[Phone])", myConn);
myComm.ExecuteNonQuery();
myComm.Dispose();
 
myConn.Close();

Allow insertion of data bigger than 255 characters

As mentioned earlier trying to insert data in a cell that is more than 255 characters long we will get an exception. We can easily overcome this if our Excel template has some data already in it.

And that way we are able to insert large data to the column [Last name].

It is only logical for us to want to remove the dummy data from our export. As we can see, the [First name] has the value fn. This is useful to write our UPDATE statement that will remove the dummy data from our file.

using System.Data.OleDb;
 
string path = "C:\path\to\excel\template.xls";
OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;");
myConn.Open();
 
myComm = new OleDbCommand("UPDATE [Customers$] SET [First name] = '', [Last name] = '' WHERE [First name] = 'fn'", myConn);
myComm.ExecuteNonQuery();
myComm.Dispose();
 
myConn.Close();

Useful links

I have written a couple more articles about exporting data to an Excel file that you might find useful.

• Export GridView or Repeater to Excel
• Export data to Excel for non latin characters