Selenium - How to copy entire excel column without using iterator

Question

I'm new to selenium and I'm having following query.

Query: Am working on a project where I need to copy 4000 values from an excel in a single go and I need to paste that in the list box of application. (The list box in the application can accept maximum of 4000 values)

The excel consists of 4000 values in its first 4000 rows(In a single column). I just need to copy all the values instead of using iterators.

Can anyone help me in achieving this?

• Mohan: Hi, I tried VB scripting to copy the value. I used cell range to perform copy action.
• IAmMilinPatel: And what went wrong with that code? Did you get any error? And again can we see your code if possible? Or at least the logic/algorithm you used?
• Dee: what kind of list box, plain text or with the formatting? It is some rich text web editor or just an inputbox? If this is plain text inpubox, then you can try to insert just text with CR/LF
• Michael Durrant: What is a 'list box'. a select dropdown? an input field? We can help you but we need more info to do it.
• Michael Durrant: And what are you trying to do. Users wouldn't type 4000 characters so that is not realistic user action you are trying to represent. So again, what are you really trying to achieve here at a higher level ?

Answer 1

@Mohan - Can you add why you particularly looking for without iterator?

Very simple solution without iterator

public Object[][] signUpPage1FieldValidationData() throws Exception{
 Object[][] retObjArr=getTestData("<your .xls file name","<your tab name>", "<fields which you wanted to fetch>");
 return(retObjArr);

Let me know, if you have some dificulty. All the best!

Answer 2

You may try jxl.Sheet.getColumn function of jxl.jar. Only catch is jxl does not support the "xlsx" file format.

• IAmMilinPatel: might want to add a little more than just a link.
• Kate Paulk: While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review
• corsiKa: I don't see this answer as being super low quality. It suggests a library and a specific function to avoid iterators. It provides a caveat for what files will work or won't. I agree it would be better with some example code, but going to the jxl api makes it fairly clear. Maybe I'm just in the Thanksgiving spirit or something. :-)
• Kenil Fadia: @KatePaulk I thought specific function from a particular library would suffice.

Answer 3

If you are using an excel file for inputting all different fields on your

application, you can put 4000 characters data in each cell for a column in excel

Then you can call that column everytime you are inputting value in your listbox,

and it will return random cell value from the column.