Feb 14, 2006 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>
Download
the source
* Only $4.95/month ASP.NET & Windows 2008 + IIS 7 Hosting! FREE SQL Included