Write Excel

Description

Write an Excel file using data described using JavaScript Object Notation (JSON).

Input

<WriteExcel>
<data/> <nodeVersionReference/> <nodeVersionReferenceType/> <nodeName/> <nodeDescription/>
<fileName/>
</WriteExcel>

The data to be written out is represented using JSON.

{
  "style": workbookStyle,
  "sheets": [
    {
      "name": sheetName,
      "style": sheetStyle,
      "columns": [
        {
          "style": columnStyle,
          "format": columnFormat,
          "width": columnWidth,
          "hidden": true|false
        }, ...
      ],
      "rows": [
        {
          "style": rowStyle,
          "hidden": true|false,
          "cells": [
            {
              "type": "string|number|boolean|date|time",
              "value": cellValue,
              "format": cellFormat,
              "style": cellStyle
            }
          ]
        }, ...
      ]
    }, ...
  ]
}

The structure of the JSON reflects the structure of an Excel workbook: a workbook is contains a set of sheets, each sheet contains a list of columns and a list of rows, each row contains a list of cells, cells are associated with columns by position.

All properties are optional.

The style properties provide styling for the sheet using CSS-like strings. The following are supported:

font-size Specified in em, e.g. "1.2em". (In Excel, 1em is 11pt.)
font-family Specified as a single phrase, e.g. "Courier New".
color Specified as a six-digit hexadecimal with a preceding #, e.g. #00FF00.
background-color Specified as a six-digit hexadecimal with a preceding #, e.g. #00FF00.
font-weight Only "bold" is supported.
font-style Only "italic" is supported.
text-align Specified as "left", "right" or "center".
vertical-align Specified as "top", "middle" or "bottom".
border-width Specified in pixels as one or four values, e.g. "3px" or "1px 0px 0px 0px".

Will set a solid black border of an appropriate width (1px = narrow border, 2px = medium border, 3px or more = wide border).

white-space Set to "pre" or "pre-wrap". "pre" is the default, and means data is not wrapped. Set to "pre-wrap" to wrap text data. Other white-space values are not supported.

These are not true CSS styles, but a subset of Excel formatting options presented as CSS properties. If the properties are not given exactly as specified they will not work, even if they are valid CSS and even if they do represent possible styling options within Excel.

Styles are applied to cells, using the other styles as defaults. The order of precedence, lowest precedence first, is:

  • workbookStyle
  • sheetStyle
  • columnStyle
  • rowStyle
  • cellStyle

format is used to format dates and times using Excel format strings. The format from the column is used as a default for the cells in that column.

columnWidth can be set to "auto" or a width in ems, e.g. "12em". The default column width is 8em.

Set hidden to true to hide a column or a row.

type indicates the type of the data, and can be string, number, boolean, date or timestamp. If type is not given, it will default to string, number of boolean depending on the data type of the value. Dates and timestamp are passed as strings using ISO 8601 format, i.e. dates are yyyy-mm-dd, timestamps yyyy-mm-ddThh:mm:ss.sss. Dates and timestamps must be given a type or they will be interpreted as strings.

value contains the value for the cell. If it does not conform to the given type, it will be coerced to that type. For booleans, this follows the normal JavaScript rules, and any non-zero length string or non-zero number is treated as true. So {"type":"boolean","value":"false"} would produce a value of true.

Details of the Excel file are written to the node identified by nodeVersionReference. If this does not exist, it will be created using the type identifier by nodeVersionReferenceType, which defaults to system.FILE_TYPE. Optionally, a name, description and fileName can be set in the output. nodeVersionReference can use a .$auto local reference, to create a new node.

Output

<WriteExcel>
<errorNumber>0</errorNumber> <nodeVersionReference/>
</WriteExcel>

Errors

101 - Not authorised

102 - Not found

103 - Parameter error

Class

com.metrici.xerula.WriteExcelService