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.