Difference between revisions of "Excel Template File"

From Vital Soft Wiki
Jump to: navigation, search
>Johno
(→‎Definition Sheets)
>Johno
(→‎Main Sheet)
 
(40 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
= Overview =
 
= Overview =
  
−
An Excel Template report is defined by a definition (template) file that specifies how the report should be created.  Each template file must contain a "select sheet", a "Main Sheet" and one or more "Definition sheets".
+
An Excel Template report is defined by a definition (template) file that specifies how the report should be created.  Each template file must contain a '''Select Sheet''', a '''Main Sheet''' and one or more '''Definition Sheets'''.
  
−
* Select Sheet
+
* '''Select Sheet'''
−
: Like the DocumentDistribution file, the select sheet contains selection definitions.  Selections defined in the template file are local and can only be used by the current report request.
+
: Like the DocumentDistribution file, the select sheet contains selection definitions.  Selections defined in the template file are local and can only be used by the current report request.  This sheet is required (and must contain valid column headers) even if no selections are defined.
  
−
* Main Sheet
+
* '''Main Sheet'''
 
: This sheet contains a list of definition sheets to execute.  The main sheet allows you to specify a selection that applies to the definition sheet as well as a banding value.  Specifying a banding field will cause "Sheet Level" banding to be applied.
 
: This sheet contains a list of definition sheets to execute.  The main sheet allows you to specify a selection that applies to the definition sheet as well as a banding value.  Specifying a banding field will cause "Sheet Level" banding to be applied.
  
−
* Definition Sheets
+
* '''Definition Sheets'''
 
: Each definition sheet is a '''template''' of the sheet that will be created when the report is executed.  The sheet contains cells that are copied "as is" without any changes as well as cells that are "evaluated".  Cells that are evaluated can contain chart exports, or text that contains substituted values.
 
: Each definition sheet is a '''template''' of the sheet that will be created when the report is executed.  The sheet contains cells that are copied "as is" without any changes as well as cells that are "evaluated".  Cells that are evaluated can contain chart exports, or text that contains substituted values.
  
 
== Select Sheet ==
 
== Select Sheet ==
  
−
This sheet contains local selection definitions used by the current report.  Each selection consists of one or more steps that share the same [[Defining a Selection|selection name]].
+
This sheet contains local [[Defining a Selection|selection definitions]] used by the current report.  Each selection consists of one or more steps that share the same selection name.
  
 
== Main Sheet ==
 
== Main Sheet ==
 
The main sheet contains a list of all definition sheets in the template file.  Each row in the main sheet refers to a single definition sheet to be executed.  Definition sheets are executed in the order they appear in the main sheet.
 
The main sheet contains a list of all definition sheets in the template file.  Each row in the main sheet refers to a single definition sheet to be executed.  Definition sheets are executed in the order they appear in the main sheet.
  
−
'''SheetName'''
+
'''SheetName''' (required)
−
: The name of the sheet that will be created.  The name can include references to the current date, the current banding value and to any QlikView variables.
+
: The name of the sheet that will be created.  The name can include references to the current date, the current banding value and to QlikView variables.
 +
: * <band> - The current banding value.  Use for file level banding.
 +
: * <date> - The current date.  This is the actual create date when the file was created.
 +
: * $(varname) - The value of any [[QlikView Variables|qlikview variable]].  Variables can be created by the selection.
  
−
'''SheetExistsAction'''
+
'''SheetExistsAction''' (ignored)
−
: Currently ignored. (Leave blank.)
+
: This field is currently unused, leave blank.
  
−
'''Banding'''
+
'''Banding''' (optional)
−
: The name of banding field.  When a banding field is specified, the definition sheet is re-evaluated for each unique value of the banding field.  Specifying a banding field causes "sheet level" banding and a new sheet will be created for each unique possible value.  The banding value can be included in the output sheet name using the syntax: <band>
+
: The name of a banding field.  When a banding field is specified, the definition sheet is re-evaluated for each unique value of the banding field.  Specifying a banding field causes "sheet level" banding and a new sheet will be created for each unique (possible) value.  The banding value should be included in the output sheet name using the syntax: <band>
  
−
'''Selection'''
+
'''Selection''' (optional)
−
: The name of a selection defined in the "Select Sheet" of either the current template file or the main document distribution file.  If a selection with the same name exists in both locations, the local (template file) definition will be used.
+
: The name of a selection defined in the "Select Sheet" of either the current template file or the main document distribution file.  If a selection with the same name exists in both locations, the local (template file) definition will be used.  
  
−
'''Definition'''
+
'''Definition''' (required)
−
: The name of a definition sheet.  This sheet must exist in the current document.  If the definition sheet name is prefixed by a # character, the sheet will be ignored.
+
: The name of a definition sheet.  A sheet with this name '''must exist''' in the current document.  If the definition sheet name is prefixed by a # character, the sheet will be ignored.
 +
 
 +
'''SheetAction''' (optional)
 +
: One of '''Copy''' or '''Process'''.  Default is '''Process'''.  When set to '''Copy''' the definition sheet is '''copied''' in it's entirety, however, individual cells are <u>not processed</u> looking for special tags.
  
 
'''Main Sheet Example:'''
 
'''Main Sheet Example:'''
Line 38: Line 44:
 
{| class="wikitable" style="padding-left: 10px;" stype="border-spacing: 2, 2, 2, 2;"
 
{| class="wikitable" style="padding-left: 10px;" stype="border-spacing: 2, 2, 2, 2;"
 
|-
 
|-
−
!SheetName || SheetExistsAction || Banding || Selection || Definition
+
!SheetName || SheetExistsAction || Banding || Selection || Definition || SheetAction
 
|-
 
|-
−
|style="padding-left: 10px;"|Member Totals || || || Clear || MbrTotals
+
|style="padding-left: 10px;"|Instructions || || || || Instructions || Copy
 
|-
 
|-
−
|style="padding-left: 10px;"|<band> Mbrs || || MBR_BRANCH_NAME || ||MbrTotalsByBranch
+
|style="padding-left: 10px;"|Member Totals  || || || Clear || MbrTotals || Process
 +
|-
 +
|style="padding-left: 10px;"|<band> Mbrs || || MBR_BRANCH_NAME || ||MbrTotalsByBranch || Process
 
|}
 
|}
  
−
In the above example, the template must contain two addition definition sheets: MbrTotals and MbrTotalsByBranch.
+
In the above example, the template must contain a total of 5 sheets: Select, Main, Instructions, MbrTotals and MbrTotalsByBranch.  The Instructions sheet is copied without any additional processing.
  
 
== Definition Sheets ==
 
== Definition Sheets ==
Line 56: Line 64:
 
:: * If an export tag is found, process the export and insert the required number of rows.
 
:: * If an export tag is found, process the export and insert the required number of rows.
 
:: * If a text tag is found, process the text statement and insert the text field in the final document
 
:: * If a text tag is found, process the text statement and insert the text field in the final document
 +
 +
= Special Template Tags =
 +
The definition sheet may contain special xml tags that are processed during the report execution.
 +
 +
== Text Tag ==
 +
The text tag will process the text within the tag and substitute any special values that it finds.
 +
 +
The general form of a text tag is:
 +
<Text>Any text with substitutions.</Text>
 +
 +
The text substitution codes are:
 +
 +
* <date>
 +
: The current date.
 +
 +
* <band>
 +
: The current banding value.
 +
 +
* $(VarName)
 +
: The value of any [[QlikView Variables|QlikView variable]].
 +
 +
* Examples:
 +
: <Text>Totals for <band> as of $(PriorMonthDate)</Text>
 +
:: Will produce: Totals for FIRST AVENUE as of 2013-03-31
 +
: <Text>Report execution date: <date></Text>
 +
:: Will produce: Report Execution date: 2013-04-19
 +
 +
The substituted text will appear in the final document with the same attributes (alignment, font, color, etc.) as it had in the definition sheet.
 +
 +
== Export Tag ==
 +
The export tag exports a single chart or table from the QlikView application into the same (relative) cell in the final document.  When the exported chart is inserted in the final document additional rows will also be inserted to make room for any other cells that may appear in following rows in the definition sheet.  (In other words, you '''do not''' need to anticipate the number of rows that will be consumed by the chart - the correct number of rows will always be inserted.)
 +
 +
The general form of the export tag is:
 +
<Export><ObjectType>QlikView Object ID</ObjectType><Select>...</Select><Dims>...</Dims><Parms>...</Parms><Format>...</Format></Export>
 +
 +
In version 1.5.3 (and higher), in addition to exporting charts and tables (as data) it is possible to export charts as an image.  Exporting the image of Text Boxes is also supported.
 +
 +
To export a chart or table as data, use:
 +
<Export><Chart>QlikView Object ID</Chart>...</Export>
 +
  For backwards compatibility, the following is also supported:
 +
  <Export><Object>QlikView Object ID</Object>...</Export>
 +
 +
To export the '''Image''' of a chart, use:
 +
<Export><Image>QlikView Object ID</Image>...</Export>
 +
 +
To export the Image of a '''text box''', use:
 +
<Export><Text>QlikView Object ID</Text>...</Export>
 +
: ''Note: This is not the same as using a Text Tag.  This exports an actual QlikView Text Box whereas a Text Tag creates a string of text.''
 +
 +
* '''[[QlikView Object ID|Object ID]]''' (required)
 +
: The object ID of the chart, table or text box.  When exporting a chart or table as data the object is exported into a temporary file (ExcelTemp) and then copied into the final sheet after the appropriate number of rows have been inserted.
 +
 +
* '''Select''' (optional)
 +
: A selection to be executed prior to exporting the object.  Once the object has been exported, the selection is returned to it's previous state.
 +
 +
* '''[[VitalSigns Chart Dimensions|Dims]]''' (optional)
 +
: Many Vital Signs charts have selectable dimensions.  To ensure the chart is exported with the correct dimension, specify the Dims tag as follows:
 +
<Dims>DimName1=FieldName1</Dims>
 +
: if the chart has more than one ''selectable'' dimension, separate the dimension names with commas as follows:
 +
<Dims>DimName1=FieldName1,DimName2=FieldName2</Dims>
 +
 +
* '''Parms''' (optional)
 +
: Several chart parameters can be temporarily changed prior to the export:
 +
: *SuppressHeader
 +
:: Suppresses the chart header.  When selected (=1) no headers will be included in the export.  In this case, chart header values should be supplied in the definition template.
 +
:: ''Note: This option is only supported with charts.  It is ignored for straight tables.''
 +
: *SuppressTotals
 +
:: Suppresses chart totals. When selected (=1) no totals will be included in the export.
 +
: *AlwaysFullyExpanded
 +
:: Valid only for Pivot Tables.  When selected (=1) forces all pivot dimensions to be fully expanded prior to the export.
 +
: *UsePartialSum
 +
:: Valid only for Pivot Tables.  When selected (=1) forces all pivot dimensions to display sub totals.
 +
: If multiple parms are being specified, they should be separated by a comma as follows:
 +
<Parms>SuppressHeader=1, AlwaysFullyExpanded=1</Parms>
 +
 +
* '''Format''' (optional)
 +
: Several formatting parameters can be specified as part of the export tag.  By default, the format from the corresponding cell of the template file is used.  These formatting options are applied after the default settings have been applied.
 +
: *FontSize
 +
:: Sets the font to the specified point size.
 +
: *Bold
 +
:: When selected (=1) displays text in bold.
 +
: *Italic
 +
:: When selected (=1) displays text in italic.
 +
: *Underline
 +
:: When selected (=1) displays text underlined.
 +
: *FontColor
 +
:: Sets the font color to the specified value.
 +
: *BackColor
 +
:: Sets the background color to the specified value.
 +
: *BorderColor
 +
:: Sets the border color to the specified value.
 +
: *AutoFitRow
 +
:: When selected (=1) adjusts the height of the row to fit the cell contents - useful for chart images.
 +
: *AutoFitColumn
 +
:: When selected (=1) adjusts the width of the column to fit the cell contents - useful for chart images.
 +
: *RangeName
 +
:: Sets the specified Excel Range Name to the cell range containing the exported data.  Range names can include <band> and $(varname).  Blanks are not allowed in a range name and will be removed.
 +
: If multiple format options are being specified, they should be separated by a comma as follows:
 +
<Format>Bold=1, AutoFitColumn=1, RangeName=LNDATA_<Band></Format>

Latest revision as of 22:26, 3 December 2015

Overview

An Excel Template report is defined by a definition (template) file that specifies how the report should be created. Each template file must contain a Select Sheet, a Main Sheet and one or more Definition Sheets.

  • Select Sheet
Like the DocumentDistribution file, the select sheet contains selection definitions. Selections defined in the template file are local and can only be used by the current report request. This sheet is required (and must contain valid column headers) even if no selections are defined.
  • Main Sheet
This sheet contains a list of definition sheets to execute. The main sheet allows you to specify a selection that applies to the definition sheet as well as a banding value. Specifying a banding field will cause "Sheet Level" banding to be applied.
  • Definition Sheets
Each definition sheet is a template of the sheet that will be created when the report is executed. The sheet contains cells that are copied "as is" without any changes as well as cells that are "evaluated". Cells that are evaluated can contain chart exports, or text that contains substituted values.

Select Sheet

This sheet contains local selection definitions used by the current report. Each selection consists of one or more steps that share the same selection name.

Main Sheet

The main sheet contains a list of all definition sheets in the template file. Each row in the main sheet refers to a single definition sheet to be executed. Definition sheets are executed in the order they appear in the main sheet.

SheetName (required)

The name of the sheet that will be created. The name can include references to the current date, the current banding value and to QlikView variables.
* <band> - The current banding value. Use for file level banding.
* <date> - The current date. This is the actual create date when the file was created.
* $(varname) - The value of any qlikview variable. Variables can be created by the selection.

SheetExistsAction (ignored)

This field is currently unused, leave blank.

Banding (optional)

The name of a banding field. When a banding field is specified, the definition sheet is re-evaluated for each unique value of the banding field. Specifying a banding field causes "sheet level" banding and a new sheet will be created for each unique (possible) value. The banding value should be included in the output sheet name using the syntax: <band>

Selection (optional)

The name of a selection defined in the "Select Sheet" of either the current template file or the main document distribution file. If a selection with the same name exists in both locations, the local (template file) definition will be used.

Definition (required)

The name of a definition sheet. A sheet with this name must exist in the current document. If the definition sheet name is prefixed by a # character, the sheet will be ignored.

SheetAction (optional)

One of Copy or Process. Default is Process. When set to Copy the definition sheet is copied in it's entirety, however, individual cells are not processed looking for special tags.

Main Sheet Example:

SheetName SheetExistsAction Banding Selection Definition SheetAction
Instructions Instructions Copy
Member Totals Clear MbrTotals Process
<band> Mbrs MBR_BRANCH_NAME MbrTotalsByBranch Process

In the above example, the template must contain a total of 5 sheets: Select, Main, Instructions, MbrTotals and MbrTotalsByBranch. The Instructions sheet is copied without any additional processing.

Definition Sheets

The definition sheet may contain any elements that you want. For example, the sheet may contain a logo, as well as text and calculated fields. The sheet may also contain special tags that will export charts and tables from the QlikView application into the final sheet. A tag can also generate customized text. The sheet is processed as follows:

  • If a selection is specified, execute the selection
  • If a banding field is specified, loop on each possible banding field value:
* Copy the "Used Range" (1) of the definition sheet into the new output file
* Set the row heights and column widths in the new sheet to match the definition sheet
* Walk all rows and columns looking for special tags
* If an export tag is found, process the export and insert the required number of rows.
* If a text tag is found, process the text statement and insert the text field in the final document

Special Template Tags

The definition sheet may contain special xml tags that are processed during the report execution.

Text Tag

The text tag will process the text within the tag and substitute any special values that it finds.

The general form of a text tag is: 

<Text>Any text with substitutions.</Text>

The text substitution codes are:

  • <date>
The current date.
  • <band>
The current banding value.
  • $(VarName)
The value of any QlikView variable.
  • Examples:
<Text>Totals for <band> as of $(PriorMonthDate)</Text>
Will produce: Totals for FIRST AVENUE as of 2013-03-31
<Text>Report execution date: <date></Text>
Will produce: Report Execution date: 2013-04-19

The substituted text will appear in the final document with the same attributes (alignment, font, color, etc.) as it had in the definition sheet.

Export Tag

The export tag exports a single chart or table from the QlikView application into the same (relative) cell in the final document. When the exported chart is inserted in the final document additional rows will also be inserted to make room for any other cells that may appear in following rows in the definition sheet. (In other words, you do not need to anticipate the number of rows that will be consumed by the chart - the correct number of rows will always be inserted.)

The general form of the export tag is: 

<Export><ObjectType>QlikView Object ID</ObjectType><Select>...</Select><Dims>...</Dims><Parms>...</Parms><Format>...</Format></Export>

In version 1.5.3 (and higher), in addition to exporting charts and tables (as data) it is possible to export charts as an image. Exporting the image of Text Boxes is also supported.

To export a chart or table as data, use: 

<Export><Chart>QlikView Object ID</Chart>...</Export>
  For backwards compatibility, the following is also supported:
  <Export><Object>QlikView Object ID</Object>...</Export>

To export the Image of a chart, use: 

<Export><Image>QlikView Object ID</Image>...</Export>

To export the Image of a text box, use: 

<Export><Text>QlikView Object ID</Text>...</Export>
Note: This is not the same as using a Text Tag. This exports an actual QlikView Text Box whereas a Text Tag creates a string of text.
The object ID of the chart, table or text box. When exporting a chart or table as data the object is exported into a temporary file (ExcelTemp) and then copied into the final sheet after the appropriate number of rows have been inserted.
  • Select (optional)
A selection to be executed prior to exporting the object. Once the object has been exported, the selection is returned to it's previous state.
Many Vital Signs charts have selectable dimensions. To ensure the chart is exported with the correct dimension, specify the Dims tag as follows:
<Dims>DimName1=FieldName1</Dims>
if the chart has more than one selectable dimension, separate the dimension names with commas as follows:
<Dims>DimName1=FieldName1,DimName2=FieldName2</Dims>
  • Parms (optional)
Several chart parameters can be temporarily changed prior to the export:
*SuppressHeader
Suppresses the chart header. When selected (=1) no headers will be included in the export. In this case, chart header values should be supplied in the definition template.
Note: This option is only supported with charts. It is ignored for straight tables.
*SuppressTotals
Suppresses chart totals. When selected (=1) no totals will be included in the export.
*AlwaysFullyExpanded
Valid only for Pivot Tables. When selected (=1) forces all pivot dimensions to be fully expanded prior to the export.
*UsePartialSum
Valid only for Pivot Tables. When selected (=1) forces all pivot dimensions to display sub totals.
If multiple parms are being specified, they should be separated by a comma as follows:
<Parms>SuppressHeader=1, AlwaysFullyExpanded=1</Parms>
  • Format (optional)
Several formatting parameters can be specified as part of the export tag. By default, the format from the corresponding cell of the template file is used. These formatting options are applied after the default settings have been applied.
*FontSize
Sets the font to the specified point size.
*Bold
When selected (=1) displays text in bold.
*Italic
When selected (=1) displays text in italic.
*Underline
When selected (=1) displays text underlined.
*FontColor
Sets the font color to the specified value.
*BackColor
Sets the background color to the specified value.
*BorderColor
Sets the border color to the specified value.
*AutoFitRow
When selected (=1) adjusts the height of the row to fit the cell contents - useful for chart images.
*AutoFitColumn
When selected (=1) adjusts the width of the column to fit the cell contents - useful for chart images.
*RangeName
Sets the specified Excel Range Name to the cell range containing the exported data. Range names can include <band> and $(varname). Blanks are not allowed in a range name and will be removed.
If multiple format options are being specified, they should be separated by a comma as follows:
<Format>Bold=1, AutoFitColumn=1, RangeName=LNDATA_<Band></Format>
Retrieved from "https://wiki.vital-soft.com/index.php?title=Excel_Template_File&oldid=1410"