Contents

• 1. Constants
  • 1.1. File types
  • 1.2. Cell types
  • 1.3. Font names
  • 1.4. Font sizes
  • 1.5. Font styles
  • 1.6. Horizontal justification
  • 1.7. Word wrapping
  • 1.8. Underlining
  • 1.9. Border styles
  • 1.10. Cell formats
  • 1.11. Copy options
  • 1.12. Goal seek result codes
  • 1.13. Sort direction
  • 1.14. Month and day order
• 2. Utility classes
  • 2.1. class GoalSeekContext
  • 2.2. class SortContext
• 3. Global functions
  • 3.1. init
  • 3.2. getVersionInfo
  • 3.3. stopOnExcelWarning
  • 3.4. setDefaultExcelMode
  • 3.5. getTypeString
  • 3.6. getActiveWorkbook
  • 3.7. textToRC
  • 3.8. rcToText
• 4. Classes
  • 4.1. class Workbook
  • 4.2. class Worksheet
  • 4.3. class Border
  • 4.4. class Range

$SS_NEXS_XS3 = SS_NEXS_XS3

NExS XS3 format

$SS_EXCEL_95 = SS_EXCEL_95

Microsoft Excel 95 format

$SS_EMPTY_CELL = SS_EMPTY_CELL

Cell has no contents

$SS_OVERFLOW_CELL = SS_OVERFLOW_CELL

Cell has no contents but an adjacent cell spills over into it

$SS_NUMERIC_CELL = SS_NUMERIC_CELL

Cell contains a number

$SS_TEXT_CELL = SS_TEXT_CELL

Cell contains a text string

$SS_FORMULA_CELL = SS_FORMULA_CELL

Cell contains a formula

$SS_CHART_CELL = SS_CHART_CELL

Cell contains a chart

$SS_FONT_FAMILY_DEFAULT = SS_FONT_FAMILY_DEFAULT

Default font

$SS_FONT_FAMILY_COURIER = SS_FONT_FAMILY_COURIER

Courier

$SS_FONT_FAMILY_TIMESROMAN = SS_FONT_FAMILY_TIMESROMAN

Times Roman

$SS_FONT_FAMILY_HELVETICA = SS_FONT_FAMILY_HELVETICA

Helvetica/Arial

$SS_FONT_FAMILY_SOUVENIR = SS_FONT_FAMILY_SOUVENIR

Souvenir

$SS_FONT_FAMILY_LUBALIN = SS_FONT_FAMILY_LUBALIN

Lubalin

$SS_FONT_SIZE_DEFAULT = SS_FONT_SIZE_DEFAULT

Default font size

$SS_FONT_SIZE_8PT = SS_FONT_SIZE_8PT

8 point

$SS_FONT_SIZE_10PT = SS_FONT_SIZE_10PT

10 point

$SS_FONT_SIZE_12PT = SS_FONT_SIZE_12PT

12 point

$SS_FONT_SIZE_14PT = SS_FONT_SIZE_14PT

14 point

$SS_FONT_SIZE_18PT = SS_FONT_SIZE_18PT

18 point

$SS_FONT_SIZE_24PT = SS_FONT_SIZE_24PT

24 point

$SS_FONT_SIZE_36PT = SS_FONT_SIZE_36PT

36 point

$SS_FONT_STYLE_DEFAULT = SS_FONT_STYLE_DEFAULT

Default font style

$SS_FONT_STYLE_NORMAL = SS_FONT_STYLE_NORMAL

Normal (plain)

$SS_FONT_STYLE_BOLD = SS_FONT_STYLE_BOLD

Bold

$SS_FONT_STYLE_ITALIC = SS_FONT_STYLE_ITALIC

Italic

$SS_FONT_STYLE_BOLDITALIC = SS_FONT_STYLE_BOLDITALIC

Bold and Italic

$SS_HJUST_DEFAULT = SS_HJUST_DEFAULT

Default horizontal justification

$SS_HJUST_LEFT = SS_HJUST_LEFT

Left justified

$SS_HJUST_RIGHT = SS_HJUST_RIGHT

Right justified

$SS_HJUST_CENTER = SS_HJUST_CENTER

Centered

$SS_HJUST_AUTO = SS_HJUST_AUTO

Auto justification

$SS_WRAP_DEFAULT = SS_WRAP_DEFAULT

Default word wrapping

$SS_WRAP_OFF = SS_WRAP_OFF

Word wrapping off

$SS_WRAP_ON = SS_WRAP_ON

Word wrapping on

$SS_UNDERLINE_DEFAULT = SS_UNDERLINE_DEFAULT

Default underlining

$SS_UNDERLINE_OFF = SS_UNDERLINE_OFF

Underlining off

$SS_UNDERLINE_ON = SS_UNDERLINE_ON

Underlining on

$SS_BORDER_NONE = SS_BORDER_NONE

No border

$SS_BORDER_THIN = SS_BORDER_THIN

Thin line border

$SS_BORDER_MEDIUM = SS_BORDER_MEDIUM

Medium line border

$SS_BORDER_DASHED = SS_BORDER_DASHED

Dashed line border

$SS_BORDER_DOTTED = SS_BORDER_DOTTED

Dotted line border

$SS_BORDER_THICK = SS_BORDER_THICK

Thick line border

$SS_BORDER_DOUBLE = SS_BORDER_DOUBLE

Double line border

$SS_BORDER_HAIR = SS_BORDER_HAIR

Hair line border

$SS_FMT_DEFAULT = SS_FMT_DEFAULT

Default format

$SS_FMT_FLOAT = SS_FMT_FLOAT

Floating point format

$SS_FMT_FIXED = SS_FMT_FIXED

Fixed point format

$SS_FMT_GEN = SS_FMT_GEN

General format

$SS_FMT_DOLLARS = SS_FMT_DOLLARS

Dollar format

$SS_FMT_COMMA = SS_FMT_COMMA

Comma format

$SS_FMT_HEX = SS_FMT_HEX

Hexadecimal format

$SS_FMT_LOGIC = SS_FMT_LOGIC

Logical format

$SS_FMT_DAY_MONTH_YEAR = SS_FMT_DAY_MONTH_YEAR

Date format: DD-MMM-YY

$SS_FMT_DAY_MONTH = SS_FMT_DAY_MONTH

Date format: DD-MMM

$SS_FMT_MONTH_YEAR = SS_FMT_MONTH_YEAR

Date format: MMM-YY

$SS_FMT_DATE = SS_FMT_DATE

Date format: MM/DD/YY

$SS_FMT_HIDDEN = SS_FMT_HIDDEN

Hidden

$SS_FMT_TIME = SS_FMT_TIME

Time format: HH:MM:SS

$SS_FMT_PERCENT = SS_FMT_PERCENT

Percent format

$SS_FMT_TEXT = SS_FMT_TEXT

Text format

$SS_FMT_INTL_DATE = SS_FMT_INTL_DATE

International date: DD.MM.YYYY

$SS_FMT_ISO8061_DATE = SS_FMT_ISO8061_DATE

ISO-8061 date: YYYY-MM-DD

$SS_FMT_DATE_ALT = SS_FMT_DATE_ALT

Alternate date: DD/MM/YY

$SS_COPY_NONE = SS_COPY_NONE

Don't copy style properties

$SS_COPY_FORMAT = SS_COPY_FORMAT

Copy format property

$SS_COPY_FONT = SS_COPY_FONT

Copy font properties

$SS_COPY_COLOR = SS_COPY_COLOR

Copy color properties

$SS_COPY_JUST = SS_COPY_JUST

Copy justification property

$SS_COPY_ALL = SS_COPY_ALL

Copy all

$SS_GS_SUCCESS = SS_GS_SUCCESS

Goal seek completed successfully

$SS_GS_FP_ERR = SS_GS_FP_ERR

Floating point error

$SS_GS_BAD_TARGET = SS_GS_BAD_TARGET

Target cell is not numerical

$SS_GS_BRKT_LIMIT = SS_GS_BRKT_LIMIT

Bracket not found within iteration limit

$SS_GS_ITER_LIMIT = SS_GS_ITER_LIMIT

Solution bracketed, but a solution was not found within the iteration limit

$SS_GS_TARGET_NOT_FORMULA = SS_GS_TARGET_NOT_FORMULA

Target cell does not contain a formula

$SS_GS_VAR_NOT_NUMBER = SS_GS_VAR_NOT_NUMBER

Variable cell does not contain a number

$SS_SORT_DESCENDING = SS_SORT_DESCENDING

Sort items in descending order

$SS_SORT_ASCENDING = SS_SORT_ASCENDING

Sort items in ascending order

$SS_MONTH_THEN_DAY = SS_MONTH_THEN_DAY

US-style month/day/year

$SS_DAY_THEN_MONTH = SS_DAY_THEN_MONTH

European-style day/month/year

GoalSeekContext name varRow varCol targetRow targetCol goal tolerance bracketLow bracketHigh iterations

GoalSeekContext contains the parameters for a goal seek. The Worksheet.performGoalSeek() method modifies bracketHigh, bracketLow, and iterations after using them and sets status.

Parameters:

varRow - the row of the cell to be varied
varCol - the column of the cell to be varied
targetRow - the row of the target cell that is intended to reach the goal
targetCol - the column of the target cell that is intended to reach the goal
goal - the goal value to which the target cell should be driven
tolerance - the absolute value of the minimum allowed difference between the goal and the resulting value in the target cell
bracketLow - the low end of the range containing the goal value; after performing a goal seek, it contains the low end of the bracket determined by the goal seek
bracketHigh - the high end of the range containing the goal value; after performing a goal seek, it contains the high end of the bracket determined by the goal seek
iterations - the number of iterations for which the goal seek should run; after a goal seek, it contains the number of iterations used in seeking the goal

Returns:

a new GoalSeekContext

rename obj {}

Destroys a GoalSeekContext.

Parameters:

none

Returns:

none

-varRow

the row of the cell to be varied

-varCol

the column of the cell to be varied

-targetRow

the row of the target cell that is intended to reach the goal

-targetCol

the column of the target cell that is intended to reach the goal

-goal

the goal value to which the target cell should be driven

-tolerance

the absolute value of the minimum allowed difference between the goal and the resulting value in the target cell

-bracketLow

the low end of the range containing the goal value; after performing a goal seek, it contains the low end of the bracket determined by the goal seek

-bracketHigh

the high end of the range containing the goal value; after performing a goal seek, it contains the high end of the bracket determined by the goal seek

-iterations

the number of iterations for which the goal seek should run; after a goal seek, it contains the number of iterations used in seeking the goal

-status

the status returned by Worksheet.performGoalSeek(); it is one of the GoalSeekStatus values

SortContext name keyCol direction update

A SortContext specifies how a sort is performed. The columns specified as keys are used as primary, secondary, etc. keys to sort the data. Up to five keys may be used and are used in the order specified in the list (i.e., first key in the list is the primary key). The sort uses keys beyond the primary key in cases where the primary does not differentiate the entries. For each key, an order of ascending or descending is specified. If the update field in the context is true, the sort updates cell references according to where the referenced cells have moved.

See Range.sortRows().

Parameters:

keyCol - the primary key column in the sort
direction - the direction to sort; one of the SortDirectionType constants
update - SpreadScript keeps track of what it moves and where and can update cell references throughout the spreadsheet to reflect the changes that occurred while sorting. If update is one (true), it performs this updating, and if update is zero (false), it does not.

Returns:

a new SortContext instance

rename obj {}

Destroys a SortContext.

Parameters:

none

Returns:

none

append keyCol direction

Appends a key column and its sort direction to the list.

Parameters:

keyCol - column to be used as a key in sorting
direction - one of SortDirectionType constants

Returns:

none

prepend keyCol direction

Prepends a key column and its sort direction to the list.

Parameters:

keyCol - column to be used as a key in sorting
direction - one of SortDirectionType constants

Returns:

none

getNumKeys

Gets the number of keys specified in the current list.

Parameters:

none

Returns:

current number of keys in the list

getKey index

Gets the specified key and direction.

Parameters:

index - the index of the sorting key to retrieve

Returns:

a list/array containing the key column and direction for the specified sorting key; column is set to -1 if a key that does not exist is requested

deleteKey index

Deletes the specified sorting key.

Parameters:

index - the key to remove from the list

Returns:

none

-update

public field that controls how cell references are updated

init pLicenseFile

Initialize SpreadScript.

Parameters:

pLicenseFile - the SpreadScript license file

Returns:

none

getVersionInfo

Gets the version information for SpreadScript.

Parameters:

none

Returns:

a string containing the version information

stopOnExcelWarning stopOnExcelWarning

Determines whether warnings generated when reading or writing Excel files are only printed to standard error or throw exceptions. The features of Excel that are not supported by SpreadScript are normally reported (by default) as exceptions. However, if you know that the unsupported features are not required for the files you are using, you may wish to change this setting to false so that the warnings are only printed. Carefully check the results to make sure that data integrity is not adversely affected.

Parameters:

stopOnExcelWarning - when set to false, warnings generated when reading or writing Excel files are printed to standard error; when true, the warnings throw an exception (the default is true)

Returns:

none

setDefaultExcelMode useExcelMode

Sets the global default to use Excel compatibility mode with all newly created Workbook objects. Turning on Excel compatibility mode results in the range syntax using : (e.g., A1:B3) rather than .. (e.g., A1..B3) to specify ranges. Also, the Excel-compatible functions that are normally prefixed with XL, such as XLCHOOSE, are not prefixed with XL. When dealing with Excel files, this mode is best.

Parameters:

useExcelMode - if true, Excel compatibility mode is turned on

Returns:

none

getTypeString type

Converts a cell type constant to a string.

Parameters:

type - the cell type to convert to a string

Returns:

cell type as a string: "Empty", "Overflow", "Number", "Text", "Formula", "Chart"

getActiveWorkbook

Retrieves a reference to the currently active Workbook. Set the active Workbook using Workbook.activate().

Parameters:

none

Returns:

the active Workbook

textToRC pText

Converts from a cell name to two integers representing a row and column. For example, textToRC("A1") results in (1, 1).

Parameters:

pText - the text cell name to convert

Returns:

a two-element list/array containing the row and column

rcToText row column

Converts two integers representing a row and column to cell name. For example, rcToText(1, 1) returns "A1".

Parameters:

row - the row

column - the column

Returns:

the text cell name

Workbook name

Create an empty Workbook.

Parameters:

none

Returns:

empty Workbook

rename obj {}

Destroys a Workbook.

Parameters:

none

Returns:

none

activate

Sets this Workbook as the active Workbook. Currently, a Workbook must be the active Workbook before calling methods on it or on Worksheets in the Workbook.

Parameters:

none

Returns:

none

setExcelMode useExcelMode

Sets this Workbook to use Excel compatibility mode. Turning on Excel compatibility mode results in the range syntax using : (e.g., A1:B3) rather than .. (e.g., A1..B3) to specify ranges. Also, the Excel-compatible functions that are normally prefixed with XL, such as XLCHOOSE, are not prefixed with XL. When dealing with Excel files, this mode is best.

Parameters:

useExcelMode - if true, Excel compatibility mode is turned on

Returns:

none

open pFileName

Opens the specified file and puts the contents into the Workbook.

Parameters:

pFileName - the name of the file in Excel 95/97/2000/XP or NExS XS/XS3 format

Returns:

none

save pFileName fileType

Saves the Workbook to a file.

Parameters:

pFileName - the name of the file
fileType - the type of file (Excel 95 or NExS XS3); see the list of file type constants

NOTE: The Excel 95 file format limits the text in cells to a maximum of 255 characters, and SpreadScript will truncate cells exceeding that. Also, the Excel 95 file format supports up to 256 columns and 16,384 rows. SpreadScript produces an exception for sheets with dimensions exceeding those limits.

Returns:

none

createSheet pName

Creates an empty Worksheet in this Workbook. The Workbook places the Worksheet at the end of its list of Worksheets.

Parameters:

pName - the name of the Worksheet

Returns:

the newly created Worksheet

deleteSheet pSheet

Deletes and destroys the specified Worksheet in this Workbook. Throws an exception if the Worksheet does not exist in this Workbook.

Parameters:

pSheet - the Worksheet to be deleted

Returns:

none

getSheetCount

Gets the number of Worksheets in this Workbook.

Parameters:

none

Returns:

the number of Worksheets in this Workbook

getSheet sheetNum

Gets the specified Worksheet in this Workbook. Throws an exception if the index is not valid.

Parameters:

sheetNum - which Worksheet to get; the first Worksheet is at index 1

Returns:

the Worksheet at the specified position

getSheetByName pSheetName

Gets the Worksheet specified by name in this Workbook. Throws an exception if the named Worksheet does not exist in this Workbook.

Parameters:

pSheetName - the name of the Worksheet to get

Returns:

the Worksheet specified by name in this Workbook

getFileName

Gets the file name for this Workbook.

Parameters:

none

Returns:

the file name of the Workbook or a null reference if it has no file name

delete

Delete and destroy this Worksheet.

Parameters:

none

Returns:

none

getName

Get the name of this Worksheet.

Parameters:

none

Returns:

the name

setName pName

Sets the name of this Worksheet.

Parameters:

pName - the name

Returns:

none

needsRecalc

Indicates whether the sheet needs to be recalculated. May indicate the need to recalc when no recalc is necessary, but is always correct if it returns false (zero).

Parameters:

none

Returns:

true (one) if the sheet requires recalculation

recalc

Recalculates this Worksheet.

Parameters:

none

Returns:

none

getDimensions

Gets the minimum bounding rectangle of non-empty cells in this Worksheet.

Parameters:

none

Returns:

a Range object with the sheet's dimensions

performGoalSeek pGoalSeekContext

Performs a "goal seek" operation that is commonly used in what-if scenarios. It answers the question, "What input generates this result?" It accomplishes this by systematically varying the input until the desired result is achieved.

The cell that is to be varied during the goal seek is indicated by (varRow, varCol). The cell whose value is intended to match the goal value is indicated by (targetRow, targetCol). The SpreadScript goal seek algorithm uses the "regula falsa" (false position) method which requires the target to be bracketed, in this case by the variables bracketLow and bracketHigh. The way it works is as follows: If storing some value bracketLow in cell (varRow, varCol) causes the value in cell (targetRow, targetCol) to be less than the value goal, and storing some other value bracketHigh in (varRow, varCol) caused the value of (targetRow, targetCol) to be greater than goal, then we say that goal is bracketed by bracketLow and bracketHigh. Goal seek works by iteratively closing down the gap between bracketLow and bracketHigh in such a way that the target always remains bracketed. As the gap narrows, the algorithm will eventually find a value which when stored in (varRow, varCol) will cause the value in (targetRow, targetCol) to be equal (within the numerical precision of the computer) to goal. Since that degree of precision is seldom required in real applications, a tolerance parameter is provided. The goal seek algorithm terminates when the absolute value of the difference between the value in (targetRow, targetCol) and goal is less than or equal to tolerance.

The SpreadScript goal seek algorithm does not require bracketLow and bracketHigh to bracket goal initially. If SpreadScript determines that the target is not bracketed, the gap between bracketLow and bracketHigh is expanded in an attempt to bracket goal. Once bracketing has been achieved, the gap is narrowed until the solution is found.

There are a few pathological cases for which goal seek cannot find a solution:

1. A bracket does not exist; i.e., for all possible values that can be stored in cell (varRow, varCol) the value in (targetRow, targetCol) will either be always greater than goal or always less than goal.
2. A bracket exists, but because the function is not continuous there is no value which can be stored in (varRow, varCol) that will cause the value in (targetRow, targetCol) to be equal to goal.
3. A solution exists, but the function is not well behaved on the interval between the bracket values bracketLow and bracketHigh. This generally means that somewhere between bracketLow and bracketHigh the value of (targetRow, targetCol) will go to plus or minus infinity, which causes the algorithm to break down.

To limit the time spent by goal seek in difficult or pathological cases, the parameter maxNumIter will cause the algorithm to give up if a bracket cannot be found within maxNumIter iterations, or once a bracket is found, a solution cannot be found within maxNumIter iterations. Since the final values of bracketLow and bracketHigh are returned to the caller in the GoalSeekContext, a failed goal seek may be continued simply by calling it again. While most functions converge very quickly (linear functions always converge in one or two iterations), some complex functions may simply require more iterations to find a solution. The programmer must decide what action to take in the event that goal seek fails. To assist in this, the following status codes are returned in GoalSeekContext.status:

• SS_GS_SUCCESS - goal seek completed successfully; (varRow, varCol) contains the value that generates the goal in (targetRow, targetCol)
• SS_GS_TARGET_NOT_FORMULA - the target cell (targetRow, targetCol) does not contain a numerical formula, and therefore a solution does not exist.
• SS_GS_VAR_NOT_NUMBER - the variable cell (varRow, varCol) does not contain a numeric constant.
• SS_GS_FP_ERR - a machine floating point error has occurred during the goal seek process, most likely indicating pathological case 3 above.
• SS_GS_BAD_TARGET - the target cell is not numerical; perhaps it is a text string or an error cell
• SS_GS_BRKT_LIMIT - a bracket was not be found after maxNumIter iterations, possibly indicating pathological case 1 above.
• SS_GS_ITER_LIMIT - the target is bracketed, but a solution was not found after maxNumIter iterations, possibly indicating pathological case 2 above.

To facilitate continuation, performGoalSeek does not restore the original contents of cell (varRow, varCol) in the event of failure. It is therefore the programmer's responsibility to save this value if desired.

The performGoalSeek() method modifies bracketHigh, bracketLow, and iterations after using them. Subsequent calls to continue a goal seek thus should pass in the same GoalSeekContext as used in prior calls.

Example:

Given

A1 = 1
A2 = sqrt(A1)

a call to performGoalSeek() with GoalSeekContext containing

varRow = 1
varCol = 1
targetRow = 2
targetCol = 1
goal = 3.14
tolerance = 1e-9
bracketLow = 0
bracketHigh = 10
iterations = 30

converges to a solution with

A1 = 9.8596

and thus the desired goal has been achieved.

Parameters:

pGoalSeekContext - the GoalSeekContext containing the parameters for the goal seek; upon return, bracketLow, bracketHigh, iterations, and status contain updated values

Returns:

none

insertColumns firstCol lastCol

Inserts columns at the specified location. For example, to insert three columns after column two, use insertColumns(3, 5).

Parameters:

firstCol - the column index at which to insert the columns
lastCol - the last column index to insert

Returns:

none

insertRows firstRow lastRow

Inserts rows at the specified location. For example, to insert three rows after row two, use insertRows(3, 5).

Parameters:

firstRow - the row index at which to insert the rows
lastRow - the last row index to insert

Returns:

none

deleteColumns firstCol lastCol

Deletes columns at the specified location.

Parameters:

firstCol - the column index at which to delete the columns
lastCol - the last column index to delete

Returns:

none

deleteRows firstRow lastRow

Deletes rows at the specified location.

Parameters:

firstRow - the row index at which to delete the rows
lastRow - the last row index to delete

Returns:

none

setDefaultFont font

Set the default cell font for this Worksheet.

Parameters:

font - the default font; see the list of font constants

Returns:

none

getDefaultFont

Gets the default cell font for this Worksheet.

Parameters:

none

Returns:

the default font; see the list of font constants

setDefaultFontSize size

Sets the default cell font size for this Worksheet.

Parameters:

size - the default font size; see the list of font size constants

Returns:

none

getDefaultFontSize

Gets the default cell font size for this Worksheet.

Parameters:

none

Returns:

the default font size; see the list of font size constants

setDefaultFontStyle style

Sets the default cell font style for this Worksheet.

Parameters:

style - the default font style; see the list of font style constants

Returns:

none

getDefaultFontStyle

Gets the default cell font style for this Worksheet.

Parameters:

none

Returns:

the default font style; see the list of font style constants

setDefaultHorizontalJustification hjust

Sets the default cell horizontal justification for this Worksheet.

Parameters:

hjust - the default horizontal justification; see the list of horizontal justification constants

Returns:

none

getDefaultHorizontalJustification

Gets the default cell horizontal justification for this Worksheet.

Parameters:

none

Returns:

the default horizontal justification; see the list of horizontal justification constants

setDefaultWordWrap wrap

Sets the default cell word wrapping for this Worksheet.

Parameters:

wrap - the default word wrapping; see the list of word wrapping contants

Returns:

none

getDefaultWordWrap

Gets the default cell word wrapping for this Worksheet.

Parameters:

none

Returns:

the default word wrapping; see the list of word wrapping constants

setDefaultUnderline under

Sets the default cell underlining for this Worksheet.

Parameters:

under - the default underlining; see the list of underlining constants

Returns:

none

getDefaultUnderline

Gets the default cell underlining for this Worksheet.

Parameters:

none

Returns:

the default underlining; see the list of underlining constants

setDefaultFormatAndPlaces format places

Sets the default cell format and number of decimal places for this Worksheet.

Parameters:

format - the default format; see the list of format constants
places - the number of places

Returns:

none

getDefaultFormatAndPlaces

Gets the default cell format and number of decimal places for this Worksheet.

Parameters:

none

Returns:

a list/array containing the default format (see the list of format constants) and decimal places

setDefaultFormat format

DEPRECATED Use setDefaultFormatAndPlaces() instead.
Sets the default cell format for this Worksheet.

Parameters:

format - the default format; see the list of format constants

Returns:

none

getDefaultFormat

DEPRECATED Use getDefaultFormatAndPlaces() instead.
Gets the default cell format for this Worksheet.

Parameters:

none

Returns:

the default format; see the list of format constants

setDefaultPlaces places

DEPRECATED Use setDefaultFormatAndPlaces() instead.
Sets the number of decimal places for numbers in cells in this Worksheet whose format is set to the default format SS_FMT_DEFAULT.

Parameters:

places - the number of places

Returns:

none

getDefaultPlaces

DEPRECATED Use getDefaultFormatAndPlaces() instead.
Sets the number of decimal places for numbers in cells in this Worksheet whose format is set to the default format SS_FMT_DEFAULT.

Parameters:

none

Returns:

the number of places

setDefaultColumnWidth colWidth

Sets the default width for columns in this Worksheet.

Parameters:

colWidth - the default column width in units of 1/100th of the zero character's width

Returns:

none

getDefaultColumnWidth

Gets the default width for columns in this Worksheet.

Parameters:

none

Returns:

the default column width in units of 1/100th of the zero character's width

setDefaultRowHeight rowHeight

Sets the default height for rows in this Worksheet.

Parameters:

rowHeight - the default row height in units of 1/20th of a point, also known as twips (twentieths of a point)

Returns:

none

getDefaultRowHeight

Gets the default height for rows in this Worksheet.

Parameters:

none

Returns:

the default row height in units of 1/20th of a point, also known as twips (twentieths of a point)

setColumnWidths firstCol lastCol colWidth

Sets the widths of the specified columns.

Parameters:

firstCol - the starting column index
lastCol - the last column index
colWidth - the column width in units of 1/100th of the zero character's width

Returns:

none

getColumnWidth col

Gets the width of the specified column.

Parameters:

col - the column index

Returns:

the column width in units of 1/100th of the zero character's width

setRowHeights firstRow lastRow rowHeight

Sets the heights of the specified rows.

Parameters:

firstRow - the starting row index
lastRow - the last row index
rowHeight - the row height in units of 1/20th of a point

Returns:

none

getRowHeight row

Gets the height of the specified row.

Parameters:

none

Returns:

the row height in units of 1/20th of a point

getType row col

Gets the type of cell contents for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the type of cell contents; see the list of cell type constants

setText row col pString

Sets the specified cell to contain the specified string.

NOTE: Microsoft Excel 95 file format supports up to 255 characters in a cell. If there are more present, the string will be truncated to 255 characters when saving to that file format.

Parameters:

row - the row index of the cell
col - the column index of the cell
pString - the text string

Returns:

none

getText row col

Gets the contents of the specified cell as a string.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the cell contents as a string

setNumber row col val

Puts a number in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
val - the number

Returns:

none

getNumber row col

Gets the contents of the specified cell as a number.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the number in the cell, or 0.0 if the cell doesn't contain a number

setFormula row col pFormula

Puts the formula in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
pFormula - the formula string in SpreadScript syntax

Returns:

none

getFormula row col

Gets the formula contained in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the formula string in SpreadScript syntax, or an empty string if the cell doesn't contain a formula

setFont row col font

Sets the font for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
font - the font; see the list of font contants

Returns:

none

getFont row col

Gets the font for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the font; see the list of font contants

setFontSize row col size

Sets the font size for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
size - the font size; see the list of font size contants

Returns:

none

getFontSize row col

Gets the font size for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the font size; see the list of font size constants

setFontStyle row col style

Sets the font style for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
style - the font style; see the list of font style contants

Returns:

none

getFontStyle row col

Gets the font style for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the font style; see the list of font style constants

setHorizontalJustification row col hjust

Sets the horizontal justification for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
hjust - the horizontal justification; see the list of horizontal justification contants

Returns:

none

getHorizontalJustification row col

Gets the horizontal justification for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the horizontal justification; see the horizontal justification constants list

setWordWrap row col wrap

Sets the word wrapping for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
wrap - the word wrapping; see the list of word wrapping contants

Returns:

none

getWordWrap row col

Gets the word wrapping for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the word wrapping; see the list of word wrapping contants

setUnderline row col under

Sets the underlining for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
under - the underlining; see the list of underlining contants

Returns:

none

getUnderline row col

Gets the underlining for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the underlining; see the list of underlining contants

setFormatAndPlaces row col format places

Sets the format and the number of decimal places for numbers in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
format - the format; see the list of format contants
places - the number of decimal places

Returns:

none

getFormatAndPlaces row col

Gets the format and number of decimal places for the specified cell.

Parameters:

none

Returns:

a list/array containing the format (see the list of format contants) and places

setFormat row col format

DEPRECATED Use setFormatAndPlaces() instead.
Sets the format for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
format - the format; see the list of format contants

Returns:

none

getFormat row col

DEPRECATED Use getFormatAndPlaces() instead.
Gets the format for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the format; see the list of format contants

setPlaces row col places

DEPRECATED Use setFormatAndPlaces() instead.
Sets the number of decimal places for numbers in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
places - the number of decimal places

Returns:

none

getPlaces row col

DEPRECATED Use getFormatAndPlaces() instead.
Gets the number of decimal places for numbers in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the number of decimal places

setBorder row col pBorder

Sets the borders for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
border - the Border object with the cell borders

Returns:

none

getBorder row col

Gets the borders for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the Border object with the cell borders

Border name top bottom left right

Creates a Border object with the specified borders. See the list of border style constants.

Parameters:

top - the border style for the top edge of a cell
bottom - the border style for the bottom edge of a cell
left - the border style for the left edge of a cell
right - the border style for the right edge of a cell

Returns:

the newly created Border object

rename obj {}

Destroys this Border object.

Parameters:

none

Returns:

none

setBottom type

Sets the bottom border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getBottom

Gets the bottom border style of a cell.

Parameters:

none

Returns:

the style of the bottom border; see the list of border style constants

setTop type

Sets the top border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getTop

Gets the top border style of a cell.

Parameters:

none

Returns:

the style of the top border; see the list of border style constants

setLeft type

Sets the left border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getLeft

Gets the left border style of a cell.

Parameters:

none

Returns:

the style of the left border; see the list of border style constants

setRight type

Sets the right border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getRight

Gets the right border style of a cell.

Parameters:

none

Returns:

the style of the right border; see the list of border style constants

Range name pSheet rowUL colUL rowLR colLR

Creates a new Range object.

Parameters:

pSheet - the parent Worksheet
rowUL - upper left row
colUL - upper left column
rowLR - lower right row
colLR - lower right column

Returns:

new Range object

rename obj {}

Destroys a Range object.

Parameters:

none

Returns:

none

Range_createFromString pSheet pString

Creates a new Range object from a string description, such as "A1..B3".

Parameters:

pString - the string describing the range (e.g., "A1..B3")

Returns:

new Range object or NULL/None if the string is not a valid range

toString

Gets a string representation of this Range object, such as "A1..B3".

Parameters:

none

Returns:

the string describing the range (e.g., "A1..B3")

getBounds

Gets the bounds of this range.

Parameters:

none

Returns:

a list/array containing the bounds of this range.

getMinRow

Gets the minimum row in this range.

Parameters:

none

Returns:

the minimum row in this range

getMaxRow

Gets the maximum row in this range.

Parameters:

none

Returns:

the maximum row in this range

getMinColumn

Gets the minimum column in this range.

Parameters:

none

Returns:

the minimum column in this range

getMaxColumn

Gets the maximum column in this range.

Parameters:

none

Returns:

the maximum column in this range

getParent

Gets a reference to the range's parent Worksheet.

Parameters:

none

Returns:

a reference to the parent Worksheet

setText pString

Sets each cell in this range to contain the specifed string.

Parameters:

pString - the string

Returns:

none

setNumber val

Sets each cell in this range to contain the specified numerical value.

Parameters:

val - the numerical value

Returns:

none

setFormula pFormula

Sets each cell in this range to contain the specified formula. Relative cell references are adjusted as they would be when pasting a formula in an interactive spreadsheet.

Parameters:

pFormula - the formula string

Returns:

none

setFont font

Sets the font for cells in this Range.

Parameters:

font - the font; see the list of font contants

Returns:

none

setFontSize size

Sets the font size for cells in this Range.

Parameters:

size - the font size; see the list of font size contants

Returns:

none

setFontStyle style

Sets the font style for cells in this Range.

Parameters:

style - the font style; see the list of font style contants

Returns:

none

setHorizontalJustification hjust

Sets the horizontal justification for cells in this Range.

Parameters:

hjust - the horizontal justification; see the list of horizontal justification contants

Returns:

none

setWordWrap wrap

Sets the word wrapping for cells in this Range.

Parameters:

wrap - the word wrapping; see the list of word wrapping contants

Returns:

none

setUnderline under

Sets the underlining for cells in this Range.

Parameters:

under - the underlining; see the list of underlining contants

Returns:

none

setFormatAndPlaces format places

Sets the format and number of decimal places for cells in this Range.

Parameters:

format - the format; see the list of format contants
places - the number of decimal places

Returns:

none

setBorder pBorder

Sets the borders for cells in this Range.

Parameters:

pBorder - the Border object with the cell borders

Returns:

none

setBorderEdges pEdgeBorder ?pInteriorBorder?

Sets the borders around the outermost edges of this range and, optionally, the interior edges. In other words, the top row of cells gets the top boder, the left side the left border, the bottom side the bottom border, and the right side the right border. If the interior border is specified, it is set on the interior cell boundaries.

Parameters:

pEdgeBorder - the Border object with the cell borders for outer edges of the outer cells
pInteriorBorder - the Border object with the cell borders for the interior cell edges; this argument is optional

Returns:

none

exportTSV pFileName

Exports this range as tab separated values to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportCSV pFileName

Exports this range as comma separated values to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportHTML pFileName

Exports this range as a formatted HTML table to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportLaTeX2E pFileName

Exports this range as a LaTeX 2E table to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportLaTeX209 pFileName

Exports this range as a LaTeX 2.09 table to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

importTSV pFileName ?monthDayOrder?

Imports the tab separated values from the specified file and inserts them into this range.

Parameters:

pFileName - the destination file

Returns:

none

importCSV pFileName ?monthDayOrder?

Imports the comma separated values from the specified file and inserts them into this range.

Parameters:

pFileName - the destination file

Returns:

none

copy pDstRange propsToCopy

Copies cells, including formulas, from this range to the destination range. The relative addresses in the formulas will be properly adjusted. The shape of the source range must match the destination range or the source or destination range must be a single cell.

Parameters:

pDstRange - the destination range or upper left cell
propsToCopy - which cell properties to copy; see the list of cell property constants

Returns:

none

copyValues pDstRange propsToCopy

Copies cell values, without formulas, from this range to the destination range. The shape of the source range must match the destination range or the source or destination range must be a single cell.

Parameters:

pDstRange - the destination range or upper left cell
propsToCopy - which cell properties to copy; see the list of cell property constants

Returns:

none

clear

Clears the contents of the cells in this range.

Parameters:

none

Returns:

none

move dstRow dstCol

Moves cells from this range to the destination cell representing the upper left corner of the new location. All formulas, named ranges, etc. referencing cells in the moved range are updated.

Parameters:

dstRow - the destination row index
dstCol - the destination column index

Returns:

none

scroll rowDelta colDelta

Moves cells from this range by the specified amount. The difference between moveRange and scrollRange is that all formulas, named ranges, etc. referencing cells in the moved range are not updated.
This is useful, for example, when retrieving data from a live source and needing to compute the average (or other statistics) of the last 10, 20, and 50 values. The formulas that reference the data cells aren't changed, but the data in the cells change. If this range represents A1..A50, calling scrollRange(1, 0) would move 49 old values down, leaving the first row empty, and putting new live data in the first row would keep the 50 most recent values in the first 50 rows of column A.

Parameters:

rowDelta - the number of rows by which to shift the range; a positive number moves the range down in the sheet, and negative moves it up
colDelta - the number of columns by which to shift the range; a positive number moves the range left in the sheet, and negative moves it right

Returns:

none

sortRows pSort

Sort the rows of this range into ascending or descending order based on the values in specified columns. Before sorting a range of cells, consider these rules that SpreadScript follows for sorting:

• Cells containing text (labels) or text-valued formulas are sorted in lexical order (alphabetical order following the same sorting conventions as a dictionary). Cells containing numeric formulas or constants are sorted by the numeric value of the cell.
• When sorting a mixed range of numeric and string values, SpreadScript assigns the following priority order to different cell types:
• cells containing text values are less than those with numeric values,
• numeric values are ordered from largest-magnitude negative to largest-magnitude positive,
• and empty cells are considered highest-ranking for ascending sorts and lowest-ranking for descending sorts. This places empty cells at the bottom of the results.
• SpreadScript does not modify range references within the sorted range. This avoids the risk of cells in the referenced range becoming non-contiguous.
• When the sort includes named cells, SpreadScript updates the definition of the named cell to reflect its new location. Named ranges are not changed.
• SpreadScript keeps track of what it moves and where - and updates cell references throughout the spreadsheet to reflect the changes that occurred while sorting. However, SpreadScript lets you choose not to update cell references, if you wish.

Parameters:

pSort - the SortContext object describing how to perform the sort

The SortContext parameter specifies how the sort is performed. The columns specified as keys are used as primary, secondary, etc. keys to sort the data. Up to five keys may be used. The sort uses keys beyond the primary key in cases where the primary does not differentiate the entries. For each key, an order of ascending or descending is specified. If the update field in SortContext is true, updates cell references according to where the referenced cell has moved.

Returns:

1 if successful, 0 otherwise