Click here to Skip to main content
Click here to Skip to main content

Database fields merge for any Office product, just with XML

, 2 Oct 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Using this simple, yet elegant and powerful code, you can provide to your users efficient Office document merging; simply exploiting XML technology and basic string manipulation.

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.

License

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

Share

About the Author

mc_kappa
Architect
Mexico Mexico
Born in 1969. First prorgram made in commodore 64 on early 80s (storing it in regular audio cassettes). Then fortran in a PDP in middle 80s. First serious program at the age of 18th in GWBASIC in late 80s. So, almost 20 years programming.
 
From 2003 to 2006 engaged into a development outsourced by an international company. They couldn't make it.
 
Hired 2 years ago by one of our COOs to be in charge of the development, so I have control on what is developed and most importantly, how. Of course, the "what" is strongly influenced by what the users need.
 
I think we can make great things with C Sharp, I am applying all my knowledge and experience in programming for the over 20 years (including experience in implementing business systems) to the C# and .NET Techonology. I don't think we need to complicate our lives very much, we can program in a smarter way and just take advantage of what this language offers without reinventing the wheel.
 
However, in the last 18 months; I've been more engaged into JavaScript, CSS and a little bit of ASP (for the database connection) finding absolute no need of using the .NET framework yet acomplishing very important advances.
 
I don't like Visual Studio, by the way. I prefer to code by hand or using simpler IDEs... antiquated? Maybe, but still think it is better. You have the *control* in your hands. And actually, rather than "by hand", I simply mean creation of complex dynamic objects that would resolve UI characteritics dynamically (i.e., a repository of UI objects in the database or via XML or similar; and a big unique object capable to resolve such objects), instead of "dragging and dropping" objects creating a huge source code base.
 
Visual Studio is still for "programmers" (quoted).
 
Raised in Mexico but currently living in New Jersey (unitl October 2010)

Comments and Discussions

 
Generaljust a thought but... Pinmemberrippo2-Oct-07 21:40 
GeneralRe: just a thought but... Pinmembermc_kappa3-Oct-07 4:58 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411022.1 | Last Updated 2 Oct 2007
Article Copyright 2007 by mc_kappa
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid