Legato
Legato

GoFiler Legato Script Reference

 

Legato v 1.5b

Application v 5.24b

  

 

Chapter EightData Functions (continued)

8.6 Data Sheet

8.6.1 Overview

The Data Sheet Object is an underlying component to a number of views and data controls within the application. It is essentially a mini spreadsheet/database specifically designed to control a matrix of data. Unlike a simple table of strings within Legato, a Data Sheet Object manages a collection of cells that can contain a wide variety of data.

Data Sheet Objects can be used without controls and views and can be a convenient way to work with complex table data. When working windows employing Data Sheets, scripts use high-level function since they will manage undo as well as many other high-level functions.

A Data Sheet Object can also be useful for managing large chunks tabular data and interchanging such data with HTML, CSV, text and Excel.

The sheet is essentially a two-dimensional array of cells (table). The above is a visual representation of a Data Sheet Object as part of a Data Control or Data View. The control can be part of a dialog control while many view employ Data View as a basic that will then employ multiple Data Sheets Objects.

In each of the above examples, the underlying data management object is the Data Sheet. Each tab of the form or workbook is a Data View which in turn relies on a Data Sheet Object. As a control:

With the Data Control, a single Data Sheet supports the window.

A principal difference in the characteristics between a simple string table and a data sheet is that data sheets allows cells (elements) to be merged (spanned). Cells can also have many more attributes ranging from names, styles, typing and other characteristics. Further, optional column and row array information can be used to control the cell matrix. Finally, while a Legato array can have key names, a Data Sheet can have row and column names and each cell can have a name.

The Data Sheet object stores the information as 8-bit null terminated strings, normal as UTF-8 encoding. While object does not concern itself with the content, Data Control and Data View treat the content as UTF-8. The maximum size of the data is 2MB.

Each cell can have the following data components:

Display Data — This is generally what is being display on the screen or the cell’s data. Depending on the type of cell, it can be as simple as 1 or 0 for checkbox or up to 2MB text such as of HTML data. This is considered the cell’s text.

Aggregate Attributes — Up to 2MB total of fields supporting the cell. So far as the actual Data Sheet Object, the only field that is directly referenced and used is the cell name. All other fields are arbitrarily named to support activities of the function that rely on the Data Sheet Object. The fields are as follows:

Field Type Data — Used to support complex cell display types.

Edit/Native Data — The native data as entered. for reformatted cells, the text is the displayed data, this value would be the raw data. For example, the display may be ‘50%’ while the raw data is ‘.5’.

Cell Name — The cell’s name. Not that uniqueness is not enforced by the Data Sheet Object.

Description — A general description.

Tip — A general tip. Can be used for hover popup windows.

Comment — A general comment.

Review — Review information.

Caller Private A — General data.

Caller Private B — General data.

Script Events — Events, if enabled by the view.

Flags (Type) — Control flags including the cell’s type;

Style Index — An index to the Data Sheet Style Table.

Cell Dimensions — The measured height and width of the cell’s display content.

Cell Span Data — Spanning information for columns and rows.

The DataSheetGetCell function will retrieve all the cells characteristics.

8.6.2 Operation

A Data Sheet object is accessed via a Data Sheet Object Handle which can be retrieved from a related objects such as Data Controls or Data View windows. A Data Sheet can also be created using the DataSheetCreate function.

8.6.3 Rows and Columns

Obviously, as a spreadsheet database, the Data Sheet contains rows and columns. However, the rows and columns can also have arrays to control column and row characteristics. For certain objects, such as one obtained from a Data View window, the Data Sheet will always have column and row array.

The column array gives each column physical display characteristics such as width and visibility. If unnamed, columns are lettered from A to ZZZ and rows from 1 to x (like a spread sheet) but are accessed programmatically by zero-based index position.

Display dimensions are not relevant to the Data Sheet Object but are used by any display object using a data sheet. The dimensions are measured in screen pixels but can be specified as CSS style units. It is up to the display window to interpret and adjust such dimensions.

Each entry in the column array has flags that control certain behaviors, these are as follows:

  Definition   Bitwise Flags   Description  
  User Settable        
    DS_CC_NO_RESIZE   0x00000001   User Cannot Resize  
    DS_CC_ALLOW_DRAG   0x00000002   User Can Drag and Drop Columns  
    DS_CC_ALLOW_SELECT   0x00000004   User Can Select  
    DS_CC_ALLOW_RENAME   0x00000008   User Can Rename  
    DS_CC_ALLOW_PRESS   0x00000010   User Can Press Button (sort)  
  Contents        
    DS_CC_DEFAULT_NAME   0x00000020   Default Name (A, B, C)  
    DS_CC_DIRTY   0x00000040   Column Needs Recalculation  
  Select (Highlight)        
    DS_CC_HIGHLIGHT   0x00000080   Column is Highlighted (legend)  
  Column (match DC_ style) Text        
    DS_CC_TEXT_MODE_MASK   0x00000C00   Text Mode Mask (Left, Right, etc)  
    DS_CC_TEXT_DEFAULT   0x00000000   Default (Left Text)  
    DS_CC_TEXT_LEFT   0x00000400   Left Text  
    DS_CC_TEXT_CENTER   0x00000800   Center Text  
    DS_CC_TEXT_RIGHT   0x00000C00   Right Text  
  Protection        
    DS_CC_READ_ONLY   0x00001000   Column is Read-Only  
    DS_CC_PROTECTED   0x00002000   Column is Protected (password)  
  Sorting        
    DS_CC_SORT_TYPE_MASK   0x000F0000   Sort Type Mask  
    DS_CC_SORT_NONE   0x00000000   Sort Not Allows  
    DS_CC_SORT_TEXT   0x00010000   Sort Text (w/ case sensitivity)  
    DS_CC_SORT_TEXT_NO_CASE   0x00020000   Sort Text (no case sensitivity)  
    DS_CC_SORT_TEXT_NUMERIC   0x00030000   Sort Text/Numbers (as File List)  
    DS_CC_SORT_NUMERIC   0x00040000   Sort Treat as Auto Number  
    DS_CC_SORT_DATE_AUTO   0x00050000   Sort Treat as Auto Date  
  Control (Private)        
    DS_CC_HIDDEN   0x00100000   Column is Hidden  
    DS_CC_DISABLED   0x00200000   Column is Disabled  
    DS_CC_SORT_MASK   0x00C00000   Column Sort Mask  
    DS_CC_SORT_ACTIVE   0x00400000   Column Sort is Active (only one)  
    DS_CC_SORT_DESCEND   0x00800000   Sort Descending/Ascending  
  Reserved        
    DS_CC_RESERVED   0xFF000000   Reserved (internal flags)  

 

Each entry in the row array has flags that control certain behaviors, these are as follows:

  Definition   Bitwise Flags   Description  
  Masks        
    DS_RC_SHEET_FLAG_MASK   0x0000FFFF   Mask for Data Sheet Flags  
    DS_RC_CALLER_FLAG_MASK   0xFFFF0000   Mask for Caller Flags (row legend button)  
  User Settable        
    DS_RC_NO_RESIZE   0x00000001   User Cannot Resize  
    DS_RC_ALLOW_DRAG   0x00000002   User Can Drag and Drop Rows  
    DS_RC_ALLOW_SELECT   0x00000004   User Can Select  
    DS_RC_ALLOW_RENAME   0x00000008   User Can Rename  
    DS_RC_ALLOW_PRESS   0x00000010   User Can Press Button  
  Display Label Mode        
    DS_RC_LABEL_MASK   0x00000C00   Vertical Text Mode Mask  
    DS_RC_LABEL_LEFT   0x00000000   Left Text (default)  
    DS_RC_LABEL_CENTER   0x00000400   Center Text  
    DS_RC_LABEL_RIGHT   0x00000800   Right Text  
  Display Label Mode        
    DS_RC_TEXT_VERT_MASK   0x00000300   Vertical Text Mode Mask  
    DS_RC_TEXT_DEFAULT   0x00000000   Default (middle)  
    DS_RC_TEXT_MIDDLE   0x00000100   Middle (default)  
    DS_RC_TEXT_TOP   0x00000200   Top Text  
    DS_RC_TEXT_BOTTOM   0x00000300   Bottom Text  
  Protection        
    DS_RC_READ_ONLY   0x00001000   Row is Read-Only  
    DS_RC_PROTECTED   0x00002000   Row is Protected  
  Control (Private)        
    DS_RC_CARET   0x00000020   Row is Caret Position  
    DS_RC_HIDDEN   0x00000040   Row is Hidden  
    DS_RC_DISABLED   0x00000080   Row is Disabled  
    DS_RC_SELECTED   0x00004000   Row is Selected  
    DS_RC_DIRTY   0x00008000   Row Needs Recalculation  

 

8.6.4 Cells and Cell Data

Each cell is an x/y or row/col position in the cell matrix. When a sheet is created, the matrix exists but actual cell records may not. A cell is considered null if there is no data associated with it. As soon as any property or data is applied to a cell, a cell record is created. When a null cell is accessed, the data return will be the same as if all data was reset. Accessing a null cell does not create a cell record.

The cell record contains three main pieces of data: display data, cell attributes and cell type. The Data Sheet object generally is not concerned with the cell contents except in the case of controls such as combo boxes which require cell object data. The display data is usually the text that is displayed. If the data sheet is treated as a simple matrix, a script may simply be concerned with the display data. Display data can also be very simple, such as 0 or 1 for a check box. The legend of the checkbox is stored as an attribute.

There are numerous attributes that can be associated with a cell. These include the edit data, cell name, tip, comments, extended type data and private information. In addition, the display style of the cell can be specified as an index to a style table.

Finally, the cell type specifies to the display window how the cell is displayed and edited.

Cell flags are defined as follows:

  Definition   Bitwise Flags   Description  
  Display Type        
    DS_CF_DISPLAY_TYPE_MASK   0x0000000F   Mask for Type Data  
    DS_CF_DISPLAY_TEXT   0x00000000   Text Cell (multiple, icons)  
    DS_CF_DISPLAY_CHECKBOX   0x00000001   Display Check Box  
    DS_CF_DISPLAY_CHECKBOX_AUTO   0x00000002   Display Check Box (auto)  
    DS_CF_DISPLAY_RADIO   0x00000003   Display Radio Button  
    DS_CF_DISPLAY_RADIO_AUTO   0x00000004   Display Radio Button (auto)  
    DS_CF_DISPLAY_PUSHBUTTON   0x00000005   Display Push Button  
    DS_CF_DISPLAY_PUSHBUTTON_TOGGLE   0x00000006   Display Push Button (auto)  
    DS_CF_DISPLAY_COMBOBOX   0x00000007   Display Combo Box Select  
    DS_CF_DISPLAY_COMBOBOX_EDIT   0x00000008   Display Combo Box and Edit  
    DS_CF_DISPLAY_LISTBOX   0x00000009   Display List Box  
    DS_CF_DISPLAY_IMAGE   0x0000000A   Display Image/Picture  
    DS_CF_DISPLAY_V_SCROLL   0x0000000B   Display Vertical Scroll  
    DS_CF_DISPLAY_H_SCROLL   0x0000000C   Display Horizontal Scroll  
    DS_CF_DISPLAY_HTML   0x0000000D   Display HTML  
    DS_CF_DISPLAY_NUMERIC   0x0000000E   Display Numeric (original in edit)  
    DS_CF_DISPLAY_PROGRESS   0x0000000F   Display As Progress  
  Display Attributes        
    DS_CF_NULL_CELL   0x00000010   Cell is not Displayed or Navigated  
    DS_CF_CALCULATE   0x00000020   Field is Calculable (by caller)  
    DS_CF_NO_CLIP   0x00000040   Display can Extend Outside of Cell  
  Protection        
    DS_CF_READ_ONLY   0x00000100   Cell is Read-Only  
    DS_CF_PROTECTED   0x00000200   Cell is Protected (password)  
    DS_CF_OPEN_AS_DISABLED   0x00001000   Cell Open as Disabled (Saved)  
    DS_CF_OPEN_AS_HIDDEN   0x00002000   Cell Open as Hidden (Saved)  
  d_data Control Items        
    DS_CF_D_DATA_LINES   0x00000400   Specifies Wrapped Line Count  
  Display and Cell Control        
    DS_CF_CONTROL_MASK   0x000F0000   Cell Control Mask  
  Dereferenced Pointers (Offset)        
    DS_CF_DRP_MASK   0x00030000   Dereferenced Pointers Mask  
    DS_CF_DRP_DISPLAY   0x00010000   Display is a Non-Pool Pointer  
    DS_CF_DRP_AA   0x00020000   Attributes is a Non-Pool Pointer  
  Cell Highlighting/State        
    DS_CF_HIGHTLIGHT_MASK   0x00F00000   Highlight Index Mask  
    DS_CF_DISABLED   0x01000000   Cell Disabled  
    DS_CF_HIDDEN   0x02000000   Cell is Hidden (transparent)  
    DS_CF_DOG_EAR_MASK   0x3C000000   Cell Dog Ear Mask  
    DS_CF_DOG_EAR_TIP_NW   0x04000000   Cell has Top Left Dog Ear Tip  
    DS_CF_DOG_EAR_TIP_NE   0x08000000   Cell has Top Right Dog Ear Tip  
    DS_CF_DOG_EAR_TIP_SE   0x10000000   Cell has Bottom Right Dog Ear Tip  
    DS_CF_DOG_EAR_TIP_SW   0x20000000   Cell has Bottom Left Dog Ear Tip  
  Cell Processing        
    DS_CF_DIRTY   0x80000000   Cell Size/Data Must be Calculated  

 

Display data flags are as follows (note these are context sensitive):

  Definition   Bitwise Flags   Description  
  As Text Field        
    DS_CD_TEXT_LINES_MASK   0x0000FFFF   Specifies Wrapped Line Count  
    DS_CD_TEXT_ALLOW_WRAP   0x00080000   Cell Data Can Wrap  
    DS_CD_TEXT_LINK   0x00100000   Cell is a link (href in type)  
  As Combo Box or List        
    DS_CD_OBJECT_INDEX_MASK   0x00FFFFFF   Mask for Object Index  
    DS_CD_OBJECT_CONTROL_MASK   0xFF000000   Mask for Object Control  
    DS_CD_OBJECT_FLAG   0x80000000   Is Object  
  Specific Types        
    DS_CD_LIST_COMBO_NOTIFY   0x10000000   Notify on Combo Actions (drop)  
    DS_CD_LIST_MULTI_SELECT   0x20000000   Allow Multi-Select (list only)  
    DS_CD_LIST_STORE_SELECT_INDEX   0x40000000   Store List Indices (else string)  
  As Numeric Field (matches DV Codes)  
    DS_CD_NUMERIC_DECIMAL_PLACE_MASK   0x000000FF   Precise Digit (- rounds number)  
  Display Options        
    DS_CD_NUMERIC_INVERT_CREDIT_DEBIT   0x00000100   Treat Numbers as Credit/Debit Values  
    DS_CD_NUMERIC_NEG_IN_HOLE   0x00000200   Show Negative Numbers in Hole (123)  
    DS_CD_NUMERIC_NEG_IN_RED   0x00000400   Show Negative Numbers As Red  
    DS_CD_NUMERIC_USE_1000   0x00000800   Use Thousand Separators  
    DS_CD_NUMERIC_ZERO_AS_DASH   0x00001000   Replace Zero with '--'  
  Sub Types        
    DS_CD_NUMERIC_SUB_MASK   0x0F000000   Numeric Sub Type Mask  
    DS_CD_NUMERIC_ACCOUNTING   0x00000000   Accounting Numbers  
    DS_CD_NUMERIC_PERCENTAGE   0x01000000   Percentage  
    DS_CD_NUMERIC_FRACTION   0x02000000   Fractions  
    DS_CD_NUMERIC_WHOLE   0x03000000   Whole Numbers  
    DS_CD_NUMERIC_REAL   0x04000000   Real (float) Numbers  
    DS_CD_NUMERIC_SCIENTIFIC   0x05000000   Scientific Exp Numbers  
  Currency (accounting only)        
    DS_CD_NUMERIC_CURRENCY_MASK   0x00F00000   Currency Mask  
    DS_CD_NUMERIC_NO_CURRENCY   0x00000000   Currency None  
    DS_CD_NUMERIC_US_DOLLAR   0x00100000   Currency Dollars  
    DS_CD_NUMERIC_EURO   0x00200000   Currency Euros  
    DS_CD_NUMERIC_POUND   0x00300000   Currency Pounds  
    DS_CD_NUMERIC_YEN   0x00400000   Currency Yen  

 

8.6.5 Cell Style

A style index is stored as part of a Data Sheet Object. The styles can be edited, added and enumerated. Certain views such as the raw Data View will allow the style information to be edited by the user. In other cases, such as various EDGAR views, the style information shapes how that display appears to the user.

8.6.6 Edit Management

As part of the base functionality of a data sheet, edit transaction and undo management is supplied. When enabled, certain actions to the data sheet can be undone. When the Data Sheet is used with a view, the editing should be performed via the view which will handle its own edit management.

Page revised 2024-10-21