Microsoft Excel Writer

The adapter converts an XML document with a specified table structure into a Microsoft Excel file (in .xls format Excel 97 – 2003 or XML-based OOXML format .xlsx)or fills an existing Excel file (in.xls format Excel 97 – 2003 or XML-based OOXML format .xlsx) with data from an XML document with a specified table structure.

Properties

Operation

Determines which operation the adapter executes

Possible values:

Create Spreadsheet: Output an Excel document with a spreadsheet that has the MIME type Application/Vnd.ms-excel.
Fill Template: Fill an existing Excel template, which is specified in the TemplateURL parameter and can have any number of spreadsheets, with data.

Parameter

Adapter	Main class of the adapter (do not change!) Possible values: en.softproject.integration.adapter.excel.ExcelWriter: Main class (default)
generateOOXML	Determine Excel file format (Microsoft Excel 97 - 2003 or OOXML) Possible values: `true`: Create XML-based Excel document (Office Open XML; .xlsx) `false`: Create Excel document in Microsoft Excel 97 - 2003 (.xls) format (default)
TemplateUrl	URL to the Excel template (if the Operation property is set to `Fill Template`). Depending on the file format selected in parameter `generateOOXML`, the specified template must be in the same format; e.g. an `.xlsx` template must be used if parameter `generateOOXML` is set. The value of the parameter `TemplateUrl` can be overwritten by the attribute `templateUrl="..."` in the input XML (see below). Possible values: URL with file:// protocol specification (e.g. `file:///%USER_COLLECTION%/Project/Template.xls`) URL with xstore:// protocol specification (e.g. `xstore://Project/Folder/Template.xlsx`)
recalc	Recalculate formulas and update field values before saving the document Possible values: `true`: Recalculate formulas `false:` Leave values of fields with formulas unchanged (default)

Status values

`1`	The operation was executed successfully.
`-1`	The operation failed due to a technical error.

Input

Depending on the selected operation, the adapter expects a specific input structure:

Operation `Create Spreadsheet`

For this operation, the adapter expects the following XML structure, which corresponds to the structure of (X)HTML tables. Here, the <table> element creates a table sheet, each <tr> element creates a new table row and each <td> element creates a new cell or <th> a header cell. If several table sheets are to be created, the <table> elements must be enclosed by a root element with any name.

The table name which is specified using the name attribute in the <table> element is restricted to 31 characters.

The formatting of the individual cells can be controlled via various attributes. These are explained in the section Possible Attributes.

Example input for the Create Spreadsheet operation

XML

<?xml version="1.0" encoding="UTF-8"?>
<root>
 <table name="Name of the first spreadsheet">
   <tr autofit="true" height="10">
     <th fontHeight="12" forceString="true" autofit="true" bold="true">Header1</th>
     <th fontHeight="12" forceString="true" autofit="true" bold="true">Header2</th>
   </tr>
   <tr>
     <td>Value1</td>
     <td>Value11</td>
   </tr>
   <tr>
     <td>Value2</td>
     <td>Value22</td>
   </tr>
   <tr>
     <td>Value3</td>
     <td>Value33</td>
   </tr>
 </table>
</root>

With the attribute forceString="true", the content is not interpreted as a number or date, but written directly into the Excel document.

Example: 008.07300 is written into the Excel document as the number 8.073 without the attribute forceString="true", with the attribute forceString="true" it is written into the Excel document as the string 008.07300.

Operation `Fill Template`

For this operation, the adapter expects the following XML structure. In the optional attribute templateUrl="... " you can specify a valid URL to a template file, e.g. templateUrl="file:///C:/Template.xlsx". This specification overwrites the value of the adapter parameter TemplateURL. Each <cell> element corresponds to a cell of the table. For row and, if applicable, column numbers, the count starts at 0.

The formatting of the individual cells can be controlled via various attributes. These are explained in the section Possible Attributes.

Example input for operation FillTemplate

CODE

<TemplateFill templateUrl="URL to template file">
	<cell  sheet = "Name of the spreadsheet" 
			row = "Row number" 
			col = "Column ID " 
			value = "Cell content" 
			formatNumber = "Format number"
			wrapText="true"/>
</TemplateFill>

Possible attributes

The formatting of the individual cells can be controlled via the following attributes:

Attribut Description

formatNumber

Number format

Possible values:

number1: Number 1
number2: Number 2
number3: Number 3
number4: Number 4
currency1: Currency 1
currency2: Currency 2
currency3: Currency 3
currency4: Currency 4
accounting: Accounting
date: Date
time: Time
percentage: Percentage
Fraktion: Fracture
scientific: Science
text: Text
postalCode: Postcode
telephone: Telephone number

If required, user-defined number formats can also be stored

fontName Font, e.g. Calibri

horizontalAlignment

Horizontal alignment of the text within the cell

Possible values:

CENTER
CENTER_SELECTION
DISTRIBUTED
FILL
GENERAL
JUSTIFY
LEFT
RIGHT

verticalAlignment

Vertical alignment of the text within the cell

Possible values:

CENTER
BOTTOM
TOP
DISTRIBUTED
JUSTIFY

fontHeight Font Size

fontColor

Font color

Possible values:

CSS colour specifications, e.g. Red, Blue, Green etc.
RGB colour code, e.g. 240, 80, 0
HEX colour code, e.g. #124361

bold

Bold font

Possible values:

true: bold font
false: normal font

italic

Italic font

Possible values:

true: italic font
false: normal font

underline

Type of underline

Possible values:

NONE
DOUBLE
DOUBLE_ACCOUNTING
SINGLE
SINGLE_ACCOUNTING

cellColor

Fill colour of the cell

Possible values:

CSS colour specifications, e.g. Red, Blue, Green etc.
RGB colour code, e.g. 240, 80, 0
HEX colour code, e.g. #124361

border
borderLeft
borderRight
borderBottom

Type of cell frame

Possible values:

DASH_DOT
DASH_DOT_DOT
DASHED
DOTTED
DOUBLE
HAIR
MEDIUM
MEDIUM_DASH_DOT
MEDIUM_DOT_DOT
MEDIUM_DASHED
SLANTED_DASH_DOT
THICK
THIN
NONE

borderColor
BorderColorLeft
borderColorRight
borderColorBottom
borderColorTop

Cell frame colour

Possible values:

Colour name, e.g. Red, Blue, Green etc.
RGB colour code, e.g. 240, 80, 0
HEX colour code, e.g. #124361

orientation

Text alignment

Possible values:

Number between 90 and -90
HORIZONTAL
VERTICAL

wrapText

Optional attribute: Allow automatic line break in cells

Possible values:

true: Enable line break
false: Deactivate line break

autofit

Optional attribute: Automatically adjust column width

Possible values:

true: Activate automatic column width
false: Disable automatic column width

Set the attribute

Please note:

For the Create Spreadsheet operation, the attribute must be set within the first line in the <td> or <th> element.

For the Fill Template operation, a new columnProperties element must first be inserted, in which the configuration of the individual columns - each in a column node - is carried out.

columnProperties

Groups the columns of a sheet.

Possible values: sheet: Name of the Excel sheet for which the configuration is made.

column

Column within a sheet

Possible values: id: Index of the column.
The first index is 0. If a negative value is given, an error message is displayed.

XML

<columnProperties sheet="Week">
	<column id="1" autofit="true" width="256" />
    <column id="11" width="500" />
    <column id="9" width="255" />
    <column id="7" width="22" />
    <column id="22" autofit="true" />
</columnProperties>

width

Optional attribute: Define fixed column width

If the autofit attribute is set, the width attribute is ignored.

Possible values: Number between 0 and 255. If the specified number is greater than 255, 255 is set as the maximum column width.

Set the attribute

Please note:

For the Create Spreadsheet operation, the attribute must be set within the first line in the <td> or <th> element.

For the Fill Template operation, a new columnProperties element must first be inserted, in which the configuration of the individual columns - each in a column node - is done.

columnProperties

Groups the columns of a sheet.

Possible values: sheet: Name of the Excel sheet for which the configuration is made.

column

Column within a sheet

Possible values: id: Index of the column.
The first index is 0. If a negative value is given, an error message is displayed.

XML

<columnProperties sheet="Week">
	<column id="1" autofit="true" width="256" />
    <column id="11" width="500" />
    <column id="9" width="255" />
    <column id="7" width="22" />
    <column id="22" autofit="true" />
</columnProperties>

height

Optional attribute: Define row height

Excel applies the automatic adjustment of the height with the autofit attribute only if a fixed row height was previously defined via the height attribute. If the autofit attribute is set for the row, the heigth attribute is ignored. In addition, the automatic line height is also controlled via the attribute wrapText.

Possible values: number between 0 and 409. If the specified number is greater than 409, 409 is set as the maximum line height.

Set the attribute

Please note:

For the Create Spreadsheet operation, the attribute must be set within the <tr> element.

For the Fill Template operation, a new rowProperties element must first be inserted in which the configuration of the individual rows - each in a row node - is carried out.

rowProperties

Groups the rows of a sheet.

Possible values: sheet: Name of the Excel sheet for which the configuration is made.

row

Line within a sheet

Possible values: id: Index of the line.
The first index is 0. If a negative value is given, an error message is displayed.

XML

<rowProperties sheet="Week">
   <row id ="7" height="50"/>
   <row id="6" height="409"/>
   <row id="8" autofit="true" height="250"/>
</rowProperties>

Properties

Parameter

Status values

Input

Operation Create Spreadsheet

Operation Fill Template

Possible attributes

Operation `Create Spreadsheet`

Operation `Fill Template`