How to write and read data from excel sheets.

santosh.kande

Hi,

I am using sahi for recording and playback . I would like to enhance script by taking data from excel
sheet and write report to the same excel once the case get passed.

Can somebody let me know how to do this.

Thanks in adv

globalworming

Hi

please search for excel in the forum, there should be enough examples out there.

Regards
Wormi

Jithin

Hi santosh,

You can read data from excel by using this function(modify according to your script)

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\Path of excel file\\Data.xls;readOnly=false", "", "");
if(db!=null)
{
$rs = db.select("select * from [Name of sheet$]");
for($i=0; $i<$rs.length; $i++)
{
name of function($rs[$i]["Name of first column"], $rs[$i]["Name of second column"], $rs[$i]["Name of third column"])
}
}

and write to excel by using

function updateReport($DB, $Table, $Status, $TestCase)
{
$DB.update("update ["+$Table+"$] set Result='"+$Status+"' where TestCase='"+$TestCase+"'");
}

vijaysaims

You need to download and install Access Database Engine from http://www.microsoft.com/en-us/download/details.aspx?id=13255 to work with excel sheets. After that you can use the script posted by Jithin to work with excel sheets.

Jithin

Thank you Sai,

On Windows, you could use the jdbc:odbc driver to access Excel.
On 64 bit windows, you need to download and install the 64 bit ODBC drivers (Download and install AccessDatabaseEngine_x64.exe)

rajivpamidi

Hi

I have followed the exact same steps what has been mentioned in this thread , the only difference is that even though my system is a 64 bit, since my Office installed is 32 bit I had to install the 32 bit driver for access database engine.
I checked if the the excel sheet path is right, I did check if the db engine driver is installed.
Kindly let me know where did I go wrong.

My code:

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Users\pamidimukkalar\Desktop\Sahi_Data.xlsx;readOnly=false", "", "");
4 if(db!=null)
5 {
6 $rs = db.select("select * from [Sheet1$]");
7 for($i=0; $i<$rs.length; $i++)
8 {
9 login_eams($rs[$i]["username"], $rs[$i]["password"])
10 }
11 }
12
13 function login_eams($username,$password)
14 {
15 _setValue(_textbox("name"), $username);
16 _setValue(_password("password"), $password);
17 _click(_submit("Submit"));
18 _click(_div("Actions"));
19 _click(_div("Meter"));
20 _click(_div("View"));
21 _click(_button("Search"));
22 _click(_link("MD_REX2_4277"));
23 _click(_cell("MD_REX2_4277"));
24 _assertExists(_cell("MD_REX2_4277"));
25 _assert(_isVisible(_cell("MD_REX2_4277")));
26 _assertEqual("MD_REX2_4277", _getText(_cell("MD_REX2_4277")));
27 _assertContainsText("MD_REX2_4277", _cell("MD_REX2_4277"));
28 _click(_div("Logoff \"$username\""));
29 }





Error:
ERROR
exception: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.sf.sahi.plugin.DBClient.getResult(DBClient.java:96)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)
at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3330)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2487)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:164)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:175)
at org.mozilla.javascript.Context.evaluateString(Context.java:1104)
at net.sf.sahi.rhino.RhinoScriptRunner.run(RhinoScriptRunner.java:114)
at java.lang.Thread.run(Unknown Source) at Jun 12, 2012 10:08:14 AM

rajivpamidi

I got that to work ...

I observed that Java installed on my system was 64 bit and that does not support a 32 bit database engine driver. So i uninstalled the 64Bit java and installed a 32 bit Java and this solved the issue.

Thanks!

Raziya89

Hi,

I have created an excel sheet with username and password. And I need to get the excel sheet values in gmail login page.
Kindly provide solution.

Thanks in advance.