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. You can also find a list of a list of previous releases and release notes.

Maven Dependency

Select

Update

Insert

Multiple Where conditions

Where method

LIKE Operator

Set table starting row and column

Version

Fillo-1.18

License

Apache License, Version 2.0

Email us, if you face any issues

[email protected]

Download Fillo

Please fill out the form below to download Fillo.

126 thoughts on “Fillo

    1. Please try as below.

      Fillo fillo=new Fillo();

      Connection connection = fillo.getConnection(“C:\\Workbook.xlsx”);

      connection.createTable(“Table Name”,new String[]{“Column_1″,”Column_2”});

    1. Hello Siva,

      Try this.

      Recordset recordset=connection.executeQuery(“Select * From Sheet1”);

      while(recordset.next()){

      for (String strColumn:recordset.getFieldNames()) {
      System.out.println(“==================>”+strColumn);
      System.out.println(recordset.getField(strColumn));
      }

      }

  1. how can I get the first item of a returned column ??
    if recordset.getField(“Data1Column”)
    returns
    1
    2
    3
    how can i select only 1 of these values

    1. Hello Mohamed,

      Try as below,

      Recordset recordset=connection.executeQuery(“Select * From Sheet1”);

      //To get First Column Value from first row in recordset
      recordset.next();
      System.out.println(recordset.getField(0).value());

      //To get First Column Value from second row
      recordset.next();
      System.out.println(recordset.getField(0).value());

  2. Hi,

    My Query is String

    strQuery=”Select * from \””+l_sheetname+”\” where \””+l_scenarioname+”\” ”” ;
    strQuery=”Select * from \””+l_sheetname+”\” where \””+l_scenarioname+”\” is not NULL ;

    This query does not skips the cell with empty data. How do i skip the cells with empty data in that column..

    1. Try the below snippet to get all sheet names in Fillo-1.18.

      Connection connection = fillo.getConnection(“C:\\Sample.xlsx”);

      System.out.println(connection.getMetaData().getTableNames());

  3. Hi, I would like to know how to get access from on a server share? “\\\\server\\share\\file.xlsx” as file string doesn’t work for me. Is there a possibility to connect file as unc path?
    Thanks in advance
    Bernd

    1. Fillo fillo = new Fillo();
      try {
      Connection connection=fillo.getConnection(“\\\\server\\share\\file.xlsx”);
      String strQuery = “Select * from Sheet1”;
      Recordset recordset = connection.executeQuery(strQuery);

      while (recordset.next()) {
      System.out.println(recordset.getField(“USERID”));
      }

      recordset.close();
      connection.close();
      } catch (FilloException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      }

      Error: com.codoid.products.exception.FilloException: Workbook is not found – \\server\share\file.xlsx

  4. Hi, I would like to know how to access excel files on a share? The following example throw me an exception: Workbook not found.
    Is there a possibility to access file as unc path?

    Thanks and Regards

    Fillo fillo = new Fillo();
    try {
    Connection connection=fillo.getConnection(“\\\\server\\share\\file.xlsx”);
    String strQuery = “Select * from Sheet1”;
    Recordset recordset = connection.executeQuery(strQuery);

    while (recordset.next()) {
    System.out.println(recordset.getField(“USERID”));
    }

    recordset.close();
    connection.close();
    } catch (FilloException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    1. Try the below snippet to get all sheet names in Fillo-1.18.

      Connection connection = fillo.getConnection(“C:\\Sample.xlsx”);

      System.out.println(connection.getMetaData().getTableNames());

  5. hi there are 4 excell sheet in one folder , can we create connection dynamically? for example some stage i need data from one excel sheet and another stage from another excel sheet

  6. Fillo fillo = new Fillo();
    Connection connection=fillo.getConnection(“TestData/trial.xlsx”);
    Recordset recordset=connection.executeQuery(“Select * from Sheet1”);

    I am getting java.lang.NoSuchFieldError, could you explain me how to deal with this ??

    1. I am also facing the same problem. Full error message
      Exception in thread “main” java.lang.NoSuchFieldError: RETURN_BLANK_AS_NULL
      at com.codoid.products.fillo.CommonExcelUtil.getColumnNames(CommonExcelUtil.java:78)
      at com.codoid.products.fillo.SelectExcelUtil.runSelectQuery(SelectExcelUtil.java:84)
      at com.codoid.products.fillo.Select.getRecordset(Select.java:61)
      at com.codoid.products.fillo.Connection.executeQuery(Connection.java:56)
      at testJavaCode.excelUsingFillo.main(excelUsingFillo.java:22)

      1. Hi Mahesh,

        We are not able to reproduce the issue.

        It would be great if you send a sample excel sheet which you have used to reproduce the issue.

        Thanks

        1. Hi Admin ,
          I have the same problem
          java.lang.NoSuchFieldError: RETURN_BLANK_AS_NULL
          at com.codoid.products.fillo.CommonExcelUtil.getColumnNames(CommonExcelUtil.java:78)
          at com.codoid.products.fillo.SelectExcelUtil.runSelectQuery(SelectExcelUtil.java:84)
          at com.codoid.products.fillo.Select.getRecordset(Select.java:61)
          at com.codoid.products.fillo.Connection.executeQuery(Connection.java:56)
          at testmain.XLSReader.getTests(XLSReader.java:26)
          my query is : “Select * from Sheet1 where Active = ‘Y’ and Module = ‘Login'”

  7. I have a problem to delete a row with more than a condition, I tried only with one condition was well, but when I put add other conditions , no row was deleted. My query is that:

    String sql = “DELETE FROM Sheet1 WHERE column1 =’Operativo’ and column2=’Meter reseteando'”
    I would want to delete only to have this two condition

  8. Hi all,

    Could it be possible to obtain the Connection connection=fillo.getConnection(path_to_file) from a byte array? I would like to read from an in-memory Excel file.

    Thanks.

  9. Another question please: Is UPPER, LOWER supported in queries? Something like this: Select * from MyTable where upper(Schedule) = upper(‘M02W2D1T1800’)

    Thank you.

  10. Hi, I am using Fillo in my project. I am getting conflict in Apache POI library. Can you please let me know have you changed any code in apache POI while using Fillo.

  11. Getting Exception in thread “main” java.lang.NoSuchFieldError: RETURN_BLANK_AS_NULL

    plese find the below code:
    Exception in thread “main” java.lang.NoSuchFieldError: RETURN_BLANK_AS_NULL

    I am using Fillo 1.10 jar and xls file

    please help me to resolve this issue.

  12. Hi Admin,
    I am trying to insert a row .when execute below code in console i can see i column effected but in Xlsx sheet no Row is getting updated

    Please fins the below code

    String path=”D:\\Sravani\\Users.xlsx”;
    String strQuery=null;
    String columnName=”TestCaseID”;
    String Value=”‘TC_7′”;

    strQuery=”INSERT INTO “+SheetName+”(“+ColumnName+”) VALUES(“+Value+”)”;
    System.out.println(“StrQuery”+strQuery);
    try{

    //Fillo Object Creation
    Fillo fillo = new Fillo();
    //Connection Establishment
    Con = fillo.getConnection(path);
    //Query Execute
    Con.executeUpdate(strQuery);
    Result:
    StrQueryINSERT INTO Sheet1(TestCaseID) VALUES(‘TC_7’)
    1 columns(s) affected
    Picked up JAVA_TOOL_OPTIONS: -agentlib:jvmhook
    Picked up _JAVA_OPTIONS: -Xrunjvmhook -Xbootclasspath/a:”C:\Program Files (x86)\HP\Unified Functional Testing\bin\java_shared\classes”;”C:\Program Files (x86)\HP\Unified Functional Testing\\bin\java_shared\classes\jasmine.jar”

    Process finished with exit code 0

  13. HI Team,
    I am requiring to compare data in two excel files.
    Using two recordsets,one created within the other recordset loop(as query is based on the each row in first recordset);while doing so I am not getting the second recordset loaded.
    Can we use two recordsets ?
    Thanks,
    Manju

    1. Hi Robert,
      Thanks for the response . Here is a sample,
      com.codoid.products.fillo.Connection connectionFit=fillo.getConnection(source1Path);
      com.codoid.products.fillo.Connection connectionProd=fillo.getConnection(source2Path);

      String fitQuery=”Select * from Sheet1″;
      Recordset recordsetFIT=connectionFit.executeQuery(fitQuery);
      String item,prodQuery;
      while(recordsetFIT.next()){
      System.out.println(recordsetFIT.getField(“ITEM”));
      item =recordsetFIT.getField(“ITEM”);
      prodQuery=”Select * from Sheet1 where ITEM='”+item+”‘”;
      System.out.println(prodQuery);
      Recordset recordsetPROD=connectionProd.executeQuery(prodQuery);
      System.out.println(recordsetPROD.getField(“ITEM”));
      }

  14. I always have a problem to read a excel file protected(encrypted) XLSX, When I decrypt that file is introduced a exception : “main” org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format.
    follow below my simple code. Have any problem with library Fillo?

    public static void main(String[] args) throws FileNotFoundException, IOException, GeneralSecurityException, FilloException {

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(“D:\\z1.xlsx”));

    EncryptionInfo info = new EncryptionInfo(fs);
    Decryptor d = Decryptor.getInstance(info);
    d.verifyPassword(“oi”);

    XSSFWorkbook wb = new XSSFWorkbook(d.getDataStream(fs));
    Fillo fillo = new Fillo();

    Connection cn = fillo.getConnection(“D:\\z1.xlsx”);

  15. Hi, is it possible to retrieve a list with all sheet’s names? I need to iterate through all sheets but I don’t know their name. Thanks!

    1. Try the below snippet to get all sheet names in Fillo-1.18.

      Connection connection = fillo.getConnection(“C:\\Sample.xlsx”);

      System.out.println(connection.getMetaData().getTableNames());

  16. How to retrieve values from first sheet irrespective of sheetName?
    I got a scenario where I download a file in which the sheet name is a random name and it contains only one sheet in that workbook. I wanted to go by first sheet here.
    Please help.

  17. Can I run queries with condition Max(ID) , Group by, Order by ?
    I already tried but no there were changes on results
    example: String strQuery = “SELECT * FROM sheet1 ORDER BY Name ASC”;
    String strQuery = “SELECT * FROM sheet1 where id = (select max(Id) from sheet1)”;

  18. Hi team,
    can you pass all queries that api fillo might do.

    We don´t know if we might do the comand As, order by, group by, max(), join.

    What comands Do we might do of that?

  19. I can’t find the lastest download of fillo 1.18 library.
    I need it to get the sheet names from a workbook, a great update btw.

  20. Hi
    I am using Selenium 2.47 with Fillo 1.15. An exception is thrown stating unsupported major minor version 52.0. please help

  21. i am using insert query. how do pass parameters values not static values?
    Ex: valuenew and valueold are method parameters.
    String strQuery=”INSERT INTO Sheetname(ColName1,ColName2) VALUES (valuenew,valueold)”;

  22. I am getting ERROR
    “Exception in thread “main” java.lang.UnsupportedClassVersionError: com/codoid/products/exception/FilloException : Unsupported major.minor version 52.0″
    Using Selenium version 2.53.1 and jdk version 7 . Used Fillo Ver 1.5 and 1.8.
    Please advice.

  23. Hello Team,
    It’s a very useful api and its working fine for me. Many Thanks.
    but here, i have one question – Does it work on wondows8 OS?
    i have tried all the versions of fillo but no luck for windows8 OS.

  24. Hi team, I am using fillo in one of my project . I am using vary large excel as a database(approximate 400 columns and 1000 rows now and will increased day by day).and now I have a question regarding performance. 1. now fillo needed about 2-3 second to get the each connection for each request. how can increase the get connection performance?. And next question is if the multiple write request is happened, is fillo handle the write is synchronized or I am need to handle the connection synchronized?

  25. I have a same question as others in this group have already asked, How to update only one row if there are duplicate values in excel?

  26. Getting Exception in thread “main” java.lang.NoSuchFieldError: RETURN_BLANK_AS_NULL
    I am using Fillo 1.18 jar and xls file

    please help me to resolve this issue.

  27. Hallo, I am facing a problem with the method getConnection(String), I become the message :
    The method getConnection(String) is undefined for the type Fillo
    Shall I add another jars to my project?

  28. How can I save the query result – the needed recordset – in a new sheet.
    connection.createTable(“Report”,new String[]{“Employee “,”ID”, “Salary”});
    This just adds a new sheet “Report” with 3 columns, namely : Employee ,ID and Salary. So how to add the recordset in these columns?

  29. Hallo, is there a way to name a selection / field out of many column and rows so that I can use getField(selectionName)

    1. Why don’t you try the below method to loop over all fields?

      while(recordset.next()){
      for (String strColumn:recordset.getFieldNames()) {
      System.out.println(recordset.getField(strColumn));
      }
      }

  30. Hello, i have an excel table and more column with same name. Can i select it all without modify excel file? Example:
    col1|col2|col3 col1|col2|col3
    Can i select col2 without merge table?

  31. Hi,
    Am trying to run a query
    Select * from Sheet1 where TOK_NBR = ‘12345’. But its showing ‘No records found’ error even though sheet has that tok_nbr value as a text. Please help on this

  32. Got
    Exception in thread “main” com.codoid.products.exception.FilloException: Table header are missing.
    I am not getting on local machine (laptop or desktop ) but got error on Virtual machines. any idea?

  33. Hello,
    Is there a way to save the query’s result in a sheet. What can I do else with my recordset other than println(recordset.getField(“fieldName”) ) ?

  34. It seems like “select distinct” is not working, am getting the duplicate values. can you please tell me know to achieve this functionality using fillo ?

  35. java.lang.nosuchfielderror: return_blank_as_null
    I am currently using eclipse oxygen version with Fillo 1.18. Still getting the above error. I had read from the earlier post that this issue had been resolve in 1.18 version. But still i can replicate it. Please help

  36. Hi Team,
    Am not able to update query with “-”

    For example :
    Update SheetName Set name=”Aparna_Veerasingam”where row =1″;

  37. Hi,

    I’m trying to get distinct values from a column but the query is returning duplicate values. Any help is appreciated
    Query
    Select DISTINCT(Test) from TestSheet

    O/P
    a
    a
    a
    b
    b
    b
    c
    c
    c

    Thanks,
    Vivek

  38. I have formulated cells in my excel. Unable to fetch data from the sheet using query: Select * from “SheetName”;
    It throws Nullpointer exception. Can anybody please help me….

  39. Very cool. Two questions:

    1) Any plans to support a JOIN clause? I can fake it for now, but curious to know if it’ll be integrated.

    2) Is the library threadsafe? I’m assuming “no”, but if it’s threadsafe I wouldn’t need to do so much of the synchronization myself.

    Thanks.

  40. Select query with OR condition is not working. Getting zero records.

    Work around is, I have to execute as two different query.

    Is there any way I can get both the record in single query?

  41. How to set the background color of a cell using fillo?
    I wanted to set the color as RED or YELLOW than how that can be done.

  42. Well it is good but it offers not a feature for e.g using same column in a where clause for e.g col2=val2 or col2=val3

  43. Hi, great API. How do you query a sheet that has a space in the name. I am getting this exception: Caused by: com.codoid.products.exception.FilloException: Invalid Query – SELECT * FROM ‘LVL1 – ATTRIBUTES’

  44. when i use query SELECT TOP 3 * FROM Abc; its giving me error Exception in thread “main” com.codoid.products.exception.FilloException: Invalid Query – SELECT TOP 3 * FROM Abc;

Leave a Reply

Your email address will not be published. Required fields are marked *