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 DoSequental and in DoRandom.
GetRowCount Gets row count.
SetCell Sets cell value by its coordinates.
SetRange Cell range used in DoSequental and in DoRandom.

Action Detail

DoAddColumn(name)

Appends a column with specified name.

Parameters:

Name Type Description
name string Name of a new column.

Returns:

boolean: true if successfull, false otherwise.

DoAddRow()

Appends a new row.

Returns:

boolean: true if successfull, false otherwise.

DoAttach(fileName, sheetName, separator)

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

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 successfull, false otherwise.

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

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.

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(fileName, sheetName, columnNames, rowCount, separator)

Creates a new spreadsheet file.

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 successfull, false otherwise.

DoInsertColumn(columnIndex, name)

Inserts a column at index.

Parameters:

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

Returns:

boolean: true if successfull, false otherwise.

DoInsertRow(rowIndex)

Inserts a new row at index.

Parameters:

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

Returns:

boolean: true if successfull, false otherwise.

DoMoveToFirstRow()

Moves to a first row in selected range.

Returns:

boolean: true if successfull, false otherwise.

DoMoveToLastRow()

Moves to a last row in selected range.

Returns:

boolean: true if successfull, false otherwise.

DoMoveToRow(index)

Sets active row index.

Parameters:

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

Returns:

boolean: true if successfull, 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.

Returns:

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

DoRemoveColumn(columnIndex)

Removes a column at index.

Parameters:

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

Returns:

boolean: true if successfull, false otherwise.

DoRemoveRow(rowIndex)

Removes a row at index.

Parameters:

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

Returns:

boolean: true if successfull, false otherwise.

DoSave(fileName)

Saves the spreadsheet.

Parameters:

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

Returns:

boolean: true if successfull, 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'.

Returns:

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

GetCell(columnId, rowId)

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

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 successfull, false otherwise.

GetColumnCount()

Gets columns count.

Returns:

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

GetColumnIndexByName(name)

Gets column index by its name.

Parameters:

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

Returns:

number: column index if found, or -1.

GetColumnName(index)

Gets column name.

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.

Returns:

number: Active row index.

GetEOF()

Gets EOF state.

Returns:

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

GetRange()

Cell range used in DoSequental and in DoRandom.

Returns:

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

GetRowCount()

Gets row count.

Returns:

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

SetCell(value, columnId, rowId)

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

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 successfull, false otherwise.

SetRange(startRow, endRow, startCol, endCol)

Cell range used in DoSequental and in DoRandom.

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 successfull, false otherwise.