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.