|
<< Click to Display Table of Contents >> Navigation: Reference Manual > Windows and dialog boxes > Spreadsheet options > Spreadsheet options |
This is reached from Tables overall 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 will be set in the following priority:
1.The settings in a Spreadsheet Options file associated with the QTF (qtfname Spreadsheet Options.xml).
2.The settings from a "Default Spreadsheet Options" XML file in the folder with the tables file.
3.The settings from the "Default Spreadsheet Options" XML file in the AppData\Roaming\NetMR Ltd\ user folder.
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.
There can be a few default settings files. The priority is as follows, highest priority first:
1.An options file saved with the tables file. This will have a name comprising the tables file name followed by "Spreadsheet Options".
2.A default settings file in the project folder called "Default Spreadsheet Options". This can be saved using the Analysis Main window menu, or from here using the "As folder defaults" button.
3.A global default settings file saved using the Analysis Main window menu option.
4.The defaults set in the Companion.
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 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.
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.
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.