Example: Convert Excel Rows to XML Files
Excel files may consist of multiple worksheets that have a different structure each, meaning that the number and order of columns could be different. On the other hand, there are also Excel files where all worksheets have identical structure and only the name and data of the worksheet is different.
With MapForce, you can configure a mapping to flexibly extract data from Excel worksheets in either of the scenarios above, namely:
1.You can show each individual Excel worksheet as a distinct structure on the mapping. This approach is useful when each Excel worksheet has a different structure. You have the flexibility to map data originating from any cell of any worksheet in your source Excel file.
2.You can treat all the Excel worksheets in the file as if they were a single worksheet. This approach is useful only if all worksheets have the same structure; it has the advantage that you draw fewer connections on the mapping. Since the structure of each worksheet is assumed to be the same, you draw connections from each column only once, regardless of the worksheet where it appears.
To configure an Excel component for either of the two approaches described above:
1.Add the Excel file to the mapping using the Insert | Excel 2007+ File menu command, see also Adding Excel 2007+ Files as Mapping Components.
2.Click the 
button adjacent to the first worksheet. The following dialog box appears:
3.Do one of the following:
a.To show the structure of each worksheet separately, make sure that the check box Show Worksheets by name is selected. This is the default option.
b.To treat all worksheets as a single mappable component, clear the check box Show Worksheets by name.
A demo mapping design file that illustrates each of the two approaches described above is available at the following path: <Documents>\Altova\MapForce2024\MapForceExamples\Tutorial\ConvertExcelRows.mfd. This mapping illustrates two different strategies of reading data from an Excel file. Since this mapping produces multiple unrelated outputs, the Preview 
button appears in the title bar of each target component. Remember to click this button before clicking the Output pane, in order to preview the respective output file.
The source Excel file is called Altova.xlsx and it consists of two worksheets: "Admin" and "Development". Note that the structure of each worksheet is identical, only the data in each worksheet differs (which is why more than one data access strategy is possible).
The upper part of the mapping illustrates reading data from the Excel file by treating each worksheet as a separate structure. If you click the button adjacent to the Admin item, you will notice that the check box Show Worksheets by name was selected. This has the effect that both worksheets, "Admin" and "Development", appear explicitly on the source component, as children of Workbook.
ConvertExcelRows.mfd (part 1)
The mapping illustrated above converts data from both Excel worksheets to XML files. A separate XML file must be generated for each person record from each row in each source worksheet. The file name must identify the department name and have a numeric ID. For example, the file Admin1.xml will store the details of the first person (row) in the "Admin" worksheet, and so on. This mapping goal was accomplished as follows:
1.The concat function produces the file name for each target XML. It joins (in this order) the following: the name of each department, the Excel row number, and the string ".xml".
2.The connection from the result of the concat function to the File: <dynamic> target item has the effect that a new file is generated at mapping runtime for each item in the incoming sequence of items. In this example, the incoming sequence contains all the rows of the source Excel worksheet; therefore, a new XML file will be generated for each Excel row.
As a side note, to configure a target component to generate file names dynamically, click the File/String button, and select the option Use Dynamic File Names Supplied by the Mapping. For more information, see Processing Multiple Input or Output Files Dynamically.
The second part of the ConvertExcelRows.mfd mapping is illustrated below. This part of the mapping achieves exactly the same result as part 1, but in a more concise way. Notice that the mapping connections are significantly reduced.
ConvertExcelRows.mfd (part 2)
The mapping logic is almost identical with the one described previously, namely:
1.For each row in each source worksheet, one Person item in the target will be created.
2.The concat function generates the name of each target file.
3.The target component was switched to generate file names dynamically.
The only difference is that all the worksheets in the source Excel file are iterated over implicitly—there is no need to show each worksheet's structure separately and draw mapping connections for each. The reason is that the source Excel component has been configured to treat all worksheets as a single one—you can click the button adjacent to the Worksheets item and observe that the Show Worksheets by name check box is not selected.
In conclusion, in this topic you have seen the differences between mapping data from individual Excel worksheets as opposed to treating all worksheets as one. Just keep in mind that the second approach requires and implies that the structure of all worksheets is the same. In this example, the assumption is that each first column in each worksheet stores the person's first name, each second column stores the person's last name, and so on.