This post shows you how you can create the functionality to export data as a CSV file to download and open in Excel.

The approach I take with this, is to load the data into a DataTable object first, which has the column names and the data in the columns.

I can then pass the DataTable into a method which outputs the DataTable to a CSV response. This means, the data can come from any source, as long as it gets loaded into a DataTable.

/// <summary>
/// Creates a response as a CSV with a header row and results of a data table 
/// </summary>
/// <param name="dt">DataTable which holds the data</param>
/// <param name="fileName">File name for the outputted file</param>
public static void WriteDataTableToCSV(DataTable dt, string fileName)
{
    WriteOutCSVResponseHeaders(fileName);
    WriteOutDataTable(dt);
    HttpContext.Current.Response.End();
}
/// <summary>
/// Writes out the response headers needed for outputting a CSV file.
/// </summary>
/// <param name="fileName">File name for the outputted file</param>
public static void WriteOutCSVResponseHeaders(string fileName)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}-{1}.csv", fileName, DateTime.Now.ToString("dd-MM-yyyy-hh-mm-ss")));
    HttpContext.Current.Response.AddHeader("Pragma", "public");
    HttpContext.Current.Response.ContentType = "text/csv";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
}

/// <summary>
/// Writes out the header row and data rows from a data table.
/// </summary>
/// <param name="dt">DataTable which holds the data</param>
public static void WriteOutDataTable(DataTable dt)
{
    WriteOutHeaderRow(dt, dt.Columns.Count);
    WriteOutDataRows(dt, dt.Columns.Count, dt.Rows.Count);
}

/// <summary>
/// Writes the header row from a datatable as Http Response
/// </summary>
/// <param name="dt">DataTable which holds the data</param>
/// <param name="colCount">Number of columns</param>
private static void WriteOutHeaderRow(DataTable dt, int colCount)
{
    string CSVHeaderRow = string.Empty;
    for (int col = 0; col <= colCount - 1; col++)
    {
        CSVHeaderRow = string.Format("{0}\"{1}\",", CSVHeaderRow, dt.Columns[col].ColumnName);
    }
    WriteRow(CSVHeaderRow);
}
/// <summary>
/// Writes the data rows of a datatable as Http Responses
/// </summary>
/// <param name="dt">DataTable which holds the data</param>
/// <param name="colCount">Number of columns</param>
/// <param name="rowCount">Number of columns</param>
private static void WriteOutDataRows(DataTable dt, int colCount, int rowCount)
{
    string CSVDataRow = string.Empty;
    for (int row = 0; row <= rowCount - 1; row++)
    {
        var dataRow = dt.Rows[row];
        CSVDataRow = string.Empty;
        for (int col = 0; col <= colCount - 1; col++)
        {
            CSVDataRow = string.Format("{0}\"{1}\",", CSVDataRow, dataRow[col]);
        }
        WriteRow(CSVDataRow);
    }
}

/// <summary>
/// Write out a row as an Http Response.
/// </summary>
/// <param name="row">The data row to write out</param>
private static void WriteRow(string row)
{
    HttpContext.Current.Response.Write(row.TrimEnd(","));
    HttpContext.Current.Response.Write(Environment.NewLine);
}

Here is a simple example to show you how to use the above code, like how it works on my Free Online Fixture List Generator Tool (after you add a team, you will see the download button):

private static void ExportFixtures(List<Match> allMatches)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("FixtureNo", typeof(int));
    dt.Columns.Add("Home", typeof(string));
    dt.Columns.Add("v", typeof(string));
    dt.Columns.Add("Away", typeof(string));     foreach (var match in allMatches)
    {
        dt.Rows.Add(match.FixtureNo, match.Home, "v", match.Away);  
    }     WriteDataTableToCSV(dt, "fixtures.csv");
}

In my next post, I will show you how to do this with a Stored Procedure.

Hopefully this should be of use to you. If you like it, use the social buttons to share it with your friends and colleagues. 

Want to thank me?

If I've helped you out and you want to thank me, why not buy me a coffee?

About the author

Paul Seal

Umbraco MVP and .NET Web Developer from Derby (UK) who specialises in building Content Management System (CMS) websites using MVC with Umbraco as a framework. Paul is passionate about web development and programming as a whole. Apart from when he's with his wife and son, if he's not writing code, he's thinking about it or listening to a podcast about it.

Related Posts

How to solve the error "Could not load file or assembly 'Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0 ..."

This post will show you how to solve the error "Could not load file or assembly 'Microsoft.CodeDom.P…

Read Post

How to use Donut Caching in Umbraco and MVC

In this post I show you how easy Donut Caching is to use in Umbraco and MVC. It's very clever and no…

Read Post

How to start working with Umbraco v8

In this post I give you step by step instructions for getting Umbraco v8 forked, checked out and wor…

Read Post

How to add default values to Umbraco properties in the backoffice

This post shows you how you can easily add a default value to a property when using the editor in th…

Read Post