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
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!
View all posts from Marc Van Cappellen on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.
Learn MoreSubscribe to get all the news, info and tutorials you need to build better business apps and sites
Progress collects the Personal Information set out in our Privacy Policy and the Supplemental Privacy notice for residents of California and other US States and uses it for the purposes stated in that policy.
You can also ask us not to share your Personal Information to third parties here: Do Not Sell or Share My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.