Forum Discussion

mesg2anil's avatar
mesg2anil
Regular Contributor
14 years ago

Using Array to get Excel data

Hi,



I'm trying to get values (data) from excel, after using if and for loop to get rows and columns details, I'm trying to put the data to an array and then use the array to input the data from array to application. Below is the code which I'm using to get the data from excel and tying to put the data into array... this the place where I'm having trouble... please help!!



Function testarray()


'Open Excel Connection


 Set Excel = Sys.OleObject("Excel.Application")


 Call Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\ExcelFile\Rental Database\RentalTermSheet.xlsx")


 Delay 1000


 


 'Read number of rows and assign it to 'i'


 i = Excel.ActiveSheet.UsedRange.Rows.Count


 j = Excel.ActiveSheet.UsedRange.Columns.Count     


 


Log.Message i   


Log.Message j


 


 If i > 1 then   


for k = 2 to i     


For l = 1 to j 


s = VarToStr(Excel.Cells(k, l)) + "#"   


 


testarray = s 


 


next 


Next 


End if   


 


Call Excel.ActiveWorkbook.Save   


Call Excel.Workbooks.Close 


End Function


 


Sub arrcells


    


Dim str, arrStr 


Str = testarray() 


arrStr=Split(Str,"#") 


Log.Message arrStr (0)


'Log.Message arrStr(1) 


'Log.Message arrStr(2) 


 


End Sub

13 Replies

Show More
  • anildasmca's avatar
    anildasmca
    13 years ago


    function TestExcelKnowRowIndex()



    {



        var Row = 1;



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



        Excel.Workbooks.Open("C:\\Copy of sstD6.xls")



    var HeadRow =Excel.ActiveCell.CurrentRegion.ListHeaderRows;



    var AllRow = Excel.ActiveCell.CurrentRegion.Rows.Count;



    var RowCount = AllRow - HeadRow;



    var MyDDTObject = DDT.ExcelDriver("C:\\Copy of sstD6.xls", "TestList", true)



     



    while (Row != RowCount)



    {



    if (Row == 5)



    { while (Row != RowCount)



    {



    Log.Message(MyDDTObject.Value(6))



    MyDDTObject.Next();



    Row++;



    }



    }



    else



    {



    Row++;



    MyDDTObject.Next();



    }



    }



    DDT.CloseDriver(MyDDTObject.Name)



    }


  • pradeep_kamatha's avatar
    pradeep_kamatha
    Contributor
    13 years ago
    Hi Anil,

    I have got a similar requirement, but I need to store 9th and 10th column from CSV file data into Arrays. I was able to get the Column data, but not able to store in an Array. Kindly help me on this.






    var lResultDriver = DDT.CSVDriver(lFullOutputFileName);  


                //var lResultArray = new Array();


               


           


               Var ColCount = 9;


               while(!lResultDriver.EOF())


               {


                    if(ColCount ==9)


                    {


                     Log.Message(lResultDriver.Value(10) + "--> "+ aqConvert.VarToStr(lResultDriver.Value(9)));


                    


                    }


                    else


                    {


                     Log.Message("error");


                    }


                 


                 


                    //Log.Message(lResultDriver.Value(10) + "--> "+ aqConvert.VarToStr(lResultDriver.Value(9)));


                    lResultDriver.Next();


                    ColCount= ColCount+1;


              }


              


              DDT.CloseDriver(lResultDriver.Name);