DataBase Testing using Excel

Step 1: Download fillo from http://www.codoid.com/products/view/2/29/validEmail
Step 2: Enter your valid email
Step 3: Download the fillo file and add jar to excipse work space

About FILLO:
Fillo is an Excel API for Java and you can query xls & xlsx files. Now, it supports SELECT, UPDATE & INSERT queries with or without WHERE clause. 
Example Scenario:
26. Consider an excel sheet having data of employee table 
EmpName Salary
Emp1 100
Emp 2 400
Emp 3 500
Emp 4 200
Emp 5 100
How will you read the excel sheet, check the condition that salary>100 and print all the emp names 

For Select:

Fillo fillo=new Fillo();

Connection connection=fillo.getConnection("C:\\Test.xlsx");

String strQuery="Select * from Sheet1 where Salary=100";

Recordset recordset=connection.executeQuery(strQuery);

while(recordset.next()){

System.out.println(recordset.getField("EmpName"));

}

 

recordset.close();

connection.close();++++++for update++++++public class Db_Excal_Fillo {public static void main(String args[]) throws FilloException{ Fillo fillo=new Fillo(); Connection connection=fillo.getConnection("C:\\v2autoW5.0\\Test\\src\\info\\e2e_zonal\\DbTest.xls"); String strQuery="Update Sheet1 Set Salary='1000' where Salary=100";   connection.executeUpdate(strQuery);   connection.close(); =========Insert+++++

Fillo fillo=new Fillo();

Connection connection=fillo.getConnection("C:\\Test.xlsx");

String strQuery="INSERT INTO sheet4(Name,Country) VALUES('Peter','UK')";

connection.executeUpdate(strQuery);

connection.close();---------------


Where method

view source

print?

Recordset recordset=connection.executeQuery("Select * from Sheet1").where("ID=100").where("name='John'");

Set row and column

view source

print?

//Now you can set table start row and column

System.setProperty("ROW", "5");//Table start row

System.setProperty("COLUMN", "3");//Table start column

Fillo fillo=new Fillo();

Connection connection=fillo.getConnection(strFile);