Forum Discussion

tpoirier's avatar
tpoirier
Contributor
13 years ago

Reading values out of cell in Excel

I am tryint to read a data to use in a test out of an excel document. I was going to use the CSV DDT but I need to read in different rows and store them in arrays and that was not working out so I am tryting to do it with excel. So if someone can provide an example of how to read 1 row and all columns (columns may have different length) that would also be a solution to the problem!



I can connect but when I attempt to rotate through the cells and write them out to an array I get an error. 




var Uarray = [];



var SEarray = [];



var Carray = [];

 



for(var i = 0; i < 6; i++) {



switch(VarToStr(Excel.Cells(i,0))) //This is line 23 

// I have tried Excel.ActiveSheet.Cell



{



case "E":



for(var j=0; j < 13; j++) {



Earray = VarToStr(Excel.Cells(i, j));



Log.Message(VarToStr(Excel.cells(i,j)));



}



break;



case "U":



for(var k=0; k < 13; k++) {



Uarray = VarToStr(Excel.Cells(i, k));



Log.Message(VarToStr(Excel.cells(i,k)));



}



break;



case "C":



for(var l=0; l < 13; l++) {



Carray = VarToStr(Excel.cells(i, l));



}



break;



default:



Log.Message("Value found that was not expected");



break;



}



}



Error: Microsoft JScript runtime error

Expected ';'

Error Location: Line 23 Col 5



Basically what I am looking to do is read in a row at a time, look at the first value (cell) then depending on its value place the remaining cells that are in that row into an array. Then move to the next row. I know the code up there is not completly finished to do that.


  • tpoirier's avatar
    tpoirier
    Contributor
    13 years ago

    Got it figured out, below is working code to search for a users My Document Folder, append a file to open and then rotate through the first three rows and grad any data that isn't the sort cells or empty then place it in an array



    Thanks for the help!



    function GetDocuments() {


      var WshShell, objShell, path;


      


      MY_DOCUMENTS  = 5;


      objShell = new ActiveXObject("Shell.Application");


      path = objShell.Namespace(MY_DOCUMENTS).Self.path; //determins the path to the users My Documents folder


     


      FilePath = path + "\\APITestFile.xls"; //adds file name to open


      


      var Excel = Sys.OleObject("Excel.Application");


      Excel.Workbooks.Open(FilePath); //open the file at the file path built above


      Excel.Visible = true;


      


      var Uarray = [];


      var Earray = [];


      var Carray = [];


      


      var e = "";


      var U = "";


      var C = "";


      


      for(var i = 1; i < 4; i++) { //iterate through the first three rows of data in the file


      var cell = VarToStr(Excel.Cells(i, 1).Value); //grab the cell value in the first col of the row


        switch(cell) //figure out which array to place the value in


        {


          case "E":


            for(var j=1; j < 13; j++) {


              strStyle = VarToStr(Excel.Cells(i,j).Value);        


              if((e !== "") &&  ( e !== "E")) //check to see if it is empty or if the sort cell is selected


              {


                Earray = e; //only keep the data that is after the sort cell


                Log.Message(e);


              }


            }


            break;


          case "U":


            for(var k=1; k < 13; k++) {


              U = VarToStr(Excel.Cells(i, k).Value);


              if((U !== "") && (U !== "U"))


              {


                Uarray = U; 


                Log.Message(U);


              } 


            }


            break;


          case "C":


            for(var l=1; l < 13; l++) {


              strC = VarToStr(Excel.cells(i, l).Value);


              if((C !== "") && (C !== "C"))


              {      


                Carray = C;


                Log.Message(C); 


              }  


            }


            break;


          default:


            Log.Message("Value found that was not expected: " + cell); //log any errors found


            break;


          }


      


      }


      Excel.Workbooks.Close(); //close it up!

  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    13 years ago

    Hi,


     


    What if you separate your line 23 in the following way?




     ...


     var cell = VarToStr(Excel.Cells(i,0));


     switch(cell) //This is line 23 


      // I have tried Excel.ActiveSheet.Cell


     {


     ...


     


      


     I recommend that you go through the TestComplete sample demonstrating how to read data from Excel files. You can find it on your computer here:


     <TestComplete Samples>\Miscellaneous\MSOffice\


      

  • tpoirier's avatar
    tpoirier
    Contributor
    13 years ago
    I have tried that, however it still throws the same error but now shows the issue to be in the assign line where I put the value in 'cell'.



    I will look at the documentation here shortly and update if I can find a fix



    Thanks!
  • tpoirier's avatar
    tpoirier
    Contributor
    13 years ago
    UPDATE: When working with Excel and looping through a row of cells make sure your index starts at 1 starting at 0 will come up with an error saying a semi colon ':' was expected. This error can be very misleading and left me looking for a problem that wasn't there!
    • hhagay's avatar
      hhagay
      Contributor
      9 years ago
      UPDATE:
       
      Replace:
      var cell = VarToStr(Excel.Cells(i, 1).Value);
       
      With:
      var cell = (VarToString(Excel.Cells.Item(i, 1)));