This page was generated
April 7, 2009
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.

.xlsx package SpreadsheetML function
[Content_Types].xml excel:content-types
_rels/.rels excel:package-rels
xl/workbook.xml excel:workbook
xl/_rels/workbook.xml.rels excel:workbook-rels
xl/worksheets/sheet1.xml,
xl/worksheets/sheet2.xml,
...xl/worksheets/sheetN.xml
excel:worksheet
 
 




Table 1
.xlsx package SpreadsheetML function
[Content_Types].xml excel:content-types
_rels/.rels excel:package-rels
xl/workbook.xml excel:workbook
xl/_rels/workbook.xml.rels excel:workbook-rels
xl/worksheets/sheet1.xml,
xl/worksheets/sheet2.xml,
...xl/worksheets/sheetN.xml
excel:worksheet
xl/worksheets/_rels/sheet1.xml.rels,
xl/worksheets/_rels/sheet2.xml.rels,
...xl/worksheets/_rels/sheetN.xml.rels
excel:worksheet-rels
xl/tables/table1.xml,
xl/tables/table2.xml
,...xl/tables/tableN.xml
excel:table
Table 2

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:a1-column(
$a1 as xs:string
)  as  xs:string
Summary:

This function returns the column letter from A1 notation cell reference.

Parameters:
$a1 : The cell reference in A1 notation.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:a1-column("D23")
=>
D
 
  

excel:a1-row(
$a1 as xs:string
)  as  xs:string
Summary:

This function returns the row number from A1 notation cell reference.

Parameters:
$a1 : The cell reference in A1 notation.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:a1-row("D23")
=>
23
       
  

excel:a1-to-r1c1(
$a1notation as xs:string
)  as  xs:string
Summary:

This function converts the cell coordinate in A1 notation to R1C1 notation (row/column notation).
Excel's limits for rows is 1048756, and for columns is 16384. This function returns R1C1 notation for any cell in that range
from A1 to WID1048576.

Parameters:
$a1notation : the A1 style coordinate reference.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:a1-to-r1c1("CJL1")
=>
R:1C:2378 
 

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:cell-string-value(
$cells as element(ms:c)*,
$shared-strings as element(ms:sst)
)  as  xs:string*
Summary:

This function returns the text values for the cells, mapping their values from the sharedStrings table. Cell values that are numbers will be returned as text.

Parameters:
$cells : The cells from the worksheet.
$shared-strings : The sharedStrings table.

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 $dir := "/Default_xlsx_parts/"
let $ss := fn:doc(excel:sharedstring-uri($dir))/node()
let $cels := (<ms:c>
                <ms:v>1</ms:v>
              </ms:c>,
              <ms:c t="s">
                <ms:v>1</ms:v>
              </ms:c>)
return excel:cell-string-value ($cels,$ss)

1
TEST
 

excel:column-width(
$widths as xs:integer+
)  as  element(ms:cols)
Summary:

This function creates the ms:cols element, which can be added to a worksheet to define custom column widths.

Parameters:
$widths : A sequence of column widths. It is assumed that the column widths are set by the order of the items in the sequence.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:column-width((15,15,15))
=>
<ms:cols xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <ms:col min="1" max="1" width="15" customWidth="1"/>
   <ms:col min="2" max="2" width="15" customWidth="1"/>
   <ms:col min="3" max="3" width="15" customWidth="1"/>
</ms:cols>


  

excel:content-types(
$worksheet-count as xs:integer,
[$tbl-count as xs:integer]
)  as  element(types:Types)
Summary:

This function creates a content types element for the .xlsx package ([Content_Types].xml)

Parameters:
$worksheet-count : The number of worksheets in the .xlsx package.
$tbl-count (optional): The number ot tablles in the .xlsx package

Example:
		    
xquery version "1.0-ml"; 
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:content-types(3) 
=>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
   <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
   <Default Extension="xml" ContentType="application/xml"/>
   <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
   <Override PartName="/xl/worksheets/sheet1" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
   <Override PartName="/xl/worksheets/sheet2" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
   <Override PartName="/xl/worksheets/sheet3" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
</Types>

 
Example:
		    
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:content-types(1,1)
=>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
   <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
   <Default Extension="xml" ContentType="application/xml"/>
   <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
   <Override PartName="/xl/worksheets/sheet1" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
   <Override PartName="/xl/tables/table1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"/>
</Types>
      
 

excel:create-row(
$values as xs:anyAtomicType*
)  as  element(ms:row)
Summary:

This function creates a row populated with cells, the values of which are passed in with the parameter sequence. The cell's values are either a number type or of type string.
This function creates a simple row, containing 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.

Parameters:
$values : number or string values that will be used as values for cells populated within row.

Example:
xquery version "1.0-ml"; 
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
let $vals :=(1,2,3,"TEST")
return excel:create-row($vals)
=>
<ms:row xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <ms:c><ms:v>1</ms:v></ms:c>
   <ms:c><ms:v>2</ms:v></ms:c>
   <ms:c><ms:v>3</ms:v></ms:c>
   <ms:c t="inlineStr"><ms:is><ms:t>TEST</ms:t></ms:is></ms:c>
</ms:row>


       
  

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-simple-xlsx(
$worksheets as element(ms:worksheet)*
)  as  binary()
Summary:

This function creates a .zip file that is a .xlsx package containing the minimum parts required to be a valid package. The parts include: [Content_Types].xml, .rels, workbook.xml,
workbook.xml.rels, and the sheets. Sheets are saved as sheet1.xml, sheet2.xml... to sheetN.xml. Pass the sheets into the function in the order you'd like them to be displayed in the workbook.

Parameters:
$worksheets : The worksheets to be used with the new workbook (.xlsx)

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 $ws:= (<ms:worksheet>
                <ms:sheetData>
                 <ms:row>
                   <ms:c t="s">
                   <ms:v>1</ms:v>
                 </ms:c>
                </ms:row>
               </ms:sheetData>
            </ms:worksheet>)
return xdmp:zip-manifest(excel:create-simple-xlsx($ws))
=>
<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="178" compressed-size="119" encrypted="false">xl/worksheets/sheet1.xml</part>
</parts>

 

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:directory-to-filename(
$directory as xs:string
)  as  xs:string
Summary:

This function converts the directory name, to the name of the .xlsx package.

Parameters:
$directory : The directory name to be converted.

Example:
xquery version "1.0-ml"; 
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:directory-to-filename("/Default_xlsx_parts/")
=>
Default.xlsx       
       
  

excel:directory-uris(
$directory as xs:string,
[$includesheets as xs:boolean]
)  as  xs:string*
Summary:

This function returns the URIs for files extracted from .xlsx package using Open XML Extract pipeline.

Parameters:
$directory : The name of the directory containing the extracted .xlsx parts.
$includesheets (optional): Set whether worksheets are returned with URIs. The Default setting is true.

Example:
xquery version "1.0-ml";		    
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
	    
excel:directory-uris("/Default_xlsx_parts/")
=>
/Default_xlsx_parts/[Content_Types].xml
/Default_xlsx_parts/_rels/.rels
/Default_xlsx_parts/customXml/_rels/item1.xml.rels
/Default_xlsx_parts/customXml/item1.xml
/Default_xlsx_parts/customXml/itemProps1.xml
/Default_xlsx_parts/docProps/app.xml
/Default_xlsx_parts/docProps/core.xml
/Default_xlsx_parts/xl/_rels/workbook.xml.rels
/Default_xlsx_parts/xl/sharedStrings.xml
/Default_xlsx_parts/xl/styles.xml
/Default_xlsx_parts/xl/theme/theme1.xml
/Default_xlsx_parts/xl/workbook.xml
/Default_xlsx_parts/xl/worksheets/sheet1.xml
/Default_xlsx_parts/xl/worksheets/sheet2.xml
/Default_xlsx_parts/xl/worksheets/sheet3.xml
   
  

excel:get-mimetype(
$uri as xs:string
)  as  xs:string?
Summary:

This function returns the mimetype for the given URI. Wrapper for xdmp:uri-content-type().

Parameters:
$uri : URI reference

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:get-mimetype("Default.xlsx")
=> 
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

  

excel:gregorian-to-julian(
$date as xs:date
)  as  xs:integer
Summary:

This function converts a gregorian date to an Excel julian day

Parameters:
$date : The date.

Example:
xquery version "1.0-ml";		    
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:gregorian-to-julian(xs:date("2009-04-06"))
=>
39909
       
  

excel:julian-to-gregorian(
$excel-julian-day as xs:integer
)  as  xs:dateTime
Summary:

This function converts and Excel julian day number to a gregorian date.

Parameters:
$excel-julian-day : The Excel julian day.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:julian-to-gregorian(39909)
=>
2009-04-06
       

  

excel:map-shared-strings(
$sheet as element(ms:worksheet),
$shared-strings as element(ms:sst)
)  as  element(ms:worksheet)
Summary:

This function dereferences cell text values so the actual text value is stored within the worksheet, instead of just a reference to the sharedStrings table. For string values in worksheet cells,
Excel stores an index to the sharedStrings table (ms:sst element, sharedStrings.xml in the .xlsx package). We dereference for search, and as people would like to get as much information
in the actual worksheet as possible, instead of having to map disparate parts within a .xlsx package. You can store this worksheet with the parts from the original .xlsx package, and still
open succesfully in Excel if you were to zip the parts back up. On save, Excel will setup the references to sharedStrings again.

Parameters:
$sheet : The worksheet with cells to be mapped to sharedStrings table.
$shared-strings : The sharedStrings table.

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 $ss := fn:doc(excel:sharedstring-uri("/Default_xlsx_parts/"))/node()

let $ws:= (<ms:worksheet>
                <ms:sheetData>
                 <ms:row>
                   <ms:c t="s">
                   <ms:v>1</ms:v>
                 </ms:c>
                </ms:row>
               </ms:sheetData>
           </ms:worksheet>)

return excel:map-shared-strings($ws,$ss)
=>
<ms:worksheet xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <ms:sheetData>
      <ms:row>
	<ms:c t="inlineStr">
	   <ms:is><ms:t>TEST</ms:t></ms:is>
	</ms:c>
      </ms:row>
   </ms:sheetData>
</ms:worksheet>

    
  

excel:package-rels( ) as element(pr:Relationships)
Summary:

This function creates a package relationships element for the .xlsx package (_rels/.rels)

Example:
		    
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:package-rels()
       =>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
   <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>
       
  

excel:r1c1-to-a1(
$row-index as xs:integer,
$col-index as xs:integer
)  as  xs:string
Summary:

This function converts the row and column numbers from R1C1 notation (row/column notation) to A1 notation.
Excel's limits for rows is 1048756, and for columns is 16384. This function returns A1 notation for any cell in that range from A1 to WID1048576.

Parameters:
$row-index : the row index
$col-index : the column index

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:r1c1-to-a1(1,2378)
=>
CJL1 
 

excel:row(
$cells as element(ms:c)+
)  as  element(ms:row)
Summary:

This function creates a worksheet row.

Parameters:
$cells : The cells to be populated within the row. It is assumed that all cells have an A1 reference and that they are in the same row. The row number
is determined by the row number identified by the cell.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";


let $cell1 := excel:cell("A3",32999,(),0)
let $cell2 := excel:cell("B3",123)
let $cell3 := excel:cell("C3","Foo")
return excel:row(($cell1,$cell2,$cell3))
=>
<ms:row r="3" xmlns:ms="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <ms:c r="A3" s="0"><ms:v>32999</ms:v></ms:c>
   <ms:c r="B3"><ms:v>123</ms:v></ms:c>
   <ms:c r="C3" t="inlineStr"><ms:is><ms:t>Foo</ms:t></ms:is></ms:c>
</ms:row>



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:sharedstring-uri(
$directory as xs:string
)  as  xs:string*
Summary:

This function returns the URI for the sharedString table, extracted from .xlsx using Open XML Extract pipeline.

Parameters:
$directory : The name of the directory containing the extracted .xlsx parts.

Example:
xquery version "1.0-ml"; 
import module namespace excel= "http://marklogic.com/openxml/excel" 
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:sharedstring-uri("/Default_xlsx_parts/")
=>
/Default_xlsx_parts/xl/sharedStrings.xml
       
       
  

excel:sheet-uris(
$directory as xs:string
)  as  xs:string*
Summary:

This function returns only the worksheet URIs for files extracted from .xlsx package using Open XML Extract pipeline.

Parameters:
$directory : The name of the directory containing the extracted .xlsx parts.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
	    
excel:sheet-uris("/Default_xlsx_parts/")
=>
/Default_xlsx_parts/xl/worksheets/sheet1.xml
/Default_xlsx_parts/xl/worksheets/sheet2.xml
/Default_xlsx_parts/xl/worksheets/sheet3.xml

  

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:workbook(
$worksheet-count as xs:integer
)  as  element(ms:workbook)
Summary:

This function creates a workbook element for the .xlsx package (xl/workbook.xml)

Parameters:
$worksheet-count : The number of worksheets in the workbook.

Example:
		    
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

excel:workbook(3)
=>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
   <sheets>
      <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
      <sheet name="Sheet2" sheetId="2" r:id="rId2"/>
      <sheet name="Sheet3" sheetId="3" r:id="rId3"/>
   </sheets>
</workbook>
       
       
  

excel:workbook-rels(
$worksheet-count as xs:integer
)  as  element(pr:Relationships)
Summary:

This function creates a workbook relationships element for the .xlsx package (xl/_rels/workbook.xml.rels)

Parameters:
$worksheet-count : The number of worksheets in the workbook.

Example:
		    
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:workbook-rels(3) 
=>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
   <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
   <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>
   <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet3.xml"/>
</Relationships>
      
  

excel:workbook-sheet-names(
$workbook as element(ms:workbook)
)  as  xs:string*
Summary:

This function returns the iworksheet names for the workbook.

Parameters:
$workbook : The workbook.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

let $workbook := fn:doc("/Default_xlsx_parts/xl/workbook.xml")/node()
return
  excel:workbook-sheet-names($workbook)
=>
Sheet1
Sheet2
Sheet3


		  
  

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:worksheet-rels(
$start-ind as xs:integer,
$tbl-count as xs:integer
)  as  element(pr:Relationships)
Summary:

This function createts a worksheet relationships element for a .xlsx package (xl/worksheets/_rels/sheet#.xml.rels)

Parameters:
$start-ind : The starting index for tables relating to the worksheet.
$tbl-count : The number of tables relating to the worksheet.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
       at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";
       
excel:worksheet-rels(2,2)
       =>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
   <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" Target="../tables/table2.xml"/>
   <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" Target="../tables/table3.xml"/>
</Relationships>
       
       
  

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>
    
  

excel:xlsx-manifest(
$directory as xs:string,
$uris as xs:string*
)  as  element(zip:parts)
Summary:

This function constructs a manifest for the given URIs in the directory.

Parameters:
$directory : The name of the directory.
$uris : The URIs within the directory to be listed in the manifest.

Example:
xquery version "1.0-ml";
import module namespace excel= "http://marklogic.com/openxml/excel" 
    at "/MarkLogic/openxml/spreadsheet-ml-support.xqy";

let $uris := ("/Default_xlsx_parts/xl/workbook.xml",
              "/Default_xlsx_parts/xl/worksheets/Sheet1.xml",
              "/Default_xlsx_parts/[Content_Types].xml",
              "/Default_xlsx_parts/_rels/.rels")

return excel:xlsx-manifest("/Default_xlsx_parts/",$uris)
=>
<parts xmlns="xdmp:zip">
  <part>xl/workbook.xml</part>
  <part>xl/worksheets/Sheet1.xml</part>
  <part>[Content_Types].xml</part>
  <part>_rels/.rels</part>
</parts>