Generating CSV-files on .NET
I have project where I need to output some reports as CSV-files. I found a good library called CsvHelper from NuGet and it works perfect for me. After some playing with it I was able to generate CSV-files that were shown correctly in Excel. Here is some sample code and also extensions that make it easier to work with DataTables.
Simple report
Here’s the simple fragment of code that illustrates how to use CsvHelper.
using (var writer = new StreamWriter(Response.OutputStream))
using (var csvWriter = new CsvWriter(writer))
{
csvWriter.Configuration.Delimiter = ";";
csvWriter.WriteField("Task No");
csvWriter.WriteField("Customer");
csvWriter.WriteField("Title");
csvWriter.WriteField("Manager");
csvWriter.NextRecord();
foreach (var project in data)
{
csvWriter.WriteField(project.Code);
csvWriter.WriteField(project.CustomerName);
csvWriter.WriteField(project.Name);
csvWriter.WriteField(project.ProjectManagerName);
csvWriter.NextRecord();
}
}
Of course, you can use other methods to output whole object or object list with one shot. I just needed here custom headers that doesn’t match property names 1:1.
Generic helper for DataTable
Some of my projects come from service layer as DataTable. I don’t want to add new models or Data Transfer Objects (DTO) with no good reason and DataTable is actually flexible enough if you need to add new fields to report and you want to do it fast.
As DataTables are not supported by default (yet?), I wrote simple extension methods that work on DataTable views. When called on DataTable it selects default view automatically. The idea is – you can set filter on default data view and leave out the rows you don’t need. If you just want to show DataTable to screen as table then check out my posting Simple view to display contents of DataTable.
public static class CsvHelperExtensions
{
public static void WriteDataTable(this CsvWriter csvWriter, DataTable table)
{
WriteDataView(csvWriter, table.DefaultView);
}
public static void WriteDataView(this CsvWriter csvWriter, DataView view)
{
foreach (DataColumn col in view.Table.Columns)
{
csvWriter.WriteField(col.ColumnName);
}
csvWriter.NextRecord();
foreach (DataRowView row in view)
{
foreach (DataColumn col in view.Table.Columns)
{
csvWriter.WriteField(row[col.ColumnName]);
}
csvWriter.NextRecord();
}
}
}
And here is simple MVC controller action that gets data as DataTable and returns it as CSV-file. The result is CSV-file that opens correctly in Excel.
[HttpPost]
public void ExportIncomesReport()
{
var data = // Get DataTable here
Response.ContentType = "text/csv";
Response.AddHeader("Content-disposition", "attachment;filename=IncomesReport.csv");
var preamble = Encoding.UTF8.GetPreamble();
Response.OutputStream.Write(preamble, 0, preamble.Length);
using (var writer = new StreamWriter(Response.OutputStream))
using (var csvWriter = new CsvWriter(writer))
{
csvWriter.Configuration.Delimiter = ";";
csvWriter.WriteDataTable(data);
}
}
One thing to notice – with CsvHelper we have full control over a stream where we write data and this way we can write more performant code.
Pingback:IIS Express Stops Working - The Daily Six Pack: June 24th
For readability I suggest wrapping your top using in {} – I know technically you don’t have to but it’s so jarring ;)