Our previous post demonstrated how easy it is to create Microsoft Office documents with DataDirect XQuery. Today we'll go through the same exercise for Open Office based on the Open Documentstandard.
We'll reuse the use case of last time, and create a .ODS spreadsheet similar to our .XLSX. We'll query the BIDS table of Use Case "R", and to keep the example simple, export the complete BIDS table ordered by BIDDER.
[cc lang="xquery"]import module namespace ddtek-odf="http://www.datadirect.com/xquery/odf" at "odf.xq"; let $content :=
{ for $bid in collection("BIDS")/BIDS order by $bid/USERIDreturn}
return ddtek-odf:createODS($content, "file:///C:/allbids.ods")[/cc]
Executing the query above creates an allbids.ods spreadsheet. Opening it with OpenOffice.org Calc, it will look as follows.
Let's now have a closer look at the query, which does basically two things. First it creates the worksheet, second this worksheet is passed to ddtek-odf:createODS(). This generic utility function facilitates the creation of Open Document spreadsheets. The ddtek-odf:createODS() utility function is completely written in XQuery, the XQuery module is available here. Changing the main query, and passing in to ddtek-odf:createODS() a different sheet is sufficient to get a spreadsheet according to your business needs.
What happens under the covers of ddtek-odf:createODS()? In the Open Documentspecification, we read a spreadsheet consists at least the following XML documents
- META-INF/manifest.xml
- meta.xml
- settings.xml
- styles.xml
- content.xml
The META-INF/manifest.xml file describes the structure of the ODF document, i.e. the files contained in the document. Here is an example,
[cc lang="xquery"]
[/cc]
Although that the meta.xml, settings.xml and styles.xml XML documents are required, for our use case the content can be almost empty. The minimal meta.xml, settings.xml and styles.xml documents are as follows.
[cc lang="xquery"]
[/cc]
And the last document, the actual spreadsheet's data, as we have seen in the main query above, is passed in as argument to ddtek-odf:createODS().
We have now created our 5 XML documents. All what is left is to serialize these documents into an ODS file. As ODS is structured according the ZIP format, we use the ddtek:serialize-to-url function, which we introduced in a previous post. Check out the code of the odf.xqmodule for all the details.
Not only have we learned how to create Microsoft Office (OOXML) and Open Office (ODF) spreadsheets with DataDirect XQuery. We also provided the necessary XQuery modules, ooxml.xq and odf.xq to jump start!