Forum Discussion

678's avatar
678
Regular Contributor
5 years ago
Solved

how to loop data from multiple sheets of datasource

how can i loop the data from Excel Datasource from multiple sheets 

 

I have single Excel File with multiple sheets like (Sheet1, Sheet2) 

 

Currently I am using as datasource step with Excel File and Sheet1 

but where as i need to use all the data from Sheet 1 than sheet 2 

  • I'm not sure if this is the best way, and it does assume all of your sheets are the same, but you could achieve this with two nested loops. The "Outer" loop can be a simple grid source which lists your sheet names, and then your "Inner" loop iterates though the Excel rows. You then set the work sheet name via Groovy within the "Outer" loop, but before the "Inner" loop. Looking something like this:

    Everything should be self explanatory, except the "Set Current Sheet" step whose code is:

     

    def sheetName = context.expand( '${DataSource - Grid - Sheet Name#SheetName}' )
    log.info('Current sheet name = "' + sheetName + '"')
    
    dataSourceTestStep = testRunner.getTestCase().getTestStepByName("DataSource - Excel")
    log.info('Data source type = ' + dataSourceTestStep.getDataSourceType()) 
    
    excelDataSource = dataSourceTestStep.getDataSource()
    excelDataSource.setWorksheet(sheetName)

    The key thing here is the Excel data source, whose class is ExcelDataSource, the linked API docs provide full details.

     

1 Reply

  • Radford's avatar
    Radford
    Super Contributor

    I'm not sure if this is the best way, and it does assume all of your sheets are the same, but you could achieve this with two nested loops. The "Outer" loop can be a simple grid source which lists your sheet names, and then your "Inner" loop iterates though the Excel rows. You then set the work sheet name via Groovy within the "Outer" loop, but before the "Inner" loop. Looking something like this:

    Everything should be self explanatory, except the "Set Current Sheet" step whose code is:

     

    def sheetName = context.expand( '${DataSource - Grid - Sheet Name#SheetName}' )
    log.info('Current sheet name = "' + sheetName + '"')
    
    dataSourceTestStep = testRunner.getTestCase().getTestStepByName("DataSource - Excel")
    log.info('Data source type = ' + dataSourceTestStep.getDataSourceType()) 
    
    excelDataSource = dataSourceTestStep.getDataSource()
    excelDataSource.setWorksheet(sheetName)

    The key thing here is the Excel data source, whose class is ExcelDataSource, the linked API docs provide full details.