Master Excel Operations in Spring Boot 3 with Fillo: Full Code Guide

Environment

Spring Boot 3.4.2

1. Introduction

In Java, operating Excel files is a common requirement for reading test data, generating reports, or handling business data. The ecosystem provides several libraries:

Apache POI – full‑featured, supports old and new formats, but high memory consumption for large files.

EasyExcel – POI‑based, uses SAX for low‑memory row‑by‑row reading, suitable for big data.

JExcelAPI – lightweight, only .xls support, now largely deprecated.

Fillo offers a SQL‑like API that simplifies Excel CRUD operations, especially useful for small files and quick data manipulation.

What is Fillo?

Fillo is a Java Excel API that supports SELECT, UPDATE, and INSERT queries with optional WHERE clauses. It abstracts low‑level row/column handling and allows developers to write standard SQL statements such as SELECT * FROM Sheet1 WHERE Age > 25 instead of verbose POI code.

2. Practical Cases

2.1 Prepare Environment

Add the Maven dependency:

<dependency>
  <groupId>com.codoid.products</groupId>
  <artifactId>fillo</artifactId>
  <version>1.23</version>
</dependency>

Prepare an Excel file (example screenshot):

2.2 SELECT Query

static String file = "C:\\Users\\MSI-NB\\Desktop\\user.xlsx";
public static void select() throws Exception {
  Fillo fillo = new Fillo();
  Connection conn = fillo.getConnection(file);
  String strQuery = "Select * from baseInfo where name='陈浩然'";
  Recordset rs = conn.executeQuery(strQuery);
  while (rs.next()) {
    System.err.printf("%s\t%s\t%s\t%s\t%s%n",
        rs.getField("id"), rs.getField("name"),
        rs.getField("age"), rs.getField("email"),
        rs.getField("idNo"));
  }
  // multiple WHERE conditions
  rs = conn.executeQuery("Select * from baseInfo where name='杨晨曦' and age='52'");
  while (rs.next()) {
    System.out.printf("%s\t%s\t%s\t%s\t%s%n",
        rs.getField("id"), rs.getField("name"),
        rs.getField("age"), rs.getField("email"),
        rs.getField("idNo"));
  }
  // build WHERE via method chaining
  rs = conn.executeQuery("Select * from baseInfo").where("name='黄俊杰'")
      .where("idNo='420105200101011000'");
  while (rs.next()) {
    System.err.printf("%s\t%s\t%s\t%s\t%s%n",
        rs.getField("id"), rs.getField("name"),
        rs.getField("age"), rs.getField("email"),
        rs.getField("idNo"));
  }
  rs.close();
  conn.close();
}

Result screenshot:

2.3 INSERT Operation

static String file = "C:\\Users\\MSI-NB\\Desktop\\user.xlsx";
public static void insert() throws Exception {
  Fillo fillo = new Fillo();
  Connection conn = fillo.getConnection(file);
  String strQuery = "INSERT INTO baseInfo (id, name, age, email, idNo) VALUES (9, 'Pack张三', 33, '[email protected]', '5555533336666')";
  conn.executeUpdate(strQuery);
  conn.close();
}

Result screenshot:

2.4 UPDATE Operation

static String file = "C:\\Users\\MSI-NB\\Desktop\\user.xlsx";
public static void update() throws Exception {
  // ... obtain connection as before
  String sql = "Update baseInfo Set name='Spring Boot3实战案例200讲' where id=9";
  conn.executeUpdate(sql);
  // ...
}

Result screenshot:

2.5 DELETE Operation

static String file = "C:\\Users\\MSI-NB\\Desktop\\user.xlsx";
public static void delete() throws Exception {
  // ... obtain connection as before
  String strQuery = "DELETE FROM baseInfo where id=9";
  conn.executeUpdate(strQuery);
  // ...
}

Result screenshot:

2.6 LIKE (Fuzzy) Query

static String file = "C:\\Users\\MSI-NB\\Desktop\\user.xlsx";
public static void likeQuery() throws Exception {
  // ... obtain connection as before
  String strQuery = "Select * from baseInfo where name like '%陈%'";
  Recordset rs = conn.executeQuery(strQuery);
  // process result
}

Result screenshot:

2.7 DISTINCT (Deduplication)

public static void distinctOperator() throws Exception {
  // ... obtain connection as before
  String strQuery = "Select DISTINCT name from baseInfo";
  Recordset rs = conn.executeQuery(strQuery);
  while (rs.next()) {
    System.err.printf("%s%n", rs.getField("name"));
  }
  // ...
}

Result screenshot:

2.8 Set Starting Row & Column

public static void setRowAndColumn() throws Exception {
  // Table start row
  System.setProperty("ROW", "1");
  // Table start column
  System.setProperty("COLUMN", "1");
  Fillo fillo = new Fillo();
  Connection conn = fillo.getConnection(file);
  String strQuery = "Select * from baseInfo";
  Recordset rs = conn.executeQuery(strQuery);
  // ...
}

Result screenshot: