Skip to main content
Skip table of contents

Microsoft Excel Writer

The Microsoft Excel Writer converts an XML document with a specific table structure to a Microsoft Excel file (in the XLS format Excel 97 - 2003 or in the XML-based OOXML format .xlsx ) or populates an existing Excel file (in the XLS format Excel 97 - 2003 or in the XML-based OOXML format .xlsx) with data from an XML document with a specific table structure.

Properties

Operation

Describes which operation the adapter performs.

Possible values:

  • Create Spreadsheet: Outputs an Excel document with a spreadsheet that has the Application/Vnd.ms-excel MIME type.

  • Fill Template: Populates the existing Excel template that is specified in the TemplateURL parameter and can have any number of spreadsheets with data.

Parameters

Adapter

Adapter main class (do not change!)

Possible values:

de.softproject.integration.adapter.excel.ExcelWriter: Main class (Default)

generateOOXML

Defines the Excel file format (Microsoft Excel 97 – 2003 or OOXML).

Possible values:

  • true: Generate an XML-based Excel document (Office Open XML; .xlsx)

  • false: Generate an 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 the generateOOXML parameter, the specified template must be in the same format; for example, an XLSX template must be used if the generateOOXML parameter has been set. The value of the TemplateUrl parameter can be defined by the templateUrl="." attribute In the input XML (see below).

Possible values:

  • URL with file:// protocol information (e.g. file:///%USER_COLLECTION%/Project/Template.xls)

  • URL with xstore:// protocol information (e.g. xstore://Project/Folder/Template.xlsx)

recalc

Defines whether formulas are recalculated and field values are updated before saving.

Possible values:

  • true: Recalculate formulas

  • false: Leave the 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:

Create Spreadsheet operation

For this operation, the adapter expects the following XML structure, which corresponds to the structure of (X)HTML tables. 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> creates a head cell. If multiple spreadsheets are to be created, the <table> elements must be enclosed by a root element with any name.

Notes:

  • The name of the table specified by the name attribute in the <table> element can be a maximum of 31 characters long.

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

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

    Example: Without the forceString="true" attribute, 008.07300 will be interpreted as the number 8.073 in the Excel document. With the forceString="true" attribute, the value is interpreted as a string and 008.07300 is written into the Excel document.

Example input for 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>

Fill Template operation

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

Note:

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

Example input for the Fill Template operation
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 each cell can be controlled by the following attributes:

Attribute

Description

formatNumber

Number format

Note:

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

Known formatting issue:

The possible values correspond to the formatting options available in Excel. If you select one of the number values number1, number2, number3, or number4, Excel displays a leading 0 in the affected table cells. This leading 0 does not change the actual numeric field value.

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 of day

  • percentage: Percent

  • fraction: Fraction

  • scientific: Scientific

  • text: Text

  • postalCode: Postal code

  • telephone: Telephone number

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 color specifications, e.g. red, blue, green etc.

  • RGB color code, e.g. 240, 80, 0

  • HEX color 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 underscore

Possible values:

  • NONE

  • DOUBLE

  • DOUBLE_ACCOUNTING

  • SINGLE

  • SINGLE_ACCOUNTING

cellColor

Fill color of the cell

Possible values:

  • CSS color specifications, e.g. red, blue, green etc.

  • RGB color code, e.g. 240, 80, 0

  • HEX color 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

Color of the cell frame

Possible values:

  • Color name, e.g. Red, Blue, Green etc.

  • RGB color code, e.g. 240, 80, 0

  • HEX color code, e.g. #124361

orientation

Text alignment

Possible values:

  • Number between 90 and -90

  • HORIZONTAL

  • VERTICAL

wrapText

This attribute is optional.

Allow automatic line wrapping in cells

Possible values:

  • true: Enable line wrapping

  • false: Disable line wrapping

autofit

This attribute is optional.

Automatically adjust column width

Notes on setting the attribute:

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

    • columnProperties: Groups the columns of a sheet.

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

    • column: Column within a sheet

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

      CODE 
      <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>

Possible values:

  • true: Enable automatic column width

  • false: Disable automatic column width

width

This attribute is optional.

Define fixed column width

Note:

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

Notes on setting the attribute:

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

    • columnProperties: Groups the columns of a sheet.

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

    • column: Column within a sheet

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

      CODE 
      <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>

Possible values:

Number between 0 and 255 If the defined number is greater than 255, 255 is set as the maximum column width.

height

This attribute is optional.

Define row height

Note:

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

Notes on setting the attribute:

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

    • columnProperties: Groups the columns of a sheet.

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

    • column: Column within a sheet

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

      CODE 
      <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>

Possible values:

Number between 0 and 409 If the defined number is greater than 409, 409 is set as the maximum row height.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close