Click here to Skip to main content
15,860,859 members
Articles / Productivity Apps and Services / Microsoft Office

XML/XSLT Word Report Generator

Rate me:
Please Sign up or sign in to vote.
3.80/5 (6 votes)
11 Jan 2009CPOL4 min read 122.9K   2.6K   84   15
The tool is based on XML/XSLT, and allows a user to create a Word report from scratch, namely: construct SQL query, construct a WordML template, generate a document.

Contents

Introduction

There are many tools which allow creating documents based on data stored in a database: Crystal Reports, Fast Reports etc. Most of them do not allow a user to edit a template. The task I’ve tried to solve is to create a tool based on XML/XSLT, which allows a user to create a template from scratch, namely:

  • Construct a SQL query in order to obtain XML and XML schema from SQL server.
  • Construct a WordML template.
  • Generate document, which could be virtually represented as a WordML template filled with data obtained from a SQL Server.

The advantage of the “XML/XSLT” approach is that a user has the opportunity to create trees with rather complex topology. Word has been chosen as a tool for constructing the WordML template due to its universal usage and ability to display XML structure. The idea to realize the generator based on XML/XSLT appeared after reading the article Generating Word Reports/Documents. I strongly advise you to read this article.

Introduction to XSLT/XSL

With XSL, you can freely modify any source text (XML) and produce different output from the same source file. An XSL processor parses an XML source and tries to find a matching template rule. If it does, instructions inside the matching template are evaluated. Parts of the XML document to which the template should be applied are determined by location paths. The required syntax is specified in the XPath specification. Simple cases look very similar to file system addressing. Processing always starts with the template match ="/". This matches the root node (the node, its only element child, is the document element, in our case "root"). Many style sheets do not contain this element explicitly. When this template is not explicitly given, the implicit template is used (it contains the only instruction). This instruction means: process all children of the current node, including text nodes. When a template for the node exists, there is no default processing invoked. If you want to include descendants of the node, you have to explicitly request their templates.

Report generation

Report generation could be virtually divided into four steps.

  • Create a SQL query in order to obtain XML data and XML schema,
  • Construct a WordML template,
  • Transform a WordML template into XSLT transformation,
  • Generate a report by means of applying XSLT transformation to XML data.

SQL - > XML

SQL query is an initial point for all transformations. It determines the data which should be requested from the server. I utilize a MSSQL 2005 (Yukon) server, which provides the opportunity to the requested data in XML format. The typical SQL query looks like the fallowing:

SQL
WITH XMLNAMESPACES(DEFAULT 
    'http://wrg/kpd_types.xsd')
SELECT *
FROM kpd_types
FOR XML PATH('kpd_types'), ROOT('root')

The response from the server:

XML
<root xmlns="http://wrg/kpd_types.xsd">
<kpd_types>
    <oid>1</oid>
    <category>cargo</category>
    <label>paper</label>
</kpd_types>
<kpd_types>
    <oid>2</oid>
    <category>cargo</category>
    <label>food</label>
</kpd_types>
<kpd_types>
    <oid>3</oid>
    <category>cargo</category>
    <label>metal</label>
</kpd_types>
</root>

XML->XML schema

What is an XML schema? The XML schema is a description of the XML structure. In short, it contains n enumeration of all the elements and attributes presented in an XML document. We will need it when we start to construct our WordML template. The set of elements we could place inside the WordML document will be determined by the XML schema.

Let’s consider the DataSet class. It has two useful methods: ReadXml() and WriteXmlSchema(). When the ReadXml() method is called, the XML structure is built automatically behind the scene. We can read or store it by means of the WriteXmlSchema() method.

C#
XmlDataDocument doc = new XmlDataDocument();
XmlReader xmlData;
xmlData = XmlReader.Create(new StringReader(xml));
doc.DataSet.ReadXml(xmlData);
doc.DataSet.WriteXmlSchema(WordReportGenerator._path + "//" + 
                           _xmlSchemaFileName + ".xsd");

XML schema -> WordML

When we have the XML schema, we are ready to create a template for our report. First of all, we have to attach it to a Word document.

C#
object schema = _xmlSchemaName;
object alias = _xmlSchemaAlias;
object schemafilename = _path + _xmlSchemaFileName + ".xsd";
App.ActiveDocument.XMLSchemaReferences.Add(ref schema, 
                   ref alias, ref schemafilename, true);

Then, do some tuning: make XML tags and the task pane visible, and allow saving the document without validation.

C#
App.ActiveWindow.View.ShowXMLMarkup =true;
App.TaskPanes[Word.WdTaskPanes.wdTaskPaneXMLStructure].Visible =true;
App.ActiveDocument.XMLSchemaReferences.AllowSaveAsXMLWithoutValidation = true;

WordML.JPG

WordML ->XSLT

Let’s consider how a template transformation into XSLT occurs. The initial WordML template could be represented in an abbreviated form as:

XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Word.Document"?>
<w:wordDocument 
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:ns0="http://wrg/kpd_types.xsd"><w:body><wx:sect><ns0:root>
    <ns0:kpd_types>
    <w:p>
        <ns0:oid/>
        <w:r><w:t>,</w:t></w:r>
        <ns0:category/>
        <w:r><w:t>,</w:t></w:r>
        <ns0:label/>
    </w:p>
    </ns0:kpd_types>
</ns0:root></wx:sect></w:body>
</w:wordDocument>

Where the tags between <ns0:root></ns0:root> are per se the form for filling with XML data. The XSTL transformation provides the mechanism for such a filling. Let’s see how it looks like.

XML
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:ns0="http://wrg/kpd_types.xsd">

<xsl:output method="xml" encoding="UTF-8" standalone="yes"/>

<xsl:template match="/">
<xsl:processing-instruction name="mso-application">
<xsl:text>progid="Word.Document"</xsl:text>
</xsl:processing-instruction>

<w:wordDocument 
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint" 
xmlns:o="urn:schemas-microsoft-com:office:office" 
xmlns:ns0="http://wrg/kpd_types.xsd"><w:body><wx:sect><xsl:apply-templates select="ns0:root" /></wx:sect></w:body></w:wordDocument>
</xsl:template>

<xsl:template match="/ns0:root">
<ns0:root>
    <xsl:apply-templates select="ns0:kpd_types" />
</ns0:root>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types">
<ns0:kpd_types>
<w:p>
    <xsl:apply-templates select="ns0:category"/>
        <w:r><w:t><xsl:text>,</xsl:text></w:t></w:r>
    <xsl:apply-templates select="ns0:label"/>
        <w:r><w:t><xsl:text>,</xsl:text></w:t></w:r>
    <xsl:apply-templates select="ns0:oid" />
</w:p>
</ns0:kpd_types>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types/ns0:label">
<ns0:label>
    <w:r><w:t><xsl:value-of select="." /></w:t></w:r>
</ns0:label>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types/ns0:oid">
<ns0:oid>
    <w:r><w:t><xsl:value-of select="." /></w:t></w:r>
</ns0:oid>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types/ns0:category">
<ns0:category>
    <w:r><w:t><xsl:value-of select="." /></w:t></w:r>
</ns0:category>
</xsl:template>

</xsl:stylesheet>

Applying XSLT transformation to XML data

Let’s get as a source XML, the following set of data:

XML
<root xmlns="http://wrg/kpd_types.xsd">
<kpd_types>
    <oid>1</oid>
    <category>cargo</category>
    <label>paper</label>
</kpd_types>
<kpd_types>
    <oid>2</oid>
    <category>cargo</category>
    <label>food</label>
</kpd_types>
<kpd_types>
    <oid>3</oid>
    <category>cargo</category>
    <label>metal</label>
</kpd_types>
</root>

Applying the XSLT transformation to this XML, we get the final document:

XML
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<?mso-application progid="Word.Document"?>
<w:wordDocument 
  xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml" 
  xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint" 
  xmlns:o="urn:schemas-microsoft-com:office:office" 
  xmlns:ns0="http://wrg/kpd_types.xsd"><w:body><wx:sect><ns0:root>
    <ns0:kpd_types>
    <w:p>
        <ns0:category><w:r><w:t>cargo</w:t></w:r></ns0:category><w:r><w:t>,</w:t></w:r>
        <ns0:label><w:r><w:t>paper</w:t></w:r></ns0:label><w:r><w:t>,</w:t></w:r>
        <ns0:oid><w:r><w:t>1</w:t></w:r></ns0:oid>
    </w:p>
    </ns0:kpd_types>
    <ns0:kpd_types>
    <w:p>
        <ns0:category><w:r><w:t>cargo</w:t></w:r></ns0:category><w:r><w:t>,</w:t></w:r>
        <ns0:label><w:r><w:t>food</w:t></w:r></ns0:label><w:r><w:t>,</w:t></w:r>
        <ns0:oid><w:r><w:t>2</w:t></w:r></ns0:oid>
    </w:p>
    </ns0:kpd_types>
    <ns0:kpd_types>
    <w:p>
        <ns0:category><w:r><w:t>cargo</w:t></w:r></ns0:category><w:r><w:t>,</w:t></w:r>
        <ns0:label><w:r><w:t>metal</w:t></w:r></ns0:label><w:r><w:t>,</w:t></w:r>
        <ns0:oid><w:r><w:t>3</w:t></w:r></ns0:oid>
    </w:p>
    </ns0:kpd_types>
</ns0:root></wx:sect></w:body></w:wordDocument>

ReportXML.JPG

Report.JPG

Conclusion

Thus, we get the Word document filled with data from the database. I have to mention that I have utilised SqlWrapper - a very suitable Data Access Layer. I will be glad to answer all your questions.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Russian Federation Russian Federation
I have Master degree in Particle Physics. During my last several years I work as software developer.

Primary Interests
- c#, c++, php, java.
- scientific programming

Comments and Discussions

 
QuestionImages Pin
Mahmoud Sudqi3-Aug-15 1:58
Mahmoud Sudqi3-Aug-15 1:58 
SuggestionDo we really need all this sophistication? Pin
Phil Grant13-Dec-14 11:37
Phil Grant13-Dec-14 11:37 
GeneralDLL included for the project Pin
Member 457280318-Feb-10 22:04
Member 457280318-Feb-10 22:04 
AnswerRe: DLL included for the project Pin
Sam Safonov13-Mar-10 1:33
Sam Safonov13-Mar-10 1:33 
QuestionThe result after Transform can not display richtext field Pin
nhutcmos17-Feb-09 22:10
nhutcmos17-Feb-09 22:10 
AnswerRe: The result after Transform can not display richtext field [modified] Pin
Sam Safonov21-Feb-09 7:14
Sam Safonov21-Feb-09 7:14 
QuestionWRG.xslt.Wms2xslt Pin
Asher Barak19-Jan-09 8:50
professionalAsher Barak19-Jan-09 8:50 
AnswerRe: WRG.xslt.Wms2xslt Pin
Sam Safonov20-Jan-09 19:36
Sam Safonov20-Jan-09 19:36 
GeneralRe: WRG.xslt.Wms2xslt Pin
feg20005-May-09 9:13
feg20005-May-09 9:13 
GeneralRe: WRG.xslt.Wms2xslt Pin
Sam Safonov7-May-09 19:45
Sam Safonov7-May-09 19:45 
QuestionRe: WRG.xslt.Wms2xslt Pin
graafjan17-Feb-11 5:02
graafjan17-Feb-11 5:02 
AnswerRe: WRG.xslt.Wms2xslt [modified] Pin
Sam Safonov21-Feb-11 9:14
Sam Safonov21-Feb-11 9:14 
AnswerRe: WRG.xslt.Wms2xslt Pin
Sam Safonov2-Mar-11 20:38
Sam Safonov2-Mar-11 20:38 
QuestionSide columns? Pin
Member 402405913-Jan-09 5:57
Member 402405913-Jan-09 5:57 
AnswerRe: Side columns? [modified] Pin
Sam Safonov13-Jan-09 23:41
Sam Safonov13-Jan-09 23:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.