Service Virtualization

How to read content from different excel sheet from same excel workbook during run time in DevTest?

Hi,

   You can try by giving excel file name in a property and give the property in File location section as {{LISA_RELATIVE_PROJ_ROOT}}/Data/{{EXCEL_FILE_NAME}}  in "Read rows from Excel" step. Let us know if you have any question.

Thanks

Srikanth

Hi Srikanth, 

Thanks for the input, but i am looking at reading different tabs (Sheet 1, Sheet2.... etc.) from same excel workbook

Assuming that you want to be able to read one sheet at a time you can do this with one step per sheet. Each step has a dataset for the sheet, with the "At end of Data" set to execute the next read step. The last step is set to end the test when the data is finished. 

The next step for each data set is your main test - this loops to the first data-set when complete.

This sounds confusing - so attached is an example project. There is a simple spreadsheet and a single test. The spreadsheet contains one row headed data per sheet, just by way of an example

I hope that this helps.

Hi Dave, 

Thanks for sharing the insights. Will work with this solution.

But is there any way that we can dynamically read No. of sheets available in excel workbook during run time  and iterate through all the sheets and data available in all work sheets of excel workbook using one are two steps in DevTest

Not out-of-the-box, that I know of - so you might have to use a scripted dataset, and write the code to do this. 

Should you decide to do this, please pay attention to the libraries already in use by DevTest - currently Apache POI (see DEVTEST_HOME/lib/shared  for details).

There is a simple example of POI usage here Apache POI – Reading and Writing Excel file in Java  - with a Sheet iterator.