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. 

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.

Proudly sponsored by

Moriyama

  • Moriyama build, support and deploy Umbraco, Azure and ASP.NET websites and applications.
AppVeyor

  • CI/CD service for Windows, Linux and macOS
  • Build, test, deploy your apps faster, on any platform.
stkrs

  • Custom stickers for startups, artists and brands.
  • Bespoke easy-apply, removable, custom brand stickers printed in the UK.
elmah.io

  • elmah.io is the easy error logging and uptime monitoring service for .NET.
  • Take back control of your errors with support for all .NET web and logging frameworks.
uSync Complete

  • uSync.Complete gives you all the uSync packages, allowing you to completely control how your Umbraco settings, content and media is stored, transferred and managed across all your Umbraco Installations.