This page was generated
November 7, 2011
1:37 PM
|
XQuery Function Reference
|

Module: SpreadsheetML Support
|
The SpreadsheetML function module is installed as the following
file:
install_dir/Modules/MarkLogic/openxml/spreadsheet-ml-support.xqy
where install_dir is the directory in which
MarkLogic Server is installed.
To use the spreadsheet-ml-support.xqy module in your own
XQuery modules,
include the following line in your XQuery prolog:
import module namespace excel="http://marklogic.com/openxml/excel"
at "/MarkLogic/MarkLogic/openxml/spreadsheet-ml-support.xqy"
The SpreadsheetML functions are used to manipulate Microsoft Office
Open XML documents.
Pieces required for creating a .xlsx
Below are functions for manipulating the XML files from Excel 2007 documents which have been extracted from their .xlsx package. Also provided are functions for the dynamic generation of complete workbooks (a .xlsx package) and their requisite related parts. Some details on standard and enhanced .xlsx packages follows.
The required files for a standard minimum .xlsx, a workbook, with just worksheets, and no tables (tables manifest as NamedRanges/AutoFiltering/Styles in Excel), requires the pieces detailed in Table1 below. In Table2 on the right are details for the pieces required when adding Excel tables to the .xlsx. In the right column of each table we've noted which functions can assist in generating these parts.
|
Note: Each worksheet will have its own file, sheet1.xml, sheet2.xml to sheetN.xml. The number of sheets and their relationships must be accounted for in [Content_Types].xml, workbook.xml, and workbook.xml.rels. When adding tables, each table has its own file as well: table1.xml, table2.xml, to tableN.xml. Each table is mapped to a sheet#.xml.rels, where each sheet has its own single relationships file for relating the tables to the sheet. If no table is related to the sheet, sheet#.xml.rels file is Not required for that sheet.
|
Function Summary
|
excel:a1-column
|
This function returns the column letter from A1 notation cell reference.
|
excel:a1-row
|
This function returns the row number from A1 notation cell reference.
|
excel:a1-to-r1c1
|
This function converts the cell coordinate in A1 notation to R1C1 notation (row/column notation).
|
excel:cell
|
This function creates a cell.
|
excel:cell-string-value
|
This function returns the text values for the cells, mapping their values from the sharedStrings table.
|
excel:column-width
|
This function creates the ms:cols element, which can be added to a worksheet to define custom column widths.
|
excel:content-types
|
This function creates a content types element for the .xlsx package ([Content_Types].xml).
|
excel:create-row
|
This function creates a row populated with cells, the values of which are passed in with the parameter sequence.
|
excel:create-row
|
This function creates a row populated with cells, the values of which are passed in with the map parameter.
|
excel:create-simple-xlsx
|
This function creates a .zip file that is a .xlsx package containing the minimum parts required to be a valid package.
|
excel:create-xlsx-from-xml-table
|
This function will attempt to generate an Excel workbook from an XML table.
|
excel:directory-to-filename
|
This function converts the directory name, to the name of the .xlsx package.
|
excel:directory-uris
|
This function returns the URIs for files extracted from .xlsx package using Open XML Extract pipeline.
|
excel:get-mimetype
|
This function returns the mimetype for the given URI.
|
excel:gregorian-to-julian
|
This function converts a gregorian date to an Excel julian day.
|
excel:julian-to-gregorian
|
This function converts and Excel julian day number to a gregorian date.
|
excel:map-shared-strings
|
This function dereferences cell text values so text is stored within
the worksheet, instead of just a reference sharedStrings table. |
excel:package-rels
|
This function creates a package relationships element for the .xlsx package (_rels/.rels).
|
excel:r1c1-to-a1
|
This function converts the row and column numbers from R1C1 notation (row/column notation) to the preferred A1
notation. |
excel:row
|
This function creates a worksheet row.
|
excel:set-cells
|
This function sets the cells within the provided worksheet.
|
excel:sharedstring-uri
|
This function returns the URI for the sharedString table, extracted from .xlsx using Open XML Extract pipeline.
|
excel:sheet-uris
|
This function returns only the worksheet URIs for files extracted from .xlsx package using Open XML Extract pipeline.
|
excel:table
|
This function creates a table element for a .xlsx package (xl/tables/table#.xml).
|
excel:workbook
|
This function creates a workbook element for the .xlsx package (xl/workbook.xml) .
|
excel:workbook-rels
|
This function creates a workbook relationships element for the .xlsx package (xl/_rels/workbook.xml.rels).
|
excel:workbook-sheet-names
|
This function returns the iworksheet names for the workbook.
|
excel:worksheet
|
This function creates a worksheet for the .xlsx package (xl/worksheets/sheet#.xml).
|
excel:worksheet-rels
|
This function createts a worksheet relationships element for a .xlsx package (xl/worksheets/_rels/sheet#.xml.rels).
|
excel:xlsx-package
|
This function creates a .zip file that is the .xlsx package containing the parts passed in as parameters.
|
excel:xlsx-manifest
|
This function constructs a manifest for the given URIs in the directory.
|
|
 |
Function Detail
|
|
excel:cell(
|
|
$a1-ref as xs:string,
|
|
$value as xs:anyAtomicType?,
|
|
[$formula as xs:string?],
|
|
[$date-id as xs:integer?]
|
) as element(ms:c) |
|
 |
Summary:
This function creates a cell.
|
Parameters:
$a1-ref
:
The row and column number for the cell provided in A1 notation.
|
$value
:
The cell value, either a number or a string.
|
$formula
(optional):
The formula for the cell.
|
$date-id
(optional):
The date style for the cell.
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
excel:cell("A1",123)
=>
<ms:c r="A1" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<ms:v>123</ms:v>
</ms:c>
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
excel:cell("A3",(),"SUM(A1:A2)")
=>
<ms:c r="A3" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<ms:f>SUM(A1:A2)</ms:f>
</ms:c>
Note:
Above we pass the empty sequence for the value, so only the formula is populated for the cell.
This way, when the cell is opened within Excel, the value will be generated by the formula and
populated within the cell. It is possible to create a cell with a value and a formula, where
the value the formula calculates and the value are not equal. Excel will not automatically correct this disparity
upon consuming the cell.
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
excel:cell("A3",32999,(),0)
=>
<ms:c r="A3" s="0" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<ms:v>32999</ms:v>
</ms:c>
Note:
Formula and date style assignment only applies to cells containing numeric values.
Formula and date style assignment will be ignored in the case that $value is of type xs:string.
|
|
|
excel:create-row(
|
|
$map as map:map,
|
|
$keys as xs:string*
|
) as element(ms:row) |
|
 |
Summary:
This function creates a row populated with cells, the values of which are passed in with the map parameter. The cell's values
are either a number or of type string. This function creates a simple row, with simple cells; there
is no way to set row number explicitly, and there is no way to set cell
row/column position. It is assumed that the cells are populated in the
row in the order that they are passed to the function within the map.
Keys are passed separately as you may have more keys than are contained
within the map, this is done for sparsely populated tables. If the key
doesn't exist within the map, the cell will be created but will not
contain any value.
|
Parameters:
$map
:
Map containing values to be used for cell values within row.
|
$keys
:
Keys to be used for column values. If key doesn't exist within map, an empty cell will be created.
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
let $map := map:map()
let $put := (map:put($map, "RequestID",45683),
map:put($map, "Customer","Pete"))
let $keys := ("Customer","Address","RequestID")
return excel:create-row($map,$keys)
=>
<ms:row xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<ms:c t="inlineStr"><ms:is><ms:t>Pete</ms:t></ms:is></ms:c>
<ms:c t="inlineStr"><ms:is><ms:t/></ms:is></ms:c>
<ms:c><ms:v>45683</ms:v></ms:c>
</ms:row>
|
|
|
excel:create-xlsx-from-xml-table(
|
|
$original-xml as node(),
|
|
[$col-cust-width as xs:integer?],
|
|
[$auto-filter as xs:boolean?]
|
|
[$tbl-style as xs:boolean?]
|
) as binary()? |
|
 |
Summary:
This function will attempt to generate an Excel workbook from an XML
table. An XML table here is defined as a single wrapper element, with a
single repeating child element. The children elements of this repeating
element may vary in name and number. The list of distinct names of all the children elements will be
used as headers for columns, and their values will populate rows. It is
possible for the children to have different element names, so its
possible to generate a sparsely populated table. If the xlsx is not able to be created, and empty sequence is returned.
|
Parameters:
$original-xml
:
The XML to be converted.
|
$col-cust-width
:
A custom width for the columns in the worksheet.
|
$auto-filter
:
If true, columns will have auto-filter enabled.
|
$tbl-style
:
If true, then the table will have style TableStyleMedium10 applied.
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
let $xml := <catalog>
<item>
<product>beach ball</product>
<sku>123123</sku>
</item>
<item>
<product>swim fins</product>
<sku>444444</sku>
</item>
<item>
<product>scuba glasses</product>
<sku>888</sku>
</item>
</catalog>
let $package := excel:create-xlsx-from-xml-table($xml,"15",fn:true())
return xdmp:zip-manifest($package)
=>
<parts xmlns="xdmp:zip">
<part uncompressed-size="627" compressed-size="232" encrypted="false">[Content_Types].xml</part>
<part uncompressed-size="229" compressed-size="146" encrypted="false">xl/workbook.xml</part>
<part uncompressed-size="241" compressed-size="140" encrypted="false">_rels/.rels</part>
<part uncompressed-size="242" compressed-size="144" encrypted="false">xl/_rels/workbook.xml.rels</part>
<part uncompressed-size="1034" compressed-size="297" encrypted="false">xl/worksheets/sheet1.xml</part>
<part uncompressed-size="237" compressed-size="141" encrypted="false">xl/worksheets/_rels/sheet1.xml.rels</part>
<part uncompressed-size="410" compressed-size="229" encrypted="false">xl/tables/table1.xml</part>
</parts>
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
let $xml := <catalog>
<item>
<product>beach ball</product>
<sku>123123</sku>
</item>
<item>
<product>swim fins</product>
<sku>444444</sku>
</item>
<item>
<product>scuba glasses</product>
<sku>888</sku>
</item>
</catalog>
let $package := excel:create-xlsx-from-xml-table($xml,"15",xs:boolean("true"))
let $doc := xdmp:zip-get($package,"xl/worksheets/sheet1.xml")
return $doc
=>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cols xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<col min="1" max="1" width="15" customWidth="1"/>
<col min="2" max="2" width="15" customWidth="1"/>
</cols>
<sheetData>
<row xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c t="inlineStr"><is><t>product</t></is></c>
<c t="inlineStr"><is><t>sku</t></is></c>
</row>
<row xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c t="inlineStr"><is><t>beach ball</t></is></c>
<c><v>123123</v></c>
</row>
<row xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c t="inlineStr"><is><t>swim fins</t></is></c>
<c><v>444444</v></c>
</row>
<row xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c t="inlineStr"><is><t>scuba glasses</t></is></c>
<c><v>888</v></c>
</row>
</sheetData>
<tableParts count="1">
<tablePart r:id="rId1" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"/>
</tableParts>
</worksheet>
|
|
|
excel:set-cells(
|
|
$worksheet as element(ms:worksheet),
|
|
$cells as element(ms:c)*
|
) as element(ms:worksheet) |
|
 |
Summary:
This function sets the cells within the provided worksheet. If the cell
exists in the worksheet, it will be replaced. If the cell doesn't
exist, it will be set in the proper order within the proper row. If the
row doesn't exist, the row will be created and populated with the cell.
|
Parameters:
$worksheet
:
The worksheet where to set the cells.
|
$cells
:
The cells to be set within $worksheet.
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
declare namespace ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main";
let $cell1 := excel:cell("A1","foo")
let $cell2 := excel:cell("B3",123)
let $cell3 := excel:cell("A5",456)
let $worksheet :=
<ms:worksheet>
<ms:sheetData>
<ms:row r="1">
<ms:c r="A1"><ms:v>1</ms:v></ms:c>
</ms:row>
<ms:row r="5">
<ms:c r="C5"><ms:v>1</ms:v></ms:c>
</ms:row>
</ms:sheetData>
</ms:worksheet>
return excel:set-cells($worksheet, ($cell1,$cell2,$cell3))
=>
<ms:worksheet xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<ms:sheetData>
<ms:row r="1">
<ms:c r="A1" t="inlineStr"><ms:is><ms:t>foo</ms:t></ms:is></ms:c>
</ms:row>
<ms:row r="3">
<ms:c r="B3"><ms:v>123</ms:v></ms:c>
</ms:row>
<ms:row r="5">
<ms:c r="A5"><ms:v>456</ms:v></ms:c>
<ms:c r="C5"><ms:v>1</ms:v></ms:c>
</ms:row>
</ms:sheetData>
</ms:worksheet>
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
declare namespace ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main";
let $cel1 := excel:cell("G23","bar")
let $worksheet :=
<ms:worksheet>
<ms:sheetData>
</ms:sheetData>
</ms:worksheet>
return excel:set-cells($worksheet, ($cel1))
=>
<ms:worksheet xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<ms:sheetData>
<ms:row r="23">
<ms:c r="G23" t="inlineStr"><ms:is><ms:t>bar</ms:t></ms:is></ms:c>
</ms:row>
</ms:sheetData>
</ms:worksheet>
|
|
|
excel:table(
|
|
$table-number as xs:integer,
|
|
$tablerange as xs:string,
|
|
$column-names as xs:string+,
|
|
[$auto-filter as xs:boolean],
|
|
[$style as xs:boolean]
|
) as element(ms:table) |
|
 |
Summary:
This function creates a table element for a .xlsx package (xl/tables/table#.xml). Tables in SpreadsheetML, manifest themselves as Named Ranges in an Excel worksheet. With these tables you can apply auto-formatting for columns, as well as styling.
|
Parameters:
$table-number
:
The number of the table.
|
$tablerange
:
The ranger of the table in Excel notation A1-colon notation. ("A1:C3")
|
$column-names
:
The names of the columns. These are the values from the first row in the cells found in $tablerange.
|
$auto-filter
(optional):
If true, columns will have autofilter enabled. Default true.
|
$style
(optional):
If true, simple style of TableStyleMedium10 is applied to table. Default none.
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
excel:table(1,"A1:C3",("Heading1","Heading2","Heading3"))
=>
<table id="1" name="Table1" displayName="Table1" ref="A1:C3" totalsRowShown="0" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<autoFilter ref="A1:C3"/>
<tableColumns count="3">
<tableColumn id="1" name="Heading1"/>
<tableColumn id="2" name="Heading2"/>
<tableColumn id="3" name="Heading3"/>
</tableColumns>
</table>
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
excel:table(1,"A1:C3",("Heading1","Heading2","Heading3"),xs:boolean("false"),xs:boolean("true"))
=>
<table id="1" name="Table1" displayName="Table1" ref="A1:C3" totalsRowShown="0" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<tableColumns count="3">
<tableColumn id="1" name="Heading1"/>
<tableColumn id="2" name="Heading2"/>
<tableColumn id="3" name="Heading3"/></tableColumns>
<tableStyleInfo name="TableStyleMedium10" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>
|
|
|
excel:worksheet(
|
|
$rows as element(ms:row)*,
|
|
[$colwidths as element(ms:cols)?],
|
|
[$tbl-count as xs:integer]
|
) as element(ms:worksheet) |
|
 |
Summary:
This function creates a worksheet for the .xlsx package (xl/worksheets/sheet#.xml)
|
Parameters:
$rows
:
The rows for the worksheet.
|
$colwidths
(optional):
The custom column widths for the worksheet.
|
$tbl-count
(optional):
The number of tables associated with the worksheet.
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
let $cells := ((excel:cell("A1",1),
excel:cell("B1",2),
excel:cell("C1",3)))
let $row := excel:row($cells)
return excel:worksheet($row)
=>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetData>
<row r="1" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c r="A1"><v>1</v></c>
<c r="B1"><v>2</v></c>
<c r="C1"><v>3</v></c>
</row>
</sheetData>
</worksheet>
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
let $cells := ((excel:cell("A1",1),
excel:cell("B1",2),
excel:cell("C1",3)))
let $row := excel:row($cells)
let $colwidths := excel:column-width((25,25,25))
return excel:worksheet($row,$colwidths)
=>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetData>
<cols>
<col min="1" max="1" width="25" customWidth="1"/>
<col min="2" max="2" width="25" customWidth="1"/>
<col min="3" max="3" width="25" customWidth="1"/>
</cols>
<row r="1" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c r="A1"><v>1</v></c>
<c r="B1"><v>2</v></c>
<c r="C1"><v>3</v></c>
</row>
</sheetData>
</worksheet>
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
let $cells := ((excel:cell("A1",1),
excel:cell("B1",2),
excel:cell("C1",3)))
let $row := excel:row($cells)
let $colwidths := excel:column-width((25,25,25))
return excel:worksheet($row,$colwidths,2)
=>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cols>
<col min="1" max="1" width="25" customWidth="1"/>
<col min="2" max="2" width="25" customWidth="1"/>
<col min="3" max="3" width="25" customWidth="1"/>
</cols>
<sheetData>
<row r="1" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<c r="A1"><v>1</v></c>
<c r="B1"><v>2</v></c>
<c r="C1"><v>3</v></c>
</row>
</sheetData>
<tableParts count="2">
<tablePart r:id="rId1"/>
<tablePart r:id="rId2"/>
</tableParts>
</worksheet>
|
|
|
excel:xlsx-package(
|
|
$content-types as element(types:Types),
|
|
$workbook as element(ms:workbook),
|
|
$rels as element(pr:Relationships),
|
|
$workbookrels as element(pr:Relationships),
|
|
$sheets as element(ms:worksheet)*,
|
|
[$worksheetrels as element(pr:Relationships)*],
|
|
[$table as element(ms:table)*]
|
) as binary() |
|
 |
Summary:
This function creates a .zip file that is the .xlsx package containing
the parts passed in as parameters. It is assumed the parts are properly
mapped to one another. Just using the constructors provided, and setting the number of worksheets, its possible to generate workbooks dynamically. It's also quite simple to add a named range to a single worksheet. It can get a little tricker when adding multiple tables to multiple worksheets, but we've aimed to simplify workbook construction for you with the constructors provided.
Some things to remember: Worksheets will appear in the Workbook in the order they are added within their sequence. Tables must have unique names, and unique relationship ids. (the constructor accounts for this by letting you add tables by number, as that's how they'll be saved within the .xlsx package natively.) Every table must also have a worksheet relationship defined. Each worksheet has its own worksheet relationships filed saved within the .xlsx package.
|
Parameters:
$content-types
:
The [Content_Types].xml part.
|
$workbook
:
The workbook.xml part.
|
$rels
:
The package Relationships part.
|
$workbookrels
:
The workbook Relationships part.
|
$sheets
:
The worksheet (sheetN.xml) parts.
|
$worksheetrels
(optional):
The sheetN.xml Relationships parts. (Required when mapping tables to sheets.)
|
$table
(optional):
The table.xml parts. (Used for named ranges, auto-filtering columns, styling, etc.)
|
|
Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel"
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
declare namespace ms = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
let $worksheets:= (<ms:worksheet>
<ms:sheetData>
<ms:row>
<ms:c>
<ms:v>1</ms:v>
</ms:c>
</ms:row>
</ms:sheetData>
</ms:worksheet>)
let $ws-count := fn:count($worksheets)
let $content-types := excel:content-types($ws-count,0)
let $workbook := excel:workbook($ws-count)
let $rels := excel:package-rels()
let $workbookrels := excel:workbook-rels($ws-count)
let $package := excel:xlsx-package($content-types, $workbook, $rels, $workbookrels, $worksheets)
return xdmp:zip-manifest($package)
=>
<parts xmlns="xdmp:zip">
<part uncompressed-size="499" compressed-size="218" encrypted="false">[Content_Types].xml</part>
<part uncompressed-size="229" compressed-size="146" encrypted="false">xl/workbook.xml</part>
<part uncompressed-size="241" compressed-size="140" encrypted="false">_rels/.rels</part>
<part uncompressed-size="242" compressed-size="144" encrypted="false">xl/_rels/workbook.xml.rels</part>
<part uncompressed-size="172" compressed-size="115" encrypted="false">xl/worksheets/sheet1.xml</part>
</parts>
|
|
|
|
|
|