Ask a Question

reading and writing to spreadsheet

SOLVED
Adagio
Frequent Contributor

reading and writing to spreadsheet

Hello,

 

I've to use a spreadsheet to read the data from. This data would be used to run the Tests. I'm trying to write Javascript code to do that. I'm having some of the difficulties in getting the rowcount in the spreadsheet and then keeping the pointer at the row that I'm reading and then moving to the next row. I've to read one row at a time and then run the test. In the next iteration, I'd be reading the second row and then running the test. any help/guidance would be appreciated!

 

Thank you

Abhi

1 ACCEPTED SOLUTION

Accepted Solutions
Adagio
Frequent Contributor

Re: reading and writing to spreadsheet

Hi Helen,

 

No, these are not functions. These are some variables which hold the values from the spreadsheet.

I figured out the issue here. It was still being treated as an object and I was trying to read it without using any property.

Here's what I've used.

 

 

var getUsageCode = aqObject.GetPropertyValue(readExcel(i, 1, "Sheet1"), "Text")



 

Thanks for your help!

Abhi

View solution in original post

9 REPLIES 9
tristaanogre
Community Hero

Re: reading and writing to spreadsheet

What is the code you're using?  Are you accessing Excel directly using Sys.OleObject or are you using a DDT.ExcelDriver?  For what you're trying to do, as explained, you're reading each row in the spreadsheet and, for each row, you run the test.  That's a classic use of the DDT.ExcelDriver.  


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Marsha_R
Community Hero

Re: reading and writing to spreadsheet

Here's a link for what @tristaanogre mentioned:

 

https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sou...

Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
Adagio
Frequent Contributor

Re: reading and writing to spreadsheet

Thanks Robert! I was using the DDT driver, but the issue that I have is:

 

1. How do I read only 1 row at a time, feed the data in the Test and Run it and then keep the RowPosition stored in a variable?

2. In the Next Iteration, I'd want to read the Second row and get the Data and Run the Test. Keep Repeating the steps until the last populated row of the spreadsheet. 

 

 

Thank you

Abhi

Marsha_R
Community Hero

Re: reading and writing to spreadsheet

The DDT driver helps you go through the rows automatically.   There's an example for you in that link that I posted.  

 

"The code below illustrates the use of DDT.ExcelDriver. This code creates a DDT driver for an Excel sheet, runs through records of the driver’s table and posts values stored in record fields to the test log"

Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
tristaanogre
Community Hero

Re: reading and writing to spreadsheet

As @Marsha_R, the driver does that automatically... pseudo code would be something like.

function myTest(testData){
    Log.Message(testData);

}

function runExcelDriver(){
    var driver
    driver = DDT.ExcelDriver('C:\\MyFolder\\MyWorkbook.xls', 'MySheet', true);
    while (!driver.EOF){
        myTest(driver.Value('MyColumn'));
        driver.Next();
    }
    DDT.CloseDriver(driver.Name);

}

Basically, this will go through every row in the indicated spreadsheet and execute the "myTest" function against whatever values I pass into it.  There are probably a number of different ways to achieve the same result but, generally, this demonstrates having a DDT driver loop through a sheet and execute the tests in sequence.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Marsha_R
Community Hero

Re: reading and writing to spreadsheet

If one would actually click on the link I provided, one would see an actual Javascript sample already there.  🙂

Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
Adagio
Frequent Contributor

Re: reading and writing to spreadsheet

Thanks Marsha! I did, but I ended up using the COM object which works pretty well.

 

I have another confusion regarding this COM object. While printing the values that the object is reading, I see that It prints 'objectCOMobject' rather than the actual value of that object.

 

In the snapshot here: 

 

  Log.Message("Row Data: " + i + " " +getUsageCode+ " ; " +getComments+ " ; " +getResonVal+ " ; " +getRefVal);

 

COM.PNG

 

 

Thank you

Abhi

 

 

 

HKosova
Moderator

Re: reading and writing to spreadsheet


@Adagio wrote:

 

Log.Message("Row Data: " + i + " " +getUsageCode+ " ; " +getComments+ " ; " +getResonVal+ " ; " +getRefVal);


Hi Abhi,

 

Are getUsageCode, getComments etc. functions? If so, you need to put () after the function names in order to call them:

Log.Message("Row Data: " + i + " " +getUsageCode() + " ; " +getComments() + " ; " + getResonVal() + " ; " +getRefVal());

 

Tip: If you use JavaScript (not JScript), you can use template strings to avoid string concatenation:

Log.Message( `Row Data: ${i} ${getUsageCode()}; ${getComments()}; ${getResonVal()}; ${getRefVal()}` );

Helen Kosova
SmartBear Documentation Team Lead
________________________
Join Wintertainment 2021 to share your stories, have fun, earn community badges, and more!
Adagio
Frequent Contributor

Re: reading and writing to spreadsheet

Hi Helen,

 

No, these are not functions. These are some variables which hold the values from the spreadsheet.

I figured out the issue here. It was still being treated as an object and I was trying to read it without using any property.

Here's what I've used.

 

 

var getUsageCode = aqObject.GetPropertyValue(readExcel(i, 1, "Sheet1"), "Text")



 

Thanks for your help!

Abhi

View solution in original post

cancel
Showing results for 
Search instead for 
Did you mean: