Method Detail

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static boolean compareTwoCells(org.apache.poi.ss.usermodel.Cell cell1, org.apache.poi.ss.usermodel.Cell cell2, boolean isValueOnly = true)

  Compare two excel cells

  Returns:

  true if two excel cells equal, false if not

  Parameters:

  cell1 - the first cell need to compare

  cell2 - the second cell need to compare

  isValueOnly - true if check value and type only regardless of cell style, false if not

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static boolean compareTwoExcels(org.apache.poi.ss.usermodel.Workbook workbook1, org.apache.poi.ss.usermodel.Workbook workbook2, boolean isValueOnly = true)

  Compare value of two excel workbooks

  Returns:

  true if two excel workbooks equal, false if not

  Parameters:

  workbook1 - the first workbook need to compare

  workbook2 - the second workbook need to compare

  isValueOnly - true if check value and type only regardless of cell style, false if not

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static boolean compareTwoRows(org.apache.poi.ss.usermodel.Row row1, org.apache.poi.ss.usermodel.Row row2, boolean isValueOnly = true)

  Compare two excel rows

  Returns:

  true if two excel rows equal, false if not

  Parameters:

  row1 - the first row need to compare

  row2 - the second row need to compare

  isValueOnly - true if check value and type only regardless of cell style, false if not

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static boolean compareTwoSheets(org.apache.poi.ss.usermodel.Sheet sheet1, org.apache.poi.ss.usermodel.Sheet sheet2, boolean isValueOnly = true)

  Compare two excel sheets

  Returns:

  true if two excel sheets equal, false if not

  Parameters:

  sheet1 - the first sheet need to compare

  sheet2 - the second sheet need to compare

  isValueOnly - true if check value and type only regardless of cell style, false if not

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void createExcelFile(java.lang.String filePath)

  Create a new excel file

  Parameters:

  filePath - path of excel file

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void createExcelSheet(org.apache.poi.ss.usermodel.Workbook workbook)

  Add a default sheet to workbook

  Parameters:

  workbook - the workbook that needs to add new default sheet

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void createExcelSheet(org.apache.poi.ss.usermodel.Workbook workbook, java.lang.String sheetName)

  Add a new sheet to workbook

  Parameters:

  workbook - the workbook that needs to add new sheet

  sheetName - the name of new sheet

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void createExcelSheets(org.apache.poi.ss.usermodel.Workbook workbook, java.util.List<java.lang.String> sheetNames)

  Add multiple sheets to workbook

  Parameters:

  workbook - the workbook that needs to add new sheets

  sheetNames - the list of sheet names

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Workbook createWorkbook(java.lang.String filePath)

  Create new workbook

  Returns:

  new workbook

  Parameters:

  filePath - location path of excel file

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Cell getCellByAddress(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String cellAddress)

  Get cell by address

  Returns:

  a cell object (this function returns null if the object is null)

  Parameters:

  sheet - the sheet object that contains cell

  cellAddress - the address of cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Cell getCellByIndex(org.apache.poi.ss.usermodel.Sheet sheet, int rowIdx, int colIdx)

  Get cell by index

  Returns:

  a cell object (this function returns null if the object is null)

  Parameters:

  sheet - the sheet object that contains cell

  rowIdx - row index of the cell

  colIdx - column index of the cell

• static java.lang.Object getCellFormula(org.apache.poi.ss.usermodel.Cell cell)

  Get cell formula

  Returns:

  a formula if cell is formula type, cell value if not

  Parameters:

  cell - the cell object that need to get formula

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.Map getCellIndexByAddress(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String cellAddress)

  Get cell position by address

  Returns:

  a map contains position of the cell, e.g: ["column":2, "row": 1]

  Parameters:

  sheet - the sheet object that contains cell

  cellAddress - the address of cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.lang.Object getCellValue(org.apache.poi.ss.usermodel.Cell cell)

  Read data from a single cell

  Returns:

  the value of cell. If the cell is in date format, the returned value is in Date format "MM/dd/yyyy".

  Parameters:

  cell - the cell that needs to read data

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.lang.Object getCellValueByAddress(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String cellAddress)

  Get cell value by address

  Returns:

  a cell value

  Parameters:

  sheet - the sheet object that contains cell

  cellAddress - the address of the cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.lang.Object getCellValueByIndex(org.apache.poi.ss.usermodel.Sheet sheet, int rowIdx, int colIdx)

  Get cell value by index

  Returns:

  a cell value

  Parameters:

  sheet - the sheet object that contains cell

  rowIdx - row index of the cell

  colIdx - column index of the cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.List<java.lang.String> getCellValueByRangeAddress(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String topLeftAddress, java.lang.String rightBottomAddress)

  Get cell values by addresses

  Returns:

  a list of cell values

  Parameters:

  sheet - the sheet object that contains cells

  topLeftAddress - the address of the first cell

  rightBottomAddress - the address of the last cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.List<java.lang.String> getCellValuesByRangeIndexes(org.apache.poi.ss.usermodel.Sheet sheet, int rowInd1, int colInd1, int rowInd2, int colInd2)

  Get cell values by indexes

  Returns:

  a list of cell values

  Parameters:

  sheet - the sheet object that contains cells

  rowInd1 - the row index of the first cell

  colInd1 - the column index of the first cell

  rowInd2 - the row index of the last cell

  colInd2 - the column index of the last cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.lang.Object getColumnsByIndex(org.apache.poi.ss.usermodel.Sheet sheet, java.util.List<java.lang.Integer> columnIndexes)

  Get map of columns data

  Returns:

  map of a columns data contains the column index and cell data

  Parameters:

  sheet - the sheet object that contains the row

  columnIndexes - indexes of columns

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.List<org.apache.poi.ss.usermodel.Row> getDataRows(org.apache.poi.ss.usermodel.Sheet sheet, java.util.List<java.lang.Integer> rowIndexs)

  Get data rows from a list of row indexes

  Returns:

  a list of rows

  Parameters:

  sheet - the sheet object contains the rows

  rowIndexes - the list of row indexes

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Sheet getExcelSheet(java.lang.String filePath, int sheetIndex = 0)

  Get an Excel sheet

  Returns:

  a sheet object

  Parameters:

  filePath - the location of excel file

  sheetIndex - the index of returned sheet

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Sheet getExcelSheet(org.apache.poi.ss.usermodel.Workbook wbs, java.lang.String sheetName = null)

  Get an Excel sheet from an opening workbook

  example:

  getExcelSheet(workbook1,"Sheet1")

  Returns:

  a sheet object.

  Parameters:

  wbs - the opening workbook

  sheetName - the sheet name of the workbook

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Sheet getExcelSheetByName(java.lang.String filePath, java.lang.String sheetName)

  Get an Excel sheet by name

  Returns:

  a sheet object

  Parameters:

  filePath - the location of excel file

  sheetName - the name of returned sheet

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.Map getMapOfKeyValueRows(org.apache.poi.ss.usermodel.Sheet sheet, int keyRowIdx, int valueRowIdx)

  Get a map of {{key1:value1},...{keyn:valuen}} from a row of keys and a row of values of a sheet

  Returns:

  a map from a row of keys and a row of values of a sheet, e.g: {{key1:value1},...{keyn:valuen}}

  Parameters:

  sheet - the sheet object that contains the row

  keyRowIdx - the index of the header row

  valueRowIdx - the index of row needs to get the data

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static int getRowIndexByCellContent(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String cellContent, int columnIdxForCell)

  Get row index by cell content

  Returns:

  the row index

  Parameters:

  sheet - the sheet object that contains the row

  cellContent - the content of the cell

  columnIdxForCell - the column index of the cell

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.List<java.lang.String> getSheetNames(org.apache.poi.ss.usermodel.Workbook workbook)

  Get name of sheets in workbook

  Returns:

  list of sheet's name

  Parameters:

  workbook

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.List<org.apache.poi.ss.usermodel.Row> getTableContent(org.apache.poi.ss.usermodel.Sheet sheet, int startRow, int endRow)

  Get the excel data to a list of rows

  Returns:

  a list of rows

  Parameters:

  sheet - the sheet object to get the data

  startRow - the start row to get the data

  endRow - the end row to get the data

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static org.apache.poi.ss.usermodel.Workbook getWorkbook(java.lang.String filePath)

  Get workbook from existing excel file

  Returns:

  workbook

  Parameters:

  filePath - location path of excel file

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static java.util.List<java.lang.Integer> locateCell(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.Object cellContent)

  Locate the first position of the cell based on the cell content

  Returns:

  a list of integer (row, column)

  Parameters:

  sheet - the sheet object contains the cell

  cellContent - the content to locate the cell. If the cell is in Date format, the cellContent should be in (MM/dd/yyyy), e.g ("10/01/2019")

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void saveWorkbook(java.lang.String filePath, org.apache.poi.ss.usermodel.Workbook workbook)

  Save workbook into file

  Parameters:

  filePath - the location of excel file that will be written

  workbook - the workbook contains data

• static void setValueToCell(org.apache.poi.ss.usermodel.Cell cell, java.lang.Object value)

  Set value to cell

  Example:

  setValueToCell(cell, 123) || setValueToCell(cell, "value@123") || setValueToCell(cell, "=COUNTA(C2:C12)")

  Parameters:

  cell - target cell needs to be set value

  value - value with any data type (String, Numeric, Boolean, Date with format MM/dd/yyyy, formula with character "=" at the beginning of the string

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void setValueToCellByAddress(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String cellAddress, java.lang.Object value)

  Set value to cell by cell Address

  Example:

  setValueToCellByAddress(sheet, "A10", 123)

  Parameters:

  sheet - sheet in workbook

  cellAddress - cell address in sheet

  value - value with any data type (String, Numeric, Boolean, Date with format MM/dd/yyyy, formula with character "=" at the beginning of the string

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void setValueToCellByAddresses(org.apache.poi.ss.usermodel.Sheet sheet, java.util.Map content)

  Set multiple values to multiple cells with cell addresses

  Example:

  content.putAt("C5",12) content.putAt("C6",true) setValueToCellByAddresses(sheet, content)

  Parameters:

  sheet - sheet in workbook

  content - map object with "key" = "cell address" and "value" = "value" with any data type (String, Numeric, Boolean, Date with format MM/dd/yyyy, formula with character "=" at the beginning of the string

• @groovy.transform.CompileStatic @com.kms.katalon.core.annotation.Keyword static void setValueToCellByIndex(org.apache.poi.ss.usermodel.Sheet sheet, int rowIndex, int colIndex, java.lang.Object value)

  Set value to cell by cell index

  Example:

  setValueToCellByIndex(sheet, 1, 2, 123)

  Parameters:

  sheet - sheet in workbook

  colIndex - column index starts by 0

  rowIndex - row index starts by 0

  value - value with any data type (String, Numeric, Boolean, Date with format MM/dd/yyyy, formula with character "=" at the beginning of the string