Facebook

For any corporate training requirements Please mail To sudhakar@qtpsudhakar.com
New Schedule in MindQ Systems, Madhapur By SUDHAKAR
Course Name Date Time Days Type Branch
Selenium+Appium June 19th 2017 7.30AM-9.00AM IST 45 ClassRoom + Online Hitech City
Selenium+Appium June 28th 2017 9.30AM-11.00AM IST 45 ClassRoom + Online Hitech City

A book "Cracking the QTP interview" authored by Sudhakar.Read More. Buy From FlipKart

Tuesday, February 7, 2012

Import XLSX Sheet to QTP Datatable

Import XLSX Sheet to QTP Datatable

Lot of people are facing problem with one of the major limitation of QTP. That is importing data from XLSX extension files. But using Excel Object Model we can overcome this problem.
The below function will import the data from XLSX file to QTP specified data sheet.

Function ImportSheetFromXLSX(dFileName,dSourceSheetName,dDestinationSheetName)

  Dim ExcelApp
  Dim ExcelFile
  Dim ExcelSheet
  Dim sRowCount
  Dim sColumnCount
  Dim sRowIndex
  Dim sColumnIndex
  Dim sColumnValue

  Set ExcelApp=CreateObject("Excel.Application")
     Set ExcelFile=ExcelApp.WorkBooks.Open (dFileName)
     Set ExcelSheet = ExcelApp.WorkSheets(dSourceSheetName)

  Set qSheet=DataTable.GetSheet(dDestinationSheetName)

  sColumnCount= ExcelSheet.UsedRange.Columns.Count
  sRowCount= ExcelSheet.UsedRange.rows.count

  For sColumnIndex=1 to sColumnCount

    sColumnValue=ExcelSheet.Cells(1,sColumnIndex)
    sColumnValue=Replace(sColumnValue," ","_")

    If sColumnValue="" Then
     sColumnValue="NoColumn"&sColumnIndex
    End If

    Set qColumn=qSheet.AddParameter (sColumnValue,"")

    For sRowIndex=2 to sRowCount
     sRowValue=ExcelSheet.Cells(sRowIndex,sColumnIndex)
     qColumn.ValueByRow(sRowIndex-1)=sRowValue
    Next

  Next
  Set ImportSheetFromXLSX=qSheet
   ExcelFile.Close
   ExcelApp.Quit
End Function
'********************************************************************************************************

There is also another way of overwriting Datatable object to import XLSX files. This is useful for the projects which are already used Datatable.ImportSheet in lot many places and now they want to enable XLSX support without modifying QTP script. Below is the example on how to over write datatable object with newly created class. If you use below code you need to update all existing QTP datatable object methods and properties.
EnableXLSXsupport()
DataTable.ImportSheet "C:\Users\sudhakar\Desktop\Blog Data\test.xlsx","Sheet2","Action1"

'********************************************************************************************************
Function EnableXLSXsupport()

 ExecuteGlobal "Dim QTPDataTable"
 
 Set QTPDataTable=Datatable
 ExecuteGlobal "Dim Datatable"
 
 Set Datatable=New CustomDatatable

End Function
'********************************************************************************************************
Class CustomDatatable

 Function ImportSheet(dFileName,dSourceSheetName,dDestinationSheetName)

  Dim ExcelApp
  Dim ExcelFile
  Dim ExcelSheet
  Dim sRowCount
  Dim sColumnCount
  Dim sRowIndex
  Dim sColumnIndex
  Dim sColumnValue

  Set ExcelApp=CreateObject("Excel.Application")
     Set ExcelFile=ExcelApp.WorkBooks.Open (dFileName)
     Set ExcelSheet = ExcelApp.WorkSheets(dSourceSheetName)

  Set qSheet=QTPDataTable.GetSheet(dDestinationSheetName)

  sColumnCount= ExcelSheet.UsedRange.Columns.Count
  sRowCount= ExcelSheet.UsedRange.rows.count

  For sColumnIndex=1 to sColumnCount

    sColumnValue=ExcelSheet.Cells(1,sColumnIndex)
    sColumnValue=Replace(sColumnValue," ","_")

    If sColumnValue="" Then
     sColumnValue="NoColumn"&sColumnIndex
    End If

    Set qColumn=qSheet.AddParameter (sColumnValue,"")

    For sRowIndex=2 to sRowCount
     sRowValue=ExcelSheet.Cells(sRowIndex,sColumnIndex)
     qColumn.ValueByRow(sRowIndex-1)=sRowValue
    Next

  Next
   'Set Datatable=QTPDataTable
   ExcelFile.Close
   ExcelApp.Quit
   'Set QTPDataTable=Nothing
 End Function
'********************************************************************
 Function AddSheet(shtName)
   Set AddSheet=QTPDataTable.AddSheet(shtName)
 End Function 
'********************************************************************
End Class
'********************************************************************************************************
If you uncomment the Set Datatable=QTPDatatable and Set QTPDatatable =Nothing then this function only works for once. When ever you want to import XLSX sheet then you need to call EnableXLSXsupport() before you use import sheet. This way is for the experts who can handle overwriting of reserved objects. Do remember that If you have not uncommented, the existing datatable methods will not work and you need to define each method in customdatatable class in order to work. You can observe the "AddSheet" method in the above class example.
_______________________________________
Please send your Suggestions and Doubts to my yahoo group http://in.groups.yahoo.com/group/qtpsudhakar

6 comments :

  1. But this does not import more than 256 columns. can any on thell me how this can be done.

    ReplyDelete
  2. That is the count of total columns in QTP datatable. in this case you must go with excel object model.

    ReplyDelete
  3. CAN WE USE DATATABLE METHODS instead of excel object model

    ReplyDelete
  4. QTP only support .xls files. That's the I have used excel object model to import xlsx file.

    ReplyDelete
  5. Can we retrieve the .xlsx file with column value more than 256?

    ReplyDelete
  6. I think you are using old MS office, but in New MS office 2010 there is not limitation of column and row in xlxs. You can use n number or row and column, hope it will help you..

    Thanks,
    Manish Soni

    ReplyDelete