Example: Supplying Data to Preformatted Excel Sheets

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

Home >  Data Sources and Targets > Microsoft OOXML Excel 2007+ >

Example: Supplying Data to Preformatted Excel Sheets

Excel sheets generated by MapForce do not contain any formatting, only data. If you need to format Excel data generated by MapForce, this is possible outside of MapForce, through standard Excel functionality, as follows:

 

1.Generate your Excel sheet with MapForce and save it to the disk (let's call it xlsx-mapforce.xlsx).
2.Create a new Excel sheet with Excel (let's call it Sales-presentation.xlsx).
3.From Sales-presentation.xlsx, create external references (links) to cell ranges from xlsx-mapforce.xlsx. This is a one-time operation; for detailed instructions, see https://support.office.com/en-us/article/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f.
4.Apply all the required formatting (fonts, colors, and so on) in Sales-presentation.xlsx.

 

From now on, you can generate data as many times as necessary into xlsx-mapforce.xslx. To update the Sales-presentation.xlsx with the most recent data generated by MapForce, click the Refresh All button in the Data tab. This will keep all existing formatting intact.

 

In the example below, you will first generate some Excel data with MapForce and save it to a file. You will then be able to see this data nicely formatted, in a second Excel sheet (which is preconfigured to have external references to the first one). To proceed, open the following sample mapping: <Documents>\Altova\MapForce2019\MapForceExamples\Sales_to_Excel.mfd.

Sales_to_Excel-ENT

Sales_to_Excel.mfd

Next, click the Output tab to generate the mapping result.

Next, click Save generated output ic-save-gen-out and save it as "xlsx-mapforce.xlsx", to the folder <Documents>\Altova\MapForce2019\MapForceExamples\.

egSales2Excel2

You can now view this data formatted in a second sheet that is already configured to read data from xlsx-mapforce.xlsx file. Open the Sales-presentation.xlsx file in Excel (from the same folder as above). A warning appears that external links have been disabled and they need to be enabled to link to the source data. The current cursor position, B6, shows that the source data range is Sales'!$B$3:$C$14 of the xlsx-mapforce.xlsx file.

Sales_to_Excel-ex02

Sales-presentation.xlsx

Click the Enable button (or Enable Content, in Excel 2016). The external links are now enabled and the underlying source data is shown as a table and a chart.

Sales_to_Excel-ex03

Sales-presentation.xlsx (with updated references)

You can also manually refresh the links between the two sheets, as follows:

 

On the Data tab, click Refresh all.

© 2019 Altova GmbH