Service Virtualization

  • 1.  Read Write From Excel File

    Posted Jun 11, 2016 12:12 PM

    Hi Folks,

     

    I need to write request & response to an excel file within my test case. I have some other fields too to be populated in excel.

    Can anybody help me with the script please.

    I am looking into Apache POI . Some new ideas or code snippets will be great.

     

     

    TIA

    Rahul V



  • 2.  Re: Read Write From Excel File
    Best Answer

    Posted Jun 11, 2016 12:41 PM

    Not sure if you have already looked into Apache POI examples.  This particular example should come very handy in your case. You can use the approach described and capture the script in JSR-223 step for this purpose. DevTest comes bundled with Apache POI library so there is no need to copy the library into the install base.

    https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/HSSFReadWrite.java 

     

    If a CSV output format can meet your need then you can also look at leveraging out of the box step "Write To Delimited File".  A comma separated file can be opened using Excel.



  • 3.  Re: Read Write From Excel File

    Posted May 03, 2017 01:46 AM

    Hi Rahul, 

     

    I also need to read/write the request and response of a test case in excel file. Since you had tried , can you please help me on this.

     

     

    Thanks in Advance

    Ram



  • 4.  Re: Read Write From Excel File

    Posted May 03, 2017 03:27 AM

    1) Capture the request message and the response message with filter on 1 of the step.

     

    2) Add in a scripted step to write into excel, I got this example by googling and amending it to fit my purpose. The example provided by Ashutosh Satyam, is pretty similar to what I have found.

     

    FileInputStream fileInputStream = new FileInputStream("C:/Users/DataStorage/SVDataStorage.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbook.getSheet("RequestMessage");
    int row1 = sheet.getLastRowNum() + 1;

    Object[] NewOrder = {"{{FilteredRequest}}", "{{FilteredResponse}}"};

    Row row = sheet.createRow(row1);

    int columnCount = 0;
    for (Object field : RequestMessage) {
    Cell cell = row.createCell(columnCount++);
    if (field instanceof String) {
    cell.setCellValue((String) field);
    } else if (field instanceof Integer) {
    cell.setCellValue((Integer) field);
    }
    }

    FileOutputStream outputStream = new FileOutputStream("C:/Users/DataStorage/SVDataStorage.xlsx");
    workbook.write(outputStream);

     

    3) To read the response from the excel file as a response, you can use the data-driven Virtual Service. If you intend to use a scripted step to query and read the response message from excel, you may need to install an extension into your DevTest in order to load the excel file into your VSM.



  • 5.  Re: Read Write From Excel File

    Posted May 03, 2017 01:50 PM

    Thanks Eric...

     

    I have tried with the above script, and got the below error. Can you please help me on this.

     

    ===========================================================================
    | Error in Script
    ============================================================================
    | Step: Write to an Excel
    ----------------------------------------------------------------------------
    | Message: Sourced file: inline evaluation of: ``File excel = new File("C:/Users/Desktop/CreditCardExpense.xlsx"); . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 4 : in file: inline evaluation of: ``File excel = new File("C:/Users/1518408/Desktop/CreditCardExpense.xlsx"); . . . '' : XSSFWorkbook

    ----------------------------------------------------------------------------
    | Trapped Exception: Sourced file: inline evaluation of: ``File excel = new File("C:/Users/Desktop/CreditCardExpense.xlsx"); . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 4 : in file: inline evaluation of: ``File excel = new File("C:/Users/1518408/Desktop/CreditCardExpense.xlsx"); . . . '' : XSSFWorkbook

    | Trapped Message: bsh.EvalError: Sourced file: inline evaluation of: ``File excel = new File("C:/Users/Desktop/CreditCardExpense.xlsx"); . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 4 : in file: inline evaluation of: ``File excel = new File("C:/Users/1518408/Desktop/CreditCardExpense.xlsx"); . . . '' : XSSFWorkbook

    ----------------------------------------------------------------------------
    STACK TRACE
    bsh.EvalError: Sourced file: inline evaluation of: ``File excel = new File("C:/Users/Desktop/CreditCardExpense.xlsx"); . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 4 : in file: inline evaluation of: ``File excel = new File("C:/Users/1518408/Desktop/CreditCardExpense.xlsx"); . . . '' : XSSFWorkbook

    at bsh.BSHAmbiguousName.toClass(BSHAmbiguousName.java:73)
    at bsh.BSHType.getType(BSHType.java:154)
    at bsh.BSHTypedVariableDeclaration.eval(BSHTypedVariableDeclaration.java:75)
    at bsh.Interpreter.eval(Interpreter.java:664)
    at bsh.Interpreter.eval(Interpreter.java:758)
    at bsh.Interpreter.eval(Interpreter.java:747)
    at com.itko.lisa.test.ScriptExecHandler.executeScript(ScriptExecHandler.java:636)
    at com.itko.lisa.test.ScriptExecHandler.executeScriptOld(ScriptExecHandler.java:528)
    at com.itko.lisa.test.ScriptNode._execute(ScriptNode.java:126)
    at com.itko.lisa.test.ScriptNode.execute(ScriptNode.java:103)
    at com.itko.lisa.test.TestNode.executeNode(TestNode.java:981)
    at com.itko.lisa.test.TestCase.execute(TestCase.java:1283)
    at com.itko.lisa.test.TestCase.execute(TestCase.java:1198)
    at com.itko.lisa.test.TestCase.executeNextNode(TestCase.java:1183)
    at com.itko.lisa.editor.WalkThruPanel.prepAndExecNode(WalkThruPanel.java:1050)
    at com.itko.lisa.editor.WalkThruPanel.access$900(WalkThruPanel.java:70)
    at com.itko.lisa.editor.WalkThruPanel$10.doCallback(WalkThruPanel.java:963)
    at com.itko.util.swing.panels.ProcessingDialog$2.run(ProcessingDialog.java:194)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.lang.ClassNotFoundException: Class: XSSFWorkbook not found in namespace
    at bsh.Name.toClass(Name.java:664)
    at bsh.BSHAmbiguousName.toClass(BSHAmbiguousName.java:71)
    ... 18 more
    ============================================================================



  • 6.  Re: Read Write From Excel File

    Posted May 04, 2017 09:57 PM

    You will need to import the libs,

     

    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.Date;

    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFDataFormat;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.xssf.util.XSSFColor;



  • 7.  Re: Read Write From Excel File

    Posted May 03, 2017 02:38 PM

    Devtest provide features to achieve this, try below options. 

     

    1. Read from excel file - Read Rows from Excel file under Data sets in step Information. Each column name by default become Lisa properties, so we can directly use to reach column values (request xml/json etc ).
    2. Write to excel file - Use “Save Property value to file” under utilities -> Filters in step Information panel. Use step name like {lisa.step name.rsp }} or property name to save in excel file. In location give path and file name like c:/test.xlsx

     

    If above not serve your requirement, you can search in community for point 2 as someone already share customer extension jar to write into excel file.  Also same can be achieve using Scripted Assertion using POI or few other library.

     

    Hoping this helpful !!

    Thanks,

    Rajesh k Singh



  • 8.  Re: Read Write From Excel File

    Posted May 03, 2017 09:51 PM

    Thank you Rajesh...

    My requirement is to check multiple match criteria (scenarios) and then write it in an excel file against appropriate scenario. To achieve the same I am trying with Javascript.


    If you have any sample scripts please share to me.

     

    Many Thanks

    Ram



  • 9.  Re: Read Write From Excel File

    Posted May 04, 2017 03:48 AM

    Replied your query on other thread !