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.

Figure 1.1 A master file

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:

1. Create a new page with a product catalog – consider setting the page to Hide in menu
2. In the product catalog templates section upload the example you downloaded above and use it as the Product details template
3. 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.

Figure 2.1 Downloaded 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.

1. First, download this example master file – it contains a simple example of a loop outputting the following for each product:
   1. Product ID
   2. Product name
   3. Product stock
2. In the product catalog templates section upload and select the master file as the product list template
3. 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.

Figure 3.1 Downloaded data from a product list

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: