Microsoft Excel tables export properties
Sample script file: ExcelExport.mrs
This example script is based on the Museum sample data set. See
Running the sample table scripts for information on running the example scripts.
When you export tables to Microsoft Excel, you can control a number of options by using the export properties.
Available export properties
AllTablesOnOneWorksheet
If true, export all tables into one worksheet that is called “All Tables”.
Type: Boolean
Default value: False
AutoFitColumnWidths
If true, automatically change the width of the table columns to accommodate the width of the text.
Type: Boolean
Default value: False
ChartCategoryElements
Set to "Per element" to produce a chart for each category element. Set to "Per variable" to produce a chart for each variable block of category elements. Set to "Per table" to produce a single chart for all category elements in the table.
When using a custom pie chart or template, ChartCategoryElements must be set to "Per element".
Type: String:
Possible values: "No chart", "Per element", "Per variable", "Per table".
Default value: "Per variable"
ChartCellItem
Where there is more than one cell item on the table, specifies the cell item to chart. The cell item must be included in the table. The cell item must be included in the table. If the specified cell item does not exist then Count will be used, and if Count does not exist, the first cell item is used.
Type: String
Possible values:
"Count"
"ColPercent"
"RowPercent"
"TotalPercent"
"Mean"
"Sum"
"Minimum"
"Maximum"
"UnweightedCounts"
"CumColPercent"
"CumRowPercent"
"Range"
"Mode"
"Median"
"Percentile"
"StdDev"
"StdErr"
"Variance"
Default value: "ColPercent"
ChartColPropResults
If true, displays the column IDs beside the category labels, and adds the column proportions test results to the chart above the relevant columns.
Type: Boolean
Default value: False
ChartRowsAsSeries
If true, table rows are used to form the chart series. If false, table columns are used.
Type: Boolean
Default value: True
ChartSpecialElements
Set to "Per element" to create a chart for any special statistical elements (mean, standard deviation, etc) in the orientation specified in the ChartRowsAsSeries property.
Set to "Per variable" to produce a chart for each variable block of special elements.
Set to "Per table" to produce a single chart for all special elements in the table.
Type: String:
Possible values: "No chart", "Per element", "Per variable", "Per table".
Default value: "Per element"
ChartTemplate
Indicates the chart template that is to be used. If you want to use a custom chart template that you have created in Excel, specify the name of the chart template.
Type: String
Default value: "" (Empty string)
ChartTemplateLocation
The location of the chart template. It is not necessary to specify this property because its value is set when the user specifies the chart template file.
Type: String
Default value: "" (Empty string)
ChartType
Indicates the type of chart that is to be created. If you want to use a custom chart that you have created in Excel, specify the name of the custom chart.
Type: String
Possible values:
"Clustered Bar"
"Stacked Bar"
"3D Clustered Bar"
"3D Stacked Bar"
"Clustered Column"
"Stacked Column"
"3D Clustered Column"
"3D Stacked Column"
"3D Column"
"Line"
"Stacked Line"
"Line with Markers"
"100% Stacked Line with Markers"
"Pie"
"3D Pie"
"Separated Pie"
"3D Separated Pie"
"Custom chart name"
Default value: "Clustered Column"
CreateTableOfContents
Determines whether a table of contents is included in the Excel document. The table of contents includes table description, filter description, base, and links to the table and chart.
Type: Boolean
Default value: True
Destination
Location and filename of the exported Excel file.
Type: String
Default value: "" (Empty string)
DisplayAnnotations
Determines whether annotations are included in the export.
Type: Boolean
Default value: True
DisplayBorders
Indicates whether tables are to be displayed with borders.
Type: Boolean
Default value: True
DisplayCharts
Indicates whether charts are to be created.
Type: Boolean
Default value: True
DisplayLastCategoryBase
Determines whether to display the base for the last data point in the chart legend. This option is applicable only when charting special elements. If the Count cell item is present, this is used. If not, the Unweighted Count cell item is used.
Type: Boolean
Default value: False
DisplaySeriesBase
Indicates that the base for each of the chart series should be displayed in the legend for the chart.
Type: Boolean
Default value: False
FormatPercentageAxes
Determines whether to chart percentages on a scale of 0 to 100%. If this option is false, the scale is based on the biggest value in the chart.
Type: Boolean
Default value: False
HideApplication
Indicates that Excel is to be hidden during the export. This makes the export faster.
Type: Boolean
Default value: False
HideBaseElements
If true, suppresses all rows or columns containing base elements in all tables, so that they are not displayed in the output file. If false, base elements are displayed or hidden according to the settings in the axis expressions for each table.
Type: Boolean
Default value: False
Interactive
Type: Boolean
Default value: False
LaunchApplication
Launch Excel after the export has completed.
Type: Boolean
Default value: False
MergeColPropResults
Combines the column proportion result with the first cell item. Charts are not produced for tables with merged column proportion results.
Type: Boolean
Default value: False
OverwriteOutput
When not using the interactive mode, this property determines whether an existing output file should be overwritten rather than causing the export to fail. See
Microsoft Excel tables export: Interactive mode for more information.
Type: Boolean
Default value: False
PercentageCellItemsOnly
If true, export only the Percentage cell items.
Consistent with tables that include only the Column Percentages cell item, the Base rows display the counts when displaying only the percentages for the rest of the table. Conversely, when exporting only Row Percentages, the Base columns display the counts.
Type: Boolean
Default value: False
PrintingOption
Determines the printing option to be used for the exported tables. This does not affect how the tables appear in Excel. It affects how the tables appear when they are printed only.
Type: String
Possible values: "None", "Fit to page", "Repeat axes"
Default value: "Repeat axes"
ShiftColPropResults
Export the significance letters to a separate column that is headed by the significance letter for the column. See also
MergeColPropResults.
Type: Boolean
Default value: False
SplitCells
Indicates that each type of cell contents is to appear in a separate table. This is useful if you want to perform calculations on the output or set up your own charts in Excel.
Type: Boolean
Default value: False
UseElementLabels
Determines whether element names or the more friendly element labels are to be used for the row and column labels in the exported tables.
Type: Boolean
Default value: True
(Use labels)
UseExcelStyles
Indicates that formatting is to be controlled by styles. See
Microsoft Excel tables export formattingfor more information.
Type: Boolean
Default value: False
UseFormattedAnnotations
Enables you to use HTML formatting of annotations when exporting to Excel. When the value of this property is True, the annotation is copied as formatted HTML, using the clipboard. When the value is False, the annotation is copied as plain text, so that all tags (except <br/>) appear in the Excel output.
Set this option to False in a server environment.
Type: Boolean
Default value: True
UseVariableLabels
Determines whether variable names or the more friendly labels (descriptions) are to be used for variable texts in the exported tables.
Type: Boolean
Default value: True
(Use labels)
WrapLabelText
Determines whether to wrap long labels (descriptions) to the next line.
Type: Boolean
Default value: True
Examples
1. Using Excel styles
You indicate that you want the formatting to be controlled by styles by setting the UseExcelStyles export property to True:
TableDoc.Exports.mrExcelExport.Properties["UseExcelStyles"] = True
2. Creating a separate table for each type of cell contents
By default, each table in your script is exported as a single table to a separate Excel worksheet. However, you can optionally choose to export each type of cell contents to a separate table. All of the tables are placed on the same worksheet. This option is useful if you want to perform calculations on the output or set up your own charts in Excel. You select this option by setting the SplitCells export property to True:
TableDoc.Exports.mrExcelExport.Properties["SplitCells"] = True
3. Exporting charts
You select the option to export charts by setting the DisplayCharts export property to True:
TableDoc.Exports.mrExcelExport.Properties["DisplayCharts"] = True
For details of how data is displayed in charts, see
Creating charts.
Requirements
▪UNICOM Intelligence Reporter
▪Microsoft Office 2007 or later
See also