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

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

Example

import java.text.SimpleDateFormat

def bytesXls = []
def sdf = new SimpleDateFormat("yyyy-MM-dd")
def newXml = p6.excel.toXml(bytesXls, sdf)

---

fromXml

Create spreadsheet as byte array given XML.

Syntax

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

Example

def xml = p6.resource.get("Invoice")
def bytesXls = p6.excel.fromXml(xml)

---

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

def xml = p6.resource.get("Invoice")
def bytesXls = p6.excel.fromXml(xml, false)