Spring Boot: Import Excel File into Database (Apache POI)

To import an Excel file into a database in Spring Boot, we accept the .xlsx file in a multipart POST request, read its rows with Apache POI, check every row with Bean Validation, and save the valid rows with Spring Data JPA. A school office that uploads a class roster with exam marks is a typical case: most rows are fine, a few have a wrong email or a mark of 105, and the office needs to know exactly which cells to fix. Below we build that endpoint with cell reading through DataFormatter, header mapping by name, a JSON report of row errors, duplicate checks, batch inserts, a streaming reader for large files and upload size limits, and we test it with MockMvc.

DataFormatter formatter = new DataFormatter();
String roll = formatter.formatCellValue(row.getCell(0));     // "101" (numeric cell)
String date = formatter.formatCellValue(row.getCell(5));     // "6/15/24" (date cell)

var violations = validator.validate(studentRow);             // email "chloe.school.com" -> "must be a well-formed email address"
repository.saveAll(students);                                // inserts in JDBC batches of 50

curl -F "file=@samples/students.xlsx" http://localhost:8080/students/import
# {"totalRows":8,"imported":4,"failed":4,"errors":[{"row":5,"column":"Email","message":"must be a well-formed email address"}, ...]}

1. How Does an Excel Import Work in Spring Boot?

An .xlsx file is a zip archive of XML files (the Office Open XML format): one XML file per sheet, a shared table for repeated strings, and a styles file that says how numbers and dates are displayed. Apache POI is the Java library that reads and writes these files. An import endpoint passes the file through four steps. The first two can reject the whole file, the last two reject single rows:

POI offers two ways to read a sheet. The usual one, XSSFWorkbook, builds an object for every row and cell in memory. The XSSF event API parses the sheet XML with SAX (a parser that reports one XML element at a time) and keeps only the current row:

	XSSFWorkbook (user model)	XSSF event API (SAX)
Memory	Whole workbook as objects	Current row plus the shared strings
Code	Loop over Sheet, Row, Cell	Callback per cell (SheetContentsHandler)
Random access	Yes, sheet.getRow(500)	No, rows arrive in file order
Formulas	Can evaluate them	Reads the cached result
Good for	Sheets with a few thousand rows	Large files, small heap

We start with XSSFWorkbook because its code is easier to follow, and switch to the event API in section 2.9. Both readers produce the same text for each cell, so validation and saving stay the same.

2. Importing an Excel File Into a Database With Spring Boot

The complete project uses Spring Boot 4.1.1, Java 25, Apache POI 5.5.1, Hibernate 7.4.5 and an H2 in-memory database. Its 12 tests check every response, log line and database row shown below.

2.1. Maven Dependencies

Spring Boot does not manage the POI version, so we set it ourselves. poi-ooxml is the module for .xlsx files and pulls in the core poi module:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-webmvc</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.5.1</version>
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>

In Spring Boot 4, the web starter is called spring-boot-starter-webmvc; the old spring-boot-starter-web name still works but is deprecated.

2.2. The Student Entity and the Sheet Layout

The class teacher fills one row per student: roll number, name, class, email, marks and admission date. The import does not care about the column order, because it finds each column by its header text:

The entity is a plain JPA class. rollNumber has a unique constraint, and the id comes from a sequence, which matters for batch inserts in section 2.8:

@Entity
public class Student {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  @SequenceGenerator(sequenceName = "student_seq", allocationSize = 50)
  private Long id;

  @Column(unique = true, nullable = false)
  private String rollNumber;

  private String name;
  private String email;
  private String className;
  private Integer marks;
  private LocalDate admissionDate;
}

We do not validate the entity directly. A StudentRow record holds one converted row together with its Excel row number, and carries the Bean Validation constraints:

public record StudentRow(
    int rowNumber,
    @NotBlank @Pattern(regexp = "\\d{1,6}", message = "must be a number with up to 6 digits") String rollNumber,
    @NotBlank @Size(max = 100) String name,
    @NotBlank @Email String email,
    @NotBlank String className,
    @NotNull @Min(0) @Max(100) Integer marks,
    @NotNull @PastOrPresent LocalDate admissionDate) {
}

An enum connects each header to a field name. The validator reports errors by field name (email), and the enum turns that back into the header (Email) for the report:

public enum StudentColumn {
  ROLL_NUMBER("Roll No", "rollNumber"),
  NAME("Name", "name"),
  EMAIL("Email", "email"),
  CLASS_NAME("Class", "className"),
  MARKS("Marks", "marks"),
  ADMISSION_DATE("Admission Date", "admissionDate");
  // constructor, header(), field() and headerOf(field) omitted
}

2.3. Uploading the File With MultipartFile

A MultipartFile is Spring’s view of one file in a multipart/form-data request. We check the file name, copy the upload to a temporary file and pass that file to the service:

@PostMapping(value = "/students/import", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public ImportReport importStudents(@RequestParam("file") MultipartFile file,
    @RequestParam(defaultValue = "false") boolean streaming,
    @RequestParam(defaultValue = "false") boolean allOrNothing) {

  String fileName = file.getOriginalFilename();
  if (file.isEmpty() || fileName == null || !fileName.toLowerCase().endsWith(".xlsx")) {
    throw new InvalidFileException("Upload a non-empty .xlsx file");
  }
  SheetReader reader = streaming ? streamingReader : workbookReader;
  Path upload = null;
  try {
    upload = Files.createTempFile("students-", ".xlsx");
    file.transferTo(upload);                     // POI reads a file part by part
    return importService.importStudents(upload, reader, allOrNothing);
  } catch (IOException | UnsupportedFileFormatException e) {
    throw new InvalidFileException("Not a readable .xlsx file");
  } finally {
    deleteQuietly(upload);
  }
}

We give POI a file, not file.getInputStream(). The POI quick guide says that an InputStream has to buffer the whole file, and section 2.9 shows how much heap that costs. A file with a .xlsx name that is not a zip archive makes POI throw NotOfficeXmlFileException, a subclass of UnsupportedFileFormatException, and the controller turns it into a 400 response.

An @RestControllerAdvice maps our exception to a ProblemDetail, the standard JSON error body from RFC 9457:

@ExceptionHandler(InvalidFileException.class)
ProblemDetail invalidFile(InvalidFileException e) {
  return ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, e.getMessage());
}

2.4. Reading Cells With DataFormatter

Every cell in Excel has a type: string, numeric, boolean, formula or blank. Dates are numeric cells with a date format. Reading a cell with the wrong getter throws an exception, and that is the most common bug in Excel imports. The roll number 101 is a numeric cell:

roll.getStringCellValue();                 // IllegalStateException: Cannot get a STRING value from a NUMERIC cell
roll.getNumericCellValue();                // 101.0

DataFormatter formatter = new DataFormatter();
formatter.formatCellValue(roll);           // "101"
formatter.formatCellValue(marks);          // "85.5"
formatter.formatCellValue(date);           // "6/15/24" (built-in format m/d/yy)
formatter.formatCellValue(formula);        // "B1*2" (the formula, not the result)
formatter.formatCellValue(formula, evaluator);   // "171"
formatter.formatCellValue(row.getCell(9)); // "" (missing cell)

DataFormatter returns the text Excel shows for a cell, whatever its type, so we read every cell as a String and convert it ourselves. Missing cells come back as an empty string instead of null.

Dates need one more step. The displayed text depends on the format the teacher picked (“6/15/24”, “15-Jun-2024” and so on), so we extend DataFormatter to return ISO dates such as “2024-06-15”. The first method is used by XSSFWorkbook, the second by the event API in section 2.9:

public class IsoDateFormatter extends DataFormatter {

  @Override
  public String formatCellValue(Cell cell) {
    if (cell != null && cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
      return cell.getLocalDateTimeCellValue().toLocalDate().toString();   // "2024-06-15"
    }
    return super.formatCellValue(cell).trim();
  }

  @Override
  public String formatRawCellContents(double value, int formatIndex, String formatString,
      boolean use1904Windowing) {
    if (DateUtil.isADateFormat(formatIndex, formatString) && DateUtil.isValidExcelDate(value)) {
      return DateUtil.getLocalDateTime(value, use1904Windowing).toLocalDate().toString();
    }
    return super.formatRawCellContents(value, formatIndex, formatString, use1904Windowing);
  }
}

A date typed as text, for example “2024-06-15”, stays as it is and parses the same way. A text date in another format, such as “15/06/2024”, fails the parsing and is reported as a row error.

2.5. Reading Rows and Mapping Headers by Name

sheet.getRow(r) returns null for a row that was never written, and a row whose cells were cleared still exists but holds only empty strings. We skip both, and number the rows the way Excel does, starting at 1:

XSSFWorkbook workbook = new XSSFWorkbook(pkg);       // pkg = OPCPackage.open(file, PackageAccess.READ)
Sheet sheet = workbook.getSheetAt(0);
for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
  Row row = sheet.getRow(r);
  if (row == null) {
    continue;                                        // row never written
  }
  List<String> values = new ArrayList<>();
  for (int c = 0; c < row.getLastCellNum(); c++) {
    values.add(formatter.formatCellValue(row.getCell(c)));   // "" for a missing cell
  }
  if (!SheetReader.isBlank(values)) {
    rows.accept(new SheetRow(r + 1, values));        // Excel row number
  }
}

The first non-blank row is the header. We store the position of each header, ignoring case and extra spaces, and reject the file when a required column is missing:

Map<String, Integer> positions = new HashMap<>();
for (int i = 0; i < row.values().size(); i++) {
  positions.put(row.value(i).trim().toLowerCase(), i);       // "roll no" -> 0, "class" -> 2
}
for (StudentColumn column : StudentColumn.values()) {
  Integer index = positions.get(column.header().toLowerCase());
  if (index == null) {
    missing.add(column.header());
  } else {
    columns.put(column, index);
  }
}
if (!missing.isEmpty()) {
  throw new InvalidFileException("Missing columns: " + String.join(", ", missing));
}

A sheet without the Email column gets this response:

{"detail":"Missing columns: Email","instance":"/students/import","status":400,"title":"Bad Request"}

Mapping by header name keeps the import working when someone inserts or moves a column, which happens often with sheets that people edit by hand. Mapping by index would silently store emails in the class field.

2.6. Validating Each Row and Reporting Errors

Each row goes through two checks. First, the text of Marks and Admission Date is converted to Integer and LocalDate; a failure is recorded with the column header. Then the validator checks the constraints on StudentRow, and we skip a violation for a column that already failed conversion, so the teacher sees one message per cell:

try {
  marks = Integer.valueOf(marksText);                  // "absent" -> NumberFormatException
} catch (NumberFormatException e) {
  badColumns.add(addError(row, StudentColumn.MARKS, "must be a whole number"));
}

for (ConstraintViolation<StudentRow> violation : validator.validate(student)) {
  String column = StudentColumn.headerOf(violation.getPropertyPath().toString());   // "email" -> "Email"
  if (!badColumns.contains(column)) {
    errors.add(new RowError(row.rowNumber(), column, violation.getMessage()));
  }
}

The report is a record with the counts and the list of RowError(row, column, message) entries, sorted by row. Spring MVC writes it as JSON. Our sample roster has 8 student rows and one blank row:

Row	Roll No	Name	Class	Email	Marks	Admission Date
2	101	Anna	7A	anna@school.com	85	6/15/24
3	102	Ben	7A	ben@school.com	92	6/15/24
4
5	103	Chloe	7A	chloe.school.com	78	6/17/24
6	104	David	7A	david@school.com	105	15/06/2024 (text)
7	102	Emma	7A	emma@school.com	88	6/18/24
8	105		7A	farah@school.com	absent	6/18/24
9	106	Grace	7B	grace@school.com	95	6/14/24
10	107	Hugo	7B	hugo@school.com	67	6/20/24

Uploading it to the running application saves four students and reports the other four rows:

curl -F "file=@samples/students.xlsx" http://localhost:8080/students/import

{"totalRows":8,"imported":4,"failed":4,"errors":[
  {"row":5,"column":"Email","message":"must be a well-formed email address"},
  {"row":6,"column":"Marks","message":"must be less than or equal to 100"},
  {"row":6,"column":"Admission Date","message":"must be a date such as 2024-06-15"},
  {"row":7,"column":"Roll No","message":"duplicate of row 3"},
  {"row":8,"column":"Name","message":"must not be blank"},
  {"row":8,"column":"Marks","message":"must be a whole number"}]}

The blank row 4 does not count in totalRows. Row 6 has two errors, and failed counts rows, not errors. GET /students returns the four saved students, for example Anna with “marks”:85 and “admissionDate”:”2024-06-15″.

2.7. Handling Duplicate Roll Numbers

A roll number can appear twice in the same file, or it can already be in the database from an earlier upload. We check both before the insert. Inside the file, a map from roll number to its first row finds the repeat:

Integer firstRow = seenRollNumbers.putIfAbsent(student.rollNumber(), row.rowNumber());
if (firstRow != null) {                               // row 7: "102" was seen in row 3
  errors.add(new RowError(row.rowNumber(), "Roll No", "duplicate of row " + firstRow));
}

For the database, one query per chunk of 500 rows returns the roll numbers that already exist:

@Query("select s.rollNumber from Student s where s.rollNumber in :rollNumbers")
List<String> findExistingRollNumbers(Collection<String> rollNumbers);

Uploading the same roster a second time saves nothing. The four valid rows now fail with already exists, next to the six errors we saw before:

{"totalRows":8,"imported":0,"failed":8,"errors":[
  {"row":2,"column":"Roll No","message":"already exists"},
  {"row":3,"column":"Roll No","message":"already exists"},
  {"row":5,"column":"Email","message":"must be a well-formed email address"},
  ...
  {"row":10,"column":"Roll No","message":"already exists"}]}

The unique constraint on rollNumber stays as the last guard. If two uploads run at the same time, the second insert fails with a constraint violation instead of storing a duplicate. Updating existing students instead of rejecting them is the other common policy; it needs a findByRollNumber() and changes to the loaded entity, which our example does not do.

2.8. Saving Rows in Batches

saveAll() calls persist() for each new entity, and Hibernate sends one INSERT per student. With JDBC batching, Hibernate groups up to 50 inserts into one database call. Two properties turn it on, and the SEQUENCE id with allocationSize = 50 keeps it working, because an IDENTITY id disables insert batching:

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true

The service saves valid rows in chunks of 500. After each chunk, flush() sends the pending inserts and clear() detaches the saved entities, so memory does not grow with the file:

repository.saveAll(students);   // persist() per student, sent in JDBC batches of 50
entityManager.flush();          // run the batched inserts now
entityManager.clear();          // free the saved entities before the next chunk

The import method is @Transactional, so all chunks belong to one transaction. To see the batches, we turned on Hibernate statistics (spring.jpa.properties.hibernate.generate_statistics=true) and the org.hibernate.session.metrics logger at DEBUG. Importing 1,200 valid rows printed:

DEBUG org.hibernate.session.metrics : HHH000401: Logging session metrics:
  5891555 ns preparing 30 JDBC statements
  17971287 ns executing 27 JDBC statements
  156534667 ns executing 24 JDBC batches
  432371392 ns executing 4 flushes (flushing a total of 1200 entities and 0 collections)

1,200 inserts went to the database in 24 calls instead of 1,200. The plain statements are the 3 roll number checks (chunks of 500, 500 and 200) and the calls to student_seq, one per 50 ids. The log has more lines (connections, cache); we trimmed them.

2.9. Reading Large Files With the SAX Event API

XSSFWorkbook is fine for a class roster, but a district office may upload a sheet with every student in the region, and XSSFWorkbook keeps an object for every cell of it. The XSSF event API reads the sheet XML as a stream: XSSFSheetXMLHandler turns the XML into calls to our SheetContentsHandler, one per cell, already formatted by our IsoDateFormatter:

XSSFReader reader = new XSSFReader(pkg);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
try (InputStream sheet = reader.getSheetIterator().next()) {        // first sheet
  XMLReader parser = XMLHelper.newXMLReader();
  parser.setContentHandler(new XSSFSheetXMLHandler(
      reader.getStylesTable(), strings, new RowCollector(rows), new IsoDateFormatter(), false));
  parser.parse(new InputSource(sheet));
}

public void startRow(int rowNum) {
  values = new ArrayList<>();
}

public void cell(String cellReference, String formattedValue, XSSFComment comment) {
  int column = new CellReference(cellReference).getCol();   // "C5" -> 2
  while (values.size() < column) {
    values.add("");                                         // empty cells are not reported
  }
  values.add(formattedValue == null ? "" : formattedValue.trim());
}

public void endRow(int rowNum) {
  if (!SheetReader.isBlank(values)) {
    rows.accept(new SheetRow(rowNum + 1, values));
  }
}

The streaming reader produces the same SheetRow objects, so the service does not change. The endpoint uses it with ?streaming=true, and the test suite runs the roster through both readers and expects the same report.

To compare memory, we generated a sheet with 100,000 students (3,007 KB on disk) and read it in a fresh JVM with a growing -Xmx until the read succeeded:

Reader	Package opened from	Smallest -Xmx that worked
XSSF event API (SAX)	File	16 MB (the smallest we tried)
XSSF event API (SAX)	InputStream	96 MB
XSSFWorkbook	File	768 MB (512 MB failed)
XSSFWorkbook	InputStream	768 MB (512 MB failed)

Each try ran in a fresh JVM with -XX:+UseSerialGC on a small, shared sandbox machine with 2 CPUs, and the steps were 16, 24, 32, 48, 64, 96, 128, 192, 256, 384, 512 and 768 MB. The exact numbers will differ on other machines, but the gap will not. Two facts stood out:

• The SAX reader only saves most of its memory when the package is opened from a file. With OPCPackage.open(InputStream), POI unzips every part into byte arrays first; the OutOfMemoryError in those runs came from that step, not from parsing. XSSFWorkbook needed the same heap either way, because it builds every cell as an object.
• ReadOnlySharedStringsTable keeps all distinct strings of the file in memory, so a sheet full of unique names and emails needs more heap than one with repeated values.

With max-file-size=5MB, a user can upload a sheet with 100,000 students. If the application runs with a small heap, make the event API the default reader instead of an option.

FastExcel is another streaming option for reading large files; we stayed with POI so that both readers share the same DataFormatter logic.

2.10. Limiting the Upload Size

Spring Boot rejects uploads over 1MB per file and 10MB per request by default, according to the Spring Boot multipart how-to. A sheet with 100,000 students is about 3 MB, so we raise both limits:

spring.servlet.multipart.max-file-size=5MB
spring.servlet.multipart.max-request-size=6MB

A larger file makes Spring throw MaxUploadSizeExceededException before our controller runs. A second handler in the same advice turns it into a 413 response:

@ExceptionHandler(MaxUploadSizeExceededException.class)
ProblemDetail tooLarge(MaxUploadSizeExceededException e) {
  return ProblemDetail.forStatusAndDetail(HttpStatus.CONTENT_TOO_LARGE,
      "The file is larger than the 5MB upload limit");
}

curl -F "file=@big.xlsx" http://localhost:8080/students/import
# HTTP 413
# {"detail":"The file is larger than the 5MB upload limit","instance":"/students/import","status":413,"title":"Content Too Large"}

A 50 MB file got the same 413 response in our run. max-request-size is a little larger than max-file-size because the request also carries the multipart headers and the other form fields.

MockMvc does not apply these limits, because it never parses a real multipart request. The project checks the 413 with @SpringBootTest(webEnvironment = RANDOM_PORT) and a real HTTP request to the embedded Tomcat.

2.11. Testing the Import With MockMvc

The test builds the roster in memory with POI, so no binary file has to live in src/test/resources. A small helper writes each value of a row with the matching cell type, the way a teacher’s sheet would store it:

rows.add(new Object[]{101, "Anna", "7A", "anna@school.com", 85, LocalDate.of(2024, 6, 15)});

switch (values) {
  case Number n -> cell.setCellValue(n.doubleValue());   // numeric cell
  case LocalDate d -> {
    cell.setCellValue(d);
    cell.setCellStyle(dateStyle);                         // data format 14, m/d/yy
  }
  default -> cell.setCellValue(values.toString());    // text cell
}

ByteArrayOutputStream out = new ByteArrayOutputStream();
workbook.write(out);
return out.toByteArray();                                 // the .xlsx bytes for the upload

MockMvc sends the bytes as a multipart request with multipart(). The test runs once per reader, compares the whole JSON report and then reads the saved rows back from the repository:

@ParameterizedTest
@ValueSource(booleans = {false, true})
void importsValidRowsAndReportsRowErrors(boolean streaming) throws Exception {
  byte[] roster = TestWorkbooks.xlsx(TestWorkbooks.roster());

  mockMvc.perform(multipart("/students/import")
          .file(new MockMultipartFile("file", "class-7.xlsx", XLSX, roster))   // file(roster) in the project
          .param("streaming", String.valueOf(streaming)))
      .andExpect(status().isOk())
      .andExpect(content().json(ROSTER_REPORT, JsonCompareMode.STRICT));

  List<Student> saved = repository.findAllByOrderByRollNumber();
  assertThat(saved).extracting(Student::getRollNumber).containsExactly("101", "102", "106", "107");
  Student anna = saved.getFirst();
  assertThat(anna.getMarks()).isEqualTo(85);
  assertThat(anna.getAdmissionDate()).isEqualTo(LocalDate.of(2024, 6, 15));
}

In Spring Boot 4, @AutoConfigureMockMvc lives in the spring-boot-webmvc-test module, which the spring-boot-starter-webmvc-test starter brings in. Other tests in the class cover the second upload, a missing column, a text file renamed to .xlsx, a 6 MB upload that MockMvc lets through, the all-or-nothing mode and the 24 batches for 1,200 rows.

3. Excel Import FAQs

3.1. Should We Accept CSV or Excel Files?

CSV is plain text with one line per row, so it is smaller, faster to parse and needs no POI. Excel is what office staff already use, and it keeps types and formats that CSV loses:

	CSV	Excel .xlsx
Format	Plain text, comma separated	Zip of XML files
Library	None, or OpenCSV	Apache POI
Dates and numbers	Text only, format depends on who saved it	Typed cells with display formats
Several sheets	No	Yes
Leading zeros, such as roll “007”	Lost when the file is opened and saved in Excel	Kept in a text cell
Memory for large files	Line by line	Needs the event API

When the people uploading the file work in Excel, we accept .xlsx. When another system produces the file, CSV is usually the simpler choice. Both can share the same SheetRow and validation code; only the reader changes.

3.2. How Do We Read a Numeric Cell as a String?

getStringCellValue() on a numeric cell throws IllegalStateException: Cannot get a STRING value from a NUMERIC cell. getNumericCellValue() works but returns 101.0. DataFormatter.formatCellValue(cell) returns “101”, the text Excel displays:

new DataFormatter().formatCellValue(roll);   // "101"

Calling cell.setCellType(CellType.STRING) before reading, as older tutorials show, is deprecated in POI 5.

3.3. How Do We Make the Import All or Nothing?

Some offices prefer to fix the file and upload it again rather than import half of it. With ?allOrNothing=true, the service still reads and checks every row, then marks the transaction for rollback when the report has errors:

if (allOrNothing && !run.errors.isEmpty()) {
  TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
  imported = 0;                               // the roster: imported 0, failed 4, no rows saved
}

The chunks that were already flushed are rolled back with the rest, and the teacher still gets the full error list.

3.4. Should We Use Spring Batch for Excel Imports?

For an upload that a user waits for, a controller and a service like ours are enough. Spring Batch fits scheduled imports of very large files, where restart after a failure, skip limits and job history matter more than an immediate HTTP response.

4. Conclusion

An Excel import in Spring Boot reads each cell as text with DataFormatter, maps columns by their header names, converts and validates every row, and saves the valid rows in JDBC batches while the invalid ones go into a JSON report with row, column and message. XSSFWorkbook is enough for sheets with a few thousand rows; for larger files, the XSSF event API opened from a file keeps the heap small, and spring.servlet.multipart.max-file-size sets the upper limit for what users can send. The reverse direction, exporting data to Excel from a REST API, uses the same POI classes.

5. References

• Apache POI: Spreadsheet Quick Guide
• Apache POI: Spreadsheet How-To (event API)
• DataFormatter JavaDoc
• XSSFSheetXMLHandler JavaDoc
• Spring Boot: Handling Multipart File Uploads
• Spring Framework: Multipart Resolver
• Spring Framework: MockMvc
• Hibernate ORM 7.4 User Guide
• Jakarta Bean Validation 3.1
• RFC 9457: Problem Details for HTTP APIs

Happy Learning !!

Source Code on Github