Click here to Skip to main content
Click here to Skip to main content

Converting a datatable to CSV, XML, and HTML by using Extensions

By , 3 Oct 2012
Rate this:
Please Sign up or sign in to vote.

Introduction

Very often I use extensions to simplify casting of different datatypes, something like string to boolean or string to datetime. So why not create an extension to cast a datatable to CSV?

Background

Extensions are very powerful and makes coding much more easy. In mostly every project I implement my own extension library. So spent some time to create that library carefully and try to handle all conversion possibilities.

Using the code

The Extension class is quite simple to realize... just refer to the thousands of samples you can find in the web:

public static class Extensions
{
    //ToString() Extensions:
    public static string ToCsvString(this DataTable dt)
    {
        return DataTableFunctions.ToCsv(dt);
    }
    public static string ToHtmlString(this DataTable dt)
    {
        return DataTableFunctions.ToHtml(dt);
    }
    public static string ToXmlString(this DataTable dt)
    {
        return DataTableFunctions.ToXml(dt);
    }
    //ToFile() Extensions
    public static void ToXmlFile(this DataTable dt)
    {
        CommonTools.WriteFile(CommonTools.SaveFileName("XML File", 
          "xml (*.xml)|*.xml|All Files (*.*)|*.*"), DataTableFunctions.ToXml(dt));
    }
    public static void ToCsvFile(this DataTable dt)
    {
        CommonTools.WriteFile(CommonTools.SaveFileName("Csv File", 
          "csv (*.csv)|*.csv|All Files (*.*)|*.*"), DataTableFunctions.ToCsv(dt));
    }
    public static void ToHtmlFile(this DataTable dt)
    {
        CommonTools.WriteFile(CommonTools.SaveFileName("Html File", 
          "html (*.html)|*.html|All Files (*.*)|*.*"), DataTableFunctions.ToHtml(dt));
    }
    //ToClipBoard() Extensions
    public static void ToXmlClipboard(this DataTable dt)
    {
        Clipboard.SetText(DataTableFunctions.ToXml(dt));
    }
    public static void ToCsvClipboard(this DataTable dt)
    {
        Clipboard.SetText(DataTableFunctions.ToCsv(dt));
    }
    public static void ToHtmlClipboard(this DataTable dt)
    {
        Clipboard.SetText(DataTableFunctions.ToHtml(dt));
    }
}

To use the conversion, you will need the conversion classes itself. This should be only a sample... no error handling at all:

private static class DataTableFunctions
{
    public static String ToCsv(DataTable dt)
    {
        var sb = new StringBuilder();
        //Add Header Header
        for (var x = 0; x < dt.Columns.Count; x++)
        {
            if (x != 0) sb.Append(";");
            sb.Append(dt.Columns[x].ColumnName);
        }
        sb.AppendLine();
        //Add Rows
        foreach (DataRow row in dt.Rows)
        {
            for (var x = 0; x < dt.Columns.Count; x++)
            {
                if (x != 0) sb.Append(";");
                sb.Append(row[dt.Columns[x]]);
            }
            sb.AppendLine();
        }
        return sb.ToString();
    }
    public static string ToXml(DataTable dt)
    {
        var writer = new StringWriter();
        var name = dt.TableName;
        if (name == string.Empty)
        {
            dt.TableName = "XMLTABLE";
        }
        dt.WriteXml(writer, true);
        dt.TableName = name;
        return writer.ToString();
    }
    public static string ToHtml(DataTable dt)
    {
        if (dt == null)
        {
            throw new ArgumentNullException("dt");
        }
        var builder = new StringBuilder();
        builder.Append("<html xmlns='http://www.w3.org/1999/xhtml'>");
        builder.Append("<head>");
        builder.Append("<title>");
        builder.Append("Page-");
        builder.Append(Guid.NewGuid().ToString());
        builder.Append("</title>");
        builder.Append("</head>");
        builder.Append("<body>");
        builder.Append("<table border='1px' cellpadding='5' cellspacing='0' ");
        builder.Append("style='border: solid 1px Silver; font-size: x-small;'>");
        builder.Append("<tr align='left' valign='top'>");
        foreach (DataColumn c in dt.Columns)
        {
            builder.Append("<td align='left' valign='top'>");
            builder.Append(c.ColumnName);
            builder.Append("</td>");
        }
        builder.Append("</tr>");
        foreach (DataRow r in dt.Rows)
        {
            builder.Append("<tr align='left' valign='top'>");
            foreach (DataColumn c in dt.Columns)
            {
                builder.Append("<td align='left' valign='top'>");
                builder.Append(r[c.ColumnName]);
                builder.Append("</td>");
            }
            builder.Append("</tr>");
        }
        builder.Append("</table>");
        builder.Append("</body>");
        builder.Append("</html>");
        return builder.ToString();
    }

So the whole class could look like that:

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
namespace Extensions
{
    public static class Extensions
    {
        //ToString() Extensions:
        public static string ToCsvString(this DataTable dt)
        {
            return DataTableFunctions.ToCsv(dt);
        }
        public static string ToHtmlString(this DataTable dt)
        {
            return DataTableFunctions.ToHtml(dt);
        }
        public static string ToXmlString(this DataTable dt)
        {
            return DataTableFunctions.ToXml(dt);
        }
        //ToFile() Extensions
        public static void ToXmlFile(this DataTable dt)
        {
            CommonTools.WriteFile(CommonTools.SaveFileName("XML File", 
              "xml (*.xml)|*.xml|All Files (*.*)|*.*"), DataTableFunctions.ToXml(dt));
        }
        public static void ToCsvFile(this DataTable dt)
        {
            CommonTools.WriteFile(CommonTools.SaveFileName("Csv File", 
              "csv (*.csv)|*.csv|All Files (*.*)|*.*"), DataTableFunctions.ToCsv(dt));
        }
        public static void ToHtmlFile(this DataTable dt)
        {
            CommonTools.WriteFile(CommonTools.SaveFileName("Html File", 
              "html (*.html)|*.html|All Files (*.*)|*.*"), DataTableFunctions.ToHtml(dt));
        }
        
        //ToClipBoard() Extensions
        public static void ToXmlClipboard(this DataTable dt)
        {
            Clipboard.SetText(DataTableFunctions.ToXml(dt));
        }
        public static void ToCsvClipboard(this DataTable dt)
        {
            Clipboard.SetText(DataTableFunctions.ToCsv(dt));
        }
        public static void ToHtmlClipboard(this DataTable dt)
        {
            Clipboard.SetText(DataTableFunctions.ToHtml(dt));
        }
        /// <summary>
        /// Common Tool Class
        /// </summary>
        private static class CommonTools
        {
            public static string SaveFileName(string title, string filter)
            {
                var  fd = new SaveFileDialog();
                fd.Title = title;
                fd.Filter = filter;
                fd.ShowDialog();
                return fd.FileName;
            }
            public static void WriteFile(string filename, string content)
            {
                var sr = new StreamWriter(filename);
                sr.Write(content);
                sr.Flush();
                sr.Close();
            }
        }
        /// <summary>
        /// DataTable Converter Class
        /// </summary>
        private static class DataTableFunctions
        {
            public static String ToCsv(DataTable dt)
            {
                var sb = new StringBuilder();
                //Add Header Header
                for (var x = 0; x < dt.Columns.Count; x++)
                {
                    if (x != 0) sb.Append(";");
                    sb.Append(dt.Columns[x].ColumnName);
                }
                sb.AppendLine();
                //Add Rows
                foreach (DataRow row in dt.Rows)
                {
                    for (var x = 0; x < dt.Columns.Count; x++)
                    {
                        if (x != 0) sb.Append(";");
                        sb.Append(row[dt.Columns[x]]);
                    }
                    sb.AppendLine();
                }
                return sb.ToString();
            }
            public static string ToXml(DataTable dt)
            {
                var writer = new StringWriter();
                var name = dt.TableName;
                if (name == string.Empty)
                {
                    dt.TableName = "XMLTABLE";
                }
                dt.WriteXml(writer, true);
                dt.TableName = name;
                return writer.ToString();
            }
            public static string ToHtml(DataTable dt)
            {
                if (dt == null)
                {
                    throw new ArgumentNullException("dt");
                }
                var builder = new StringBuilder();
                builder.Append("<html xmlns='http://www.w3.org/1999/xhtml'>");
                builder.Append("<head>");
                builder.Append("<title>");
                builder.Append("Page-");
                builder.Append(Guid.NewGuid().ToString());
                builder.Append("</title>");
                builder.Append("</head>");
                builder.Append("<body>");
                builder.Append("<table border='1px' cellpadding='5' cellspacing='0' ");
                builder.Append("style='border: solid 1px Silver; font-size: x-small;'>");
                builder.Append("<tr align='left' valign='top'>");
                foreach (DataColumn c in dt.Columns)
                {
                    builder.Append("<td align='left' valign='top'>");
                    builder.Append(c.ColumnName);
                    builder.Append("</td>");
                }
                builder.Append("</tr>");
                foreach (DataRow r in dt.Rows)
                {
                    builder.Append("<tr align='left' valign='top'>");
                    foreach (DataColumn c in dt.Columns)
                    {
                        builder.Append("<td align='left' valign='top'>");
                        builder.Append(r[c.ColumnName]);
                        builder.Append("</td>");
                    }
                    builder.Append("</tr>");
                }
                builder.Append("</table>");
                builder.Append("</body>");
                builder.Append("</html>");
                return builder.ToString();
            }
        }
    }
}

To use the Extensions simply try this in your app:

var x = myDataTable.ToCsvString();

or to save the datatable to an XML file:

myDataTable.ToXmlFile()

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Stefan Huy
Engineer
Germany Germany
No Biography provided
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberfredatcodeproject4-Oct-12 1:48 
GeneralMy vote of 4 PinmemberDrABELL21-Sep-12 13:33 
The idea of converting .NET DataTable Object to CSV or XML string, or rendering as HTML definietly is not new, but the Solution might be useful/practical if it demonstrates some competitive advantages vs. existing ones. Thus couple questions should be answered:
1. XML: DataTable.WriteXml is a standard Method in NET framework. What is the reason to bypass the built-in method and implement the one suggested in your post?
2. HTML: DataTable can be easily converted to DataView and then latter one can be used to bind a GridView object in just a couple lines of code. What is the advantage of your method that involves quite lengthy chunk of code?
Thanks and regards, AB
GeneralRe: My vote of 4 PinmemberStefan04104-Nov-13 8:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 3 Oct 2012
Article Copyright 2012 by Stefan Huy
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid