Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

Reading Excel spreadsheets without using a DSN connection in Silk4Net and SilkTest Workbench

Reading Excel spreadsheets without using a DSN connection in Silk4Net and SilkTest Workbench

Please note: this can ONLY be done if you have installed the Office Programming Tools (interop assemblies) during the installation of Microsoft Office.

The following code will open an Excel spreadsheet, count the number of worksheets in use and then return any data within those worksheets. You can copy and paste this code into an existing testcase - make sure you change the hard-coded spreadsheet path - or, more usefully, include it in a reusable functions module. The output from this code is sent to the results via console.writeline - you will need to modify this to suit your testing framework.

Within your project you will need to add a reference to the Microsoft.Office.Interop.Excel assembly and you will also need to import the assembly. Please note that as both SilkTest.ntf and Microsoft.Office.Interop.Excel have methods called 'Range' we need to give the fully qualified name to the Interop.Excel version. Failure to do so will result in an 'ambiguous method' error message when the code is compiled.

Imports System
Imports System.Diagnostics
Imports System.Collections.Generic
Imports Microsoft.VisualStudio.TestTools.UnitTesting
Imports Microsoft.Office.Interop.Excel
Imports SilkTest.Ntf
 
<SilkTestClass()> Public Class UnitTest1
 
	<TestMethod()> Public Sub TestMethod1()
  
		Dim excel As Application = New Application
 
		' Open the Excel spreadsheet
		Dim wBook As Workbook = excel.Workbooks.Open("C:\temp\test.xls")
 
		' Get the number of worksheets in use
		For i As Integer = 1 To wBook.Sheets.Count
			' Load the current worksheet
			Dim wSheet As Worksheet = wBook.Sheets(i)
			' Get the range of cells in the current worksheet
			Dim rCells As Microsoft.Office.Interop.Excel.Range = wSheet.UsedRange
			' Load all used cells into an array.
			Dim array(,) As Object = rCells.Value(XlRangeValueDataType.xlRangeValueDefault)
			' Scan the cells for data
			If array IsNot Nothing Then
				Console.WriteLine("Length: {0}", array.Length) 
				' Get the bounds of the array
				Dim boundUpper As Integer = array.GetUpperBound(0)
				Dim boundLower As Integer = array.GetUpperBound(1)
 
				Console.WriteLine("Dimension 0: {0}", bound0)
				Console.WriteLine("Dimension 1: {0}", bound1)
 
				' Loop through all the elements
				For j As Integer = 1 To boundUpper
					For x As Integer = 1 To boundLower
						Dim sData As String = array(j, x)
						Console.Write(sData)
						Console.Write(" "c)
					Next
					Console.WriteLine()
				Next
			End If
		Next
 
		' Close.
		wBook.Close()
	End Sub

If you use the attached spreadsheet as a test then it should generate the following results:

Length: 6
Dimension 0: 3
Dimension 1: 2
1 4
2 5
3 6

3162.excel_test.zip

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2013-04-23 03:57
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.