Database fields merge for any Office product, just with XML

Introduction

This piece of code leverages power users the ability to use Office (Microsoft or any other XML compatible) by having them creating Excel or Word documents as complex as they want (and their own skills allow them); and simply use basic field mark-ups nomenclature. Then, by running this utility from within your application (or even from SQL using xp_cmdshell), get them the same complex documents pre-populated with actual data.

Background

As development manager in the company I work for, and having to provide solutions to power users who are familiar with Office products, I was looking for a good tool or utility that allowed me to simply create Office documents that can be pre-populated with actual data from our enterprise databases; so that users take care of all cosmetics and Office-provided tools without having to manually enter data from our base systems.

There are very few samples on the net, but most of them require more programming than what I was willing to deal with, or rather required external pieces (Microsoft Interop DLL, just to mention some, which is not installed "naturally"; or the simple fact that the server required it; with this code, the server doesn't even need to have Office!). One of my principles is: "do a lot with very little, but efficient programming". So, I did a little bit of research on XML and string manipulation, and I think the code here presented is very simple to program (or to even incorporate to any of your .NET applications, or be executed externally using any other language); yet powerful. A test with a really complex Excel template, using almost 200K of XML code, took less than 2 seconds to be properly merged.

Using the Code

The essentials is to provide four parameters:

• Name of the source XML template (say, previously created by the "power user" in regular Excel, Word, or any tool he wants and saved "As" standard XML).
• Name of the destination file (has to be XML too; and is expected to be opened by XML compatible Office - MS Office 2003 and 2007 make it "transparent" for users; they don't even know it is XML).
• List of fields expected to be found marked-up within the template (users will simply type the name of the field between tag marks, e.g., [[Customer_Name]] ). E.g.:
• CustName|CustLastName|Address|City|ZipCode
• List of actual values (can be obtained from a database or any other means, then sent as input parameters to this little program). E.g.:
• John|Doe|123 Main Street|New York|08540

The code is as simple as a single class, which opens XML files in "Line mode" and scans all lines, and whenever it finds a beginning of mark-up (in my case: "[["), then it tries to find what fields are referenced and replaces them to be written in the output file. Otherwise, it simply puts the original input string in the output file.

using System;
using System.IO;
using System.Text;
namespace XML_Merge
{
   class XML_Merge
   {
      public static int Main(string[] args) {
         StreamReader sr = new StreamReader(args[0]);
         string XmlLine="";
         string[] Fields=null;
         string[] Values=null;
         StreamWriter sw=new StreamWriter(args[1]);
         int i=0;
         if ( args.Length != 4  ) {
            Console.WriteLine("Parameters: ");
            Console.WriteLine("    Office_XML_Merge Source.XML " + 
              "Target.XML 'field1|field2|...|fieldn' 'value1|value2|...|valueN'");
            return(-1); // exit code
         }
         else {
            Fields=args[2].Split('|');
            Values=args[3].Split('|');
            if (Fields.Length != Values.Length) {
                Console.WriteLine("Warning!");
                Console.WriteLine("   Your fields string has " + 
                  "different number of items than your values string");
                return(-1);  // exit code
            }
            else
            {
               while( sr.Peek() >= 0 )
               {
                  XmlLine = sr.ReadLine();
                  i =XmlLine.IndexOf( "[[", 0 );
            
                  if (i>=0) {
                     for( i=0; i<Fields.Length; i++ ) 
                     XmlLine = XmlLine.Replace( "[["+Fields[i]+"]]", Values[i] );
                  }
             
                  sw.WriteLine(XmlLine);
               }
                    
               sr.Close();
               sw.Close();
               return(0); //Success
            }
         }         
      }
   }
}

Points of Interest

Truthfully speaking, the code is not rocket science. However, it is fast enough to do what it is supposed to do and does not require a lot of complex programming (neither external nor third party components other than the .NET Framework - you don't even need any Visual nothing!) yet obtaining a lot of benefit to be re-usable in practically any (not too old) Operating System, any programming language, and can even be put in a .BAT file.

I specifically use it in a SQL stored procedure (remember a database principle: make databases as independent of the application as possible). I have a simple button in my application that calls a Stored Procedure. The Stored Procedure simply creates the input strings for this little application and... voilá!

Below is an example of how to use this little application (I omitted all the details of the Stored Procedure, leaving just a simple SQL code that illustrates the usage of this):

declare @cmd nVarChar(4000)

set @cmd =
  Replace(
'C:\AppDir\Office_Xml_Merge.exe
 C:\AppDir\My_Template.XML
 C:\AppDir\Merged_001.XML
 "Today|Rec_Country|Rec_LC|Rec_LC_ID|Iss_Country|Iss_LC|
  Iss_LC_ID|Company|Iss_Comp|Address1|Address2|Iss_Address1|Iss_Address2" 
 "Oct 1, 2007|Mexico|Polanco|052005|United States|RockCenter|
  001211|Berlitz Inc.|Berlitz Mexico, S.A. de C.V.|
  400 Alexander Park|Princeton|Jaime Balmes 22A|Polanco"',
 char(13)+char(10), '' )

exec xp_cmdshell @cmd

History

• First version, October 2007.