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.