|
<< Click to Display Table of Contents >> Navigation: Reference Manual > Windows and dialog boxes > Analysis > Spreadsheet options |
This is reached from Tables overall or CL Window to set the options for the spreadsheet tables output which are converted from CSV tables to a formatted Excel file.
These settings can also be set globally as the default house style, or for this project only, using [Analysis] [Default spreadsheet options].
It can also be reached from the main menu [CL] [Convert CSV tables] and [CL] [Run CL STP]. Some of the options below will then not be available because these control the CSV production, not the conversion.
When using a tables file these options are stored in a separate XML file with the same name as the QTF file followed by " Spreadsheet Options".
The options shown will be set in the following priority:
1.The settings in a Spreadsheet Options file associated with the QTF or CL script (name Spreadsheet Options.xml).
2.The settings from a "Default Spreadsheet Options.xml" file in the current folder.
3.The settings from the global "Default Spreadsheet Options.xml" file stored in the user folder AppData\Roaming\NetMR Ltd.
4.If none of the above, the program defaults are shown.
Whenever a QTF is saved, the associated Spreadsheet Options file is also saved.
Whenever a QTF is opened, any associated Spreadsheet Options file is also loaded.
NOTE: with care you can edit the saved Spreadsheet Options file to change many of the settings, not just those available to change here.
The spreadsheet options used for tables or scripts will be determined from the priority list above.
This restores the settings to the default values as explained above.
This loads the settings from an existing spreadsheet options XML file.
This causes each output table to be put in a separate sheet in the spreadsheet file produced.
Each table has a link back to the contents sheet.
When not set, you can name sheets for blocks of tables by adding headers to the list of tables.
This will cause the tables CSV file to be deleted after it has been converted to Excel.
This removed the information lines from the Excel output.
If this is not set then information lines are hidden in the Excel output.
The markers showing significant differences (+ and - signs) are removed from the output.
These markers are not needed because they cause the cells to change to the appropriate colour.
The significance markers are normally put in a separate row below each row of the table. This option causes the markers to be added to the cells containing the figures, usually the vertical percentages.
Keep empty marker rows
Include marker rows even if there are no markers to show.
This will hide the link on tables that takes you back to the contents sheet. This link is still there but there is no text showing.
You may want to do this if the tables are being printed.
The default font for spreadsheet tables is Aptos.
You can use this button to change the font. The font chosen is shown alongside the button.
To revert to Aptos use the "Default" button next to this button.
Use this button to change the colours and font sizes used for labels, identifiers and markers in spreadsheet tables, see Spreadsheet visuals.
A logo can be placed on Excel tables when printed.
Use this to choose the file containing the logo to show in the header when tables are printed from Excel.
The logo is shown half size below the button.
This will be the name of the worksheet that contains the tables when "A separate sheet for each table" is not selected in Spreadsheet options.
Note that this setting may not be used unless you have some text in the header box.
You can switch to another worksheet at any time by adding a header and setting the new sheet name. In CL use the new <&sheet=...> label control in a job header special.
Sheet names can only contain letters, numbers or underscores (_). Other special characters are not allowed.
This causes the header text from Tables overall to be shown at the top of every sheet. It will not then be used in the print header when printing tables from Excel.
This causes the footer text from Tables overall to be shown at the top of every sheet under the heading (if present). It will not then be used in the print footer when printing tables from Excel.
Format FSL will normally put the levels used at the bottom of each table. This option puts the level in the global footer instead, so it only appears once in the spreadsheet file.
IMPORTANT: You cannot change the levels during a run if this option is used.
The format LBR, default LBR1, causes empty lines to be output before the rows of a table. These are normally stripped out from the Excel/OpenXML output. Using this option will cause these lines to be preserved (at half height) in the output.
These options produce additional copies of each table with some of the information removed.
Links to these additional tables are shown alongside the table names in the Contents sheet.
If "All" is selected alongside an additional copy, then every table is output even if it will contain no relevant information.
Produces a copy of each table with percentages and significance markers removed.
Only tables with row figures or statistics are included unless "All" is selected alongside.
Produces a copy of the table with figures and significance markers removed from the rows.
Only tables with percentages or statistics are included unless "All" is selected alongside.
Produces a copy of the table with only the vertical percentages and significance markers.
Only tables with vertical percentages or statistics and with markers are included unless "All" is selected alongside.
Produces a copy of the table with only figures and the total rows and columns removed.
If there are no columns other than the total column this is not removed.
Produces a copy of the table with only vertical percentages and the total rows and columns removed.
If there are no columns other than the total column this is not removed.
This saves these settings in the current folder as Default Spreadsheet Options.xml.
These settings will be used as the default settings for every new conversion in the current folder (containing the CSV to be converted) unless there is a specific file with settings for a tables file.
This saves these settings to an XML file with the name you choose.
These can be retrieved later using the Load a named options file button.
NOTE: we recommend including Spreadsheet Options as part of the name when saving named options settings files.