Use OpenXML to generate Office document

This article will show how to generate Office (2007 and above) document with OpenXML SDK and runtime data source.

1.  Install DocumentFormat.OpenXml from Nuget

2.  Download Open XML SDK 2.5 for Microsoft Office and install it.

3.  Create a template WORD document, for example,

PS. The words with red color will be replaced later in the run-time.

Also, create a Profile DTO.

public class Profile { public String Name { get; set; } public String Age { get; set; } public String Email { get; set; } public String Date { get; set; } }

4.  Open Microsoft SDK Tool ( Open XML SDK 2.5 Productivity Tool for Microsoft Office )

ü   Click 【Open File】and select the directory of the template doc.

There will be a list of all element in the document.

ü   Click 【Reflect Code】 and select the “body (Body)” node. The tool shows the Open XML in the top area, and shows the codes* in the bottom area. COPY the whole Open XML in the top area.

※If you would like to write the codes of Open XML, the codes will help a lot.

5.  Development

ü   Architecture

(1)     OpenXmlFactory.Console : Main process

(2)     OpenXmlFactory.Model : Models

(3)     OpenXmlFactory.Utility : Serializer … etc

(4)     OpenXmlFactory.Service : Generate document

ü   Add a XSLT into our Visual Studio project, and replace the following section with the Open XML.

Original XSLT

After pasting the Open XML

<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"     xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl" >      <xsl:output method="xml" indent="yes"/> <xsl:template match="@* | node()">         <w:body xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">          … </w:body> </xsl:template> </xsl:stylesheet>

ü   Now we are going to clean the original data in the XSLT, and replace them with tag : <xsl:value-of select="./[property name of Profile]"/>

For example, the original data of 「Name」 is “JB”

<w:t>JB</w:t>

Modify the content with correct property of the Class =>

<w:t> <xsl:value-of select="./Name"/> </w:t>

Repeat the step to modify 「Date」,  「Age」 and 「Email」 as well.

ü   Build a “WordFactory” which is responsible for generating document.

/// <summary> /// Create Document : Word /// </summary> /// <typeparam name="T">DTO</typeparam> public class WordFactory<T> : IDocFactory<T> { private String _templateFilePath; //原始範本Word檔路徑 private String _xsltFilePath = String.Empty; //XSLT路徑 public WordFactory(String templateFilePath, String xsltFilePath) { this._templateFilePath = templateFilePath; this._xsltFilePath = xsltFilePath; } /// <summary> /// Generate document in the root directory /// </summary> public void Generate(String newFilePath, T obj) { FileStream fs = this.getResultFile(newFilePath); WordprocessingDocument doc = WordprocessingDocument.Open(fs, true); Document document = doc.MainDocumentPart.Document; //Get Open XML with binding data var tableXML = this.generateOpenXmlBindingData(obj); Body updatedBodyContent = new Body(tableXML); //Replace the existing Document Body with the new content. doc.MainDocumentPart.Document.Body = updatedBodyContent; doc.MainDocumentPart.Document.Save(); doc.Close(); fs.Close(); } /// <summary> /// Use XML(Serialized DTO) and XSLT to produce Open XML /// </summary> /// <param name="xmlDoc">DTO</param> /// <returns>Open XML</returns> private String generateOpenXmlBindingData(T obj) { XmlDocument xmlDoc = new XmlDocument() ; //Serialize T to XML using (var xs = new OpenXmlFactory.Utility.XmlSerializer<T>()) { String xml = xs.ObjToXmlStr(obj); xmlDoc.LoadXml(xml); } //XSLT XslCompiledTransform xslt = new XslCompiledTransform(); xslt.Load(this._xsltFilePath); MemoryStream ms = new MemoryStream(); xslt.Transform(xmlDoc.DocumentElement, null, ms); ms.Flush(); ms.Position = 0; StreamReader reader = new StreamReader(ms); string result = reader.ReadToEnd(); reader.Close(); XmlDocument docRender = new XmlDocument(); docRender.LoadXml(result); result = docRender.DocumentElement.OuterXml; docRender = null; xslt = null; return result; } } /// <summary> /// Must create the output file by copying existed docx file /// </summary> /// <param name="newFilePath">New document file path</param> /// <returns>FileStream</returns> private FileStream getResultFile(String newFilePath) { if (!File.Exists(newFilePath)){ File.Copy(this._templateFilePath, newFilePath); } return new FileStream(newFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite); } }

ü   Utility : XML Serializer

/// <summary> /// Xml Serialization /// </summary> /// <typeparam name="T">DTO</typeparam> public class XmlSerializer<T> : IDisposable { public String ObjToXmlStr(T myObj) { XmlSerializer xsSubmit = new XmlSerializer(typeof(T)); StringWriter sww = new StringWriter(); XmlWriter writer = XmlWriter.Create(sww); try{ xsSubmit.Serialize(writer, myObj); var xml = sww.ToString(); // Your xml return xml; } catch (Exception ex){ throw; } finally{ //GC xsSubmit = null; writer.Close(); writer.Dispose(); sww.Close(); sww.Dispose(); } }

ü   Main program

static void Main(string[] args) { String templateXsltPath = String.Empty; var baby = new Profile(){ Name = "Leia", Age = "1.5", Email = "leia@cute.com.tw", Date = DateTime.Now.ToString("yyyy/MM/dd") }; templateXsltPath = @"..\..\XSLT\WordTemplate.xslt"; String templateWordPath = @"..\..\Template\MyTemplate.docx"; generateWord(templateWordPath, templateXsltPath, baby); } private static void generateWord( String templateWordPath, String templateXsltPath, Profile profile) { String newFilePath = Path.Combine( @"Output\", "new_"+DateTime.Now.ToString("yyyyMMdd HHmmss")+".docx"); using (var docGen = new WordFactory<Profile>(templateWordPath, templateXsltPath)) { docGen.Generate(newFilePath, profile); } }

ü   The output file :

 

6.  For Excel, the Open XML hierarchy is more complex. Using the codes which are generated by the OpenXML SDK tool will be easier to generate the document.

For example, create an Excel template like this …

After reflecting code with OpenXML SDK tool.

ü   Copy the code and use it to create a class : SdkExcelPackage  in OpenXmlFactory.Service.

In the constructor, add a DTO parameter.

public class SdkExcelPackage { private Profile _profile = null; public void CreatePackage(string filePath, Profile profile) { this._profile = profile; using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { CreateParts(package); } } // … Other codes from OpenXML SDK tool }

ü   In the other codes, modify the binding model part. For example,

Original :

SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "JB";

Modify with our model

SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = this._profile.Name;

ü   Build a “ExcelFactory” which is responsible for generating document.

public class ExcelFactory<T> : IDocFactory<T> {          public void Generate(String newFilePath, T obj) { var excelSdkPackage = new SdkExcelPackage(); excelSdkPackage.CreatePackage(newFilePath, obj as Profile); excelSdkPackage = null; } }

ü   Main program

static void Main(string[] args) { var baby = new Profile(){ Name = "Leia", Age = "1.5", Email = "leia@cute.com.tw", Date = DateTime.Now.ToString("yyyy/MM/dd") }; generateExcel(baby); } private static void generateExcel(Profile profile) { String newFilePath = Path.Combine( @"Output\", "new_"+DateTime.Now.ToString("yyyyMMdd HHmmss")+".xlsx"); using (var docGen = new ExcelFactory<Profile>()) { docGen.Generate(newFilePath, profile); } }

ü   Output Excel :

7.  Reference

Using XSLT and Open XML to Create a Word 2007 Document

[Office開發系列] 誰說寫 Open XML文件產生程式一樣要寫落落長的程式碼？