About the Excel 2007+ Component

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > 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.

nofile_default_excel_component

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

disp_excel_workbook

Represents an Excel workbook.

Worksheet

disp_excel_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

disp_excel_row

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

disp_excel_cell

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.

ex-sepItem2

Change Selection

AddRemove_Selections

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.


© 2019 Altova GmbH