Forum Discussion

MartinS's avatar
MartinS
Occasional Contributor
14 years ago

Inserting data to excel with Groovy script

Hello, i am trying to make a groovy script which reads data from excel and inserting them to the same excel but on different sheet. Is there any possiblity to do it with Groovy? Actually i tryed something but it doesnt work how i want

I have excel file "doc_import_detail.xls" and there are 2 sheets, "import response" and "index request". I want to read some data from "import response" sheet and insert it to "index request" sheet. In soapUI pro it can be done with 2 data sinks but i have to insert multiple datas to differents sheets so i dont think that its good to have multiple data sinks in one test case.... So i wanted to try write a script which can save data from response to excel sheets.

I red a lot of tutorials about writing to excel but all of them are creating whole new excel workbook. I want 1 existing workbook with multiple sheets and inserting data from response to multiple sheets by one groovy script.

If u can give me some link where i can learn how to do that i will be gratefull(u can give me a sample source code too ).
  • deepesh_jain's avatar
    deepesh_jain
    Frequent Contributor
    14 years ago
    Hello Martin,

    You can easiliy do this using open source jxl library files. Download jxl.jar which is available freely on net and do an import for following classes.

    import jxl.*;
    import jxl.write.*

    You can then use excel objects easily, something like this:

    def inFile = "C:\\inputFile";
    def outFile = "C:\\outputFile";

    Workbook inputFile = Workbook.getWorkbook(new File(inFile));
    Workbook outputFile = Workbook.getWorkbook(new File(outFile));
    // IF you are using the same file, you don't need to create 2 workbook objects.

    Sheet inputWorkSheet = inputFile.getSheet("Sheet1");
    Sheet outputWorkSheet = outputFile.getSheet("Sheet2");

    You should be able to get a detailed api of jxl.jar online using which you can read and write cells in your excel. Here is the link for the documentation of classes and objects:

    http://jexcelapi.sourceforge.net/resour ... index.html

    Hope this helps.

    Regards,
    Deepesh Jain
  • Sandeepss's avatar
    Sandeepss
    Occasional Contributor
    13 years ago
    Hi
    I have used your code only and done some R&D finally i got your expected result here belowo is code.

    import jxl.*;
    import jxl.write.*

    Workbook workbook = Workbook.getWorkbook(new File("d:\\NewExcel.xls"));
    WritableWorkbook copy = Workbook.createWorkbook(new File("d:\\NewExcel.xls"),workbook);


    Sheet sheet1 = copy.getSheet("Sheet4");
    WritableSheet sheet2 = copy.getSheet("Sheet1");


    CL = 0;
    rows = sheet1.getRows();
    for( tc_row in 1..rows-1){
    WritableCell tmpA = sheet1.getCell(CL,tc_row);
    String s1 = tmpA.getContents();
    log.info("$s1")
    Label label = new Label(CL,tc_row,s1);
    sheet2.addCell(label);

    }
    copy.write();
    copy.close();
    workbook.close();


    I have also one query plz help me out in that...

    Regards,
    Sandeep S S
    • Don's avatar
      Don
      Contributor
      9 years ago

      Hello,

       

      I know this thread is old but hopefully I can get some answers. I am trying to do the same, get data from an excel file and write more data onto the same Excel file but in a different row. I tried to use DataSink but it only writes it into a new Excel Tab page.

       

      Is there a way I can write the results on the same excel file but on different columns,

       

      Thanks

      • mani1's avatar
        mani1
        Occasional Contributor
        8 years ago

        Hi

        I am having the same requirement. 

         

        I want to update the soap response into existing excel file with specific row and column, I tried, but Not get the result yet. 

         

        I am able to save the response into new excel sheet, 

         

        Can anyone help me on this, I am new to this groovy script.

         

        Thanks & Regards,

        Murugan Chelliah

    • raviteja143's avatar
      raviteja143
      New Contributor
      8 years ago

      will u please explain the above code.i am beginner in  soap ui pro

    • raviteja143's avatar
      raviteja143
      New Contributor
      8 years ago

      will u please explain the above code.i am beginner in  soap ui pro

       

       


      CL = 0;
      rows = sheet1.getRows();
      for( tc_row in 1..rows-1){
      WritableCell tmpA = sheet1.getCell(CL,tc_row);
      String s1 = tmpA.getContents();
      log.info("$s1")
      Label label = new Label(CL,tc_row,s1);
      sheet2.addCell(label); 

  • MartinS's avatar
    MartinS
    Occasional Contributor
    14 years ago
    Hi Jain.

    I tryed to make a script where i am accesing data in one sheet and inserting them to another sheet in one excel.
    Workbook excelFile= Workbook.getWorkbook(new File("c://test.xls"));
    Sheet inputSheet = excelFile.getSheet("Sheet1");
    Sheet outputSheet = excelFile.getSheet("Sheet2");

    Now i have excel file and sheet. And the only think i can do is to read cell content (Cell A = inputSheet.getCell(Columm, Row) ).So i tryed something with WritableWorkbook, sheet and cell. But i didnt find if WritableWorkbook can read existing excel file and then change content of this file. The only thing which works in some way is this sample:

    import jxl.*;
    import jxl.write.*

    Workbook workbook = Workbook.getWorkbook(new File("d:\\test.xls"));
    WritableWorkbook copy = Workbook.createWorkbook(new File("d:\\tmp.xls"), workbook);

    Sheet sheet1 = copy.getSheet("Doc Input");
    WritableSheet sheet2 = copy.getSheet("Doc Response");
    CL = 0;
    rows = sheet1.getRows();
    for( tc_row in 1..rows-1){
    WritableCell tmpA = sheet1.getCell(CL,tc_row);
    String s1 = tmpA.getContents();
    Label label = new Label(CL,tc_row,s1);
    sheet2.addCell(label);

    }
    copy.write();
    copy.close();
    workbook.close();

    But in this sample i am creating new excel file which i dont want I started to learn soapUI and Groovy 5 days ago so be patient with me please

    Martin
  • Preetinder's avatar
    Preetinder
    Frequent Visitor
    6 years ago

    Hi,

     

     

    Using SoapUI Open Source.

    How to save each request and response in file , when input the data from Excel.

     

    I tried but I can only save first input from excel (Request and response).can anyone help on this please.

     

    This my code:

    def res=context.expand('${GetProduct#Request}')
    new File("C:/Users/Psandhu/Req/REQUESTDATA.xml").write(res)

    def myOutFile = "C:/Users/Psandhu/RES/RESPONSEDATA.xml"
    def response = context.expand( '${GetProduct#Response}' )
    def f = new File(myOutFile)
    f.write(response, "UTF-8")

     

     

    Second Code I tried this, but give me error for :

    Groovy.Lang>missingPropertyException: No such Property:tetsStepresult for class

     

    def pName = context.currentStep.testCase.testSuite.project.name //get project name
    def pDate = new Date().format( 'yyyyMMdd' )//get current date
    def sDate = pDate.toString()//convert date to string
    def pTestSuite = context.currentStep.testCase.testSuite.name//get TestSuite name
    def pTestCase = context.currentStep.testCase.name//get TestCase name
    def filePath = 'C:/Users/Psandhu/'+pName+'_'+sDate+'/'+pTestSuite+'/'+pTestCase+'/'//compose the folder path
    //
    File file2 = new File(filePath)
    if (!file2.exists()) file2.mkdirs()//create the destination folder
    //

    //
    fos = new FileOutputStream(filePath+ testStepResult.testStep.label + '.txt', true)
    pw = new PrintWriter( fos )
    testStepResult.writeTo( pw )
    pw.close()
    fos.close()