Reading data from Excel

Class library usage, coding and language questions.
dal
Posts: 72
Joined: Thu Jun 24, 2010 8:59 am

Reading data from Excel

Post by dal » Wed Oct 13, 2010 11:36 am

Team,

I am using the following code to read value from an Excel Sheet. The probelm is whenever this function is getting called the new EXCEL.EXE is getting added in Task Manager. Its not killing the earlier Excel Processes.

Can you please suggest me what would be the change I need to make in my code?

Code: Select all

Public Shared Function ExcelRead (ByVal ExcelPath As String, ByVal SheetName As String, ByVal RowNo As Integer, ByVal ColumnName As String, ByRef CellValue As String) As String
						
	Dim xcApp As New Excel.Application
	Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open(ExcelPath)
	Dim xcWS As Excel.Worksheet = CType(xcWB.Worksheets(SheetName), Excel.Worksheet)
	Dim xcRange As Excel.Range
	Dim columnCount As Integer
							
	Try
		xcRange = xcWS.UsedRange
				
		For columnCount = 1 To xcRange.Columns.Count
		                Dim cellData As String
			xcRange = xcWS.Cells(1, columnCount)
			cellData = xcRange.Text
					If cellData = ColumnName Then
						Exit For
					End If
				Next
				
				xcRange = xcWS.Cells(RowNo,columnCount)
				CellValue = xcRange.Text
				Marshal.ReleaseComObject(xcRange)
			Catch e As Exception
				Return Nothing
				Report.Error("Reading the Data from Excel Sheet - Unexpected Exception Occured"+ e.ToString())
			End Try
			
			Return CellValue
						
                'clean up
	xcRange = Nothing
	xcWS = Nothing
	xcWB.Close()
	xcWB = Nothing
	xcApp.Quit()
	xcApp = Nothing
End Function
Regards,
Dal...

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Reading data from Excel

Post by Support Team » Wed Oct 13, 2010 12:09 pm

Hi,

We have an own Excel Plug-In which supports the access for the worksheets and cells. Therefore please take a look to following blog http://www.ranorex.com/blog/excel-test- ... or-ranorex

Regards,
Peter
Ranorex Team
.
Image

dal
Posts: 72
Joined: Thu Jun 24, 2010 8:59 am

Re: Reading data from Excel

Post by dal » Wed Oct 13, 2010 1:16 pm

Yes I have gone through the Blog...

Can you provide some sample code to retrive the data from an Excel Sheet located in C:// Drive.

Bit struggling to understand the Adaptors provided for Excel.

Regards,
Dal...

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Reading data from Excel

Post by Support Team » Thu Oct 14, 2010 12:26 pm

Hi Dal,

I think you should place your return "string" after the clean up section, because at the moment the clean up section will never executed.

Regards,
Peter
Ranorex Team
.
Image

atom
Posts: 357
Joined: Sun Dec 07, 2008 11:14 pm
Location: Dublin, Ireland

Re: Reading data from Excel

Post by atom » Fri Oct 15, 2010 12:43 pm

For excel 2003 documents we use a library called NPOI (http://npoi.codeplex.com/)
It has the advantage of reading binary .xls documents - without starting Excel process

dal
Posts: 72
Joined: Thu Jun 24, 2010 8:59 am

Re: Reading data from Excel

Post by dal » Tue Oct 26, 2010 5:40 am

Yes it was my mistake by placing the return statement on top of the clean up. I thought the return variable might loss the data after the clean up.

Its working fine by placing it after the clean up.

Regards,
dal...