Altova MapForce 2024 Enterprise Edition

Navigation: Structural Components > Microsoft OOXML Excel 2007+

About the Excel 2007+ Component

When you add an Excel 2007+ file to the mapping area without specifying a sample file (see Adding Microsoft Excel Files as Mapping Components ), MapForce creates a default component which includes three worksheets (illustrated below). If you provide a sample file, MapForce reads the sample file and creates only the required worksheets.

Default Excel component

The structure of the Excel 2007+ component in MapForce reflects the structure of data in the Excel workbook, with the difference that in MapForce it is expressed in a tree structure which makes it possible to map each individual cell.

Before you can connect the Excel 2007+ component to any other component type, you will need to instruct MapForce precisely what are the columns and rows to be used in the mapping. Unlike other MapForce components such as XML or JSON, Excel 2007+ files do not have an explicit schema that MapForce can use to infer the structure of your data. Instead, MapForce provides you with settings from where you can define:

•What data precisely (such as worksheets, named ranges and tables, columns, rows) must be selected from your workbook (if you are reading from a workbook);

•To which worksheets, columns, and rows must MapForce write data (if you are writing to a workbook).

You can also configure the component to read from multiple locations within a workbook, or write to multiple locations, in the same mapping operation.

The required component configuration settings are available directly on the component. Use the following table to get started.

Component Item

Icon

Description

Workbook

Represents an Excel workbook.

Worksheet

Represents an Excel worksheet. The AddRemove_Selections button displayed next to the first worksheet lets you specify worksheet-related settings, see Adding and Removing Worksheets.

Rows

Represents a range of Excel rows. You can add multiple ranges of rows within a worksheet, see Adding and Removing Row Ranges. This enables you read from (or write to) multiple ranges of cells in the same mapping operation.

For each defined range, you can specify individual data selection options. For example, one range may begin at row 1 and include all columns of that row, while another range may begin at row 3, and consist of a dynamic number of rows, depending on the amount of data in the source Excel file.

To help you see all range settings at a glance, the component provides visual clues about them, as shown below.

Rows n	Indicates a range which begins at row n.
Row n	Indicates a single-line range of row n.
Rows prev+n	Indicates a range which begins n rows after the previous range.
Rows n(h)	Indicates a range which begins at row n, and the first row is designated as a header row.
n=n	Indicates a range which consists of exactly n rows.
n=dyn	Indicates a dynamic range. Dynamic ranges may have an unlimited number of rows.

The AddRemove_Selections button displayed next to each row range lets you specify advanced data selection settings for that range.

Cells

Represents all the cells (columns) of a particular row. This item appears if the component is configured to show a single cell for all columns (this is the default MapForce behavior).

Alternatively, you can configure a component to display each column separately, in which case it would look as shown in the following sample.

Change Selection

The AddRemove_Selections button displayed next to each worksheet or row lets you specify settings meaningful in that context. Using this button, you can modify the mapping structure of the Excel component as required, see Excel 2007+ Component Settings.

For example, if you are reading data from an Excel file, you can specify the worksheet, row and column from where MapForce should read data. If you are writing to an Excel file, you can specify the worksheet, row and column to which MapForce should write data.