I have my business data residing in a relational database, a set of EDI messages, or yet some other format and want to export it into Excel. Preferable in only a few lines of code. Without the requirement to have access to Microsoft Office, the solution needs to easily integrate with my enterprise application architecture.
In this blog we have discussed several times the ability to query office documents, including Microsoft Excel spreadsheets. Querying... but what about creating or updating such documents? For sure you can create such beasts!
We know Office Open XML documents are based on the ZIP standard. And as we learned last week, the latest version of DataDirect XQuery makes it almost trivial to construct XML documents embedded in a ZIP file.
For this example, we'll use the BIDS table from Use Case "R", remember we also used it in the XQuery for the SQL programmer series. To keep the example simple, we'll export the complete BIDS table ordered by BIDDER in an Excel spreadsheet.
[cc lang="xquery"]import module namespace ddtek-ooxml = "http://www.datadirect.com/xquery/ooxml" at "ooxml.xq";
declare namespace sml = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
let $sheet :=
{ for $bid in collection("BIDS")/BIDS order by $bid/USERID return
{ $bid/USERID/text() }
{ $bid/ITEMNO/text() }
{ $bid/BID/text() }
}
return ddtek-ooxml:createXLSX( $sheet, "Bids", "file:///C:/allbids.xlsx")[/cc]
Executing this query results in the creation of a allbids.xlsx spreadsheet, opening it in Excel it could look as follows. The main query creates a SpreadsheetML worksheet, which is passed to ddtek-ooxml:createXLSX(). This generic utility function facilitates the creating of an Excel spreadsheet with a single sheet. Changing the main query, and passing in to ddtek-ooxml:createXLSX() a different sheet is sufficient to get an Excel spreadsheets according to your business needs. The ddtek-ooxml:createXLSX() utility function is completely written in XQuery, the XQuery module is available here.
We're done... Using DataDirect XQuery and less than 25 lines of code you create your Excel spreadsheets!
You might as well stop reading and try it out. Or if you want, stay with us while we scratch the surface and discuss what happens under the covers in ddtek-ooxml:createXLSX(). As defined in the Open Office XML (OOXML) standard, a SpreadsheetML document, includes at least 5 XML documents.
- [Content_Types].xml
- _rels/.rels
- xl/_rels/workbook.xml.rels
- /xl/workbook.xml
- xl/worksheets/sheet1.xml
A spreadsheet has at minimum two parts, the workbook and a sheet, for both the content type needs to be specified in the [Content_Types].xml document. It looks like something as follows.
[cc lang="xquery"]
[/cc]
Next the required package-level relationship to the workbook must be defined in the _rels/.rels document.
We also need to define the workbook-level relationship to our single sheet in the xl/_rels/workbook.xml.rels.
Our workbook includes one sheet. The workbook specifies the name of the sheet. In our case we make this parameterized, our ddtek-ooxml:createXLSX() function is defined with a $sheetName parameter.
The fifth and last document is the sheet, remember we have created that in our main query and passed it as argument to ddtek-ooxml:createXLSX().
We have now created our 5 XML documents. All what is left is to serialize these documents into an XSLX file. As XSLX 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 ooxml.xq module for all the details.
We have shown how easy it is to create office documents. And by making the code for the ddtek-ooxml:createXLSX() function available, you can easily extend it to create more advanced Excel spreadsheets, for example including multiple sheets.
In a next post, we'll do the same exercise, but then creating an Open Office spreadsheet, based on the OpenDocument Format.