Excel to XML component

The Excel to XML component provides a way to select specific ranges of a Excel document and aggregate them together in a single XML body. Each Excel range can be specified by a single rule

Configuration

Each rule has the following configuration options:

• XML node name
• Worksheet
• Cell range
• Transpose

XML Node name

Description

Each rule starts with a name. The data of the specified cell range will be wrapped in an XML node of the specified name.

Remarks

• Since it will be used in an XML element the following rules apply:
  • Names can't start with a digit
  • Names can't start with special characters like hypens or periods
  • Names can't start with any variation of 'xml'
  • Names can't contain special characters other than the period, hyphen, underscore, and colon
• The name has to be unique.

Worksheet

Description

Excel documents can contain mutliple worksheets. You have to specify the exact name of the worksheet from which the date of the cell range will be taken.

Cell range

Description

The cell range from which the data will be taken and wrapped into the specified XML node name.

Remarks

• You have to use contiguous cell series, for example B2:B11. Single cells like B2;B4;B6;B8;B10 are not supported.

Transpose

Description

By enabling this option you can switch data from columns to rows, or vice versa.

Rule Actions

Every rule has some actions:

• Add a new rule
• Duplicate a rule
• Remove a rule

Add a new rule

The new rule is added below the current rule.

Duplicate a rule

The duplicated rule is added below the current rule.

Remove a rule

Example

This Excel Document:

Excel Doc

Combined with this Excel to XML component configuration:

Will yield this XML:

<?xml version="1.0" encoding="UTF-8"?>
<Excel>
  <Monday>
    <excelRow>
      <cell colIndex="A" rowIndex="0">Monday</cell>
      <cell colIndex="B" rowIndex="0">1</cell>
      <cell colIndex="C" rowIndex="0">8273</cell>
    </excelRow>
  </Monday>
  <Tuesday>
    <excelRow>
      <cell colIndex="A" rowIndex="1">Tuesday</cell>
      <cell colIndex="B" rowIndex="1">2</cell>
      <cell colIndex="C" rowIndex="1">239</cell>
    </excelRow>
  </Tuesday>
  <Wednesday>
    <excelRow>
      <cell colIndex="A" rowIndex="2">Wednesday</cell>
      <cell colIndex="B" rowIndex="2">3</cell>
      <cell colIndex="C" rowIndex="2">23</cell>
    </excelRow>
  </Wednesday>
  <Thursday>
    <excelRow>
      <cell colIndex="A" rowIndex="3">Thursday</cell>
      <cell colIndex="B" rowIndex="3">4</cell>
      <cell colIndex="C" rowIndex="3">43</cell>
    </excelRow>
  </Thursday>
  <Friday>
    <excelRow>
      <cell colIndex="A" rowIndex="4">Friday</cell>
    </excelRow>
    <excelRow>
      <cell colIndex="B" rowIndex="4">5</cell>
    </excelRow>
    <excelRow>
      <cell colIndex="C" rowIndex="4">51</cell>
    </excelRow>
  </Friday>
  <Saturday>
    <excelRow>
      <cell colIndex="A" rowIndex="5">Saturday</cell>
      <cell colIndex="B" rowIndex="5">6</cell>
      <cell colIndex="C" rowIndex="5">125</cell>
    </excelRow>
  </Saturday>
  <Sunday>
    <excelRow>
      <cell colIndex="A" rowIndex="6">Sunday</cell>
      <cell colIndex="B" rowIndex="6">7</cell>
      <cell colIndex="C" rowIndex="6">23</cell>
    </excelRow>
  </Sunday>
</Excel>

Remarks

• The symbols <, >, & and “ in Excel data will be replaced by <, >, " and ' respectively.
• Each formula will be transformed into a value. Formulas that result in a number will by default have one decimal, but if there are more decimals you will get the rest as well, for example 1÷3 will result in 0.333333333333333.
• In order to handle huge XML-output it could be necessary to set a flow in synchronous mode. For example: a worksheet with 200.000 rows and 12 colums can create an XML output of 150 MB.