This chapter provides a complete listing and reference for the methods in the OpenScript DataTableService Class of Data Table Module Application Programming Interface (API).
The following section provides an alphabetical listing of the enums in the OpenScript DataTableService API.
The following section provides an alphabetical listing of the methods in the OpenScript DataTableService API.
The following table lists the DataTableService API methods in alphabetical order.
Table 20-2 List of DataTableService Methods
| Method | Description |
|---|---|
|
Adds a column before the column whose index is columnAdded in the specific sheet. |
|
|
Adds a sheet in the current datatable before given sheet name without using first row as column header. |
|
|
Changes the input sheet name to the new sheet name. |
|
|
Prints the content of current datatable into the Console. |
|
|
Deletes the column whose name is specified by columnName in the sheet whose name is specified by sheetName. |
|
|
Deletes a new row before the current row. |
|
|
Deletes a sheet in the current datatable. |
|
|
Exports a sheet in the current datatable into a new excel document. |
|
|
Exports a sheet in the current excel document into excel document. |
|
|
Exports the current excel document into an excel document whose name is specified by path. |
|
|
Gets the column name in the specified sheet. |
|
|
Gets column count in the specified sheet. |
|
|
Gets the column index by column name in the specified sheet. |
|
|
Gets the current active row number which is 0 based in the current sheet. |
|
|
Gets the name of the current sheet. |
|
|
Creates an instance of datatable in top-level script in the chain of parent scripts. |
|
|
Creates an instance of datatable in parent script, if one exists and declared its own datatable service. |
|
|
Gets the total count of the rows in the specific sheet. |
|
|
Gets the sheet name. |
|
|
Gets the total count of the sheets. |
|
|
Gets the value of the specific cell in the specified sheet. |
|
|
Imports all sheets into the current data table. |
|
|
Imports a new excel document by path specifying if first row is used as header row. |
|
|
Imports a sheet into the current data table. |
|
|
Import sheet list into the current data table. |
|
|
Inserts a new row before the current row. |
|
|
Check to find if a sheet uses the first row as headers for columns. |
|
|
Sets the specified row number to the active row in the specified sheet. |
|
|
Sets the current sheet as indicated by sheet name. |
|
|
Sets the next row to the active row in the current sheet. |
|
|
Sets the previous row to the active row in the current sheet. |
|
|
Sets the value to the specific cell in the specified sheet. |
|
|
Updates the name of the column whose name is specified by columnName in the sheet whose name is sheetName. |
|
|
Sets the first row of data as columns header. |
The following sections provide detailed reference information for each method and enum in the DataTableService Class of Data Table Module Application Programming Interface.
Adds a column before the column whose index is columnAdded in the specific sheet.
The datatable.addColumn method has the following command format(s):
datatable.addColumn(columnName);
datatable.addColumn(columnName, columnAdded);
datatable.addColumn(sheetName, columnName);
datatable.addColumn(sheetName, columnName, columnAdded);
a String specifying the new added column name.
is 0 based column index,the new column will be added beforethis column.
a String specifying the sheet name.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Adds a sheet in the current datatable before given sheet name without using first row as column header.
First row of new sheet will not be used as column header.
The datatable.addSheet method has the following command format(s):
datatable.addSheet(sheetName);
datatable.addSheet(sheetName, firstRowAsColHeader);
datatable.addSheet(sheetName, aheadSheetName);
datatable.addSheet(sheetName, aheadSheetName, firstRowAsColHeader);
a String specifying the name of the sheet to be added.
a Boolean specifying whether to use the first row as the column name.
a String specifying the name of the sheet to add the new sheet before.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Changes the input sheet name to the new sheet name.
The datatable.changeSheet method has the following command format(s):
datatable.changeSheet(oldSheetName, newSheetName);
a String specifying the sheet name that will be changed.
a String specifying new sheet name.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Prints the content of current datatable into the Console.
Use this method during script debugging to dump the content of current datatable into Console. Do not use on normal playback, as it slows down execution and increases the size of the Console. After debugging just change value of dumDT to false.
The datatable.debugDump method has the following command format(s):
datatable.debugDump(notes);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Deletes the column whose name is specified by columnName in the sheet whose name is specified by sheetName.
The datatable.deleteColumn method has the following command format(s):
datatable.deleteColumn(columnName);
datatable.deleteColumn(sheetName, columnName);
a String specifying the deleted column name.
a String specifying the sheet name in which the column will be deleted.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Deletes a new row before the current row.
The datatable.deleteRow method has the following command format(s):
datatable.deleteRow(sheetName, rowIndex);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Deletes a sheet in the current datatable.
The datatable.deleteSheet method has the following command format(s):
datatable.deleteSheet(sheetName);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Exports a sheet in the current datatable into a new excel document.
The datatable.exportSheet method has the following command format(s):
datatable.exportSheet(path, sourceSheetName, destSheetName);
datatable.exportSheet(path, sourceSheetName, destSheetName, exportMode);
a String specifying the absolute path of the new excel document.
a String specifying the name of the sheet to be exported.
a String specifying the new name of the sheet to be exported.
an ExportMode enum of ExportMode.CREATE or ExportMode.MERGE. CREATE means create new excel file comprised of exported sheet only. MERGE means add exported sheets to existing sheets. If the existing spreadsheet has the same name as the exported sheet, then the existing sheet will be overwritten.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Exports a sheet in the current excel document into excel document.
The datatable.exportSheets method has the following command format(s):
datatable.exportSheets(destPath, sheetlist);
datatable.exportSheets(destPath, sheetlist, expMode);
a String specifying the absolute path of the new excel document.
a List specifying the name of the sheet to be exported.
an ExportMode enum of ExportMode.CREATE or ExportMode.MERGE. CREATE means create a new excel file comprised of exported sheets only. MERGE means add exported sheets to existing sheets. If the existing spreadsheethas the same name as the exported sheet, then the existing sheet will be overwritten.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Exports sheets in the current datatable document into a new excel document.
import java.util.List;
import java.util.ArrayList;
//[...]
List<String> sheetList = new ArrayList<String>();
sheetList.add("Sheet1");
sheetList.add("Sheet2");
datatable.exportSheets("C:\\OracleATS\\Book1.xls", sheetList,
ExportMode.CREATE);
Exports the current excel document into an excel document whose name is specified by path.
The method does the same job as {@code exportSheets()} for all sheets in the current datatable.
The datatable.exportToExcel method has the following command format(s):
datatable.exportToExcel(path);
datatable.exportToExcel(path, expMode);
a String specifying the absolute path of the new excel document.
an ExportMode enum of ExportMode.CREATE or ExportMode.MERGE. CREATE means create a new excel file comprised of exported sheets only. MERGE means add exported sheets to existing sheets. If the existing spreadsheet has the same name as the exported sheet, then the existing sheet will be overwritten.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the column name in the specified sheet.
The datatable.getColumn method has the following command format(s):
datatable.getColumn(sheetIndex, columnIndex);
datatable.getColumn(sheetName, columnIndex);
specifies 0 based sheet index.
specifies 0 based column index.
a String specifying sheet name.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets column count in the specified sheet.
The datatable.getColumnCount method has the following command format(s):
datatable.getColumnCount(sheetIndex);
datatable.getColumnCount(sheetName);
specifies 0 based sheet index.
a String specifyingthe name of sheet.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the column index by column name in the specified sheet.
The datatable.getColumnIndex method has the following command format(s):
datatable.getColumnIndex(colName);
datatable.getColumnIndex(sheetName, colName);
a String specifying the name of column.
a String specifying the name of sheet.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the current active row number which is 0 based in the current sheet.
The datatable.getCurrentRow method has the following command format(s):
datatable.getCurrentRow( );
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the name of the current sheet.
The datatable.getCurrentSheet method has the following command format(s):
datatable.getCurrentSheet( );
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Creates an instance of datatable in top-level script in the chain of parent scripts.
Each script declares its own datatable service. Can be used to manipulate top-level datatable from the child script.
The datatable.getGlobalDatatable method has the following command format(s):
datatable.getGlobalDatatable( );
Creates an instance of datatable in parent script, if one exists and declared its own datatable service.
Can be used to manipulate parent datatable from the child script.
The datatable.getParentDatatable method has the following command format(s):
datatable.getParentDatatable( );
instance of parent datatable, if any,or null, if this script runs alone or parent script doesn't declared datatable service.
Gets the total count of the rows in the specific sheet.
The datatable.getRowCount method has the following command format(s):
datatable.getRowCount( );
datatable.getRowCount(sheetName);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the sheet name.
The datatable.getSheet method has the following command format(s):
datatable.getSheet(index);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the total count of the sheets.
The datatable.getSheetCount method has the following command format(s):
datatable.getSheetCount( );
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Gets the value of the specific cell in the specified sheet.
The datatable.getValue method has the following command format(s):
datatable.getValue(row, column);
datatable.getValue(sheetName, row, column);
specifies the 0 based row index.
a String specifying the column name.
a String specifying the sheet name.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Imports all sheets into the current data table.
If a sheet with the same name as the imported sheet exists in the data table, then behavior depends on overwrite parameter. If overwrite is true, then existing sheet will be overwritten. If overwrite is false, then the existing sheet will be preserved, but the imported sheet name will be appended with a numerical value from 1 to 50000.
The datatable.importAllSheets method has the following command format(s):
datatable.importAllSheets(path, overwrite, firstRowAsColHeader);
a String specifying the absolute path of the excel document that contains the imported sheets.
a Boolean indicating whether to overwrite if same sheet name exists.
a Boolean specifying how to treat the first row of imported sheets.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Imports a new excel document by path specifying if first row is used as header row.
Differs from {@code improtAllSheets} in that existing sheets won't be preserved.
The datatable.importExcel method has the following command format(s):
datatable.importExcel(path);
datatable.importExcel(path, firstRowAsColHeader);
a String specifying the absolute file path of the new document.
a Boolean specifying how to treat first row of imported sheets.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Imports a sheet into the current data table.
Existing sheets will be preserved. If the name of the existing sheet matches the name of the imported destination sheet, then name of destination sheet will be appended by a numbered suffix.
Example:
Destination sheet name is Sheet1, but data table already has sheet named Sheet1. After importing existing sheet, Sheet1 will be preserved, and imported sheet will get name Sheet11, unless Sheet11 already exists in data table. After import, the first row of the imported sheet will not be considered as a column header.
The datatable.importSheet method has the following command format(s):
datatable.importSheet(path, sourceSheet, destSheet);
datatable.importSheet(path, sourceSheet, destSheet, firstRowAsColHeader);
a String that specifies the absolute path of the Excel document that contains the imported sheet.
a String specifying the imported sheet name in excel document.
a String specifying the new sheet name in the current data table.
a boolean specifying how to use first row of the imported sheet.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Import sheet list into the current data table.
If a sheet with the same name as imported sheet exists in the data table, then behavior depends on the overwrite parameter. If overwrite is true, then existing sheet will be overwritten. If overwrite is false, then existing sheet will be preserved, but the imported sheet name will be appended with a numerical value from 1 to 50000.
The datatable.importSheets method has the following command format(s):
datatable.importSheets(sourcePath, sheetList, overwirte);
datatable.importSheets(sourcePath, sheetList, bOverwirte, bUsingFirstRow);
a String specifying the absolute path of the excel file that contains the imported sheets.
a List specifying the sheet(s) that will be imported.
a Boolean specifying whether to overwrite if the same sheet name exists.
a Boolean specifying whether to overwrite if the same sheet name exists.
a Boolean specifying whether to use the first row as the column name.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Inserts a new row before the current row.
The datatable.insertRow method has the following command format(s):
datatable.insertRow(rowIndex);
datatable.insertRow(sheetName, rowIndex);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Sets the specified row number to the active row in the specified sheet.
The datatable.setCurrentRow method has the following command format(s):
datatable.setCurrentRow(row);
datatable.setCurrentRow(sheetName, row);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Sets the current sheet as indicated by sheet name.
Many data table methods are used against the current sheet in the table.
The datatable.setCurrentSheet method has the following command format(s):
datatable.setCurrentSheet(sheetName);
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Sets the next row to the active row in the current sheet.
If the current active row is at the end, set it to the beginning row.
The datatable.setNextRow method has the following command format(s):
datatable.setNextRow( );
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Sets the previous row to the active row in the current sheet.
If the current active row is at the beginning, set it to the end row.
The datatable.setPreviousRow method has the following command format(s):
datatable.setPreviousRow( );
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Sets the value to the specific cell in the specified sheet.
If the value begins with an equals sign (=), it will be considered a formula. For example "=sum(a1,a2)".
The datatable.setValue method has the following command format(s):
datatable.setValue(row, column, value);
datatable.setValue(row, column, value);
datatable.setValue(row, column, value);
datatable.setValue(sheetName, row, column, value);
datatable.setValue(sheetName, row, column, value);
datatable.setValue(sheetName, row, column, value);
specifies the 0 based row index.
a String specifying the column name.
the value to set. There are setValue methods for String value, Boolean value, and Double value.
a String specifying the sheet name.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Updates the name of the column whose name is specified by columnName in the sheet whose name is sheetName.
The datatable.updateColumn method has the following command format(s):
datatable.updateColumn(col, value);
datatable.updateColumn(columnName, value);
datatable.updateColumn(sheetName, col, value);
datatable.updateColumn(sheetIndex, col, value);
datatable.updateColumn(sheetName, columnName, value);
0 based column index.
a String specifying the new column name.
a String specifying the column name to be updated..
a String specifying sheet name.
0 based sheet index.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
Sets the first row of data as columns header.
This setting can completely change the sheet behavior, if it was not previously set by one of import methods or by the import action in the datatable view.
When the first row is set as column headers, the first row of data in the in datatable sheet is used as a header for columns. The number of rows in the sheet is decremented, as all rows starting from the second row will be moved up. If the first row has any cell values that are not assigned yet, then a default Excel column name will be used for the column [A,B,..,Z,AA,AB,...,ZZ]. For example, before the first column policy is set to use first row as headers, the sheet with 2 rows:
col1, col2, col3
a21, a22, a23
was shown in Excel as:
headers: A, B, C
row1: col1, col2, col3
row2: a21, a22, a33
it will be shown the same way in the datatable sheet.
After the first column policy is set to use first row as headers, you will see datatable data in Excel the same as before, but in the datatatable view it shows as:
headers: col1, col2, col3
row1: a21, a22, a23
The datatable.useFirstRowAsColumnHeader method has the following command format(s):
datatable.useFirstRowAsColumnHeader(sheetName);