Office Materials Generator - Beta 2

  • Article

The Office Materials Generator (OMG) is now at beta 2 and includes full source code under the Apache 2.0 licence.  The original (old) beta announcement is here: https://blogs.technet.com/b/digital_musketeer/archive/2011/10/18/office-materials-generator-beta-document-generation.aspx.    All time-limitations have been removed, and full source code for Visual Studio 2010 is included.

The licence is at the bottom of this page, and all aspects of the licence must be complied with in order to use the code.

​Digital Marketing Office Materials Generator

OMG includes a "language" within Word 2010 that can be used to update template-style documents with information from Excel models.  OMG models all complexity and mathematics in Excel (where it is easily managed), whilst document templates are created within Word 2010.  A generation cycle blends the Excel model information with the Word template and creates a set of output documents.

Multiple documents can be created during a single generation run - allowing a single Excel model to be the reference information for documents that may be overviews, in-depth technical architectures, or pricing..

OMG is used to output Word documents such as:

  • Architecture and pricing documents
    • Solution benefits overview
    • Architecture documents
    • Pricing documents
  • Templated "forms" from Excel or databases (requires custom code for databases)

 

OMG uses a simple templating language system within Word to output the generated documents. It is designed with extensiblity in mind and permits the easy definition of new template functions to be added to the processing cycle. 

 

 

Template commands within Word supports standard programming features such as:

  • Excel Model references - Easy references to Excel model information  (simply include cell names in curly braces - eg {customer} )
  • IF statements  (if Excel value THEN include Word text )
  • FOREACH statements (foreach cell in a group of cells from Excel)
  • INCLUDE files  (include a file, including the ability to conditionally include a file)

 

The Hello World template

Creating templates are easy - take any existing Word 2010 document and externalise template in it to Excel, then include references in your template to the Excel document.

 

1. Create a template

 

A simple example document could be: "Hello World, this is your lucky day - you have been templated!"

 

2. Externalise content to Excel 2010

 

Change the above Word document to:

 

"Hello {noun}, this is your lucky day - you have been {verb}-ed!"

 

The Word document should have a name that ends in ".template".   Note also the use of curly braces around 'variables' - See the following for information on variables - https://en.wikipedia.org/wiki/Variable_(programming)

 

Create an Excel file - a common name for the Excel file is "model.xlsx" or similar.  This file will typically contain the information used by all templates within the generation, although each document could use different Excel files.  Management of information - including validation - is made easier by centralising all the mathematics, modelling and information into a single file however.  The reference in the Open Excel call (below) must always be 'model'.

 

Create named cells in the Excel file (see below) - for "Noun" and "Verb"

 

Eg

 

Cell A1 - Value "World", name "Noun"

Cell A2 - Value "Template", name "Verb"

 

3. Include a command to open the Excel file in your template

 

The full template text (Hello world.template.docx) becomes:

{Excel.Open("model", "Test.xlsx")}

"Hello {noun}, this is your lucky day - you have been {verb}-ed!"

 

4. Run OMG -  from the attached distribution use the "OMG_Program_beta_2_2011_12_13a" ZIP file and look in the Debug directory

 

Run "OfficeTemplateEngine.exe" or "__start_TemplateEngine.cmd"

 

For the "Choose a directory..." - use the directory that contains your Word file ending in ".template.docx" - for example if your directory is as follows:

 

c:\users\David\Documents\OMG_Example

c:\users\David\Documents\OMG_Example\Hello world.template.docx

 

containing a file "Hello world.template.docx" - then use the directory "c:\users\David\Documents\OMG_Example".

 

Loading this directory will indicate that "Hello world.template.docx" is ready to generate.

 

Click the "generate" button

 

5. New files generated

A new file - "Hello world.docx" is created. The '.template' is removed from the filename - this becomes the name of the new output file.

 

The output in the file will be:

"Hello World, this is your lucky day - you have been Template-ed!"

Note that multiple files can be generated in a single run.

 

Overview

Templates are enclosed in curly braces and either refer to an Excel model or execute commands such as opening the model. For example this next line is blank in the output document, but it opens the named Excel document. It creates an internal variable "model" that's referred to later in the document. The italics are ignored for the purposes of executing the template, although the output is styled identically.

The default name that’s used for shorthand references to Excel named cells is “model”, so this is the variable name to generally use when opening an Excel document.

  • {Excel.Open("model", "Test.xlsx")}

Referring to Excel cells:

Referring to Excel cells is via references enclosed in curly braces. These references optionally include an output style (format) after the reference. Creating these names within Excel is covered later in this document.

  • Customer: {customer}
  • Donation amount: {Donation_amount dollar}
  • Date of donation: {Date_of_donation date}
  • Quantity: {Quantity}
  • Percent: {Percent_value percent}
  • Will you donate? {Definitely_donate yesno}

There are three types of template commands above:

  • Open the Excel model
  • A simple reference to a named cell – e.g. “customer”
  • A reference to a named cell with an output format – e.g. “Date_of_donation date”
    • This is the same as a simple reference but with the type after the reference
    • Valid formats are date, percent, dollar, yesno or truefalse

Creating named cells in Excel

In order to create named cells within Excel use the technique “Name a cell or a range of cells” in the following document: https://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010342417.aspx#BMcreate_a_name_by_using_a_selection_of

The simplest technique to create a name is as follows (Office 2010):

  • Select a cell
  • Click in the box shown below and type the name – eg “Customer” into the box in the top left underneath the ribbon (highlighted below)

Including other documents

Include a file:

  • {Word.Include(“IncludedFile.docx”)}

Conditionally include a file:

{include if ($model.GetNamedCell(”Definitely_donate”)) “IncludeIfTrue.docx”}

IF statements

To have a section of the document included or not depending on a variable use the following template text:

{if ($model.GetNamedCell(”Definitely_donate”))}

     This section of text will only be included if the named cell is true

{end}

FOREACH statements

A typical requirement is to produce copies of a document for a number of similarly named variables in the Excel document as follows. In the Excel document there are four named cells: Name1, Name2, Name3 and Name4. Note that because Name4 doesn’t contain anything it is not included in the foreach results.

{ foreach ($nameSuffix in $model.GetAllCellsLike(“Name”))}

  • Name : {Name$nameSuffix}

{end}

Installation and 'hello world' Guide

  • Download test
    • Unpack the download
    • Start __start_TemplateEngine
    • Click "..."
    • Choose the configuration file "OMG_Configuration.xml" in the TestDocs
      directory
    • Click "generate"
    • Examine the generated TestDocs\Test.docx file (delete and regenerate if
      required)
    • Note well: While the template file can be open in Word during generation -
      the Excel model can't be open

Comments, suggestions or improvements are always welcomed!

 

Screen snapshot below:

LICENCE

This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software.

Copyright Microsoft Corporation

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
in compliance with the License. You may obtain a copy of the License at https://www.apache.org/licenses/LICENSE-2.0

THIS CODE IS PROVIDED *AS IS* BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OR CONDITIONS OF TITLE,
FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABLITY OR NON-INFRINGEMENT.

See the Apache 2 License for the specific language governing permissions and limitations under the License.

distribution_OMG_Beta_2_2011_12_13a.zip