The fastest way to export data to Excel

Export data to Excel in ASP.NET C#

It is not so rare that you have to provide some sort of export of your data stored in database. I have a feeling that clients really like to have that functionality, probably because they are more comfortable with Excel than with any other tool.

First thing that might pop to your mind is to add a reference to Office library, but that would require that you have Office on the hosting machine which might not be the case as most of hosts do not have it.

Second thing is that if you add reference to Office library you would probably end up with using of unmanaged code which, sometimes is not so easy to handle regarding releasing the memory and processes that support it.

Luckily you do not need to have Office installed on your hosting machine to produce an Excel sheet. So far, I've been doing this two ways:

Exporting data to CSV (Comma Separated Values) file

CSV is legacy, but still often used

DataTable result = new DataTable();
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorks2008R2"].ConnectionString;
DateTime startDate = SqlDateTime.MinValue.Value;
DateTime endDate = SqlDateTime.MaxValue.Value;
if (!DateTime.TryParse(this.dpStartDate.Text, out startDate))
{
startDate = SqlDateTime.MinValue.Value;
}
if (!DateTime.TryParse(this.dpEndDate.Text, out endDate))
{
endDate = SqlDateTime.MaxValue.Value;
}
using (SqlCommand command = new SqlCommand())
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
command.CommandText = "SELECT * FROM Employee WHERE HireDate BETWEEN @StartDate AND @EndDate";
command.Connection = connection;
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@StartDate", startDate);
command.Parameters.AddWithValue("@EndDate", endDate);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
if (result != null)
{
using (result)
{
Response.ContentEncoding = new UTF8Encoding(true);
Response.Charset = Encoding.UTF8.WebName;
Response.ContentType = "application/vnd.ms-excel; charset=utf-8";
                            Response.AddHeader("content-disposition", string.Format("attachment;filename=dataexport_{0}.csv", DateTime.Now.ToString("yyyyMMddHHmmss")));
                            StringBuilder fileContent = new StringBuilder();
                            foreach (var col in result.Columns)
                            {
                                fileContent.Append(col.ToString()   ",");
                            }
                            fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
                            foreach (DataRow dr in result.Rows)
                            {
                                foreach (var column in dr.ItemArray)
                                {
                                    fileContent.Append("""   column.ToString()   "",");
                                }
                                fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
                            }

                            //Write encoding characters first
                            Response.Write('\uFEFF');

                            //Write the content
                            Response.Write(fileContent.ToString());

                            Response.End();
                        }
                    }
                }
            }
        }
    

at for exporting tabular data. Excel is pretty good in dealing with CSV data, so all you have to do in your ASP.NET application is to return CSV data with headers that will offer user to open file using Excel application.

For example I'm going to export filtered employee records from MS sample AdventureWorks database which can be found on Microsoft's website http://msftdbprodsamples.codeplex.com/releases/view/93587

Note

For the complicity of code, I used simple ADO parametarized query for fetching data

 DataTable result = new DataTable();
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorks2008R2"].ConnectionString;
        DateTime startDate = SqlDateTime.MinValue.Value;
        DateTime endDate = SqlDateTime.MaxValue.Value;

        if (!DateTime.TryParse(this.dpStartDate.Text, out startDate))
        {
            startDate = SqlDateTime.MinValue.Value;
        }
        if (!DateTime.TryParse(this.dpEndDate.Text, out endDate))
        {
            endDate = SqlDateTime.MaxValue.Value;
        }
        using (SqlCommand command = new SqlCommand())
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                command.CommandText = "SELECT * FROM Employee WHERE HireDate BETWEEN @StartDate AND @EndDate";
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@StartDate", startDate);
                command.Parameters.AddWithValue("@EndDate", endDate);
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    adapter.Fill(result);
                    if (result != null)
                    {
                        using (result)
                        {
                            Response.ContentEncoding = new UTF8Encoding(true);
                            Response.Charset = Encoding.UTF8.WebName;
                            Response.ContentType = "application/vnd.ms-excel; charset=utf-8";
                            Response.AddHeader("content-disposition", string.Format("attachment;filename=dataexport_{1}.csv", DateTime.Now.ToString("yyyyMMddHHmmss")));
                            StringBuilder fileContent = new StringBuilder();
                            foreach (var col in result.Columns)
                            {
                                fileContent.Append(col.ToString() + ",");
                            }
                            fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
                            foreach (DataRow dr in result.Rows)
                            {
                                foreach (var column in dr.ItemArray)
                                {
                                    fileContent.Append("\"" + column.ToString() + "\",");
                                }
                                fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
                            }

                            //Write encoding characters first
                            Response.Write('\uFEFF');

                            //Write the content
                            Response.Write(fileContent.ToString());

                            Response.End();
                        }
                    }
                }
            }
        }
    

Exporting data as HTML table

This is another way to push non native Excel file to Excel and make it displayed as tabular data. The idea is to load DataTable or any collection data source and bind it to a GridView.

When it's binded, render out control layout which will be an HTML table and push it to excel by attaching headers, same as in previous example.

 DataTable result = new DataTable();
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorks2008R2"].ConnectionString;
        DateTime startDate = SqlDateTime.MinValue.Value;
        DateTime endDate = SqlDateTime.MaxValue.Value;

        if (!DateTime.TryParse(this.dpStartDate.Text, out startDate))
        {
            startDate = SqlDateTime.MinValue.Value;
        }
        if (!DateTime.TryParse(this.dpEndDate.Text, out endDate))
        {
            endDate = SqlDateTime.MaxValue.Value;
        }
        using (SqlCommand command = new SqlCommand())
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                command.CommandText = "SELECT * FROM Employee WHERE HireDate BETWEEN @StartDate AND @EndDate";
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@StartDate", startDate);
                command.Parameters.AddWithValue("@EndDate", endDate);
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    adapter.Fill(result);
                    if (result != null)
                    {
                        using (result)
                        {
                            Response.ContentEncoding = new UTF8Encoding(true);
                            Response.Charset = Encoding.UTF8.WebName;
                            Response.ContentType = "application/vnd.ms-excel; charset=utf-8";
                            Response.AddHeader("content-disposition", string.Format("attachment;filename=dataexport_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));

                            StringWriter sw = new StringWriter();
                            HtmlTextWriter hw = new HtmlTextWriter(sw);
                            var grid = new GridView();
                            grid.DataSource = result;
                            grid.DataBind();
                            grid.RenderControl(hw);


                            //Write encoding characters first
                            Response.Write('\uFEFF');

                            //Write the content
                            Response.Write(sw);

                            Response.End();
                        }
                    }
                }
            }
        }
    
Note

GridView control does not need to have DataTable or DataSource as a source to bind with. You can bind it to pretty much any kind of collection.

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article