how to get total row count in Excel File Datasource

[JayKondra]

Using groovy script,

1) how can I get total row count in a Excel File which is used as DataSource?

2) How can I get Data from a specific row in a Excel File which is used as DataSource?

Same question was posted couple of years back, no reply for that post. Hopefully I will get an answer form SmartBear

[TNeuschwanger · Accepted solution]

Hello JayKondra,

I don't know answer for DataSource step. If you need a workaround however, here is groovy code that uses the Apache POI library that gives you great capability for interogating Excel files...
.
.
.
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataFormatter;

def currentFullFileName = 'C:\\Users\\{whatever path to your excel file}\\sample_file.xlsx';
excelFile = new File(currentFullFileName);

log.info "excelFile=" + excelFile;

srcBook = new XSSFWorkbook(new FileInputStream(excelFile)); // Create a new workbook using POI API
fEval = new XSSFFormulaEvaluator(srcBook); // Create formula evaluator to handle formula cells

dFormatter = new DataFormatter(); // handle the formatting of numeric data when it's converted to text for use with our property setter methods
sheetCount = srcBook.getNumberOfSheets();
log.info "sheetCount=" + sheetCount;

sheetIndx = 0;
while(sheetIndx < sheetCount) {
   sourceSheet = srcBook.getSheetAt(sheetIndx); // Get first sheet of the workbook (assumes data is on first sheet)
   log.info "################### sheet=" + sheetIndx;
   log.info "################### sheetName=" + sourceSheet.getSheetName();
   log.info "################### sheetRows=" + sourceSheet.getPhysicalNumberOfRows();
   log.info "################### sheetLastRow=" + sourceSheet.getLastRowNum();
   //row = sourceSheet.getRow(sourceSheet.getLastRowNum());
   //log.info "################### sheetLastColumn=" + row.getPhysicalNumberOfCells();
   sourceSheet.eachWithIndex { row, rIndex -> // each row of sheet
      colIndx = 0;
      while(colIndx < 10) { // just looking at first 10 columns
         log.info '>' + dFormatter.formatCellValue(row.getCell(colIndx), fEval) + '<';
         colIndx++;
      };
   };
   sheetIndx++;
};
log.info 'Test Step "' + testRunner.runContext.currentStep.name + '" done...';

.

.

.

You might be able to pick something out of this sample that you could use in a pinch. 🙂

Regards.

[JayKondra · Accepted solution]

Can we skip first row from the data sheet ??

[TNeuschwanger · Accepted solution]

replace previous sample loop with this...

.

.

.

sourceSheet.eachWithIndex { row, rIndex -> // each row of sheet
   if (rIndex > 0) {
      colIndx = 0;
      while(colIndx < 10) {
         log.info '>' + dFormatter.formatCellValue(row.getCell(colIndx), fEval) + '<';
         colIndx++;
      };
   };
};

.

.

.

The added 'if' statement skips the first row of a sheet.

Regards.

[JayKondra · Accepted solution]

def sourceName = testRunner.testCase.getTestStepByName('DataSource');
def dsRows = sourceName.rowCount;

[tech321]

Thanks a lot 🙂

[tech321]

@JayKondra