1. Home
  2. FSPREADSHEET procedure

FSPREADSHEET procedure

Creates a Genstat spreadsheet file (GWB or GSH) from specified data structures, PC Windows only (D.B. Baird).

Options

CURSOR = variateA variate of length 2 giving the active cell position (x,y) when the spreadsheet is first displayed

OUTFILE = text Name of GSH file to store data in
SHEET = number Sequence number of existing sheet, if this is set to 0 the data will be added to the first compatible spreadsheet open in the Windows interface
METHOD = string token What to do with any existing columns with the same names as the new columns (replace, rename); default rena
READONLY = string token Whether to make the complete sheet read-only (yes, no); default no
TITLE = text The title associated with the spreadsheet
POINTER = pointer or text A pointer or a name of a pointer to the columns in the spreadsheet
ANALYSIS = text Genstat directives to analyse columns in the spreadsheet
ASETUP = text Genstat directives to be run once before the analysis of any columns in the spreadsheet
ADUMMY = text The name of the dummy (if any) used in the ANALYSIS directives
NOUNITS = string token Whether to stop the inclusion of a units column in the spreadsheet (yes, no); default no
BOOK = number Window number of existing book, if this is set to 0 the sheet will be created in a new book, if to -1 it will be created in the last book formed with BOOK=0, and if set to -2 it will be created in the last book created in the Windows interface.
PAGENAME = text The 32 character text to be displayed on the sheet tab
ROWCOLOURS = factor The factor to be used for colouring the rows (the factor must have colours defined by the FACCOLOURS parameter)
TABLEFORMAT = string token The format to use when displaying tables with two or more classifying factors (page, column); default page
FILEFORMAT = string token The format to use for the spreadsheet file (GWB, GSH); default GWB
MARGINNAME = text The 60 character text to be displayed for the margin labels
FROZENCOLUMNS = scalar The number of columns to freeze on the left hand side of the spreadsheet; default 0 i.e. none

Parameters

DATA = identifiers Data to write to the spreadsheet
PROTECT = string tokens Whether to protect each data column by making it read-only (yes, no); default no
FACCOLOURS = variates, texts or pointers Specifies background colours for factor columns
FOREGROUND = variate, text, scalar or pointer Specifies foreground colours for columns
BACKGROUND = variate, text, scalar or pointer Specifies background colours for columns
HIDDEN = string tokens Whether to hide each DATA column (yes, no); default no

Description

FSPREADSHEET can be used to create a new spreadsheet or spreadsheet file, or to update a spreadsheet already open in the windows interface.

The DATA parameter lists the data structures to put into the spreadsheet. FSPREADSHEET regards the following structures as compatible:

●     variates, factors or texts with identical lengths and restrictions,

●     one-way tables with the same classifying factor, and

●     multi-way tables with the same classifying factors (when option TABLEFORMAT=column), and

●     scalars.

Structures that are compatible with each other are put into a single page of the spreadsheet. Matrices, diagonal matrices, symmetric matrices and incompatible structures go into separate pages. Multi-way tables (with two or more dimensions) also use separate pages when option TABLEFORMAT=page (see below). If the spreadsheet is being displayed in the Genstat Client, the structures are sorted into compatible groups each of which is displayed on a separate page.

The TABLEFORMAT option controls how tables with two or more classifying factors are displayed, with settings:

    page to put each table onto a separate page, with the last classifying factor displayed across the columns, and
    column to put each table into a single column, so that several tables are displayed on a single page.

The default is TABLEFORMAT=page.

By default, FSPREADSHEET writes the spreadsheet in the more recent GWB format, but you can set option FILEFORMAT=gsh to use the older GSH format instead.

If vectors unit labels, they will be included by default as the initial column of the spreadsheet. However, you can set option NOUNITS=yes to exclude them.

You can set option READONLY=yes to make the entire spreadsheet read-only (so that its contents cannot be changed). Alternatively, you can use the PROTECT parameter to protect any individual column (by making it read-only). Settings of the PROTECT parameter override the setting of the READONLY option. You can use set the HIDDEN parameter to yes to hide columns. The FROZENCOLUMNS option allows you to specify a number of columns on the left, that must not scroll off the screen when you scroll to the right.

If OUTFILE is set, the output is sent to the specified file; otherwise a new spreadsheet containing the data is formed and displayed in the Genstat Client. The SHEET and METHOD options are for updating open spreadsheets.

The number provided by SHEET is the position of the spreadsheet in the list of currently open spreadsheets. Thus SHEET=1 will add or update data in the first spreadsheet in the window list, SHEET=2 the second etc. Setting SHEET=0 will cause Genstat to update the first sheet with matching stuctures (i.e. for a variate this will be a VECTOR sheet with the same number of rows). The Genstat interface uses internal pointers to the spreadsheet structures which appear as large integers starting from 100000, and these should not be re-used in your saved code as they depend on how many spreadsheets have been opened from the start of the session. The BOOK option allows you to specify the particular book that the sheet is to be created in (if SHEET is not set). Setting BOOK=0 will cause the sheet to be placed in a new book, even if the default option in the Windows interface is to add sheets from the server to the last book with focus, and setting BOOK=-1 will cause the sheet to be added to the last book created by FSPREADSHEET with BOOK=0. Setting BOOK=-2 will cause the sheet to be added to the last created spreadsheet. The PAGENAME option allows the tab name displayed when multiple sheets are in a book to be specified, and the MARGINNAME option specifies the labels to use for the margins of tables.

The METHOD option controls what happens when new columns have the same names as existing columns of the spreadsheet, with settings:

    replace to replace existing columns by new columns that have the same name, or
    rename to retain the old columns, and rename new columns whose names are the same as those of existing columns.

By default METHOD=rename.

The TITLE option can supply textual information (e.g. a title or a description) to be stored with the spreadsheet. The CURSOR option specifies the current cell to have focus when the spreadsheet is opened in a window. The POINTER, ANALYSIS, ASETUP and ADUMMY options allow Genstat directives to be attatched to the spreadsheet for use in the Spread > Sheet > Analysis menu.

The colours displayed in the cells of a spreadsheet can be controlled by using the FOREGROUND and BACKGROUND parameters to specify the foreground and background colours of the cells in each column. The setting can contain either colour names or RGB values (see the PEN directive for details). You can specify a scalar or a text of length one if all the cells in a column have the same colour. You can specify a variate or text with several values to define different a colour for each cell. Finally you can specify a single pointer to a set of variates or texts if the corresponding DATA setting will need several columns in the spreadsheet. Alternatively, you can specify the background columns for factor columns using the FACCOLOURS parameter. This should be set to a variate or text with the same number of values as the number of levels of the factor. You can apply the colours defined for background of each cell of a factor to the cell’s complete row by setting the ROWCOLOURS option to the identifier of the factor. A missing value, empty string or undefined setting for any of these parameters will retain the default colour for the foreground or background.

FSPREADSHEET cannot create GWB files containing more than one sheet. To add further sheets to a GWB file, you can use EXPORT with option METHOD=add.

Options: OUTFILE, SHEET, METHOD, READONLY, TITLE, POINTER, ANALYSIS, ASETUP, ADUMMY, CURSOR, NOUNITS, BOOK, PAGENAME, ROWCOLOURS, TABLEFORMAT, FILEFORMAT, MARGINNAME, FROZENCOLUMNS.

Parameters: DATA, PROTECT, FACCOLOURS, FOREGROUND, BACKGROUND, HIDDEN.

Method

Internal directives are used to write the data to a GWB or GSH file which holds the data in a binary format. A message is sent to the Windows interface to read this file if OUTFILE is not set.

Action with RESTRICT

Restrictions on the structures are included in the spreadsheet created. If the restrictions on the structures are not consistent, a fault will occur.

See also

Directive: SPLOAD.

Procedures: TABTABLE, EXPORT, IMPORT, DDEEXPORT, DBEXPORT.

Commands for: Input and output.

Updated on June 19, 2019

Was this article helpful?