new();
Create an empty Workbook.
Parameters:noneReturns:empty Workbook
DESTROY
Destroys a Workbook.
Parameters:noneReturns: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:noneReturns: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 onReturns: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 formatReturns: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 WorksheetReturns: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 deletedReturns:none
getSheetCount();
Gets the number of Worksheets in this Workbook.
Parameters:noneReturns: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 1Returns: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 getReturns:the Worksheet specified by name in this Workbook
getFileName();
Gets the file name for this Workbook.
Parameters:noneReturns:the file name of the Workbook or a null reference if it has no file name
delete();
Delete and destroy this Worksheet.
Parameters:noneReturns:none
getName();
Get the name of this Worksheet.
Parameters:noneReturns:the name
setName(pName);
Sets the name of this Worksheet.
Parameters:pName - the nameReturns: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:noneReturns:true (one) if the sheet requires recalculation
recalc();
Recalculates this Worksheet.
Parameters:noneReturns:none
getDimensions();
Gets the minimum bounding rectangle of non-empty cells in this Worksheet.
Parameters:noneReturns: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:
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:
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 = 1a call to performGoalSeek() with GoalSeekContext containing
A2 = sqrt(A1)
varRow = 1converges to a solution with
varCol = 1
targetRow = 2
targetCol = 1
goal = 3.14
tolerance = 1e-9
bracketLow = 0
bracketHigh = 10
iterations = 30
A1 = 9.8596and 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 valuesReturns: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 columnsReturns:
lastCol - the last column index to insertnone
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 rowsReturns:
lastRow - the last row index to insertnone
deleteColumns(firstCol,lastCol);
Deletes columns at the specified location.
Parameters:firstCol - the column index at which to delete the columnsReturns:
lastCol - the last column index to deletenone
deleteRows(firstRow,lastRow);
Deletes rows at the specified location.
Parameters:firstRow - the row index at which to delete the rowsReturns:
lastRow - the last row index to deletenone
setDefaultFont(font);
Set the default cell font for this Worksheet.
Parameters:font - the default font; see the list of font constantsReturns:none
getDefaultFont();
Gets the default cell font for this Worksheet.
Parameters:noneReturns: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 constantsReturns:none
getDefaultFontSize();
Gets the default cell font size for this Worksheet.
Parameters:noneReturns: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 constantsReturns:none
getDefaultFontStyle();
Gets the default cell font style for this Worksheet.
Parameters:noneReturns: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 constantsReturns:none
getDefaultHorizontalJustification();
Gets the default cell horizontal justification for this Worksheet.
Parameters:noneReturns: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 contantsReturns:none
getDefaultWordWrap();
Gets the default cell word wrapping for this Worksheet.
Parameters:noneReturns: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 constantsReturns:none
getDefaultUnderline();
Gets the default cell underlining for this Worksheet.
Parameters:noneReturns: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 constantsReturns:
places - the number of placesnone
getDefaultFormatAndPlaces();
Gets the default cell format and number of decimal places for this Worksheet.
Parameters:noneReturns: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 constantsReturns:none
getDefaultFormat();
DEPRECATED Use getDefaultFormatAndPlaces() instead.
Gets the default cell format for this Worksheet.
Parameters:noneReturns: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 placesReturns: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:noneReturns: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 widthReturns:none
getDefaultColumnWidth();
Gets the default width for columns in this Worksheet.
Parameters:noneReturns: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:noneReturns: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 indexReturns:
lastCol - the last column index
colWidth - the column width in units of 1/100th of the zero character's widthnone
getColumnWidth(col);
Gets the width of the specified column.
Parameters:col - the column indexReturns: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 indexReturns:
lastRow - the last row index
rowHeight - the row height in units of 1/20th of a pointnone
getRowHeight(row);
Gets the height of the specified row.
Parameters:noneReturns: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 cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
pString - the text stringnone
getText(row,col);
Gets the contents of the specified cell as a string.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe cell contents as a string
setNumber(row,col,val);
Puts a number in the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cell
val - the numbernone
getNumber(row,col);
Gets the contents of the specified cell as a number.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
pFormula - the formula string in SpreadScript syntaxnone
getFormula(row,col);
Gets the formula contained in the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
font - the font; see the list of font contantsnone
getFont(row,col);
Gets the font for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
size - the font size; see the list of font size contantsnone
getFontSize(row,col);
Gets the font size for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
style - the font style; see the list of font style contantsnone
getFontStyle(row,col);
Gets the font style for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
hjust - the horizontal justification; see the list of horizontal justification contantsnone
getHorizontalJustification(row,col);
Gets the horizontal justification for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
wrap - the word wrapping; see the list of word wrapping contantsnone
getWordWrap(row,col);
Gets the word wrapping for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
under - the underlining; see the list of underlining contantsnone
getUnderline(row,col);
Gets the underlining for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
format - the format; see the list of format contants
places - the number of decimal placesnone
getFormatAndPlaces(row,col);
Gets the format and number of decimal places for the specified cell.
Parameters:noneReturns: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 cellReturns:
col - the column index of the cell
format - the format; see the list of format contantsnone
getFormat(row,col);
DEPRECATED Use getFormatAndPlaces() instead.
Gets the format for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe 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 cellReturns:
col - the column index of the cell
places - the number of decimal placesnone
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 cellReturns:
col - the column index of the cellthe number of decimal places
setBorder(row,col,pBorder);
Sets the borders for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cell
border - the Border object with the cell bordersnone
getBorder(row,col);
Gets the borders for the specified cell.
Parameters:row - the row index of the cellReturns:
col - the column index of the cellthe Border object with the cell borders
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 cellReturns:
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 cellthe newly created Border object
DESTROY
Destroys this Border object.
Parameters:noneReturns:none
setBottom(type);
Sets the bottom border style of a cell.
Parameters:type - the style of border; see the list of border style constantsReturns:none
getBottom();
Gets the bottom border style of a cell.
Parameters:noneReturns: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 constantsReturns:none
getTop();
Gets the top border style of a cell.
Parameters:noneReturns: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 constantsReturns:none
getLeft();
Gets the left border style of a cell.
Parameters:noneReturns: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 constantsReturns:none
getRight();
Gets the right border style of a cell.
Parameters:noneReturns:the style of the right border; see the list of border style constants
Creates a new Range object.
Parameters:pSheet - the parent WorksheetReturns:
rowUL - upper left row
colUL - upper left column
rowLR - lower right row
colLR - lower right columnnew Range object
DESTROY
Destroys a Range object.
Parameters:noneReturns: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:noneReturns:the string describing the range (e.g., ``A1..B3'')
getBounds();
Gets the bounds of this range.
Parameters:noneReturns:a list/array containing the bounds of this range.
getMinRow();
Gets the minimum row in this range.
Parameters:noneReturns:the minimum row in this range
getMaxRow();
Gets the maximum row in this range.
Parameters:noneReturns:the maximum row in this range
getMinColumn();
Gets the minimum column in this range.
Parameters:noneReturns:the minimum column in this range
getMaxColumn();
Gets the maximum column in this range.
Parameters:noneReturns:the maximum column in this range
getParent();
Gets a reference to the range's parent Worksheet.
Parameters:noneReturns:a reference to the parent Worksheet
setText(pString);
Sets each cell in this range to contain the specifed string.
Parameters:pString - the stringReturns:none
setNumber(val);
Sets each cell in this range to contain the specified numerical value.
Parameters:val - the numerical valueReturns: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 stringReturns:none
setFont(font);
Sets the font for cells in this Range.
Parameters:font - the font; see the list of font contantsReturns:none
setFontSize(size);
Sets the font size for cells in this Range.
Parameters:size - the font size; see the list of font size contantsReturns:none
setFontStyle(style);
Sets the font style for cells in this Range.
Parameters:style - the font style; see the list of font style contantsReturns:none
setHorizontalJustification(hjust);
Sets the horizontal justification for cells in this Range.
Parameters:hjust - the horizontal justification; see the list of horizontal justification contantsReturns:none
setWordWrap(wrap);
Sets the word wrapping for cells in this Range.
Parameters:wrap - the word wrapping; see the list of word wrapping contantsReturns:none
setUnderline(under);
Sets the underlining for cells in this Range.
Parameters:under - the underlining; see the list of underlining contantsReturns: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 contantsReturns:
places - the number of decimal placesnone
setBorder(pBorder);
Sets the borders for cells in this Range.
Parameters:pBorder - the Border object with the cell bordersReturns: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 cellsReturns:
pInteriorBorder - the Border object with the cell borders for the interior cell edges; this argument is optionalnone
exportTSV(pFileName);
Exports this range as tab separated values to the specified file.
Parameters:pFileName - the destination fileReturns:none
exportCSV(pFileName);
Exports this range as comma separated values to the specified file.
Parameters:pFileName - the destination fileReturns:none
exportHTML(pFileName);
Exports this range as a formatted HTML table to the specified file.
Parameters:pFileName - the destination fileReturns:none
exportLaTeX2E(pFileName);
Exports this range as a LaTeX 2E table to the specified file.
Parameters:pFileName - the destination fileReturns:none
exportLaTeX209(pFileName);
Exports this range as a LaTeX 2.09 table to the specified file.
Parameters:pFileName - the destination fileReturns:none
importTSV(pFileName,monthDayOrder);
Imports the tab separated values from the specified file and inserts them into this range.
Parameters:pFileName - the destination fileReturns:none
importCSV(pFileName,monthDayOrder);
Imports the comma separated values from the specified file and inserts them into this range.
Parameters:pFileName - the destination fileReturns: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 cellReturns:
propsToCopy - which cell properties to copy; see the list of cell property constantsnone
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 cellReturns:
propsToCopy - which cell properties to copy; see the list of cell property constantsnone
clear();
Clears the contents of the cells in this range.
Parameters:noneReturns: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 indexReturns:
dstCol - the destination column indexnone
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 upReturns:
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
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:
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