cancel
Showing results for 
Search instead for 
Did you mean: 

Inserting data to excel with Groovy script

Occasional Contributor

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 ).
11 REPLIES 11
Frequent Contributor

Re: Inserting data to excel with Groovy script

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
Occasional Contributor

Re: Inserting data to excel with Groovy script

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
Occasional Contributor

Re: Inserting data to excel with Groovy script

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
Contributor

Re: Inserting data to excel with Groovy script

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

Occasional Contributor

Re: Inserting data to excel with Groovy script

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

Don
Contributor

Re: Inserting data to excel with Groovy script

Hello mani1.

 

I'm not sure I understand your question.

 

So you get a response from the API and you want to update the response into an excel file? So you get the API response, and insert it into a specific row and column and when you run the test again you want to over write the response from before?

 

 

Occasional Contributor

Re: Inserting data to excel with Groovy script

Hello Don,

 

Thanks for your response, Yes, I am getting the response from API and I want to update the response content in to an existing excel file in to a specific cell (row, column) and it shouldn't delete any existing cell value (row, column) from that excel.

I am able to update the response in to new excel file, which I don't want.

Please help me to solve this, it would be fine if any sample code with example, will be very helpful

 

Thanks & Regards,

Murugan Chelliah

 

 

Occasional Contributor

Re: Inserting data to excel with Groovy script

Hello Don,

 

Yes, Also if I run the test again, it should overwrite the content on the specific cell.

New Contributor

Re: Inserting data to excel with Groovy script

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

New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors