Introduction
There are many times when we need to export the contents of a DataTable to a format compatible with spreadsheet applications, such as Microsoft Excel or Apple Numbers. This quick little class will write the contents of a DataTable to a TextWriter instance, whether this is a physical file or network stream.
Here's the class itself:
using System.Data;
using System.IO;
using System.Linq;
public static class Rfc4180Writer
{
public static void WriteDataTable(DataTable sourceTable, TextWriter writer, bool includeHeaders)
{
if (includeHeaders) {
IEnumerable<String> headerValues = sourceTable.Columns
.OfType<DataColumn>()
.Select(column => QuoteValue(column.ColumnName));
writer.WriteLine(String.Join(",", headerValues));
}
IEnumerable<String> items = null;
foreach (DataRow row in sourceTable.Rows) {
items = row.ItemArray.Select(o => QuoteValue(o?.ToString() ?? String.Empty));
writer.WriteLine(String.Join(",", items));
}
writer.Flush();
}
private static string QuoteValue(string value)
{
return String.Concat("\"",
value.Replace("\"", "\"\""), "\"");
}
}
Imports System.Data
Imports System.IO
Imports System.Linq
Public Class Rfc4180Writer
Public Shared Sub WriteDataTable(ByVal sourceTable As DataTable, _
ByVal writer As TextWriter, ByVal includeHeaders As Boolean)
If (includeHeaders) Then
Dim headerValues As IEnumerable(Of String) = sourceTable.Columns
.OfType(Of DataColumn)
.Select(Function(column) QuoteValue(column.ColumnName))
writer.WriteLine(String.Join(",", headerValues))
End If
Dim items As IEnumerbale(Of String) = Nothing
For Each row As DataRow In sourceTable.Rows
items = row.ItemArray.Select(Function(obj) QuoteValue(If(obj?.ToString(), String.Empty)))
writer.WriteLine(String.Join(",", items)
Next
writer.Flush()
End Sub
Private Shared Function QuoteValue(ByVal value As String) As String
Return String.Concat("""", value.Replace("""", """"""), """")
End Function
End Class
Using the Code
Making use of the class is straightforward in a number of applications. For the desktop world, here's a console app demonstrating the use via StreamWriter:
public static class Program {
public static void Main() {
DataTable sourceTable = new DataTable();
sourceTable.Columns.AddRange(new DataColumn[] {
new DataColumn("ID", typeof(Guid)),
new DataColumn("Date", typeof(DateTime)),
new DataColumn("StringValue", typeof(string)),
new DataColumn("NumberValue", typeof(int)),
new DataColumn("BooleanValue", typeof(bool))
});
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, true);
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, false);
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, true);
using (StreamWriter writer = new StreamWriter("C:\\Temp\\dump.csv")) {
Rfc4180Writer.WriteDataTable(sourceTable, writer, true);
}
}
}
Public Class Program
Public Shared Sub Main()
Dim sourceTable As DataTable = New DataTable()
sourceTable.Columns.AddRange(New DataColumn() {
New DataColumn("ID", GetType(Guid)),
New DataColumn("Date", GetType(Date)),
New DataColumn("StringValue", GetType(String)),
New DataColumn("NumberValue", GetType(Integer)),
New DataColumn("BooleanValue", GetType(Boolean))
})
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, True)
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, False)
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, True)
Using writer As StreamWriter = New StreamWriter("C:\Temp\dump.csv")
Rfc4180Writer.WriteDataTable(sourceTable, writer, True)
End Using
End Sub
End Class
For the ASP.NET MVC world, specifically MVC 3 or later, the demonstration isn't much different than the above example except the result is returned as a FilePathResult instance on a controller action:
public class ReportController : Controller
{
[HttpGet()]
public ActionResult Export()
{
DataTable sourceTable = new DataTable();
sourceTable.Columns.AddRange(new DataColumn[] {
new DataColumn("ID", typeof(Guid)),
new DataColumn("Date", typeof(DateTime)),
new DataColumn("StringValue", typeof(string)),
new DataColumn("NumberValue", typeof(int)),
new DataColumn("BooleanValue", typeof(bool))
});
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, true);
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, false);
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, true);
byte[] outputBuffer = null;
using (MemoryStream tempStream = new MemoryStream()) {
using (StreamWriter writer = new StreamWriter(tempStream)) {
Rfc4180Writer.WriteDataTable(sourceTable, writer, true);
}
outputBuffer = tempStream.ToArray();
}
return File(outputBuffer, "text/csv", "export.csv");
}
}
Public Class ReportController
Inherits Controller
<HttpGet()> _
Public Function Export() As ActionResult
Dim sourceTable As DataTable = New DataTable()
sourceTable.Columns.AddRange(New DataColumn() {
New DataColumn("ID", GetType(Guid)),
New DataColumn("Date", GetType(Date)),
New DataColumn("StringValue", GetType(String)),
New DataColumn("NumberValue", GetType(Integer)),
New DataColumn("BooleanValue", GetType(Boolean))
})
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, True)
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, False)
sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, True)
Dim outputBuffer() As Byte = Nothing
Using tempStream As MemoryStream = New MemoryStream()
Using writer As StreamWriter = New StreamWriter(tempStream)
Rfc4180Writer.WriteDataTable(sourceTable, writer, True)
End Using
outputBuffer = tempStream.ToArray()
End Using
Return File(outputBuffer, "text/csv", "export.csv")
End Function
End Class
Points of Interest
If you're not familiar with the original RFC, take a second to read it over at http://tools.ietf.org/html/rfc4180.
