Programmatically creating an Excel file without using OpenXML(or Excel Interop)

With the switch to the Excel XLSX format, it became possible to create Excel files using the new XML based OpenXML format. Great news for us developers as we no longer needed Excel Interop or 3th party components to generate our XML files programmatically.

However, the world ain’t perfect yet, as the OpenXML format is very rich and complex. So it’s still not easy to generate an XML file without any help or tooling. Luckily for us there is the OpenXML SDK which helps a lot when creating these Excel files.

But did you know there is a simpler alternative which doesn’t even require the OpenXML SDK? Using a simple XML is enough. Let’s have a look…

• First create a new MVC application
• Inside the App_Data folder add the following XML template:

	<?xml version="1.0" encoding="utf-8" ?>
	<?mso-application progid="Excel.Sheet" ?>
	<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
	xmlns:html="http://www.w3.org/TR/REC-html40">
	<Worksheet ss:Name="Sheet1">
	<Table>
	$ROWSPLACEHOLDER$
	</Table>
	</Worksheet>
	</Workbook>

view raw ExcelTemplate.xml hosted with ❤ by GitHub

• Let’s now create an action method that reads the XML template, replaces the placeholder token with some data and send the results to the browser:

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Web;
	using System.Web.Mvc;
	namespace SampleApp.Controllers
	{
	public class HomeController : Controller
	{
	public ActionResult Index()
	{
	return View();
	}
	public ActionResult DownloadExcel()
	{
	var tableData = new StringBuilder();
	var products = GetProducts();
	foreach(var product in products)
	{
	tableData.Append("<Row ss:AutoFitHeight='0'>");
	tableData.Append("<Cell><Data ss:Type='String'>" + product.ProductName + "</Data></Cell>");
	tableData.Append("<Cell><Data ss:Type='Number'>" + product.UnitPrice + "</Data></Cell>");
	tableData.Append("</Row>");
	}
	// read the excel file template from the resource
	var xmlTemplate=System.IO.File.ReadAllText(Server.MapPath("~/App_Data/ExcelTemplate.xml"));
	//Replace placeholder content
	xmlTemplate = xmlTemplate.Replace("$ROWSPLACEHOLDER$", tableData.ToString());
	var data=System.Text.Encoding.UTF8.GetBytes(xmlTemplate);
	return File(data,"application/vnd.ms-excel", "Products.xml");
	}
	private IEnumerable<Product> GetProducts()
	{
	using (var ctx = new NorthwindEntities())
	{
	return ctx.Products.ToList();
	}
	}
	}
	}

view raw HomeController.cs hosted with ❤ by GitHub

• Run the application and browse to the /home/downloadexcel url
• A download dialog will be shown and once you open the file in Excel you’ll see something like: