Taken from The 20 second commute: The Blue Zone

Meet Jay Schmelzer - he'll be delivering 2 sessions at TechEd Developers  in Barcelona 5-9 Nov 2007  and I'm not going to miss either one. Jay is one of those rare breeds - a speaker who can keep you totally captivated throughout a session , so much so  that the session is over in what appears to be a mere matter of minutes. Here you see him , in his natural habitat , in that great TechEd innovation - The TLC  ZONE - ready to answer any and every question that a VB nerd could lay on him. In the background on the left you have his able sidekick Chris Smith sharing the goodness that is VB's unique features when it comes to handling XML with LINQ I can personally vouch for the fact that when any of these Microsoft guys or gals give you their card and say 'contact me if you have any further questions' they deliver BIG TIME. Without their help (among others) I would not have been able to offer my take on producing dynamic report using Open XML  and VB9

Wouldn't it be grand if you could use this XML snippet ....     ... to produce this Word Document ....

... Well - here's one solution.....  download and unpack the zip file , read the SetupReadme.txt  and you're on your way.

 Being a very lazy West of Ireland guy , I never like having to do the same thing over and over again - after all, that's what programming is all about - code once, use many times. The snippet above contains 3 types of Sections (document sections if you will) - one produces the Report Title , then there's a line break section and finally there's the the table section - if you need any more types you just add to the case statement in the code (Line 81 in Module1.vb) - again extend once, use many times. With a little thinking (preferable on a hilltop over looking a stretch of water with a gentle westerly breeze in your face - and your mind full of the great ideas disseminated by a podcast from Dot Net Rocks that you've just listened to on your MP3 player) you'd be saying to yourself - All I have to do is change the Title , the SQL Query and the Column Captions and Widths and indicate if I want subtotals  and I'd have a new report - but then you' ld say - Couldn't I just add a root element say <Printout> and then a 2nd element like the one above with the new details - save it off to a file and then call the file from my program [next installment] - then to produce further reports all I'd have to do is edit the xml file that is external to the program with Notepad.

Don't you just love it when a plan comes together ...

this is all made possible by the fact that the Office team produced their Open XML solution and the VB/C# teams collaborated on LINQ to XML - of course the VB guys just had to make it easier by extending the VB implementation with their XML literals and 'Expression holes'. A simple example should show this

In VB

In C#

[One of the most important issues in programming - is to be able to return to your code after say 6 months - and understand instantly it's intent - I would suggest that when it comes to Open XML  VB is the winner  - I know - I'm totally biased - but 10 years of using this technique with VB6 and VBA has converted me to the easy life - I do most of my coding in XML - isn't declarative  programming great]

This blog will mainly concern itself with the VB LINQ implementation and maybe a few words about Open XML but fear not an infinitely better source of Open XML knowledge is available in Wouter van Vugt's excellent free book  Open XML The Markup Explained.

By this stage I'm hoping that you've downloaded the zip file and have the program opened showing the Module1.vb code and the line numbers turned on [Tools - Options - Text Editor - All Languages - General - Display - Line Numbers]

This module has 3 parts to it [isn't that like something out of Julius Caesar's Gallic Wars - Gaul is divided into 3 parts - boy did I not GROK Latin]

1. Sub Main - test harness - calls the other 2 functions and Displays (hopefully) the resultant word document [you do have Word 2007 or Word 2000/XP/2003 with OpenXml extensions raring to go - don't you?]
2. CreateDocument function - black box that takes in the required filename and the Main xml document and produces the document - takes care of all the plumbing and uses the Microsoft SDK for Open XML - takes care of all the relationship issues etc - and produces the zip packaged docx file.
3. GetDocumentXML  function - which I'll now attempt to elaborate on

On line 46 we define an XElement variable [Printout] which contains the XML Snippet for our report - notice that there are no quotes around the XML and that in VS2008 the variable type is INFERRED from the data to the right of the equal sign. [This variable could equally be populated from an external file - Next Installment]

Next on line 66 we set up our main XElement [docXML] which we'll attach directly to the root of our XDocument - like HTML Open XML uses a body element - the namespace used to interpret this is defined as w  [see imports statement line 6]

On Line 69 we set up an XElement [Xel] as a temporary container to hold the XML elements we will generate for each section type

Now we're ready to start - this LINQ to XML really replaces XSLT - see Bill Burrows - Linq to XML videos and XLinq Portal for more info

With XLINQ we can easily traverse thru' the various sections of the XML snippet using the FOR EACH statement on line 72 - note how we address the various section elements - see traversing XML in XLinq overview

Let's take each section in turn

1. Type is Text - we place the element value in the w:t element using an expression hole with the variable section.value.ToString  [notice the familiarity this brings to old ASP programmers]  and we set up the style to use within the w:pPr element again using an expression hole with the variable section.@Style as this time it's an attribute within the section element.
2. Type is LineBreak - we just use a blank paragraph entry w:p to place us on a new line
3. Type is Table -  Line 85 - the main theme for this blog
   • 1st off we've set up a temporary XElement [GridData] to hold the dynamic table data - this will be generated using an SQL Query later [Next Installment] - notice the attributes run form C1 to C6 - this was used to conserve memory - but it also turned out to be very useful as you'll see anon.
   • next we determine the number of rows in our table - you've got to admit that the syntax is cool - full intellisense is available detailing all the options and that's even cooler
   • we assign a value to MinimumNoRows and if the number of Rows [3] is less than this value  [10] we set up a list of integers [4,5,6,7,8,9,10] so that we can iterate thru' the list later on to programmatically produce a blank row for each entry
   • of course to produce a blank row we need to produce a blank cell [<w:tc><w:p/></w:tc>] for each column - which we do in line 103
   • the table look [line 107] depends on whether we have a header row and/or a Total row and is assigned accordingly
   • If the attribute TotalRow on the table is set = "Y"
     • we iterate thru' each column and check whether the SubTotal attribute is set = "Y" .
       • if it is
         • we use the AGGREGATE function to sum up the values in the columns - summing an in memory object - awesome. Notice that the item.attribute needs a "Column Name" parameter which we supply by concatenating "C" with the indexed position of the column [sometimes you just strike lucky]
       •   otherwise
         • we add a blank cell [<w:tc><w:p/></w:tc>].
       • On completion we add a blank paragraph
     • otherwise
       • we set the total row to a blank paragraph
   • On Line 131 with the preliminaries out the way we start to put our table XElement together. Notice that the root of the table element is w:tbl
     • We set the style and look for the table using the w:tblPr element using expression holes to apply the style attribute [line 46] and the tableLook variable we calculated [Line 107]
     • we set up the Column Widths using the w:tblGrid element using an expression hole to apply the column width attribute within a LINQ FROM ... SELECT statement - itself within an expression hole.
     • similarly we set up the Column headers or captions using the column caption attribute.
     • then the piece de resistance - the grid data - a double  FROM ... SELECT  loop - notice the IIf function - if the attribute / column cell value is blank use a blank paragraph otherwise use the value within a w:t element which is inside a w:r element which is within a w:p element [refer back to the example at the start - how many lines of C# code would this take]
     • we set a row for each blank row by enclosing the Cells  list within a w:tr  element - if there are no entries in the BlankRows list we get none
     • lastly we apply the TotalRow element which is either a row containing totals plus a blank paragraph or a blank paragraph

at the end of each iteration the Xel  is added as an element to docXML.

After the last section is traversed the resultant docXML is used to create the documentxml  which is returned to the main function. The document is created and displayed.

The End result - we've converted / transformed an XML Snippet into a WordprocessingML document without using a single line of incomprehensible XSLT.

 Fin.