This blog post originates from a post previously written by Aaron Bartell.
The process of creating an Excel document is very similar to Word. Below are the steps I followed to accomplish the task.
- 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
- 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.
- Now that we have an instance of an XML document we are in a perfect place to create an RPG-XML Suite template (at this point download and install a free trial of RPG-XML Suite from http://rpg-xml.com/freedownload.aspx if you haven’t done so already). 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.
- 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 Excel document 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.
This is yet another use showing how RPG-XML Suite can be utilized to meet your business needs and gain ROI. For more information on RPG-XML Suite please visit http://www.rpg-xml.com or email firstname.lastname@example.org.