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...
