Example: Updating Existing Excel Sheets
If you choose to generate Excel sheets with MapForce, the generated sheets do not contain any formatting, only data. However, it is also possible to update existing Excel files and preserve their formatting (as opposed to generating them), which is the goal of this example. You can find the mapping described in this example at the following path: <Documents>\Altova\MapForce2023\MapForceExamples\Sales_to_Excel.mfd. As illustrated below, this mapping reads data from a file in XML format and writes data to an existing Excel file (sales-report.xlsx). Specifically, it updates a sheet called "Sales", starting from the fifth row, in columns A, B, and C.
The target component was configured to update the existing file as follows:
1.Set the transformation language to BUILT-IN.
2.Right-click the title bar of the target component, and select Properties. The Component Settings dialog box opens.
3.In the Output Excel File text box, enter the path to the existing Excel file. In this example, the file is in the same folder, so the path is relative, and the check box Save all file paths relative to MFD file is selected.
4.Select the Update existing file option.
The target worksheet was selected by clicking the button adjacent to the "Sales" node ("Sales" here is the name of the worksheet).
Finally, one can select the range of cells/rows to write to by clicking the button adjacent to the "Rows" node. According to the configuration below, the mapping will update the worksheet starting with row 5, and create as many rows as there are items in the mapped source data. The columns A, B, and C were given distinct names so that they can be easily mapped to.
When you click the Output tab to preview the mapping result, the existing worksheet is updated and all existing formatting is preserved. The existing chart is also updated as a consequence (because the source cells used by the chart changed their values).
Note that previewing the mapping does not actually save changes to the Excel file. To save the file displayed in the Output pane to the disk, select the menu command Output | Save Output File, or click the Save generated output toolbar button. Alternatively, you might want to enable the option Write directly to final output files from Tools | Options | General. The latter option should be exercised with caution, because it overwrites any existing files without requesting further confirmation.
To automate running mappings such as this one and eliminate the need for button clicks, you can prepare and deploy the mapping to MapForce Server, as described in Automating Mappings and MapForce.