XEROF

xlsgen 4.5 preview #4 : XML import

xlsgen imports structured data inside an XML file/stream/buffer with and without XPath selection. xlsgen provides auto-import and custom-import. Auto-import infers table columns as well as data types. Custom import lets you add personal formatting, particularly number and color formatting.

Auto-import XML files is achieved by passing an XML file (local or url) to the engine Open() method. Auto-import will find structured data inside the XML document.

For instance here is a sample XML document, a bookstore holding a number of books :


<?xml version="1.0" encoding="UTF8"?>
<Bookstore>
   <!--J&R Booksellers Database-->
   <Book Genre="" In_Stock="Yes">
      <Title>The Round Door</Title>
      <Author>Tom Evans</Author>
      <Year_Published>1996</Year_Published>
      <ISBN>0-9546-0274-3</ISBN>
      <Price>$23.00</Price>
      <Review>An Intriguing Tale Of A Round Door In A Wall</Review>
   </Book>
   <Book Genre="Non-Fiction" In_Stock="Yes">
      <Title>Creating Real Xml Applications</Title>
      <Author>Bill Eaton</Author>
      <Year_Published>1998</Year_Published>
      <ISBN>7-4562-0167-8</ISBN>
      <Price>$35.00</Price>
      <Review>A Look At How To Build Real Xml Applications</Review>
   </Book>
   <Book Genre="Fiction" In_Stock="No">
      <Title>Over The Hills Of Yukon2</Title>
      <Author>Bert Colewell</Author>
      <Year_Published>1993</Year_Published>
      <ISBN>5-6524-3054-1</ISBN>
      <Price>$22.00</Price>
      <Review>A Warm Story About A Man And A Moose In Yukon</Review>
   </Book>
   <Book Genre="Fiction" In_Stock="Yes">
      <Title>The Lion's Gold</Title>
      <Author>Daphne Griswald</Author>
      <Year_Published>1989</Year_Published>
      <ISBN>6-7896-2498-2</ISBN>
      <Price>$15.00</Price>
      <Review>One Of The Most Compelling Books Since "The Tiger's Silver".</Review>
   </Book>
</Bookstore>

In order to import the books into a spreadsheet, the following sample code can be used :

VB/VB.NET code


Dim engine
Set engine = CreateObject("ExcelGenerator.ARsTdesign")

Dim wbk As IXlsWorkbook 
Set wbk = engine.Open "C:\tmp\Bookstore.xml", "output.xlsx"
wbk.Close

XML document imported in xlsgen

Custom-import is available in the worksheet import interface. If for the need of an application, the structured data must be filtered or selected inside the XML document, it is possible to pass an XPath selector, which is available in the custom-import interface.

Here are XPath selection examples :

/Bookstore/Book
Fully qualified path to the XML elements of interest. In the example above, this is the list of all 4 books.
//Book
XML elements of interest that are direct or indirect descendant. In the example above, this is the list of all 4 books.
/Bookstore/Book[2]
2nd element of the XML element list of interest. In the example above, this selection includes the book by Bill Eaton
/Bookstore/Book[2,3]
2nd and 3rd elements of the XML element list of interest. In the example above, this selection includes the books by Bill Eaton and Bert Colewell

And here is the corresponding source code :

VB/VB.NET code


Dim engine
Set engine = CreateObject("ExcelGenerator.ARsTdesign")

Dim wbk As IXlsWorkbook 
Set wbk = engine.New( "output.xlsx" )

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet( "Sheet1" )

wksht.Import.XML.Options.XPath = "/Bookstore/Book"
wksht.Import.XML.ImportFile( "C:\\tmp\\Bookstore.xml" )

wbk.Close