This is technical documentation for system integrators
This article presents some sample code from this file:
Open the table of contents of the samples via this URL: _plugins/LABS_EXAMPLE_EXPORTEXCEL/page/examples.jspz
Latest JAR File for using the Excel Import / Export as a translation tool:
https://drive.google.com/file/d/1PBjiqItJ2jsI4v9BXNX1Eq8PQoo03l8A/view
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 :
Type | Tags |
object tag | exportxls |
object tag | exportxls_exclude |
object tag | importxls/new |
object tag | importxls/append |
object tag | exportxls_locked |
property tag | exportxls_hidden |
property tag | exportxls_asId |
property tag | exportxls_asName |
property tag | exportxls_asIdName |
property tag | exportxls_tree |
property tag | exportxls_norender |
property tag | exportxls_include |
property tag | exportxls_exclude |
property tag | exportxls_variation/* |
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.
A "password" type field is never exported, regardless of the settings.
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
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)
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
During import, it is possible to respond to import errors and conflicts through a callback. This is a reflection implementation of an interface.
Creation of the callback
By API:
IImportCallback callback = new IImportCallback() {
public int callback(int code, CallbackContext context, Throwable exception) throws Throwable {
return IImportCallback.EXCEPTION;
}
public boolean canImport(CTSurfer surfer, String objectName) {
return !”activated”.equalsIgnoreCase(objectName);
}
public boolean canImport(CTSurfer surfer, String objectName, String fieldName) {
if ( “status”.equalsIgnoreCase(fieldName) ) {
return IMPORT_FIELD_IGNORE;
}
else if ( “parent”.equalsIgnoreCase(fieldName) ) {
return IMPORT_FIELD_FALSE; // interdit
}
return IMPORT_FIELD_TRUE;
}
}
By reflection, in a JSP for example:
final InvocationHandler callback = new InvocationHandler() {
@SuppressWarnings("unchecked")
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if ( “callback”.equals(method.getName() ) {
return callback((Integer)args[0],(Map<String, Object>)args[2], (Throwable)args[3]);
}
else if ( “canImport”.equals(method.getName()) {
if ( args.length==3 ) {
return canImport((CTSurfer)args[0], (String)args[1], (String)args[2]);
}
return canImport((CTSurfer)args[0], (String)args[1]);
}
}
protected int callback(int code, Map<String, Object> context, Throwable exception) {
return -4; // EXCEPTION
}
protected boolean canImport(CTSurfer surfer, String objectName) {
return !”activated”.equalsIgnoreCase(objectName); // allows import of all objects except “activated”
}
protected int canImport(CTSurfer surfer, String objectName, String fieldName) {
return 1; // all allowed
}
}
Context
The context is a set of properties (in the form of Map<String, Object>) containing information related to the context of the code.
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
EXCEPTION: -4
throws an exception (and thus stops the import as it is, without rollback)
STOP: -3
immediately stops the import as it is (without rollback)
SKIP_ROW: 4
ignores the spreadsheet row, or the object instance, or action
SKIP_SHEET: 5
ignores the spreadsheet, or object, or action
SKIP_COLUMN: 3
ignores the column, or object, or action
DEFAULT: 1
attempts a default action when possible
CHANGE_VALUE = 2
indicates a replacement value (via the context)
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