Archive for February, 2011

XML to HTML conversion for IT

Posted: February 18, 2011 in Scripting

Lately, I’ve been finding myself retrieving back end data from systems on a more frequent basis. The data comes out as XML in most cases which is great for manipulation in scripts and integration with other systems but is not so great for presentation.

A recent task of this nature got me started down the path of converting the XML data to HTML so that the result can be easily published as a report to an intranet or emailed to recipients. Such a mechanism would allow me to create scheduled reports for clients without requiring a reporting platform.

As soon as I started digging into this task, it seemed that I couldn’t avoid learning a lot more about the XML data format and the associated standards that allow the data to be converted and transformed. Specifically, it seemed that I would have to get very familiar with XSLT, the transformation defining cousin of XML.

This prospect was not appealing to me since I’m not and have never been a web programmer. I’m not comfortable with HTML or other web based development technology and really have no need for those in-depth skill sets in my job. So I decided to spend some time looking for an alternative that was easier for me to pick up.

After going through various options, including third party solutions, my approach, which fit well within my scripting skill sets and is easy to research and learn, is to use Microsoft Excel as my transformation engine. Using simple calls to the Excel object model, I can quickly create a basic HTML page with my data. If needed, that page can then be passed on to web gurus to be processed by existing portals, intranets, CSS based configurations, etc. Or, left alone as an unattractive but informative and usable web page.

I used PowerShell to get this done so the sample code below is in PowerShell but VBA and VBScript could be used just as easily (although VBA isn’t as easy to secure and schedule).

The steps to execute are extremely simple – open the XML file in Excel, make any modifications to the data as needed and then save as HTML. Just the right level of simplicity for me; although the solution will require Excel installed on the system, this still seems like a simple approach that can easily be supported by client or server engineering teams down the road (rather than needing web/xml developers that aren’t typically part of IT engineering teams).

This code sample processes an XML file into an HTML file after reordering columns and replacing headers:

$excelApp = new-object -comobject "excel.application"

$excelapp.visible=$true

$excelWorkbook=$excelapp.workbooks.open("d:\downloads\excelps\xmldata.xml")

 

#restructure field

$excelWorkbook.Activesheet.Columns.item("O").cut()

$excelWorkbook.Activesheet.Columns.item("A").Insert()

 

$excelWorkbook.Activesheet.Columns.item("D").cut()

$excelWorkbook.Activesheet.Columns.item("B").Insert()

 

$excelWorkbook.Activesheet.Columns.item("G").cut()

$excelWorkbook.Activesheet.Columns.item("C").Insert()

 

$return=$excelWorkbook.Activesheet.Columns.item("G:Q").delete()

 

$return=$excelWorkbook.Activesheet.Cells.item(2,1)="User Name"

$return=$excelWorkbook.Activesheet.Cells.item(2,2)="Physical Hostname"

 

$excelWorkbook.saveas("d:\downloads\excelps\report.html",44)

$excelworkbook.close()

$excelapp.quit()

Note: I do set the application to be visible for troubleshooting purposes, the final automated code should not require the application to be visible.

The best part about this for me is that you perform any manipulation of the data prior to saving it – search and replace, format numbers and strings, etc. And for any manipulation, you can use the macro recorder to get the exact Excel code required.

Advertisements