Excel Mapping

Excel Mapping

MapForce includes support for mapping data based on the spreadsheet format for Microsoft^® Excel 2007 and later versions, under the Office Open XML (OOXML) file format specification. MapForce supports Excel spreadsheets as mapping sources or targets, enabling you to take advantage of its powerful graphical interface for data mapping either to or from Excel files.

To develop an Excel mapping based on an existing spreadsheet or workbook, simply open your source file in MapForce using the File menu command, or drag and drop your document into the design pane. MapForce will display a graphical representation of the file structure, depicting rows, columns, and cells, as well as references to numbers and names. This component includes clickable icons which allow you to define and specify mappable data.

Additionally, MapForce supports an optional file format introduced in Microsoft Office 2013, the Strict Open XML Spreadsheet format (ISO/IEC 29500 Strict) as a data mapping input component.

MapForce allows you to select and map individual cells or ranges from each unique data table in the spreadsheet, and to address ranges statically or dynamically, avoiding manual extraction, export, or other pre-processing of complex Excel worksheets outside MapForce before they are inserted into your mapping design.

Once your mapping is defined, the built-in MapForce Engine allows you to view and save the results with one click. Mappings to Excel produce Office Open XML (OOXML) markup.

Read this step-by-step guide on converting Excel data with MapForce.

Easily Insert or Remove Columns in Excel Mappings

MapForce includes a feature to easily add new columns in between any existing columns mapping data to an Excel spreadsheet, without redoing the existing mapping connections. The user can select any cell in the Excel mapping, and add a cell from the context menu.

Selecting this option opens a dialog where the user can specify the range and datatypes of inserted cells:

In the example illustrated here, the existing Excel Spreadsheet contained two columns of numeric data with the column headings West and East. The user inserted a new column with the heading North.

Similarly, the Remove Cell menu option deletes the cell from the target Excel spreadsheet, but does not otherwise modify the mapping.

This feature simplifies modifications and updates to existing mappings when a change is required in the Excel file output, because all existing connections are automatically preserved.

Clicking the button next to a worksheet node in an Excel mapping, as seen near the Sales worksheet in the illustration above, opens a dialog to add worksheets manually. Alternately, you can simply reload from an updated input.xlsx file.