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.
=====================================================================================
Select
=====================================================================================
package Fillo;
import org.testng.annotations.Test;
import Exception.FilloException;
public class TestFillo {
//public static Connection connection;
@Test
public void pawan() throws FilloException{
Fillo fillo=new Fillo();
Connection connection=fillo.getConnection("C:\\Test.xlsx");
String strQuery="Select * from Sheet1 where ID=100 and name='John'";
Recordset recordset=connection.executeQuery(strQuery);
while(recordset.next()){
System.out.println(recordset.getField("Details"));
}
recordset.close();
connection.close();
}
}
====================================================================================
Update
=====================================================================================
Fillo fillo=new Fillo();
Connection connection=fillo.getConnection("C:\\Test.xlsx");
String strQuery="Update Sheet1 Set Country='US' where ID=100 and name='John'";
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();
=====================================================================================
Muliple Where conditions
=====================================================================================
//This is an enhancement in Fillo-1.11, now you can mention multiple conditions in a query as shown below.
Recordset recordset=connection.executeQuery("Select * from Sheet1 where column1=value1 and column2=value2 and column3=value3");
=====================================================================================
@ Where method @
=====================================================================================
Recordset recordset=connection.executeQuery("Select * from Sheet1").where("ID=100").where("name='John'");
=====================================================================================
Set table starting row and column
=====================================================================================
//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);
For Dowmloading API
http://www.codoid.com/products/view/2/29
and Enter mail id
7 Comments
Is (where like '%laila%' supported ?
ReplyDeleteYes
Deletehow to implement distinct query in selenium java using fillo
ReplyDeleteWe can use where query in insert statement using Fillo ???
ReplyDeletei tried below query but not inserted any record :-
String strQuery1=(“INSERT INTO vasista(status) VALUES(‘evan’).where(name =’pk’)”);
getting as ” 0 columns(s) affected ”
please help me
You need to write update statement instead of insert.
DeleteHello, how i can insert variable in query? Can we use something like prepared statement which there are in sql?
ReplyDeletehow can in insert dynaimacly using prepared statement
ReplyDeletePost a Comment