Setting up the Excel Import / Export

This article presents some sample code from this file:

Introduction

The WXM_EIEXCEL plugin allows:

• the integration of a simplified data export button to an Excel 2007 file in a datalist, not intended to be re-imported.

• performing exports or imports of data to or from an Excel 2007 file

Plugin Configuration

tempdir

Working directory: indicates the temp of the directory to store temporary files created by the plugin. If the path is relative, it is relative to the SAN directory.

defaultVariation

Default variation: name of the default variation for exporting resource files.

Possible values (case insensitive):

• No value here means the binary file is exported as is;

• NATIVE: means the binary file is exported as is;

• NONE: means no file will be exported;

• BIG (or thumbnailBig): means we export the big type variation;

• SMALL (or thumbnailSmall): means we export the small type variation;

• TINY (or thumbnailTiny): means we export the tiny type variation;

• FULL, (or REAL, or thumbnailReal): means we export the full type variation;

• WXML: means we export the WXML type variation

• PDF: means we export the PDF type variation

• ZIP: means we export the ZIP type variation

• HTML: means we export the HTML type variation

autosizeEnabled

Automatic column width calculation enabled: this option activates the autosize export parameter.

Warning: this feature requires the use of Java's graphical classes, which under certain conditions may be absent (headless mode), or which may cause memory overflows (perm_gen_space) under certain conditions. This is why this option is disabled by default.

Datalist export: Enabling the export button on object lists

The export button is set up using structure tags.

The button appears as soon as the exportxls tag is placed on an object.

Fields marked "in list", except technical fields (child, parent), are exported as soon as the button is clicked in the datalist. The export takes into account the context of the request and full text search.

It is possible to add fields, or to remove them, and to partially modify the way the value is exported.

Fields are exported in the order of position.

The export is always localized, relative to the locale of the surfer: if an i18n field is part of the exported fields, the corresponding master field is not localized if it is exported. If a master field is exported, and the localized field corresponding to this field and to the export locale is not exported, the master field is localized when exported.

The exported file is either :

• an xlsx file (Excel 2007);

• a zip file if resource files are also exported.

This is the list of the supported tags, with the reference below the table :

Object Tags

exportxls

To activate the appearance of the export to Excel button in a datalist, place the exportxls tag on the object.

exportxls_exclude

Excludes all fields by default, allowing only those specified by exportxls_include to be included.

importxls/new

Uses the creation callback.

importxls/append

Allows the addition of instances.

exportxls_locked

All locking options are disabled by default. This tag reactivates the default Excel document locking behavior.

Property Tags

exportxls_include

By default, a field is exported if and only if:

• the object it belongs to has the exportxls tag;

• the field is "on list";

• the field does not have an arbo_field tag, and is not a "technical" field (parent, child...);

• if the field is a localization field and its locale is the same as the surfer's.

To export a property that does not meet all these conditions, add the exportxls_include tag to the field.

exportxls_exclude

Regardless of the other options, if a field has this tag, it will not be exported.

exportxls_hidden

To hide a column, add the exportxls_hidden tag to the field.

Exporting 'child' and 'multiple child' type fields.

You can choose the presentation of the values of 'child' or 'multiple child' type fields:

• the exportxls_asName tag displays the name;

• the exportxls_asId tag displays the id;

• the exportxls_asIdName tag displays the id and the name, separated by a hyphen.

By default, the name is displayed. If 2 tags, or more, are present, priority is applied in the above order.

Binary export (file or image) and variations

When exporting a binary type field, the file is also exported. You can indicate the variation you want to export. It is not possible to export two different variations for the same propery. In the case of multiple tags, the exported variation will be undetermined among those requested.

To indicate the variation, add the exportxls_variation tag followed by a sub-tag, which characterizes the variation.

none

To not export any binary at all, apply the exportxls_variation/none tag.

native

To export the binary, apply the exportxls_variation/native tag (or none)

big

To export the thumbnailBig variation, apply the exportxls_variation/big or exportxls_variation/thumbnailBig tag

small

To export the thumbnailSmall variation, apply the exportxls_variation/small or exportxls_variation/thumbnailSmall tag

tiny

To export the thumbnailTiny variation, apply the exportxls_variation/tiny or exportxls_variation/thumbnailTiny tag

full

To export the thumbnailReal variation, apply the exportxls_variation/full or exportxls_variation/real or exportxls_variation/thumbnailReal tag

pdf

To export the pdf variation, apply the exportxls_variation/pdf tag

wxml

To export the wxml variation, apply the exportxls_variation/wxml tag

zip

To export the zip variation, apply the exportxls_variation/zip tag

html

To export the html variation, apply the exportxls_variation/html tag.

Tree Export

When an object has a field marked arbo_field and it is marked with the exportxls_tree label, its name is exported as a path and the field with the "arbo_field" label is never exported. The paths are the names from the root, separated by // (double-slash).

HTML Field Export

HTML fields are exported as plain text (removal of HTML tags). To export the field in its native HTML form, add the exportxls_norender label.

Triggering Export

By Plugin Invocation

The method is called exportTo. Several signatures are available:

Refer to the example JSP: EXAMPLE_EXPORTEXCEL/page/exampleExportByPlugin.jsp

By Forward

You can perform an export by forward on the jsp page/exportexcel.jsp which can be included to perform a parameterized export that generates a file that can be intended for import. To set the export, simply pass a parameter object via the request attribute exportxls_cfg, or by a parameter of the same name.

The value of the attribute can be

• a org.json.JSonObject object

• a java.lang.String object that contains a parsable JSon

• the path of a file, or a File, relative to the SAN, which contains a JSon

• the name of an object (the configuration will be automatically generated according to pre-established rules)

• a url whose flow is a JSon type content

Refere to the example JSP: EXAMPLE_EXPORTEXCEL/page/exampleExportByForward.jsp

By API

exportexcel.jar is an API that you can use directly in your plugin code.

The jar can be retrieved in the lib folder of the EXAMPLE_EXPORTEXCEL plugin.

Different examples of invocation can be seen in the fr.wedia.excel.exporter.examples.ExportExamples class.

JSon Configuration Object

The configuration object is a json object, whose properties are object names. It is therefore possible to export several objects in the same file.

For example :

{“rubrique”: null}

Global Configuration

By default, files will be exported in ZIP containing an XLSX and the associated binaries, or in XLSX if there are no binaries exported. The name of the file is always “wedia”.

You just need to add a $global property to the configuration json to modify these parameters.

Example :

{“$global”={ “fileType”: “zip”, “filename”:”wedia_activated” }, “activated”: null }

fileType

A file type among:

• zipx: the exported file is always a zip with an xlsx inside

• zip: the exported file is always a zip with an xls inside

• xlsx: the exported file is an xlsx, included in a zip if and only if resource files are also exported

• xls: the exported file is an xls, included in a zip if and only if resource files are also exported

fileName

The name of the exported file (be careful not to put forbidden characters (/, :, etc). Do not put the extension, except in case of double extension. For example, if you want to have wedia.assets.zip or wedia.assets.xlsx depending on whether you have resources or not, you will have to put:

“fileName”:”wedia.assets.zip” (regardless of the final extension, it will be replaced by the appropriate extension.

JSON Configuration Object for the Export of an Object

If no configuration object is associated with an object, a default configuration will be established. Otherwise, simply provide a json object, with the following fields, all optional.

{“rubrique”: {
    “where”: “pstatus=6”,
    “orderby”: “pname ASC”,
    “properties”: {
         “name”: null,
         “created”: null
    }
}}

where

The SQL Wedia clause to be used for selecting the instances to export. fulltext The full text clause to be used for selecting the instances to export. order The SQL sorting clause to be used for sorting the instances to export.

secure

A boolean indicating whether to apply security or not. properties The list of properties to export: the value of this property must be a JSON object, whose properties are field identifiers to export.

skipFieldException A boolean which, if set to true, allows to ignore the exceptions generated during the interpretation of the field configuration (for example, field not existing in the object). This parameter is useful during automatic configuration, to prevent the exported fields without explicit configuration from causing errors that prevent complete export (the columns are simply ignored in this case). locked The spreadsheet is locked (by default, true) password Lock password, or null to disable. By default, enabled.

freezePane

freezeColumnUntil

JSON Object Configuration of a Propery to Export

In the JSON object value of the properties field, the identifier of the field to export is indicated as a property name. It can be:

• a field name of the object, or an integer corresponding to its position

• a calculated field identifier

  • either in the form of an Excel formula (dynamic)

  • or in the form of a calculated field in Java (static)

The value of the corresponding property is a JSON object, or null for default configuration.

Properties that don't have an object for a field type are ignored.

Default values of unspecified properties depend on the position and type of the field, and the locale, as applicable.

hidden

This property allows you to hide a column

choice

This property indicates whether the validation/choice list is generated or not

childType

This property indicates how a child type field, or multiple child, is exported.

• id: the identifier is exported

• name: the name is exported

• id_name: the id and name are exported

label

This property allows you to indicate a personalized title to the column.

variation

This property allows you to indicate which variation you want for a binary.

Variation Codes

• big, or thumbnailBig

• small, or thumbnailSmall

• tiny, or thumbnailTiny

• full, real or thumbnailReal

• pdf

• zip

• wxml

• html

• native

• none

tree

This property allows you to activate the export in tree mode of a property name of an object having a field with the arbo_field label.

formula

This property allows you to indicate the Excel formula for a formula type field

computedField

This property allows you to indicate the calculated field identifier for a computed field type field

targetField

This property allows you to indicate the name of the destination field for the import of a formula or a calculated field

raw

This property allows you to indicate if the exported value should not be localized

labelLocked

This property allows you to lock the column title (by default, true)

locked

This property allows you to lock the content of each cell (by default, false)

ignoreIfNull

ignore

width

tag

matches

Styles

Styles may be applied with the following options:

• fillForegroundColor

• fillBackgroundColor

• fillPattern

• fontColor

• fontHeight

• fontStyle

• fontEscapement

• underlineType

• borderBottomColor

• borderBottom

• borderTopColor

• borderTop

• borderDiagonalColor

• borderDiagonal

• borderLeftColor

• borderLeft

• borderRightColor

• borderRight

Values

$global

auto

pw:

Matcher expressions

• field

• configuration

• choice

• tree

• formula

• computedField

• rendered

• locked

• ignore

• ignoreIfNull

• tags

• localized

• wrapped

• tagged

• listable

• editable

• hasLocale

• locales

• i18nFor

• i18nFields

• types

• attachment

• mandatory

• viewable

• i18n

Exporting a field with an Excel formula

Only English language for formulas is accepted. You can reference a column in the row of the concerned cell by the following pattern: ${identifier} where identifier is a column identifier.

Exporting a calculated field

(TBD)