Forum Discussion

bassun's avatar
Regular Visitor
6 years ago

Updating Excel via Groovy script, crashes during loops.

Hello all - I'm a novice with ReadyAPI and Groovy, but here is the situation:
I want to update individual cells within an excel sheet, utilizing a loop such that I am updating new information on each line of the datasheet for subsequent uses during each execution of the datasource loop.  Basically, each iteration of the test, I want to update data into the datasheet.


I can make it work fine, one time.  As soon as I begin looping through for additional runs I get errors.  Usually either a Biff error about the file not existing or an array out of bounds.  In either case, the problem is driven by the excel book being open.  I cannot even open it manually as SoapUI has hold on it.  If, however, I restart my script, and do NOT allow it to loop - it works fine and closes as expected.  Even if it had the error previously.


I am not sure how the flow impacts it, but the mechanics are that I am taking a datafile from location "X", making a copy of it into a dynamically named folder structure, and placing the xls file there (not xlsx) [using Ant for this portion].  I then pull data from the sheet, hit whatever service, and want to store the returns in the same file for later runs / test cases, etc.  To do so, I am using jxl, and leveraging a copy of the book with a writableworkbook.


For this example, I am simply trying to write the var "info" into each cell as a proof of concept.  Gathering the data isn't an issue.  I am controlling the row pointer using a suite variable to align the output with the correct input from the datasource.


Here is the code:


import jxl.*
import jxl.write.*
def location = context.expand('${#TestSuite#dataFilePath}') location //Path remains correct

def row = context.expand('${#TestSuite#currentRow}') row //Row is correct

def myrow = row.toInteger()
def inFile = (location);
def info = "SuperDOG" //Just some rando data to test the flow.
        Workbook aWorkBook = Workbook.getWorkbook(new File(location));
        WritableWorkbook aCopy = Workbook.createWorkbook(new File(location), aWorkBook);
        WritableSheet aCopySheet = aCopy.getSheet(1);
        Label writableCell1 =  new Label(0,2,info);
    /* Commented out just for simplicity during troubleshooting
        Label writableCell2 =  new Label(1,myrow,info);
                Label writableCell3 =  new Label(2,myrow,info);
                Label writableCell4 =  new Label(3,myrow,info);
                Label writableCell5 =  new Label(4,myrow,info);
                Label writableCell6 =  new Label(5,myrow,info);
                Label writableCell7 =  new Label(6,myrow,info);
        aCopy.close();  //This is where the problem seems to be.  It's as if it is not actually closing the file.

      myrow = myrow + 1
      def returnrow = myrow.toString()
      def testSuiteProperty =  testRunner.testCase.testSuite.setPropertyValue( "currentRow", returnrow )

I've banged around on this for too long and cannot seem to figure out where the problem is, or how to work through it.  I would rather update the file on each iteration, but I guess as a work around I "could" write all of the data into an array, then write the array to the file all at once in the end.  In retrospect, that seems like it would be the faster and more efficient method; but I am still perplexed as to why this "wont" work.  

Any insight would be apprecaited!


1 Reply

  • Olga_T's avatar
    SmartBear Alumni (Retired)

    Hi bassun,

    Thank you for the detailed description.
    Community, can anyone please look into this to try to find out why the code doesn’t work properly?