Wednesday, April 30, 2008

Accessing Excel - SilkTest

Few tools are providing built-in functions to read and write the data from Excel. Testers are comfortable with Excel than text file formats.

Silktest does not have any built-in functions to access data from Excel. But you can retrieve the data from Excel by treating Excel as Database. By using below code snippet, you can access the Excel data without pre-defined DSN. For this code snipper, You need to provide Excel filename with full path and worksheet name.

Silktest Excel - Code:

[+] public void ProcessExcelData(STRING sExcelFile, String sExcelSheet) [ ] // Purpose: Drives test suite based on excel sheet(testcase) information. [ ] // Executes for given worksheet. [ ] //SQL declaration [ ] HANDLE hDB [ ] HANDLE hSQL [ ] List of STRING glsData //To get testdata [ ] [ ] Print ("Test data:{sExcelFile} & Worksheet: {sExcelSheet} ") [ ] [-] do [ ] hDB = DB_Connect ("DRIVER=Microsoft Excel Driver (*.xls);DRIVERID=790;FIRSTROWHASNAMES=1;READONLY=FALSE;DBQ={sExcelFile}") [ ] [ ] //execute a SQL statement [ ] hSQL = DB_ExecuteSQL (hDB, "SELECT * from [{sExcelSheet}$]") [ ] //while there are still rows to retrieve [-] while DB_FetchNext (hSQL, glsData) [ ] GetListData (glsData) //Get all data without null [ ] [ ] [ ] //clean up the query [ ] DB_FinishSql (hSQL) [ ] [ ] //disconnect from the database [ ] DB_Disconnect (hDB) [ ] [-] except [ ] ExceptLog() [ ] Print ("Excelsheet {sExcelFile} couldn't be accessed by SilkTest.")

2 comments:

Anonymous said...

Hi,
this is very helpful to me.
Just that, how you define the function GetListData()?
You didn't mention in the post.

Palani Selvam said...

That function will remove the empty values. You can directly use the contents from glsData.