This article will show how to
generate Office (2007 and above) document with OpenXML SDK and runtime data
source.
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”
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
[Office開發系列] 誰說寫 Open XML文件產生程式一樣要寫落落長的程式碼？
