Excel

Purpose

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

Methods

Binding name: p6.excel

---

Method: String toXml(byte[] excelBytes, SimpleDateFormat sdf)

Create an XML from spreadsheet bytes and a SimpleDateFormat.

---

Method: byte[] fromXml(String xml)

Create spreadsheet as byte array given XML.

---

Method: byte[] fromXml(String xml, boolean reevaluateFormula)

Create spreadsheet as byte array given XML.

Note

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.

Details

The format of the input XML document should be:

<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>

Examples

import java.text.SimpleDateFormat

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

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