Inserting data to excel with Groovy script

[MartinS]

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]

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

[MartinS]

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

[Sandeepss]

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]

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]

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]

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?

[mani1]

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

[mani1]

Hello Don,

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

[raviteja143]

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