Excel

Purpose

Generates an Excel file from an XML document or generate an XML document from an Excel file.

Methods

Binding name: p6.excel

The format of the input XML document should be

Example

<template uri="file://some/path/myspreadsheet.xls">
    <sheet name="sheet1">
        <cells>
            <cell type="string" position="A1" row="0" column="0">Brian is in the kitchen</cell>
            <cell type="date" format="yyyy-MM-dd" row="1" column="1">1989-07-14</cell>
            <cell type="numeric" position="C3" row="2" column="2">42</cell>
        </cells>
         <table position="A4" hasHeaderRow="false">
            <columns>
                <column index="1" type="string" />
                <column index="2" type="numeric" />
                <column index="3" type="date" />
            </columns>
            <data type="csv"><![CDATA[...]]></data>
        </table>
         <table position="A4" hasHeaderRow="true">
            <columns>
                <column index="2" type="string">HeaderNameA</column>
                <column index="1" type="numeric">HeaderNameB</column>
                <column index="4" type="date">HeaderNameC</column>
            </columns>
            <data type="xml">
                <row>
                    <cell>Val1</cell>
                    <cell>Val2</cell>
                    <cell>Val3</cell>
                    <cell>Val4</cell>
                </row>
            </data>
        </table>
     </sheet>
     <sheet name="sheet2">[...]</sheet>
</template>

---

toXml

Create an XML from spreadsheet bytes and a SimpleDateFormat.

Syntax

byte[] p6.excel.toXml(byte[] excelBytes, SimpleDateFormat sdf)

Example

import java.text.SimpleDateFormat

// Read an uploaded Excel file from the pipeline and convert to XML
def excelBytes = p6.pipeline.getBytes('platform6.attachment.data.0')
def sdf = new SimpleDateFormat("yyyy-MM-dd")
def xmlBytes = p6.excel.toXml(excelBytes, sdf)
p6.log.debug 'Converted Excel to XML, length: ' + xmlBytes.length

---

toXml (inputstream)

New Feature

Since 6.10.20

Create an XML from a spreadsheet InputStream and a SimpleDateFormat.

Syntax

byte[] p6.excel.toXml(InputStream inputStream, SimpleDateFormat sdf)

Example

import java.text.SimpleDateFormat

new File('p6file:/opt/p6core.data/path/to/file.xlsx').newInputStream().withCloseable { stream ->
    def sdf = new SimpleDateFormat("yyyy-MM-dd")
    def xmlBytes = p6.excel.toXml(stream, sdf)
    p6.log.debug 'Converted Excel to XML, length: ' + xmlBytes.length
}

---

fromXml

Create spreadsheet as byte array given XML.

Syntax

byte[] p6.excel.fromXml(String xml)

Example

// Generate an Excel invoice report from the XML template resource
def xml = p6.resource.get('InvoiceTemplate')
def excelBytes = p6.excel.fromXml(xml)
p6.file.write excelBytes to 'p6file://${P6_DATA}/resources/exports/invoice-report.xlsx'

---

fromXml

Create spreadsheet as byte array given XML.

Syntax

byte[] p6.excel.fromXml(String xml, boolean reevaluateFormula)

Info

By default all formula cells are reevaluated after cells have been modified. If you want to avoid this behavior for some performance issue, you can set reevaluateFormula to false.

Example

// Generate a large report without re-evaluating formulas for performance
def xml = p6.resource.get('MonthlyReport')
def excelBytes = p6.excel.fromXml(xml, false)
p6.file.write excelBytes to 'p6file://${P6_DATA}/resources/exports/monthly-report.xlsx'