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?

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 assets file project.assets.json not found in Visual Studio

This post tells you how to solve the error assets file project.assets.json not found in Visual Studi…

Read Post

Code to help you debug an umbraco issue on a remote site

This post gives you some razor code to help you see the values of the IPublishedContent item's prope…

Read Post

How to set the default page base type to UmbracoViewPage in Umbraco

In this post I share with you what Ronald Barendse taught us about setting the pageBaseType in Umbra…

Read Post

How to fix corrupt accented characters in Excel for a C# export as CSV

This post will help you fix the issue where you csv export has corrupt characters when opening it in…

Read Post