Export a DataTable to XML and CSV
If you have a DataTable or DataView you want to export to XML or CSV, I will show you have to do just that. The CSV format is also compliant with Excel, which in my opinion makes it the best suited format for spreadsheets in general for simple data tables. I have made export.aspx for the purpose, but it would be preferable to use an ashx if possible. The reason to use a DataView as source for the export function is because a DataView can be sorted. If you apply sorting to a DataView, you properly want to export it with that sorting still applied.
Demo XML export
Demo CSV export
You only have to add the DataView to the session before calling export.aspx, and that’s it. Here is how you could call export.aspx:
<%@ Page language="c#" AutoEventWireup="true" %>
<%@ Import NameSpace="System" %>
<%@ Import NameSpace="System.Data" %>
<script runat="server" language="C#">
void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable("Export");
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Amount", typeof(double));
dt.Columns.Add("Cost", typeof(double));
DataRow row = null;
for (int i = 0; i < 30; i++)
{
row = dt.NewRow();
row[0] = "Name" + i;
row[1] = i.ToString();
row[2] = (i*2.5).ToString();
dt.Rows.Add(row);
}
DataView dv = dt.DefaultView;
dv.Sort = "Amount DESC";
Session["dataview"] = dv;
}
</script>
<html>
<head>
<title>Export to XML/CSV</title>
</head>
<body>
<a href="export.aspx?type=xml">Export dataview (XML)</a><br />
<a href="export.aspx?type=csv">Export dataview (CSV)</a>
</body>
</html>
Here is the code for export.aspx:
<%@ Page language="c#" AutoEventWireup="true" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System" %>
<%@ Import NameSpace="System.Data" %>
<%@ Import NameSpace="System.Web" %>
<%@ Import NameSpace="System.Text" %>
<script runat="server" language="C#">
void Page_Load(object sender, EventArgs e)
{
string type = Request.QueryString["type"] == null ? string.Empty : Request.QueryString["type"].ToLower();
_Name = Request.QueryString["name"] == null ? "export" : Request.QueryString["name"].ToLower();
Response.Clear();
switch (type)
{
case "xml":
ExportXML();
break;
case "csv":
ExportCSV();
break;
default:
Response.Write("Wrong export type");
break;
}
}
private string _Name = "export";
#region XML
private void ExportXML()
{
DataTable dt = this.CleanUpDataTable(Session["dataview"] as DataView);
StringBuilder sb = new StringBuilder();
sb.Append("<" + dt.TableName + ">");
foreach (DataRow row in dt.Rows)
{
sb.Append("<item>");
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append("<" + dt.Columns[i].ColumnName + ">" + row[i].ToString() + "</" + dt.Columns[i].ColumnName + ">");
}
sb.Append("</item>");
}
sb.Append("</" + dt.TableName + ">");
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment; filename=" + _Name + ".xml");
Response.AppendHeader("Content-Length", sb.Length.ToString());
Response.ContentType = "text/csv";
Response.Write(sb.ToString());
Response.End();
}
#endregion
#region CSV
private void ExportCSV()
{
DataTable dt = this.CleanUpDataTable(Session["dataview"] as DataView);
StringBuilder sb = new StringBuilder();
foreach (DataColumn col in dt.Columns)
{
sb.Append(col.ColumnName + ";");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(Environment.NewLine);
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(row[i].ToString() + ";");
}
sb.Append(Environment.NewLine);
}
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment; filename=" + _Name + ".csv");
Response.AppendHeader("Content-Length", sb.Length.ToString());
Response.ContentType = "text/csv";
Response.Write(sb.ToString());
Response.End();
}
#endregion
private DataTable CleanUpDataTable(DataView dv)
{
DataTable dt = new DataTable(dv.Table.TableName);
DataRow dtRow = null;
for (int i = 0; i < dv.Table.Columns.Count; i++)
{
dt.Columns.Add(dv.Table.Columns[i].ColumnName, dv.Table.Columns[i].DataType);
bool isInt = dv.Table.Columns[i].DataType == typeof(double);
foreach (DataRow row in dv.Table.Rows)
{
if (isInt)
{
if (row[i] is DBNull)
row[i] = "0";
}
}
}
foreach (DataRowView row in dv)
{
dtRow = dt.NewRow();
for (int i = 0; i < dv.Table.Columns.Count; i++)
{
dtRow[i] = row[i].ToString();
}
dt.Rows.Add(dtRow);
}
return dt;
}
</script>