Our vBulletin migration is complete.
Welcome vBulletin users! All content and user information from the Micro Focus Forums (vBulletin) site has been migrated to this site. READ MORE.

Read and Write to an Excel spreadsheet without a DSN connection (Rev.4.1)

Read and Write to an Excel spreadsheet without a DSN connection (Rev.4.1)

Note:

------------------

If you do not have the Microsoft Office Primary Interop Assemblies installed in either Silk4Net or Silk Test Workbench, you can download the installer package from here:

Office 2007 - http://www.microsoft.com/en-us/download/details.aspx?id=18346
Office 2010 - http://www.microsoft.com/en-us/download/details.aspx?id=3508

If you are using Silk Test Workbench, please see

http://microfocus.telligenthosting.net/borland/test/silk_test/w/knowledge_base/26421.silk-test-workbench-will-not-reference-the-microsoft-office-primary-interop-assemblies.aspx


before continuing.

This archive only works with versions of Office prior to Office 2016 or Office 365.

------------------


It is customary to use an ActiveData asset to read and write to an Excel spreadsheet but there are limitations to this approach; for example, ActiveData can only access a single sheet within the named workbook and this sheet cannot be changed dynamically during testing. ActiveData reads and writes single rows but there are times when it would be more convenient to read and write to a single cell, to read and write to multiple rows, to read and write a complete Excel sheet in one go or to add a new sheet to an existing workbook during testing.

The Microsoft Office Primary Interop Assemblies provide us with a series of methods that don't need a DSN to read and write to an Excel spreadsheet from both Silk Test Workbench and Silk4Net. The attached archive contains the class library as a dll (ExcelRW.dll) and a simple wrapper for the StreamReader and StreamWriter methods in dotNet (mffileio.dll). We'll use the wrapper to output Excel data to a text file when running the sample application. Wherever you put these dlls, if you are using Silk Test Workbench then you MUST ensure that the required Interop Assembly is in the same folder as ExcelRW.dll. For both Silk Test Workbench and Silk4Net you must then add a reference to the Interop Assembly and to the ExcelRW dll.

If you require, there should be no problems in using these libraries to read and write to an additional spreadsheet (or spreadsheets) from inside an ActiveData loop. However, it would be preferable to use either these libraries or ActiveData rather than both together.


The ExcelRW class library contains the following public methods:

getError()
readOneCell()
readOneRow()
readOneSheet()
writeOneCell()
writeOneRow()
writeSheet()

It also has a reasonably robust error reporting system so that in the event of a failed call you can query the dll for the exact error.

The code examples in the archive are written in vb.Net as this is common to both Silk Test IDEs.

 

Read Methods.


Please note that if any optional parameters are required then ALL preceeding optional parameters must also be filled in.

 

getError()

Returns the last internal error message as a string. The messages are:

error0 = "Success"
error1 = "Workbook not found"
error2 = "Sheet not found"
error3 = "Unknown error"
error4 = "No data"
error5 = "Sheet already exists"

e.g.

dim excelRW as new excelrW.excelrw

' Do something...
console.writeline(excelrw.getError())

So if the last internal error was error1 then

console.writeline(excelrw.getError())

will print

Workbook not found

 


readOneCell(ByVal xls As String, ByVal cell As String, Optional ByVal sheetname As String = "Sheet1")

xls = the path to the spreadsheet including the name of the spreadsheet.
cell = the alphanumeric specifier for the cell to receive the data e.g. G6, A9, BF3, etc..
sheetname = the name of the specific sheet within the workbook to read the cell data from. The default is 'Sheet1'.

Returns the data from a single specified cell as a string.

e.g.

dim excelRW as new excelrW.excelrw
dim sData as string = nothing

try
  sData=readOneCell("c:\temp\test.xls","A6")
catch
  console.writeline(excelrw.getError())
end try


readOneRow(ByVal xls As String, Optional ByVal sheetname As String = "Sheet1", Optional ByVal row As Integer = 0, Optional ByVal sep As String = "default")

xls = the path to the spreadsheet including the name of the spreadsheet
sheetname = the name of the specific sheet within the workbook to read the cell data from. The default is 'Sheet1'.
row = 0 to continue from the last row, -1 to reset to the start of the sheet, any valid number to read that specific row (default is zero).
sep = any valid alpha character to be used as a separator for the returned data. The default is a comma (chr(44)).

Returns a complete row of data from the specified row as a string. The individual cells within the string are delinated by sep.

e.g.

dim excelRW as new excelrW.excelrw
dim sData as string = nothing

try
  sData=readOneRow("c:\temp\test.xls","customers")
catch
  console.writeline(excelrw.getError())
end try

If the row parameter is never changed then each call to readOneRow() will return the data from each succeeding row. If a row number is specified at any point then the following call will begin from the specified row+1. To reset the data read to the beginning of the sheet, pass in row as -1 (minus one). It is up to you to ensure that the row to be read is valid in the context of your application as repeated unchecked calls to readOneRow() will run to the maximum number of rows allowed in a spreadsheet and will then error out.

In this and other methods with the 'sep' parameter, if you require to change the default separator to e.g. a full stop (decimal point) but do not want to change the row number then the method call will be

sData=readOneRow("c:\temp\test.xls","customers",0,chr(46))

or

sData=readOneRow("c:\temp\test.xls","customers",0,".")

i.e. a parameter for 'row' must be provided. Once the separator has been changed it will remain changed for the remainder of the session or until you make it something else. To return to default you will need to supply the parameter as chr(44) or ",". Changing the separator affects all methods that have 'sep' as an optional parameter.


readOneSheet(ByVal xls As String, Optional ByVal sheetname As String = "Sheet1")

xls = the path to the spreadsheet including the name of the spreadsheet.
sheetname = the name of the specific sheet within the workbook to read the cell data from. The default is 'Sheet1'.

Returns all the data on the sheet as a two-dimensional array of objects.

e.g.

dim excelRW as new excelrW.excelrw
dim obData As object(,) = nothing

try
  obData = excelRW.readOneSheet("c:\temp\test.xls", "customers")
catch
  console.writeline(excelrw.getError())
end try

The object array holds the data as row/column and is zero-based. The size of the object array can be found by using array.GetUpperBound(0) for the number of rows and array.GetUpperBound(1) for the number of columns. This is illustrated in the attached sample.

NOTE: readOneSheet() returns only the cells that have been used, so if your data starts in (e.g.) cell D4 and ends in (e.g.) cell G9, the object array will be four columns (D, E, F, G) by six rows (4, 5, 6, 7, 8, 9).



Write Methods.

The write methods all return the last internal error message directly.


writeOneCell(ByVal xls As String, ByVal cell As String, ByVal data As String, Optional ByVal sheetname As String = "Sheet1", Optional ByVal create As String = "false") as string

xls = the path to the spreadsheet including the name of the spreadsheet.
cell = the alphanumeric specifier for the cell to receive the data e.g. G6, A9, BF3, etc..
data = the data to be written to the cell.
sheetname = the name of the specific sheet within the workbook to write the cell data to. The default is 'Sheet1'.
create = if the spreadsheet does not exist then create it. Default is false (don't create).

e.g.

dim excelRW as new excelrW.excelrw
dim result as string = nothing

result=excelRW.writeOneCell("c:\temp\test.xls", "B3", "John Doe", "customers", true)
console.writeline(result)

This will create the spreadsheet text.xls if it doesn't exist in c:\temp and will name the first sheet as "customers". It will then enter "John Doe" into cell B3. Finally, the last internal error message will be placed into 'result'.

Use the 'create' parameter with care as a typographical error in the path or name of the Excel spreadsheet can lead to a spreadsheet being created in another folder and/or under a different name.


writeOneRow(ByVal xls As String, ByVal data As String, Optional ByVal sheetname As String = "Sheet1", Optional ByVal row As Integer = -1, Optional ByVal create As String = "false", Optional ByVal sep As String = "default") as string

xls = the path to the spreadsheet including the name of the spreadsheet.
data = the data to be written to the row. Must be presented in csv format.
sheetname = the name of the specific sheet within the workbook to write the data to. The default is 'Sheet1'.
row = 0 to continue from the last row on the sheet or any valid number to write to that specific row (default is zero).
create = if the spreadsheet does not exist then create it. Default is false (don't create).
sep = any valid alpha character to be used as a separator for the data string. The default is a comma (chr(44)).

e.g.

dim excelRW as new excelrW.excelrw
dim sData as string = nothing
dim result as string = nothing

sData="write,one,row,test"

result=excelRW.writeOneRow("c:\temp\test.xls", sData)
console.writeline(result)

Note: this call will fail if sep is not the same as the separator used in the data string. All data held in sData will be written to a single cell.


writeSheet(ByVal xls As String, ByVal data(,) As Object, Optional ByVal sheetname As String = "Sheet1", Optional ByVal create As String = "false") as string

xls = the path to the spreadsheet including the name of the spreadsheet.
data = the data to be written presented as an array of objects
sheetname = the name of the specific sheet within the workbook to write the data to. The default is 'Sheet1'.
create = if the spreadsheet does not exist then create it. Default is false (don't create).

e.g.

dim excelRW as new excelrW.excelrw
dim obData(,) as object = nothing
dim result as string = nothing

' Fill an object array
For iRow = 0 To 5
  For iCol = 0 To 5
    obData(iRow, iCol) = iRow * iCol
  Next iCol
Next iRow

sheetName = "writeTest"
result=excelRW.writeSheet("c:\temp\writeExcel.xls", obData, sheetName)
console.writeline(result)

Using the sample archive

The archive contains a sample spreadsheet, the ExcelRW and MFfileIO dlls, a complete Visual Studio 2015 Silk4Net solution and a Silk Test Workbench asset xml file. The spreadsheet (test1.xlsx) and the DLLs should be placed in your c:\temp folder; if c:\temp is not available then you will need to change the paths in the test code and change the DLL references to point to the new folder.

The results of each individual test are written to c:\temp\excelresults.txt and during the execution of the test a second spreadsheet (c:\temp\writetext.xlsx) is created. Again, the path to both files can be changed in the script.

6428.ReadWriteExcel41.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 #:
2 of 2
Last update:
‎2019-03-07 09: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.