Generic Excel Exporter
From Dynamicweb 9.8 and forward you have access to a generic Excel exporter which handles merging object values into placeholders in an excel worksheet.
The basic principle is this:
- You define what you want to output inside an excel worksheet Master file
- You can then call the Excel exporter and provide the following information:
- The object you want to export
- The master file to use
- A file location to save the data to (if applicable)
- The exporter then either writes an excel file or returns a bytestream of excel data to you, depending on the write method used.
You can read more about the various methods and parameters in the API documentation on the ExcelWriter class.
A master file is an excel file containing a number of placeholders – e.g. Product_ActualPrice or Product_Name – which are then overwritten when an object is exported to Excel. A master file for downloading data about a single product could look like Figure 1.1 – download this example.
In order to know which placeholders you have access to you can add &dwtemplatetags=true to any URL using the Excel Exporter – this will auto-generate and download an excel sheet with all placeholders and loops available in the context. If this is a little hard to wrap your head around don’t worry – you can see it demonstrated in example 1 below.
Dynamic file names
If you want the downloaded file to be named after e.g. the product number you can use placeholders in the file name - for example {Product_Name}.xlsx.
Example 1: Download product data
The easiest way to download product data is via a product catalog app which uses excel master files instead of regular template files. You can then link to this catalog from your regular product catalog templates triggering the download:
- Create a new page with a product catalog – consider setting the page to Hide in menu
- In the product catalog templates section upload the example you downloaded above and use it as the Product details template
- In the regular product catalog list template create a button or a link pointing to this page: /default.aspx?ID=[page id]&ProductID=[product id]
When this link is clicked, an excel sheet with the product details is downloaded (Figure 2.1) – as you can see, it is generated from the master file you downloaded. To see which placeholders are available to you add &dwtemplatetags=true to the url – this will auto-generate a help file for the context (the product details template) and download it.
Example 2: Download product list data
So maybe you want to download an excel sheet with a list of products instead of only a single product. To do so you basically repeat the steps from example 1 – or use the same product catalog.
- First, download this example master file – it contains a simple example of a loop outputting the following for each product:
- Product ID
- Product name
- Product stock
- In the product catalog templates section upload and select the master file as the product list template
- In your regular product catalog list template – or wherever really – create a button or link to the page with this product catalog, i.e. /default.aspx?ID=[page id]
When this link is clicked, an excel sheet with a list of products is downloaded (Figure 3.1) – as you can see, it too is generated from the master file you downloaded. As in example 1, you can simply add &dwtemplatetags=true to the URL to generate a help file with information about the placeholders & loops available to you.
Example 3: Using ExcelWriter directly in templates
While using the ExcelWriter class via a template is probably the most common usecase, you can also call it directly in a template to export an object: