That's Cool! RPG-XML Suite Tips & Tricks for Developers
by Aaron Bartell

This month's tip from the RPG-XML Suite developers is an extension of last month's successful attempt at creating a Microsoft Word document using XML.  This time we’ll be creating a Microsoft Excel spreadsheet.  You can find the previous Microsoft Word tip here.

The process of creating an Excel document is very similar to Word. Below are the steps I followed to accomplish the task.

1. Locate an existing Excel document you would like to create using XML.  For my purposes I went out to www.microsoft.com and found an existing template which you can see at the following URL: http://tinyurl.com/23q3k7
2. I saved the Excel template to my hard drive, opened it, then did a Save As and selected the "XML Document (*.xml)" in the "Save as type" drop-down.  Name it msexcel_aged_ar.xls.  Note that I still gave it a .xls extension because I want Excel to always open it up vs. other programs on the system.  At this point you can view the new AR aging in Notepad (or other text viewer) and the document’s contents will all be raw XML.
3. Now that we have an instance of an XML document we are in a perfect place to create an RPG-XML Suite template.  To do this I simply copied the msexcel_aged_ar.xls file to /www/myrxs/templates/msexcel_aged_ar.tpl on my System i5.  I then modified the contents and replaced the Excel defaults (i.e. [Company Name] and [Date]) with variable data place holders (i.e. .:company_name:., etc).  It was also necessary to create three different sections (i.e. doc_beg, row, doc_end) so I could have repeating customer lines.  You can see the finished template file at the bottom of the following URL: See the finished template.
4. At this point an RPG-XML Suite program needs to be written to fill in the data and write out the sections.  You can see a concise RPG program named MSEXCEL here.  A dummy AR PF was created so you could see how one can read from a file to generate the resulting Excel document.  The aged_ar.xls generated by program MSEXCEL can be seen here.

That's all there is to it!  The beautiful part about this process is your users can have full control of the Excel document design.  They then hand it off to you and the rest can be accomplished by following this tutorial.  Stay tuned in the coming months for many more examples showing how XML can be utilized within your business!

This is yet another use showing how RPG-XML Suite can be utilized to meet your business needs and gain ROI.  Have fun!

Have you seen XML in action with other applications that would make for a good next topic in an upcoming newsletter?  Email sales@krengeltech.com! 

To download a new 30-day trial of RPG-XML Suite, please visit
http://www.rpg-xml.com/freedownload.aspx.