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. You can also find a list of a list of previous releases and release notes.
Maven Dependency
<dependency> <groupId>com.codoid.products</groupId> <artifactId>fillo</artifactId> <version>1.21</version> </dependency>
Select
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();
Multiple 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'");
LIKE Operator
Recordset recordset=connection.executeQuery("Select * from Sheet1 where Name like 'Cod%'");
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);
Version
Fillo-1.21
Comments(1)
Posted on Sep 07, 2020
1 year ago
how to clear all rows and columns except header