Import Wizard — Reading CSVs and Excel Sheets in Spring Boot

Handling file imports is a common task for many applications. In this post, we’ll learn how to easily read CSV and multi-sheet Excel files in a Spring Boot app using plain Java.

Image by J S from Pixabay

The Game Plan

1. Set up a Spring Boot project.
2. Create REST endpoints to accept file uploads.
3. Use OpenCSV to parse CSV data.
4. Use Apache POI to read Excel sheets.
5. Return analysis of file contents.

Creating the Spring Boot App

First, we’ll bootstrap a Spring Boot app with the Web and DevTools dependencies:

<!-- pom.xml -->

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-devtools</artifactId>
  <scope>runtime</scope>
  <optional>true</optional>
</dependency>

This sets up a runnable Spring MVC app that we can develop rapidly.

Next, we create a main application class:

@SpringBootApplication
public class FileImportApp {

  public static void main(String[] args) {
    SpringApplication.run(FileImportApp.class, args);
  }

}

This launches our app with an embedded Tomcat server.

Implementing the REST API

We add a controller with two endpoints, one for CSV and one for Excel uploads:

@RestController
@RequestMapping("/api/files")
public class FileController {

  @PostMapping("/csv")
  public String parseCSV(...) {
    // parse CSV
  }  

  @PostMapping("/excel")
  public String parseExcel(...) {
    // parse Excel  
  }

}

The @RequestParam MultipartFile file parameter will contain the uploaded file data.

Next, we’ll implement the parser logic.

Reading CSV Data

For CSV parsing, we’ll leverage OpenCSV:

<!-- pom.xml -->

<dependency>
  <groupId>com.opencsv</groupId>
  <artifactId>opencsv</artifactId>
  <version>5.7.1</version>
</dependency>

Then parse the rows in our endpoint:

@PostMapping("/csv")  
public String parseCSV(@RequestParam MultipartFile file) throws IOException {

  Reader reader = new InputStreamReader(file.getInputStream());  

  // Parse CSV data
  CSVReader csvReader = new CSVReaderBuilder(reader).build();
  List<String[]> rows = csvReader.readAll();

  // Analyze data...

  return "Processed " + rows.size() + " rows!";
}

The CSVReader provides a stream-like interface for easily parsing the rows. We can then process or analyze the data.

Reading Excel Files

For Excel, we’ll use the Apache POI library:

<!-- pom.xml -->

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.2.3</version>
</dependency>

Then in our endpoint:

@PostMapping("/excel")
public String parseExcel(@RequestParam MultipartFile file) throws IOException {

  XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());

  // Loop through sheets
  for(Sheet sheet : workbook) {
    System.out.println(sheet.getSheetName());
  
    // Loop through rows
    for(Row row : sheet) {
      // Process cell data
    }
  }

  return "Processed Excel with " + workbook.getNumberOfSheets() + " sheets!";
}

This gives us access to rows and cells across all the sheets. We can then extract, and process as needed.

Key Takeaways

• Spring Boot’s REST support makes it easy to handle file uploads
• OpenCSV provides a simple API for reading and parsing CSV data
• Apache POI gives access to multi-sheet Excel documents
• A bit of Java is all you need to implement robust file importers

Hope you enjoyed this quick tutorial on ingesting CSVs and Excel sheets in Spring Boot apps! Let me know if you have any other questions.

Learn More

• OpenCSV Documentation
• Apache POI Components

And as always, thanks for reading! Be sure to follow me for more helpful programming tutorials.