I've just finished reading Robert Scoble's and Shel Israel's book Naked Conversations. It's about corporate blogging and "how blogs are changing the way businesses talk with customers". This falls in the category I call, "books by American authors who get paid by the number of pages and is therefore unnecessary long and boring". The book is filled with stories from different companies and their experiences with blogging. What I really wanted was a sort of checklist of do's and don'ts, and the book actually has one, sort of. I have definitely learned something from the book, but it could be done in three or four chapters instead of 15. However, I would recommend reading it to anyone who plan to blog, because it has some interesting pointers and facts.
The reason I read this book was because the company I work for (Traceworks) is about to embark in the world of corporate blogging. So I thought I'd better get acquainted with the basics before writing something stupid on there. I probably will anyway...
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