How to Build a Bulletproof Excel Import in Spring Boot Using Apache POI

Developers building administrative dashboards frequently face the challenge of importing user-generated Excel files into a database without crashing the server or corrupting data. Processing an .xlsx file in Spring Boot requires handling multipart uploads, parsing cells with Apache POI, validating rows, and executing batch inserts. A school office uploading a class roster with exam marks is a typical case: most rows are fine, a few have a wrong email or an invalid mark, and the system must report exactly which cells to fix.

The following example reads two cells of a roster row with DataFormatter and checks the row with Bean Validation before the valid students go to the database. After that, a curl command uploads the roster to the import endpoint.

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"}, ...]}

Notice that DataFormatter returns the numeric roll number as the text "101", and the endpoint answers with a JSON report that names the row and the column of every error. Below is the complete architecture for building this endpoint, including cell reading, header mapping, duplicate checks, and streaming large files.

An .xlsx file is a zip archive of XML files in the Office Open XML format. Each sheet is one XML file, and two more files hold the shared table of repeated strings and the styles that dictate how Excel displays numbers and dates. Apache POI is the Java library that reads and writes these files. An import endpoint passes the file through four steps: file validation, header mapping, row validation, and database insertion.

Apache POI offers two ways to read a sheet. The standard method, XSSFWorkbook, builds an object for every row and cell in memory. Alternatively, 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 in memory.

Feature	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

Both readers produce the same text for each cell, ensuring that validation and saving logic remains identical regardless of the chosen parsing method.

Spring Boot does not manage the Apache POI version, requiring manual configuration. The poi-ooxml module handles .xlsx files and automatically pulls in the core poi module.

  org.springframework.boot
  spring-boot-starter-webmvc


  org.springframework.boot
  spring-boot-starter-data-jpa


  org.springframework.boot
  spring-boot-starter-validation


  org.apache.poi
  poi-ooxml
  5.5.1


  com.h2database
  h2
  runtime

The database entity is a standard JPA class. The rollNumber field enforces a unique constraint, and the ID is generated from a sequence, which is critical for enabling batch inserts later in the process.

@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;
}

Instead of validating the entity directly, a StudentRow record holds one converted row alongside its Excel row number, carrying 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 maps each header to a specific field name. The validator reports errors by field name, and the enum translates that back into the original header text for the final user 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
}

In Spring, a MultipartFile represents a file uploaded via a multipart/form-data request. The controller verifies the file extension and transfers the upload to a temporary file on the disk.

@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);
  }
}

Apache POI requires a physical file rather than an InputStream to avoid buffering the entire file in memory. If the file is not a valid zip archive, POI throws an exception, which the controller maps to a 400 response using RFC 9457 standard Problem Details.

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

Every cell in Excel possesses a specific type: string, numeric, boolean, formula, or blank. Attempting to read a cell with the incorrect getter method throws an exception, which is the most frequent cause of import bugs.

String text = roll.getStringCellValue();                        // IllegalStateException: Cannot get a STRING value from a NUMERIC cell
double number = roll.getNumericCellValue();                     // 101.0

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

The DataFormatter class returns the exact text Excel displays for a cell, regardless of its underlying type. Dates require additional handling because their displayed text depends on user formatting. Extending DataFormatter ensures dates are consistently returned in ISO format.

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);
  }
}

The system skips rows that were never written or contain only empty strings. Rows are numbered starting at 1, matching Excel's native interface.

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 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 serves as the header. The system maps each column by its header text, ignoring case and extra spaces. This approach prevents data corruption if a user inserts or moves a column before uploading.

Map 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));
}

If a required column is missing, the import halts immediately and returns a 400 Bad Request response detailing the exact missing headers.

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

Each row undergoes conversion and validation. Text values for marks and dates are converted to their respective Java types, and any failure is recorded against the column header.

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

for (ConstraintViolation 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 final report is a JSON object containing the total row count, successful imports, failed rows, and a detailed array of errors. Uploading a sample roster triggers this validation process.

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"}]}

Duplicate entries can exist within the uploaded file itself or conflict with existing database records. A memory map tracks duplicates within the current file during parsing.

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 database conflicts, a single query per chunk of 500 rows identifies roll numbers that have already been saved.

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

If the same file is uploaded twice, the system rejects the previously saved rows and appends an "already exists" message to the error report.

{"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"}]}

Standard JPA operations send one INSERT statement per entity. Enabling JDBC batching allows Hibernate to group up to 50 inserts into a single database call, drastically reducing network overhead.

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

The service processes valid rows in chunks of 500. Calling flush() executes the batched inserts, and clear() detaches the entities to prevent memory bloat.

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

When importing 1,200 valid rows, Hibernate statistics confirm that the inserts are executed in just 24 database calls rather than 1,200 individual statements.

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)

While XSSFWorkbook is sufficient for small rosters, large datasets require the XSSF event API. This streaming approach reads the XML as a continuous stream, triggering callbacks for each cell without loading the entire document into memory.

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));
  }
}

Testing a 3,007 KB file containing 100,000 rows reveals a massive difference in memory consumption between the two parsing methods.

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)

The SAX reader only achieves maximum memory efficiency when the package is opened directly from a file. Using an InputStream forces POI to unzip every part into byte arrays first, negating the memory benefits.

Spring Boot rejects uploads exceeding 1MB per file by default. To accommodate a 3 MB file containing 100,000 rows, the application properties must be adjusted.

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

If a user attempts to upload a file exceeding these limits, Spring throws a MaxUploadSizeExceededException. A dedicated exception handler intercepts this and returns a clean 413 Content Too Large 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 "[email protected]" 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"}

Integration tests build the Excel file entirely in memory using POI, eliminating the need to store binary files in the test resources directory.

rows.add(new Object[]{101, "Anna", "7A", "[email protected]", 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

The MockMvc framework sends these bytes as a multipart request, verifying both the JSON response and the resulting database state.

@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 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));
}

Choosing between CSV and Excel depends entirely on the end-user workflow. CSV is faster to parse and requires no external libraries, but it strips away formatting and leading zeros.

Feature	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 processing Excel files, reading a numeric cell as a string requires specific handling. The deprecated method of altering the cell type before reading has been replaced by DataFormatter.

String rollText = new DataFormatter().formatCellValue(roll);   // "101"

Certain business requirements demand that an entire file be rejected if even a single row fails validation. By passing an allOrNothing flag, the service can mark the active transaction for rollback.

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

For massive, scheduled imports where users do not wait for an immediate HTTP response, Spring Batch is the superior architectural choice, offering built-in restart capabilities and skip limits.

The architectural decision between XSSFWorkbook and the SAX event API dictates the scalability of the entire application. The benchmark data reveals a critical vulnerability: a seemingly harmless 3 MB Excel file containing 100,000 rows forces XSSFWorkbook to consume 768 MB of heap space. In a containerized environment with strict memory limits, concurrent uploads of this size will trigger immediate OutOfMemory errors and crash the pod.

By combining a strict 5 MB upload limit with the SAX event API, memory consumption drops to just 16 MB per file. This configuration ensures that the application remains stable under load, processing massive datasets sequentially without buffering the entire XML structure. Developers must prioritize the event API for any public-facing import endpoint, reserving the simpler user model exclusively for tightly controlled, low-volume internal tools.