Excel Mapping

Map and Transform Excel Data

Define Excel as a data mapping source or target
Use a pre-formatted workbook as a target template
Add or remove columns from an Excel target
Add or remove worksheets from an Excel target
Process multiple input and/or output files

Map Excel and PDF, XML, JSON, EDI, Protobuf...
PDF Extractor for PDF to Excel
Map between databases and Excel workbooks
Map Excel to XBRL
Map Excel to or from Web services
Automate repetitive execution of Excel mappings

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.

Use a Pre-Formatted Excel Workbook as a Target Template

You can take advantage of external links in Excel to map any data type supported by MapForce to a pre-formatted Excel workbook. This means you can use any existing richly-formatted Excel document as a template to generate output.

For instance, you could create a monthly report in the form of an Excel workbook with charts and graphs and update the report by mapping data from a database, a Web service, or any other data source.

The screenshot above shows an Excel mapping installed with MapForce examples where the source data is XML. Note also that a variety of other Excel mapping samples are included with MapForce and listed in the Project window on the left side of the screenshot.

The partial view to the right shows the mapped data from the example mapping populated as cells in the Excel workbook and illustrated in a chart.

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:

Insert new columns in an Excel mapping in MapForce

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.

Insert or Remove Worksheets in an Excel Mapping Target

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.

Automate Excel Mapping Execution with MapForce Server

Altova MapForce Server includes the built-in data transformation engine developed for MapForce and is greatly enhanced to operate in server environments. MapForce Server performs data transformations for any combination of XML, JSON, PDF, database, EDI, XBRL, flat file, Excel, Protobuf, and Web service using preprocessed and optimized data mappings stored in execution files based on data mappings defined in MapForce. MapForce Server takes data transformation to the next level with server capabilities including parallel processing and multi-threading, bulk SQL merge, cross-platform support, and more.

Preprocessing enables faster performance and reduced memory footprint for most data mappings. MapForce Server operates under the management of FlowForce Server, in a standalone configuration executed from a command line, or programmatically via an API.

After a MapForce Excel mapping is designed and tested, it can be executed by MapForce Server to automate business processes that require repetitive data transformations.

Creating a MapForce Server Execution File

MapForce pre-processes and optimizes Excel data mappings, stores them in MapForce Server Execution files for command-line execution by MapForce Server, and uploads them for use in FlowForce Server jobs.

When MapForce Server operates under the management of FlowForce Server, data mappings are executed as FlowForce Server job steps, based on triggers defined as part of the FlowForce Server job. For example, a new XML instance document lands in a directory, which triggers a multi-step FlowForce Server job to first validate the file using RaptorXML+XBRL Server, then execute MapForce Server to extract certain data from the XML and insert it into an Excel workbook.

Language:
EN
DE
ES
JA
FR