|
|
Excel export templates |
|
|
|
|
|
Excel export templates |
|
|
|
|
|
|
Excel export templates |
|
|
|
|
|
Excel export templates |
|
|
|
|
|
|
||
It is possible to customize the default appearance of exported XLSX templates. On each export, the CMS searches for a Template.xlsx file in the following locations:
1. ~\App_Data\CMSModules\DataExport\<site code name>\<object type>\Template.xlsx
2. ~\App_Data\CMSModules\DataExport\<object type>\Template.xlsx
3. ~\App_Data\CMSModules\DataExport\<site code name>\Template.xlsx
4. ~\App_Data\CMSModules\DataExport\Template.xlsx
The DataExport folder is not present under ~\App_Data\CMSModules by default, so you have to create it and all its required sub-folders manually in case that you want to use custom templates. The <object type> folder name should be identical to the name of the exported file, e.g. cms_user for user listings as the exported file name is cms_user.xlsx (the actual object type name is cms.user, but dots are replaced with underscores in the file names). The <site code name> folder is only searched when exporting site-related objects, not for global objects that can be shared among all websites in the system.
The CMS searches for the templates with the priorities as stated above. This means that when exporting listings of an object type on a specified website, the path stated in 1. is searched first. If the Template.xlsx file is not found there, it searches location 2, and so on. This allows you to have dedicated templates for each object type and website in your system.
|
Please note
If the Template.xlsx file is not found in any of the locations, the default template is used. The same happens if the template is opened for editing at the time of export or if the current user doesn't have the Read and Write permissions for the template file (on operating system level).
|
Excel export templates can also be stored at a different location than the default ~\AppData\CMSModules\DataExport. The custom location can be defined by adding the following key to the AppSettings section of the web.config file:
<add key="CMSDataExportTemplateFolder" value="\\server1\MyDataExportTemplates" /> |
As the value of the key, you can either use a local disk path (e.g. C:\MyDataExportTemplates) or a UNC path (e.g. \\server1\MyDataExportTemplates). Using the UNC path may be useful in cases when you want to share the same templates between several Kentico CMS instances running on separate servers.
The template spreadsheet can contain any graphics, text or any other pre‑filled data in it, while the following macros can be used in any cells. On export, the macros are replaced with the actual exported data:
•##HEADER## - replaced with the header row. When advanced export is used, it is only replaced if the Export header row option is enabled.
•##DATA## - replaced with the actual exported data.
•##TABLE## - replaced with both the header row and the exported data.
•{%WhereCondition%} - replaced with the WHERE condition used for export (only relevant when exporting via the 
Advanced export dialog).
•{%OrderBy%} - replaced with the ORDER BY expression used to order exported items (either the expression configured in the Advanced export dialog or the default column according to which records are sorted when exporting using the 
Export to Excel action).
•{%TotalRecords%} - replaced with the total number of exported records.
•{%ObjectType%} - replaced with the type of exported object (e.g. cms.userlist).
You can also use all standard Context (data) macros the same way as you are used to within Kentico CMS user interface.
So for example, if you create a Template.xlsx file as in the screenshot below and upload it to ~\App_Data\CMSModules\DataExport\cms_user\ ...
... the XLSX file with exported users will look as you can see below.
