Genstat spreadsheets can have multiple pages within a Window, as can be seen in spreadsheet applications such as Microsoft Excel. Within Genstat a multi-paged spreadsheet is known as a book, and the pages within the book are known as sheets. The pages have associated tabs which are displayed across the top of the window (as shown below). If a book only contains one sheet then the tab is not displayed.
New sheets can be added to any book from any source, such as the Genstat server, other files including older Genstat Spreadsheet (GSH) file formats and foreign data sets, and from DDE, ODBC and clipboard sources. Each sheet is independent of the other sheets in the book and are updated to the server independently. The order in which sheets are updated to the server is dependent on the each sheet type, where vector and scalar sheets are updated first, then matrix sheets, and finally table sheets.
Genstat Book (GWB) File format
Multi-paged books can be saved using the Genstat Book (GWB) format. This is a more compact format than the Genstat spreadsheet (GSH) format, and can be compressed (the option for this is found in the Spreadsheet Options | Books tab). Individual pages of a book can be saved in GSH format, or as any of the other file formats Genstat supports for export. By default, a single paged book is saved as a GSH file, and multi-paged books are saved as a GWB file. If a single page is saved from a multi-paged book to either a GSH file or another foreign data format, you will get a warning that the other pages will not be saved to this file.
Spreadsheet Book Menu
The sheets within a book can be manipulated using the Spread>Book menu shown below.
A shortcut menu can be opened by right clicking the mouse on any tab within the book (see below).
Changing the Displayed Sheet
The currently displayed sheet is shown by a highlighted tab and can be changed by using the Sheet | Book | Display Sheet menu. Individual sheets can be displayed by clicking on the tab for the sheet. The two shortcut keys CTRL+Page Up and CTRL+Page Down can be used to navigate through the tabs by displaying the previous and next sheet respectively. The tab navigation buttons shown below can also be used to navigate through the tabs.
Tool Tips for Tabs
If you move the mouse over a tab in a book, a tool tip is displayed providing details of the data within the sheet that the tab belongs to.
Each sheet requires a name and by default these are Sheet1, Sheet2, etc… The name for a sheet can be changed using the Spread | Book | Rename Sheet menu. Alternatively, a sheet can be renamed by double clicking on a tab. This opens a dialog where a new name can be entered (see below). The maximum length of a sheet name is 32 characters, and any characters can be used in the name.
The File | New – Spreadsheet menu allows new pages to be added to a book, by selecting the appropriate book name from the Create in Book list as shown below.
Sheets can also be added to books by selecting the Spread | Book | Add Sheet menu item. This opens the same dialog as with the File | New – Spreadsheet, but selects the currently active book in the Create in Book list.
The Spread | Book | Delete Sheet menu can be used to delete sheets from a book. Multiple sheets can be deleted at one time, but you will be prompted on each sheet that is deleted, as deleting sheets cannot be undone. Sheets can also be deleted by clicking on the sheets tab and dragging it off the top of the book window. The cursor will to show a delete symbol when the cursor is in a position to indicate that the sheet can be deleted (see below).
The sheets in a book can be reordered using the Spread | Book | Reorder Sheets menu. Individual tabs can also be moved by clicking on the sheets tab and dragging it to a new position in the list. A yellow triangle indicates the position where the sheet will be inserted, as shown below.
The sheets in a book can be split into separate books using the Spread | Book | Split menu. Individual sheets can be moved to a new book by clicking on the sheets tab and dragging it off the window, and dropping it anywhere on another book window as shown below.
If a sheet is dropped on another book window, the sheet will be placed at the end of this book. The cursor changes to have a plus when the tab is dragged over another book, as shown below.
Saving all pages to a single Excel file
A book can be saved to a multi-paged Excel file using the File | Save as menu item. You can select one or more sheets from the book to be saved within the Excel file. Note that if the Excel file already exists it will be overwrite the whole file.
General Options for Books
The Books Tab in the Tools | Spreadsheet Options can be used to control the behaviour when updating the server, opening new spreadsheets from foreign data files and from the server.
Using the FSPREADSHEET procedure
The FSPREADSHEET procedure includes two options, BOOK and PAGENAME, that can be used to specify details about which book a sheet is added to, and to specify the tab name. For example, the following command shows the syntax required to add a sheet with the tab name ‘Estimates’ to the second book in the windows list. If you want to force a sheet to go into a new book (despite the options set in the Book Options), use BOOK=0.
FSPREADSHEET [BOOK=2;PAGE='Estimates'] Est,SE,TValue,Probability
Using the SPLOAD directive
The SPLOAD directive can be used to import Genstat book (GWB) files, and includes a parameter called SHEETNAME that can be used to import individual sheets from the book.
SPLOAD 'Book.GWB'; SHEETNAME='Sheet2' SPLOAD 'Book.GWB'; SHEETNAME=!T('Sheet3','Sheet5') SPLOAD 'Book.GWB'; SHEETNAME=2 SPLOAD 'Book.GWB'; SHEETNAME=!(3,5)