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.

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)