Export / Convert DataTable to Excel (XML format XLS file) in MVC

I see a thousand ASP.NET developers wanting to output an Excel-format document based on a lowly .NET DataTable.

I see people trying to output in CSV and then running into problems with Excel misinterpreting the cell datatype formatting.

I see people wanting to create an Excel Workbook with more than one Worksheet.

So, I present this! A little standalone class that lets you

• create an Excel document (XMLSS format)
• add as many Worksheets as you like by simply chucking DataTables at it
• send it to the browser for download

Usage an an MVC Controller Action:

public void ExportData()
{
    DataTable dtYourData = YourApp.GetYourDataTable();
    ExcelWorkbookGenerator exGen = new ExcelWorkbookGenerator();
    exGen.AddWorksheet("YourWorksheetTitle", dtYourData);
    exGen.SendToBrowser("YourSuggestedFilename");
}

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Web;

namespace DaddyCode.Utilities
{
    /// <summary>
    /// ExcelWorkbookGenerator : Generate Excel XML - compatible documents from DataTables.
    /// </summary>
    /// <remarks>
    ///      Author: James McCormack, DaddyCode Ltd
    /// </remarks>
    public class ExcelWorkbookGenerator
    {
        private class Worksheet
        {
            public string Title = "";
            public DataTable Data = null;

            public Worksheet(string title, DataTable dataTable)
            {
                this.Title = title;
                this.Data = dataTable;
            }
        }

        private List<Worksheet> Worksheets = new List<Worksheet>();

        /// <summary>
        /// Add a new Worksheet to the Workbook, based on a DataTable that you provide
        /// </summary>
        /// <param name="title"></param>
        /// <param name="dataTable"></param>
        public void AddWorksheet(string title, DataTable dataTable)
        {
            Worksheets.Add(new Worksheet(title, dataTable));
        }

        /// <summary>
        /// Send the current Workbook to the Web Browser to view or save the file
        /// </summary>
        /// <param name="suggestedFileName"></param>
        public void SendToBrowser(string suggestedFileName)
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + suggestedFileName);
            HttpContext.Current.Response.Write(getWorkbookXML());
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// Generate an Excel-compliant XML Workbook
        /// </summary>
        /// <returns></returns>
        private string getWorkbookXML()
        {
            XmlDocument xDoc = new XmlDocument();
            xDoc.AppendChild(xDoc.CreateNode(XmlNodeType.XmlDeclaration, null, null));

            string strCustomNamespace = "urn:schemas-microsoft-com:office:spreadsheet";

            XmlElement root = xDoc.CreateElement("Workbook");
            root.SetAttribute("xmlns", strCustomNamespace);
            xDoc.AppendChild(root);

            XmlElement styles = xDoc.CreateElement("Styles");

                XmlElement styleBold = xDoc.CreateElement("Style");
                    XmlElement font = xDoc.CreateElement("Font");

                    XmlAttribute xStyleBoldID = xDoc.CreateAttribute("dc", "ID", strCustomNamespace);
                    xStyleBoldID.Value = "dc1";
                    styleBold.Attributes.Append(xStyleBoldID);

                    XmlAttribute xFontWeight = xDoc.CreateAttribute("dc", "Bold", strCustomNamespace);
                    xFontWeight.Value = "1";
                    font.Attributes.Append(xFontWeight);

                    styleBold.AppendChild(font);
                styles.AppendChild(styleBold);

                XmlElement styleDateTime = xDoc.CreateElement("Style");
                    XmlElement numberFormat = xDoc.CreateElement("NumberFormat");

                    XmlAttribute xStyleDateTimeID = xDoc.CreateAttribute("dc", "ID", strCustomNamespace);
                    xStyleDateTimeID.Value = "dcDateTime";
                    styleDateTime.Attributes.Append(xStyleDateTimeID);

                    XmlAttribute xStyleNumberFormat = xDoc.CreateAttribute("dc", "Format", strCustomNamespace);
                    xStyleNumberFormat.Value = "General Date";
                    numberFormat.Attributes.Append(xStyleNumberFormat);

                    styleDateTime.AppendChild(numberFormat);
                styles.AppendChild(styleDateTime);

            root.AppendChild(styles);

            // Populate worksheets

            foreach (Worksheet wSheet in Worksheets)
            {
                XmlElement worksheet = xDoc.CreateElement("Worksheet");

                XmlAttribute xSheetTitle = xDoc.CreateAttribute("dc", "Name", strCustomNamespace);
                xSheetTitle.Value = System.Text.RegularExpressions.Regex.Replace(wSheet.Title, "[^a-z0-9 -]", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                worksheet.Attributes.Append(xSheetTitle);

                XmlElement table = xDoc.CreateElement("Table");

                // Populate header row

                XmlElement header = xDoc.CreateElement("Row");
                XmlAttribute xHeaderStyle = xDoc.CreateAttribute("dc", "StyleID", strCustomNamespace);
                xHeaderStyle.Value = "dc1";
                header.Attributes.Append(xHeaderStyle);

                foreach (DataColumn col in wSheet.Data.Columns)
                {
                    XmlElement headerCell = xDoc.CreateElement("Cell");
                    XmlElement headerData = xDoc.CreateElement("Data");
                    headerData.InnerText = col.ColumnName;

                    XmlAttribute xHeaderDataType = xDoc.CreateAttribute("dc", "Type", strCustomNamespace);
                    xHeaderDataType.Value = "String";
                    headerData.Attributes.Append(xHeaderDataType);

                    headerCell.AppendChild(headerData);
                    header.AppendChild(headerCell);
                }
                table.AppendChild(header);

                // Populate data rows

                foreach (DataRow drData in wSheet.Data.Rows)
                {
                    XmlElement row = xDoc.CreateElement("Row");

                    foreach (DataColumn col in wSheet.Data.Columns)
                    {
                        XmlElement cell = xDoc.CreateElement("Cell");
                        XmlElement cellData = xDoc.CreateElement("Data");
                        XmlAttribute xCellDataType = xDoc.CreateAttribute("dc", "Type", strCustomNamespace);

                        if (drData[col.ColumnName] == DBNull.Value)
                        {
                            cellData.InnerText = "";
                            xCellDataType.Value = "String";
                        }
                        else
                        {
                            switch (col.DataType.Name)
                            {
                                case "Single":
                                case "Double":
                                case "Decimal":
                                case "Int16":
                                case "Int32":
                                case "Int64":

                                    cellData.InnerText = drData[col.ColumnName].ToString();
                                    xCellDataType.Value = "Number";
                                    break;

                                case "DateTime":

                                    XmlAttribute xCellStyleID = xDoc.CreateAttribute("dc", "StyleID", strCustomNamespace);
                                    xCellStyleID.Value = "dcDateTime";
                                    cell.Attributes.Append(xCellStyleID);
                                    if (drData[col.ColumnName] != null 
                                            && drData[col.ColumnName] != DBNull.Value
                                            && (DateTime)drData[col.ColumnName] != DateTime.MinValue)
                                    {
                                        cellData.InnerText = ((DateTime)drData[col.ColumnName]).ToString("o");  // ISO 8601 DateTime String Format
                                    }
                                    xCellDataType.Value = "DateTime";
                                    break;

                                case "Boolean":

                                    cellData.InnerText = (bool)drData[col.ColumnName] ? "1" : "0";
                                    xCellDataType.Value = "Boolean";
                                    break;

                                default:

                                    cellData.InnerText = drData[col.ColumnName].ToString(); // XmlElement.InnerText escapes reserved XML characters automatically
                                    xCellDataType.Value = "String";
                                    break;
                            }
                        }

                        cellData.Attributes.Append(xCellDataType);
                        cell.AppendChild(cellData);
                        row.AppendChild(cell);
                    }

                    table.AppendChild(row);
                }

                worksheet.AppendChild(table);
                root.AppendChild(worksheet);
            }

            StringWriter swOut = new StringWriter();
            xDoc.Save(swOut);

            return swOut.ToString();
        }
    }
}

If you're reading this and know a better way to do this sort of thing - PLEASE LET ME KNOW. I was driven to this because of the crap documentation on the web. I only achieved this limited success by reverse-engineering an existing Excel doc and latterly discovering the MS XML Spreadsheet Reference. Why they don't tell you that the XMLSS DateTime format is ISO 8601, I don't know...