<< Munich by Steven Spielberg | Book review: Naked Conversations >>

Export a DataTable to XML and CSV

by Mads Kristensen 15. February 2006 06:38

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

77b3fa4c-7533-4d84-b220-499aa5f95142|2|5.0

Tags:

ASP.NET

Export a DataTable to Excel in ASP.NET Back in February I wrote a post on how to export DataTables to XML and Excel and I still ge...XML-RPC ping endpoint in C# and ASP.NETShows how to receive XML-RPC ping requestsDo more with a DataTable and improve performance There are some features in the System.Data.DataTable class that a lot of developers don’t u...

Comments

3/30/2006 12:41:13 AM #

Thanks very much for this post. It's helped me to complete a "DataTable to CSV" feature in my current work project.

Where exactly did you come up with the "Content-Disposition" functionality?

Uri |

3/30/2006 2:00:05 AM #

I'm glad you liked it. I'm planning on adding a PDF export to the example as well. I actually have written it, I just need to post it.

Content-disposition is where you tell the browser two things about the file you are serving:

1. It's an attached file. It could be inline, which would make it open inside the browser if supported.
2. The name of the file, so the user can see it in the download dialog box.

I don't know where I saw it the first time, but I have seen it many times since and have used it for years. I think it's a standard header.

Mads Kristensen |

8/16/2007 8:42:12 PM #