Using VSTA to Create Office Documents Through XML

    With the introduction of LINQ to XML and the OpenXml formatting of Office 2007 documents it is incredibly easy to use VSTA to import and export data from a host application to an Office document and vice versa.  One benefit of using OpenXml (or for Excel straight xml as well) is that this can be performed without installing Office or using the office API's, which allows this processing to be done on servers as well as desktops.

A new sample is available showing how to export ShapeApp shape data into Word and Excel using VSTA v 2 and taking advantage of the easy to use LINQ to XML with Visual Basic.

To download the sample add-ins, click here.  To go to the download page, click here.

    Office 2007 introduced a new storage method for Office document.  The new documents are actually packages- they are zip files of xml documents.  To explore one of these packages, change the extension to .zip and explore it like any other zip file, or download the package explorer tool from CodePlex to explore and edit the package without unzipping or using Office.  This new document packaging format makes it very easy to import or export data between a host and an Office document.

    Two add-ins for ShapeAppCSharp (VSTA v 2) are available demonstrating how to save ShapeApp data into a Word or Excel document.  The SaveToWord add-in uses the Open Xml Format SDK to add data to the main document (document.xml) of the Word package (System.IO.Packaging could be used instead with minor modifications).  The SaveToExcel add-in uses straight Xml instead of OpenXml to get data into Excel because it is far easier to work with straight Xml in Excel.  Excel can save and open Xml documents without worrying about packaging.  Visual Basic was used to write both add-ins because Visual Basic works better than C# with LINQ to XML.

'This method populates the word document with ShapeAppData

Public Sub SetMainDocumentContent(ByVal part As MainDocumentPart)

    Const name As String = "Name:  "

    Const size As String = "Size:  "

    Const loc As String = "Location:  "

    Const color As String = "Color:  "

    'query the shapes in the first drawing using LINQ to XML

    Dim docBodyData = From shape In Me.Document.Drawings(0).Shapes _

        Select _

         <w:body>

             <w:p><w:r><w:t><%= name & shape.Name %></w:t></w:r></w:p>

             <w:p><w:r><w:tab/></w:r><w:r><w:t>

                 <%= size & shape.Size.Height.ToString() & ", " & shape.Size.Width.ToString() %>

                 </w:t></w:r></w:p>

             <w:p><w:r><w:tab/></w:r><w:r><w:t>

                 <%= loc & shape.Location.X.ToString() & ", " & shape.Location.Y.ToString() %>

                 </w:t></w:r></w:p>

             <w:p><w:r><w:tab/></w:r><w:r><w:t>

                 <%= color & shape.Color.A.ToString() & ", " & shape.Color.R.ToString() & ", " & _

                     shape.Color.B.ToString() & ", " & shape.Color.G.ToString() %>

                 </w:t></w:r></w:p>

         </w:body>

    'to get the xml stuff from word- create a new doc, add dummy data as a place holder

    ' in the format you want to export it, then copy the xml from the document.xml part

    ' of the package.  Remove the dummy data and insert the LINQ query.

    Dim docXml = <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

                 <w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">

                     <%= docBodyData %>

                 </w:document>

    'save the xml to the main document part of the word package

    Dim stream As StreamWriter = New StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write))

    docXml.Save(stream)

End Sub

·        Open Xml Format SDK

·        The PackageExplorer tool from CodePlex.  Click on the V3.0.0 RC1 link to start the download.

·        The MSDN article "How to: Create an Office Open XML Package by Using the Open XML API"

·        The MSN video “How Do I Create Excel Spreadsheets using LINQ to XML?”