Create dynamic Excel files based on a template, using XML data.

Question

I'm currently looking for a way to create dynamic excel files, based on an existing template, using data coming from an XML-file. For example; I have a template ExcelTemplate.xlsx with some layouting in it. A user calls a method which will create a copy of the template and fill it with data coming from an XML. After that the user will get a MemoryStream returned with the newly created xlsx.

Anyone has an idea how to work this out in the best possible way? This is supposed to run on a server (as a service) where there isn't any office installed.

Answer 1

When writing this yourself (*no other tools used) you could consider using an XSLT transform. Use the template as basis for your XSLT, push in the XML data as parameter(s) into the XSLT transform, get a new XML out (your new xslx)

Hope this helps,

Answer 2

I'm thinking of doing the following:

• Create a template.xlsx and a templatesettings.xml.

The templatesettings.xml will containt the startrow & startcol of the table which I want to fill.

• Create an xlsx from the template, using OpenXml. Some code will iterate trough my data, and because I know where I have to start inputting my data (templatesettings.xml), I should be able to fill all data errorfree..

My solution is based on following tut: Creating Spreadsheets from a server application

Let's hope this works as good as it works in my head.

Answer 3

Take a look at these projects:

• NPOI - Mature and Stable but does not yet support the xlsx (Open XML) format yet.
• ExtremeML - Alpha and Active - Looks promising.
• SimpleOOXML - Stable and Active
• XOOX - Alpha and Active - Still young.

I think ExtremeML is the best match to your requirements. It has very good support for working with template xslx documents and is extensible.

Answer 4

I've worked out a solution for this, using ExcelPackage.