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

Read and Write to an Excel 2016/Excel 365 spreadsheet without a DSN connection (Rev.1.0)

Read and Write to an Excel 2016/Excel 365 spreadsheet without a DSN connection (Rev.1.0)

ExcelRW365v1.zip

 

This archive only works with versions of Office from Office 2013 onwards (currently Office 2013, Office 2016 and 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. From Office 2013 onwards the Interop Assemblies were changed so that they are embedded in the project that uses them, rather than being a separate download.

The attached archive contains the class library as a dll (ExcelRW365.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. For both Silk Test Workbench and Silk4Net you must then add a reference to the ExcelRW365 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 ExcelRW365 class library contains the following public methods:

getError()
readOneCell()
readOneRow()
readOneSheet()
writeOneCell()
writeOneRow()
writeSheet()
deleteCellData()
deleteRowData()
deleteColumnData()

Note that there is no 'deleteSheet()' method. The com method for deleting a worksheet will always delete the last sheet which may not be what is wanted. 

The class library 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 tailored to return information on the failed action


e.g.


dim excelRW as new excelrw365.excelrw

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

So if the method called failed to locate the workbook then

console.writeline(excelrw.getError())

will print e.g

C:\Temp\MyTestSpreadheet.xlsx: 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 excelrw365.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 excelrw365.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 excelrw365.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). If you need the entire worksheet then loop through readOneRow().



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 excelrw365.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 excelrw365.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 excelrw365.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)

Delete Methods.

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

xls = the path to the spreadsheet including the name of the spreadsheet.
cell = the cell from which to delete the data presented in alphanumeric format e.g. J7 
sheetname = the name of the specific sheet within the workbook that contains the cell to be cleared. The default is 'Sheet1'.

e.g.

sheetName = "number grid"
result = excelRW.deleteCellData(excelFile, "B3", sheetName)

Note that this method does not remove any cell formatting. 'cell' can also be presented as a range of cells to be cleared where the first and last cells are separated by a colon e.g. "B3:G8". In this case the cells would be cleared in the order B3-B8, C3-C8, D3-D8, etc.. 

 

deleteRowData(ByVal xls As String, ByVal row As String, Optional ByVal sheetname As String = "Sheet1", optional ByVal moveup as Boolean= False) As String

xls = the path to the spreadsheet including the name of the spreadsheet.
row = the number of the row to delete
sheetname = the name of the specific sheet within the workbook that contains the cell to be cleared. The default is 'Sheet1'.
If the optional parameter 'moveup' is set to True then the act of deleting a row will move all the rows below it up by one row to fill the deleted row space.

If the optional parameter moveup is left as default, only the row data is cleared. Any cell formatting is left intact.

e.g.

sheetName = "test sheet"
result = excelRW.deleteRowData(excelDeleteFile, "2", sheetName, True)

 

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

xls = the path to the spreadsheet including the name of the spreadsheet.
column = the column to be deleted presented in alphanumeric format e.g. A, BF
sheetname = the name of the specific sheet within the workbook that contains the cell to be cleared. The default is 'Sheet1'.

e.g.

sheetName = "number grid"
result = excelRW.deleteColumnData(excelFile, "F", sheetName)

'column' can also be presented as a range of columns to be cleared where the first and last columns are separated by a colon e.g. "B:G".

 

Using the sample archive

The archive contains a sample spreadsheet, the ExcelRW365 and MFfileIO dlls and a complete Visual Studio 2015 Silk4Net solution. 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. Please note that there are no 'Delete' tests in the solution.

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.

There are no changes to calling the methods if you are using Silk Test Workbench. 

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:
‎2018-06-14 23:29
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.