In this post, we’ll explore how to use Apache POI in a Spring Boot REST API to read data from Excel files and store it in a database.
· Prerequisites
· Overview
∘ What’s Apache POI?
∘ Why Automate Excel to Database Processing?
· Let’s code: Practical Example
∘ Adding Apache POI dependency
∘ Apache POI Component Map
∘ Creating the Domain Model and Repository
∘ Excel Processing Service
∘ Creating the REST Controller
· Testing the API
· Handling Challenges and Best Practices
∘ Large Excel Files
∘ Process Rows in Batches (Never store all rows in memory)
∘ Do Not Block the Request Thread (Async Import)
∘ Monitoring
∘ Security
· Conclusion
· References
Prerequisites
This is the list of all the prerequisites:
- Spring Boot 3 or later
- Maven 3.6.3 or later
- Java 21
- IntelliJ IDEA, Visual Studio Code, or another IDE
- Postman / insomnia or any other API testing tool.
- H2 / MySQL / PostgreSQL (depending on your choice of database)
Overview
What’s Apache POI?
Apache POI, a project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.
Why Automate Excel to Database Processing?
Working with Excel files is a common requirement in enterprise applications — whether it’s importing bulk data, generating reports, or automating recurring processes. Many organizations rely on spreadsheets to track data. Manually transferring this data to a database is both time-consuming and prone to errors. Automation offers:
- Time savings: Bulk import hundreds or thousands of rows in minutes.
- Accuracy: Reduces human errors during data entry.
- Scalability: Process multiple files or large datasets programmatically.
Let’s code: Practical Example
We’ll implement:
- A REST endpoint that accepts an Excel file
- A service that reads the workbook using Apache POI
- Conversion of Excel rows into
Bookentities - Saving the books into PostgreSQL via JPA
Let’s start by creating a simple Spring Boot project from start.spring.io, with the following dependencies: Spring Web, Lombok, PostgreSQL Database, Spring Data JPA, and Validation.
Adding Apache POI dependency
We need to add the Apache POI dependency to our pom.xml to handle Excel files.
<!-- Apache POI for Excel processing -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.5.0</version> <!-- Use the latest version -->
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.5.0</version>
</dependency>Apache POI Component Map
The Apache POI distribution consists of support for many document file formats.
- Components named H??F are for reading or writing OLE2 binary formats.
- Components named X??F are for reading or writing OpenOffice XML (OOXML) formats.
For Excel files, POI provides:
- HSSF: For Excel 97(-2007) file format (.xls)
- XSSF: For Excel 2007 OOXML file format (.xlsx)
- SXSSF: For streaming very large Excel files (a low-memory footprint)
Creating the Domain Model and Repository
Let’s model our data. We’ll create a simple Book entity that we want to import from Excel.
@Builder
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "book")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private UUID id;
private String title;
private String isbn;
private String description;
private Integer page;
private BigDecimal price;
}Next, we’ll create a Spring Data JPA repository to handle database operations.
BookRepository.java
@Repository
public interface BookRepository extends JpaRepository<Book, UUID> {
/**
* Retrieves all {@link Book} entities whose titles are present in the given collection.
* <p>
* Useful for checking existing titles in bulk to avoid duplicates before saving new records.
*
* @param titles a collection of book titles to search for
* @return a list of {@link Book} entities that have a title matching one of the provided titles
*/
List<Book> findAllByTitleIn(Collection<String> titles);
}Excel Processing Service
We’ll create a service that uses Apache POI to read the Excel file.
/**
* Service class responsible for processing Excel files and managing {@link Book} entities.
* <p>
* The service reads an Excel file, converts each row to a {@link Book} entity, and saves
* new books to the database while skipping titles that already exist.
*/
@Slf4j
@Service
public class BookService {
// Column index constants for clarity
private static final int COL_TITLE = 0;
private static final int COL_ISBN = 1;
private static final int COL_DESCRIPTION = 2;
private static final int COL_PAGE = 3;
private static final int COL_PRICE = 4;
private final BookRepository bookRepository;
public BookService(BookRepository bookRepository) {
this.bookRepository = bookRepository;
}
/**
* Processes an Excel file containing book data and saves new books to the database.
* <p>
* Only books with titles not already present in the database will be saved. Duplicate titles
* in the Excel file itself are ignored.
*
* @param file the Excel file to process
* @return a list of {@link Book} entities that were successfully saved
*/
@Transactional
public List<Book> processExcelFile(MultipartFile file) {
List<Book> books = new ArrayList<>();
try {
try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
Sheet sheet = workbook.getSheetAt(0);
// Start at row 1 to skip header
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) continue;
Book book = buildBookFromRow(row);
if (isValidBook(book)) {
books.add(book);
}
}
}
Set<String> titlesFromExcel = books.stream()
.map(Book::getTitle)
.collect(Collectors.toSet());
// Query database once for existing titles
List<String> existingTitles = bookRepository.findAllByTitleIn(titlesFromExcel)
.stream()
.map(Book::getTitle)
.toList();
// Filter out books whose title exists in DB
List<Book> newBooks = books.stream()
.filter(book -> !existingTitles.contains(book.getTitle().trim()))
.toList();
// Save all new books
return bookRepository.saveAll(newBooks);
} catch (Exception e) {
log.error("Cannot import Excel file", e);
return Collections.emptyList();
}
}
/* -------------------------
Book Builder
-------------------------- */
/**
* Builds a {@link Book} entity from a given Excel row.
*
* @param row the Excel row containing book data
* @return a {@link Book} entity with data extracted from the row
*/
private Book buildBookFromRow(Row row) {
return Book.builder()
.title(getCellValueAsString(row.getCell(COL_TITLE)))
.isbn(getCellValueAsString(row.getCell(COL_ISBN)))
.description(getCellValueAsString(row.getCell(COL_DESCRIPTION)))
.page(getCellValueAsInteger(row.getCell(COL_PAGE)))
.price(getCellValueAsBigDecimal(row.getCell(COL_PRICE)))
.build();
}
/* -------------------------
Validators
-------------------------- */
/**
* Checks if a {@link Book} entity is valid for saving.
* <p>
* Currently, the only validation rule is that the title must be non-null and non-blank.
*
* @param book the {@link Book} entity to validate
* @return {@code true} if valid, {@code false} otherwise
*/
private boolean isValidBook(Book book) {
return book.getTitle() != null && !book.getTitle().isBlank();
}
/* -------------------------
Cell Value Helpers
-------------------------- */
/**
* Extracts a {@link String} value from an Excel {@link Cell}.
*
* @param cell the Excel cell
* @return the string value of the cell, or {@code null} if the cell is empty
*/
private String getCellValueAsString(Cell cell) {
if (cell == null) return null;
return switch (cell.getCellType()) {
case STRING -> cell.getStringCellValue().trim();
case NUMERIC -> DateUtil.isCellDateFormatted(cell)
? cell.getDateCellValue().toString()
: String.valueOf(cell.getNumericCellValue());
case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
case FORMULA -> cell.getStringCellValue();
default -> null;
};
}
/**
* Extracts an {@link Integer} value from an Excel {@link Cell}.
*
* @param cell the Excel cell
* @return the integer value of the cell, or 0 if conversion fails
*/
private Integer getCellValueAsInteger(Cell cell) {
if (cell == null) return null;
try {
return switch (cell.getCellType()) {
case NUMERIC -> (int) cell.getNumericCellValue();
case STRING -> Integer.parseInt(cell.getStringCellValue().trim());
default -> null;
};
} catch (NumberFormatException e) {
return 0;
}
}
/**
* Extracts a {@link BigDecimal} value from an Excel {@link Cell}.
*
* @param cell the Excel cell
* @return the BigDecimal value of the cell, or 0 if conversion fails
*/
private BigDecimal getCellValueAsBigDecimal(Cell cell) {
if (cell == null) return null;
try {
return switch (cell.getCellType()) {
case NUMERIC -> BigDecimal.valueOf(cell.getNumericCellValue());
case STRING -> new BigDecimal(cell.getStringCellValue().trim());
default -> null;
};
} catch (NumberFormatException e) {
return BigDecimal.valueOf(0);
}
}
}This BookService class handles the complete workflow of importing books from an Excel file into the database.
The Workbook interface is the entry point, representing the entire Excel file.
// For .xlsx files
Workbook workbook = new XSSFWorkbook();
// For .xls files
Workbook workbook = new HSSFWorkbook();
// Or better - use the factory method
Workbook workbook = WorkbookFactory.create(inputStream);Each Workbook contains one or more Sheet objects, representing individual tabs.
// Create a new sheet
Sheet sheet = workbook.createSheet("Books");
// Get sheet by name
Sheet firstSheet = workbook.getSheet("Sheet1");
// Get sheet by index
Sheet secondSheet = workbook.getSheetAt(0);
// Iterate through all sheets
for (Sheet sheet : workbook) {
System.out.println("Sheet name: " + sheet.getSheetName());
}Each Sheet contains Row objects, representing horizontal data rows.
// Create rows
Row headerRow = sheet.createRow(0); // Row index starts at 0
Row dataRow = sheet.createRow(1);
// Get existing row
Row row = sheet.getRow(0);
// Iterate through rows
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row currentRow = rowIterator.next();
// Process row
}Each Row contains Cell objects, representing individual data cells.
// Create cells with different data types
Row row = sheet.createRow(0);
// String cell
Cell stringCell = row.createCell(0, CellType.STRING);
stringCell.setCellValue("Product Name");
// Numeric cell
Cell numericCell = row.createCell(1, CellType.NUMERIC);
numericCell.setCellValue(29.99);
// Boolean cell
Cell booleanCell = row.createCell(2, CellType.BOOLEAN);
booleanCell.setCellValue(true);
// Date cell
Cell dateCell = row.createCell(3, CellType.NUMERIC);
dateCell.setCellValue(new Date());Apache POI Workbook Structure Overview
Workbook
├── Sheet(s) // Represents individual sheets in the workbook
│ ├── Row(s) // Rows inside each sheet
│ │ ├── Cell(s) // Cells inside each row
│ │ │ ├── CellStyle // Formatting and styling for the cell
│ │ │ ├── CellType // Type of data stored (STRING, NUMERIC, BOOLEAN, FORMULA, etc.)
│ │ │ └── CellValue // The actual value contained in the cell
│ │ └── RowStyle // Optional row-level styling
│ └── PrintSetup // Page setup information for printing
├── Font(s) // Fonts available in the workbook
├── CellStyle(s) // Predefined cell styles that can be applied to cells
├── DataFormat // Custom data formats (e.g., date, currency, percentage)
└── FormulaEvaluator // Utility to evaluate Excel formulas programmaticallyCreating the REST Controller
Now, let’s expose an endpoint to upload the Excel file.
@RestController
@RequestMapping("/api/books")
@CrossOrigin(origins = "*")
public class BookController {
private final BookService bookService;
public BookController(BookService bookService) {
this.bookService = bookService;
}
@PostMapping("/upload")
public ResponseEntity<Map<String, Object>> uploadExcelFile(@RequestParam("file") MultipartFile file) {
Map<String, Object> response = new HashMap<>();
try {
// Check if file is Excel format
if (!Objects.requireNonNull(file.getOriginalFilename()).endsWith(".xlsx")) {
response.put("success", false);
response.put("message", "Please upload an Excel file (.xlsx)");
return ResponseEntity.badRequest().body(response);
}
// Process Excel file and extract books
List<Book> extractedBooks = bookService.processExcelFile(file);
if (extractedBooks.isEmpty()) {
response.put("success", false);
response.put("message", "No data to found");
return ResponseEntity.internalServerError().body(response);
}
response.put("success", true);
response.put("message", "File processed successfully");
response.put("extractedBook", extractedBooks.size());
return ResponseEntity.ok(response);
} catch (Exception e) {
response.put("success", false);
response.put("message", "Error processing file: " + e.getMessage());
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
}
}
}Testing the API
Your Excel file might look like this:
For this post, we have an Excel file containing 5000 generated book lines.
Test the API: Use a tool like Postman or cURL to send a POST request.
In the database
Handling Challenges and Best Practices
Large Excel Files
Reading huge Excel files (50,000 to 1,000,000+ rows) is a common challenge in Spring Boot applications, and Apache POI alone (XSSFWorkbook) is not enough — its default model loads the entire file into memory, which can easily cause:
- OutOfMemoryError
- Very slow imports
- Server crashes
XSSF + SAX (Event API) is sufficient; this may be the best approach, or implement Spring Batch with Chunk-Based Processing
Process Rows in Batches (Never store all rows in memory)
For 1M rows, do NOT collect everything in a List.
Use a batch size of 500–5,000 rows.
Process each chunk:
- Validate
- Filter duplicates
- Save to DB (
saveAll) - Clear memory
- Continue
Do Not Block the Request Thread (Async Import)
For very large imports, always process in the background:
- Use Spring @Async
- Or message queue (Kafka, RabbitMQ)
- OR store import job in DB and process via scheduled worker
Monitoring
Log row counts and processing time. Use Spring Boot Actuator for metrics.
Security
Sanitize file inputs to prevent XML external entity (XXE) attacks in event parsing.
Conclusion
🏁 Well done !!. In this post, we explored how to efficiently process Excel files in Spring Boot, from reading and validating data to saving it in a PostgreSQL database.
The complete source code is available on GitHub.
Support me through GitHub Sponsors.
Thank you for reading!! See you in the next post.
