Best Practices (4/6): Export to Excel

You can export to Excel in two ways:

1. Using a Master Template
   recommended for simple Online Exports
   • Define one generic master template with company logo, corporate design and generic placeholder %CONTENT%
   • Include placeholder %FILTER_INFO%:VALUE to display filters of the application
   • Select export relevant components using the Visual Components property
   • Each component will be exported on a separate worksheet
2. Using a specific Export Template (see section 3.2 of the User Manual)
   recommended for Booklet Exports and sophisticated requirements
   
   • Define a special Excel template with company logo, corporate design
   • Each component is explicitly included via its own placeholder
   • Placeholders can be freely arranged – multiple placeholders on one sheet are possible
   • This specific template is uploaded to the server and referenced via property “Template Filename”

Examples for export templates are contained in the Documentation package on the website. Instructions on how to create an Export Template for Excel can be found below.

Using a Master Template

The following steps are necessary to create a Master Template for the export (see section 3.1 of the User Manual for details):

1. Create the master template like the following one:
   1. Create a header area (e.g. with a white background)
   2. Include the company logo (via Insert -->Pictures in Excel)
   3. Add placeholders as plain texts wherever you like:
      • For Export date and time: %EXECUTION_DATE% and %EXECUTION_TIME%
      • For title, filter and user info: The component names %TEXT_TITLE%:VALUE, %FILTER_INFO%:VALUE and %TEXT_USER%:VALUE
   4. Write %CONTENT% into one cell. The worksheet containing this placeholder will be copied for each element of the Visual Components list.
2. Store the Excel file as master_template.xlsx in export_resources subfolder of the Export service installation
3. Define the components to be included via property “Visual Components” in the Excel group. Make sure “Excluded” is set to false for all entries.

a) b) All components will be exported on separate worksheets, unless an explicit placeholder has been defined for the component (e.g. the case for TEXT_TITLE or FILTER_INFO). The worksheet of master_template.xlsx, which contains the %CONTENT% placeholder, acts as a template for these worksheets. Text and table components are automatically exported as editable text, all other components as images.

OPENBIEXPORTTABLE_MR03 has a very special role: As an Export Table it renders those rows of CROSSTAB_MR03, which are hidden by default by the scroll bars (see “Exporting big tables” in “General application design”). The Export Table must be referenced specifically, so its contents are displayed in the Export Document.

Using a Specific Template

The following steps are necessary to create a specific Export Template for the export (see section 3.2 of the User Manual for details). In this example we create a template for a Booklet Export.

1. Create an Excel file like the following one:
   1. Add generic placeholders as plain texts wherever you like:
      • For Export date and time: %EXECUTION_DATE% and %EXECUTION_TIME%
      • For title and user info: The component names %TEXT_TITLE%:VALUE, and %TEXT_USER%:VALUE
   2. Add one or more text boxes on those slides where you want to include components from the Design Studio application(s). Write the name of the components, which you want to export from the Design Studio application(s), into the cells of the worksheets. Surrounded the name by % (e.g. %CHART_1%).
      You can also explicitly define the width of images and use switches for the table export (see section 3.2 of the User Manual).
   3. Add filter information via %FILTER_INFO_xxx%:VALUE.
2. Store the Excel file in export_resources subfolder of the Export service installation
3. Write the name of the file into the “Template Filename” property ofExcel group

All components will be exported exactly to the slides, on which they were referenced by the placeholder.

This was number 4 in our series of Best Practices Blog Posts, which shall give you several hints how to design you Design Studio application in order to enable export in the best and most efficient way to PowerPoint, Word, PDF and Excel. We describe the export of the current view (referred to as “Online Export”), as well as the automatic export of different views of the application (referred to as “Booklet Export”).

We have started with general export concepts that you should keep in mind when designing an application.
We will finish the series with a “Tips & Tricks” section, which informs you about the most important configuration parameters and how to perform updates.
Check out also the other blog posts about exporting to other document formats!