XLS Adapter

Usage

The XLS Format Adapter generates XML formats from Microsoft Excel files, and vice versa.

  • XLS-XML

    You can import files with the format XLS of the Excel versions 95, 97, 2000, 2003, 2007, and 2010. You can handle an Excel file’s individual worksheets or all of its worksheets. Excel-specific display formats (such as font and font size, colors, orientation, etc.) can also be represented in the XML structure. For cell formatting, fonts, dates, and numbers are supported. Formulas can also be applied. However, only arithmetic formulas are supported. String functions are not supported. The last calculated value from any formula type can be applied.

    Graphics can neither be generated nor imported.

    The output XML has a three-dimensional structure corresponding to the original Excel file format. The three dimensions correspond to the worksheet, row and column in the Excel file.

  • XML > XLS

    Format

    When converting XML messages into the XLS forma, the XLS Adapter expects input messages in XMLSS format and outputs messages in Excel 97 format.

    Named colors from the Microsoft XML spreadsheet format (MXLSS) are not supported. Only certain colors are supported. All other colors are converted into a color contained in the list of supported colors that is most similar to the desired color, e.g.: #CD5C5C is replaced by #993366.

AutoFilter

The XML-XLS Adapter supports AutoFilter in the generated XLS files.

For this purpose, the cell range to which the AutoFilter is to be applied must be specified in the XML file. The attribute ref in the AutoFilter tag is used for this purpose.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<Workbook>
  <Styles>
    ...
  </Styles>
  <Worksheet Name="Worksheet_1" Protected="0">
    <Table DefaultRowHeight="12.8" DefaultColumnWidth="48">
      ...
    </Table>
    <AutoFilter ref="A1:C4">
    </AutoFilter>
  </Worksheet>
</Workbook>

Explanation of the cell range specification:

<!-- To Enable filtering for a range of cells, add AutoFilter block with cell range information inside "ref" attribute -->
<AutoFilter ref="A1:C4">
  <!-- Here “A1” refers to the first cell in the worksheet, where column A and row 1 intersect -->
  <!-- Here “C4” refers to the last cell at the intersection of column C and row 4 -->
</AutoFilter>

The mapping template for the XLS Adapter has been updated accordingly (repository folder Global > System > Mapping Templates > XLS Adapter). If necessary, an update patch for the global repository folder is required to get the latest template and example.

This feature is supported in INUBIT only for Apache POI >= 3.16.

Creating Input Messages for the XML-XLS Adapter

The XML-XLS Adapter needs input messages in the XMLSS format. To generate this format, use an XSLT Converter and a mapping template provided by the INUBIT software.

Proceed as follows

  1. Create an XSLT Converter.

  2. In the XML target file area, click the module guide 929 1 button and select Open from > Repository. The Repository Explorer is displayed.

  3. Open the Global > System > Mapping Templates > XLS Adapter directory and select the file template.xml.

  4. Click OK. The explorer closes, and the template is displayed.

  5. Create a mapping to generate a format.

    Refer to XSLT Command Wizard in the XSLT Converter

XLS > XML: Change Time Zone for Converting Values with Type Date

When converting Microsoft Excel files to XML you can explicitly define the time zone which is to be used for converting values with the type date.

In order to guarantee that the summer-time is ignored so that there is a value for 2:00 a.m. in time series extending the day of clock change, you must specify the time zone relatively to Greenwich Mean Time, for example as GMT+1.

Proceed as follows

  1. Add the module property timeZone to your module.

    Refer to Adding Module Properties

  2. Enter the time zone abbreviation of the dates contained in your Excel file as value of the module property.

Dialog Descriptions in the XLS Adapter

Dialog XLS-XML Adapter Properties

This dialog offers the following options:

Choose XLS processor

  • Processor

    Select the XLS or XLSX processor suitable for your input messages.

Choose worksheets

  • Worksheet

    • All sheets

      Select this option to import all worksheets in an Excel file.

    • Selected sheet

      Select this option to import a selected worksheet. Click Select to load an Excel file. The worksheets in the file are shown in the selection box.

Rows and columns

  • Include empty rows

    Select this checkbox if you want empty rows located above data-filled rows to be imported into the XML format as empty XML tags. Otherwise, this type of empty row will be ignored. Empty rows located at the end of a worksheet are always ignored.

  • Include empty cells

    Select this checkbox if you want empty cells located in front of data-filled cells to be imported into the XML format as empty XML tags. Otherwise, this type of empty cell will be ignored. Empty cells located at the end of a row are always ignored.

  • Evaluate formula (available only as of POI 3.1 processor)

    When activating this checkbox, formulas are executed, and the result is added as Value attribute to the Cell tag in the XML output message.

  • Output only values based on current filter selection

    When activating this checkbox, the data from the selected column and row in the Excel sheet will be extracted based on the current filter selection. If the filter selection is set to "All" then all values will be included in the output. Similarly, if no filter selection is chosen, all values will be included in the output.

  • Skip rows from top

    Number of rows to be ignored.

  • Skip columns from left

    Number of columns to be ignored in each row.

XML spreadsheet (XMLSS) configuration

  • Number representation

    • Exponential format

      Display numbers in an exponential format

    • Decimal format

      If you select decimal format, you must next define the decimal separator.

  • Decimal separator

    Select a period or a comma.

  • Minimum fractional digits

    Number of decimal places.

  • Date format

    Select a format for the date.

  • With XMLSS namespaces

    • When selected: XML is generated with namespaces.

    • When not selected: XML is generated without namespaces.

  • Convert layout information to XML

    When the checkbox is selected, the information regarding colors, lines and font weight, etc., is imported the XML file. Otherwise, this information is stripped. Importing layout information results in a significant increase in the size of the XML file.

  • Encoding

    The standard character encoding in Excel is UTF-8. In addition, the following character sets can be selected: US-ASCII, UTF-8, UTF-16BE, UTF-16LE, and UTF-16. These six character sets are pre-selected because they are part of the Java Platform Specification and therefore always supported. The character-set selection box can also be modified. You can enter any other character set into this box.

Dialog XML-XSL Adapter Properties in the XLS Adapter

This dialog offers the following options:

Choose XLS processor

Select the XLS or XLSX processor suitable for your input messages.

The following XML Schema contains all elements and attributes which are supported referring to POI 37: Global/System/Mapping Templates/XLS Adapter/POI37.xsd

XML spreadsheet (XMLSS) configuration

  • Date format

    You can select one of the date formats.

Action if no document exists

Specify what the adapter should do if the XML file is empty:

  • Create XLS document with empty sheet

  • Throw error

Press Esc to exit full screen
Modal image placeholder
Press Esc to exit full screen