Setting up the Excel Import / Export

This article presents some sample code from this file:

Open

Open the table of contents of the samples via this URL: _plugins/LABS_EXAMPLE_EXPORTEXCEL/page/examples.jspz

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

The Excel Import / Export can be used as a translation tool, to export and reimport strings that need to be exported by an external tool. you may use the dedicated plugin here:

https://drive.google.com/file/d/1PBjiqItJ2jsI4v9BXNX1Eq8PQoo03l8A/view

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

exportxls_exclude

importxls/new

importxls/append

exportxls_locked

Property Tags

exportxls_include

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

exportxls_exclude

exportxls_hidden

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

native

big

small

tiny

full

pdf

wxml

zip

html

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 :

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.

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)

Excel Automatic Import

Documents exported by the button are not normally intended to be imported. However, under certain conditions, and with reduced performance, this is possible.

Documents exported by the export feature can be imported.

By request

The import URL is /_plugins/WXM_EIEXCEL/page/importexcel.jspz

Parameters:

• reporting

• dryRun

• failfast/diehard

• append

• create => use of the default callback

Attributes (use by include)

• importxls_reporting

• importxls_callback

Example

• Example JSP: EXAMPLE_EXPORTEXCEL/page/exampleImportByRequest.jsp

By tag:

importxls: adds a button that displays a standard import page that uses the standard system (configuration comes from the file)

importxls_append: allows the addition of an instance

By API

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

The jar is retrievable in the lib folder of the plugin EXAMPLE_EXPORTEXCEL.

Different examples of invocation are visible in the class fr.wedia.excel.importer.examples.ImportExamples.

Callback

General operation

Creation of the callback

Context

The properties are:

• LOCALE: the locale (java.util.Locale)

• FILENAME: the name of the file (java.lang.String)

• FILE: the file (java.io.File)

• APPEND_MODE: the import mode (Boolean)

• OBJECT_NAME: the object name (java.lang.String)

• FIELD_NAME: the object field name (java.lang.String)

• CELL_VALUE: the value read in an Excel cell (java.lang.Object)

• VALUE: a value (java.lang.Object)

• FIELD: a field (wsnoheto.engine.IObjectField)

• ACTION: the localized name of a workflow action (java.lang.String) input, output can be an action (noheto.workflow.IAction)

• OBJECT: an object (wsnoheto.engine.IObjectReadOnly)

• OBJECT_ID: an object identifier (java.lang.String)

Callback Codes

Return codes

Reports

Simulate an import without modifying the database

This mode allows you to perform the import without making any changes to the database. When combined with a report, it allows you to see potential results and conflicts and debug the callback without modifying object data in the database or creating unnecessary objects. The simulation does not operate in fail-fast mode: in case of exception, the exception is ignored, and the import tries to go to the end, except for insurmountable exceptions (unreadable file for example).

Fail-fast / diehard mode

During the simulation, by default, as soon as the first exception occurs, the process is stopped (fail-fast mode). It is possible to force the simulation to go to the end by using diehard mode.

Import a document that has not been exported

During export, tags are added to the document to allow an import. It is possible to manually tag a document to take advantage of the automatic import.

It is useful to simulate the import to check that the tagging is complete before attempting a definitive import.

The tagging uses Excel names.

Metadata

Global tagging uses a special spreadsheet that must be called __metadata.

The first cell at the top left must be called __DOCUMENT.

The first cell below __DOCUMENT contains the import locale. If the cell is empty, the surfer's locale is used.

The second column contains the version (1.0) (or nothing for version 1.0).

Spreadsheet tagging (object)

For a spreadsheet to target an object,

• either the sheet is tagged: the header line is named by the tag _objectname followed by the object's name (for example __objectname_damelement for the object damelement. The name of the sheet can then be anything.

  • if no object in the database corresponds to the tag, the callback is called with the code UNKNOWN_OBJECT (5)

• during import, if the object is not determinable by tag, the callback is called:

  • if no object tag is found, first the code NO_OBJECT_NAMES (4)

    • then if there are sheets that are not tagged, the code REMAINING_SHEETS (7)

    • then for each untagged sheet, the sheet name is used as the object name

    • if no object corresponds, we call the callback with the code UNKNOWN_OBJECT_IN_SHEET (6).

Column tagging (property)

For a column to target a field:

• either the column title is tagged: the cell is named by the tag _fieldname followed by the field name

  • if the field does not exist the callback is called with the code UNKNOWN_FIELD (10).

• during import, if the field corresponding to a column is not determinable, the callback is called

  • if no field tag is found, first the code NO_FIELD_NAMES (11)

  • then if there are columns that are not tagged, the code REMAINING_COLUMNS (8)

  • then for each column, the value in the cell is used as the field name

    • if no field corresponds, we call the callback with the code UNKNOWN_FIELD_IN_SHEET (12).

Import functionality by template

Problems during import, errors, conflicts, disputes: understanding how we report “issues”.

Introduction

When a problem occurs during the import, an exception is raised. It is possible to intercept this exception in order to indicate to the import process an alternative to solve the problem. This allows:

• to stop the process silently (without exception)

• to ignore objects, object instances, fields, etc.

• to propose replacement values in case of erroneous values, or to activate auto-correction procedures

• etc.

Every problem is characterized by an issue. An issue is:

• an identification code

• an exception

• a context that allows knowing and/or modifying certain information

The resolution of the issues is done by a callback method which will be invoked for any issue. The return value of the callback determines how the process will handle the conflict:

• the value -4 (EXCEPTION) indicates that the planned exception is raised. The process then stops, except in diehard simulation mode, where the process will continue as much as it can;

• the value -3 (STOP) indicates that the process is stopped without raising the exception

• the value 4 (SKIP_ROW) indicates that the import of the current object instance is cancelled

• the value 5 (SKIP_SHEET) indicates that the import of the current object (of all instances of the object) is cancelled

• the value 3 (SKIP_COLUMN) indicates that the import of the current field is cancelled

• the value 2 (CHANGE_VALUE) indicates that a replacement value is proposed for a problematic value, or a lack of mandatory value, or any hindrance. If the proposed value continues to cause trouble, the original exception will be raised.

• the value 1 (DEFAULT) allows to select a default automatic operation

• any other value corresponds to a default behavior depending on the issue. Generally, it is EXCEPTION or DEFAULT.

List of issues

NO_METADATA_LOCALE

Use the API in your own plug-in