Skip to content

Spreadsheet

Spreadsheet object. Use it to read and write Excel and text spreadsheets.

Action Summary

Action Description
DoAddColumn Appends a column with specified name.
DoAddRow Appends a new row.
DoAttach Loads a sheet from Excel file (.xls, .xlsx) or csv file (.txt, *.csv).
DoCompare Compares two spreadsheets.
DoCreate Creates a new spreadsheet file.
DoInsertColumn Inserts a column at index.
DoInsertRow Inserts a new row at index.
DoMoveToFirstRow Moves to a first row in selected range.
DoMoveToLastRow Moves to a last row in selected range.
DoMoveToRow Sets active row index.
DoRandom Selects random row and column in the range.
DoRemoveColumn Removes a column at index.
DoRemoveRow Removes a row at index.
DoSave Saves the spreadsheet.
DoSequential Advances to the next row in the range.
GetCell Cell value by its coordinates.
GetColumnCount Gets columns count.
GetColumnIndexByName Gets column index by its name.
GetColumnName Gets column name.
GetCurrentRowIndex Gets zero-based active row index.
GetEOF Gets EOF state.
GetRange Cell range used in DoSequential and in DoRandom.
GetRowCount Gets row count.
SetCell Sets cell value by its coordinates.
SetRange Cell range used in DoSequential and in DoRandom.

Action Detail

DoAddColumn

Appends a column with specified name.

Spreadsheet.DoAddColumn(name)

Parameters:

Name Type Description
name string Name of a new column.

Returns:

boolean: true if successful, false otherwise.

DoAddRow

Appends a new row.

Spreadsheet.DoAddRow()

Returns:

boolean: true if successful, false otherwise.

DoAttach

Loads a sheet from Excel file (.xls, .xlsx) or csv file (.txt, *.csv).

Spreadsheet.DoAttach(fileName, sheetName, separator)

Parameters:

Name Type Description
fileName string Path to Excel or csv file.
sheetName string Sheet name to load. If not specified - load the first sheet.
Optional.
separator string Separator for values in csv file.
Optional, Default: ",".

Returns:

boolean: true if successful, false otherwise.

DoCompare

Compares two spreadsheets. In strict mode number of columns and rows matter and all cell values must be equal. When strict mode is off then spreadsheet2 must contain spreadsheet1 to return 'true' as a result.

Spreadsheet.DoCompare(spreadsheet1, spreadsheet2, strict, sheet1Name, sheet2Name)

Parameters:

Name Type Description
spreadsheet1 string | Spreadsheet Path to a spreadsheet file or Spreadsheet object loaded via DoAttach or DoCreate actions.
spreadsheet2 string | Spreadsheet Path to a spreadsheet file or Spreadsheet object loaded via DoAttach or DoCreate actions.
strict boolean If 'true' then number of columns and rows in both spreadsheets must be exactly the same. If 'false' then check that spreadsheet1 is contained in spreadsheet2.
Optional, Default: "false".
sheet1Name string Name of a sheet to compare. Specify this parameter when spreadsheet1 is a path to a file. By default first sheet is compared.
Optional.
sheet2Name string Name of a sheet to compare. Specify this parameter when spreadsheet2 is a path to a file. By default first sheet is compared.
Optional.

Returns:

boolean: true if spreadsheets are equal, false otherwise.

DoCreate

Creates a new spreadsheet file.

Spreadsheet.DoCreate(fileName, sheetName, columnNames, rowCount, separator)

Parameters:

Name Type Description
fileName string Path to a file. Extension defines file format. Supported extensions: .xls, .xlsx, *.csv, *.txt.
sheetName string Name of the first sheet.
Optional, Default: "Sheet1".
columnNames array Array of column names.
Optional.
rowCount number Initial number of rows with values.
Optional, Default: "10".
separator string Separator for values in csv files.
Optional, Default: ",".

Returns:

boolean: true if successful, false otherwise.

DoInsertColumn

Inserts a column at index.

Spreadsheet.DoInsertColumn(columnIndex, name)

Parameters:

Name Type Description
columnIndex number Index of a new column. Zero-based.
name string Name of a new column.

Returns:

boolean: true if successful, false otherwise.

DoInsertRow

Inserts a new row at index.

Spreadsheet.DoInsertRow(rowIndex)

Parameters:

Name Type Description
rowIndex number Index of a new row. Zero-based.

Returns:

boolean: true if successful, false otherwise.

DoMoveToFirstRow

Moves to a first row in selected range.

Spreadsheet.DoMoveToFirstRow()

Returns:

boolean: true if successful, false otherwise.

DoMoveToLastRow

Moves to a last row in selected range.

Spreadsheet.DoMoveToLastRow()

Returns:

boolean: true if successful, false otherwise.

DoMoveToRow

Sets active row index.

Spreadsheet.DoMoveToRow(index)

Parameters:

Name Type Description
index number Index to set. Zero-based.

Returns:

boolean: true if successful, false otherwise.

DoRandom

Selects random row and column in the range. The range is either set by SetRange or it is the default range that includes all rows (except first row which is considred to contain column names) and columns on the sheet.

Spreadsheet.DoRandom()

Returns:

boolean: false if the spreadhseet is not attached, true otherwise.

DoRemoveColumn

Removes a column at index.

Spreadsheet.DoRemoveColumn(columnIndex)

Parameters:

Name Type Description
columnIndex number Index of a column to remove. Zero-based.

Returns:

boolean: true if successful, false otherwise.

DoRemoveRow

Removes a row at index.

Spreadsheet.DoRemoveRow(rowIndex)

Parameters:

Name Type Description
rowIndex number Index of a row to remove. Zero-based.

Returns:

boolean: true if successful, false otherwise.

DoSave

Saves the spreadsheet.

Spreadsheet.DoSave(fileName)

Parameters:

Name Type Description
fileName string Path to a file where to save the spreadsheet.
Optional.

Returns:

boolean: true if successful, false otherwise.

DoSequential

Advances to the next row in the range. The range is either set by SetRange or it is the default range that includes all rows on the sheet except first row which is considred to contain column names. When the end of the range is reached DoSequential stays at the last row in the range and returns 'false'.

Spreadsheet.DoSequential()

Returns:

boolean: false if being called when active row is the last row or the spreadsheet is not attached, true otherwise.

GetCell

Cell value by its coordinates. Refers to active cell value after DoSequential or DoRandom if params not set.

Spreadsheet.GetCell(columnId, rowId)

Parameters:

Name Type Description
columnId number | string Column index or name. If not set active column is used.
Optional, Default: "0".
rowId number Row index. If not set ActiveRow is used.
Optional, Default: "0".

Returns:

string: Getter returns string with cell value and null in the case of error. Setter returns true if successful, false otherwise.

GetColumnCount

Gets columns count.

Spreadsheet.GetColumnCount()

Returns:

number: Number of columns in the spreadsheet. -1 if no spreadsheet is attached.

GetColumnIndexByName

Gets column index by its name.

Spreadsheet.GetColumnIndexByName(name)

Parameters:

Name Type Description
name string Name of the column to find.

Returns:

number: column index if found, or -1.

GetColumnName

Gets column name.

Spreadsheet.GetColumnName(index)

Parameters:

Name Type Description
index number Index of a column in the spreadsheet.

Returns:

string: Name of a column in the spreadsheet. Null in the case of error.

GetCurrentRowIndex

Gets zero-based active row index.

Spreadsheet.GetCurrentRowIndex()

Returns:

number: Active row index.

GetEOF

Gets EOF state.

Spreadsheet.GetEOF()

Returns:

boolean: false if spreadsheet is attached and active row and column point to existing cell, true otherwise.

GetRange

Cell range used in DoSequential and in DoRandom.

Spreadsheet.GetRange()

Returns:

object: Spreadsheet state object: { startRow, endRow, startCol, endCol, activeRow, activeColumn }.

GetRowCount

Gets row count.

Spreadsheet.GetRowCount()

Returns:

number: Number of rows in the spreadsheet including the row with column names. -1 if no spreadsheet is attached.

SetCell

Sets cell value by its coordinates. Sets active cell value after DoSequential or DoRandom if params not set.

Spreadsheet.SetCell(value, columnId, rowId)

Parameters:

Name Type Description
value string Value to set.
columnId number | string Column index or name. If not set active column is used.
Optional, Default: "0".
rowId number Row index. If not set active row is used.
Optional, Default: "0".

Returns:

boolean: true if successful, false otherwise.

SetRange

Cell range used in DoSequential and in DoRandom.

Spreadsheet.SetRange(startRow, endRow, startCol, endCol)

Parameters:

Name Type Description
startRow number Start row index.
endRow number Last row index.
startCol number Start column index. If not set then it is the first column.
Optional.
endCol number Last column index. If not set then it is the last column.
Optional.

Returns:

boolean: true if successful, false otherwise.