Introduction to Excel Data Processing with Spring Boot
In today’s business environment, Excel spreadsheets remain a crucial tool for storing and analyzing data. Integrating this data with your enterprise-level applications can streamline workflows, automate processes, and increase efficiency. Spring Boot, known for its convention-over-configuration approach, provides robust tools and libraries to handle such integration seamlessly. This blog will explore five effective ways to read Excel data into your Spring Boot application.
Method 1: Using Apache POI
Apache POI, or Poor Obfuscation Implementation, is one of the most popular libraries for working with Microsoft Office documents in Java. Here’s how you can use Apache POI to read Excel files:
- Download and Include Apache POI in Your Project: You need to add the POI dependencies in your
pom.xmlif you’re using Maven, or in yourbuild.gradleif you’re on Gradle.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
- Read the Excel File:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
public class ExcelReader {
public static void main(String[] args) throws Exception {
FileInputStream excelFile = new FileInputStream(new File("path/to/excel/file.xlsx"));
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
while (cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
if (currentCell.getCellType() == CellType.STRING) {
System.out.print(currentCell.getStringCellValue() + " ");
}
}
System.out.println();
}
workbook.close();
}
}
Important Notes:
👀 Note: Always remember to close the workbook after processing to free up resources.
Method 2: Using JExcelAPI
JExcelAPI is another library for working with Excel files in Java, mainly focusing on .xls files. Here’s how to integrate it:
- Include JExcelAPI in Your Project:
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
- Read Excel Files:
import jxl.*;
import jxl.read.biff.BiffException;
import java.io.File;
import java.io.IOException;
public class JExcelReader {
public static void main(String[] args) {
try {
Workbook workbook = Workbook.getWorkbook(new File("path/to/excel/file.xls"));
Sheet sheet = workbook.getSheet(0);
for (int row = 0; row < sheet.getRows(); row++) {
for (int col = 0; col < sheet.getColumns(); col++) {
Cell cell = sheet.getCell(col, row);
CellType type = cell.getType();
if (type == CellType.LABEL) {
System.out.print(((LabelCell) cell).getString() + " ");
}
}
System.out.println();
}
workbook.close();
} catch (BiffException | IOException e) {
e.printStackTrace();
}
}
}
Method 3: Using OpenCSV with Excel Files
OpenCSV can be used to read Excel data by exporting the Excel sheet to a CSV file first:
- Add OpenCSV to Your Project:
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>5.5</version>
</dependency>
- Process the CSV:
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import java.io.FileReader;
public class CSVReaderExample {
public static void main(String[] args) {
try (CSVReader reader = new CSVReaderBuilder(new FileReader("path/to/excelExported.csv"))
.withSkipLines(1) // Skip header
.build()) {
String[] nextLine;
while ((nextLine = reader.readNext()) != null) {
System.out.println("Line 1: " + nextLine[0]);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Method 4: Using EasyExcel
For a more modern approach, especially with the rise of annotation-based configuration, EasyExcel comes into play:
- Add EasyExcel Dependency:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
- Define Data Classes and Read Excel:
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.EasyExcel;
import java.util.List;
import java.util.Map;
class Data {
@ExcelProperty(value = "Column1")
private String column1;
// Getters & Setters
}
public class EasyExcelReader {
public static void main(String[] args) {
List<Map<Integer, String>> data = EasyExcel.read("path/to/excel/file.xlsx", Data.class, new PageReadListener<Map<Integer, String>>()).sheet().doReadSync();
for (Map<Integer, String> row : data) {
System.out.println(row.get(0)); // Assuming you want to print the first column
}
}
}
Method 5: Using Spring Batch for Scheduled Excel File Processing
Spring Batch offers a framework to process large volumes of data in batch processes, which can be scheduled or triggered:
- Setup Spring Batch Job:
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.file.FlatFileItemReader;
import org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper;
import org.springframework.batch.item.file.mapping.DefaultLineMapper;
import org.springframework.batch.item.file.transform.DelimitedLineTokenizer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@EnableBatchProcessing
public class BatchConfig {
@Autowired
public JobBuilderFactory jobBuilderFactory;
@Autowired
public StepBuilderFactory stepBuilderFactory;
@Bean
public FlatFileItemReader<Person> reader() {
FlatFileItemReader<Person> reader = new FlatFileItemReader<>();
reader.setResource(new FileSystemResource("path/to/excel/file.csv"));
reader.setLineMapper(new DefaultLineMapper<Person>() {{
setLineTokenizer(new DelimitedLineTokenizer() {{
setNames("name", "age", "department");
}});
setFieldSetMapper(new BeanWrapperFieldSetMapper<Person>() {{
setTargetType(Person.class);
}});
}});
return reader;
}
// Define Step and Job
}
In conclusion, processing Excel data in a Spring Boot application can be achieved through various libraries and methods, each offering distinct advantages depending on your project’s requirements. Apache POI provides extensive support for all Excel features, while JExcelAPI might be more suitable for older .xls files. For simpler needs or integration with other formats, OpenCSV or EasyExcel could be preferred due to their simplicity. Lastly, Spring Batch is the way to go if you’re dealing with large scale data processing or scheduled tasks. Remember to choose the method that best fits your needs in terms of functionality, performance, and maintenance.
Can I use these methods for both .xls and .xlsx files?
+
Yes, Apache POI supports both .xls and .xlsx files. However, libraries like JExcelAPI are specifically for .xls files. EasyExcel and Spring Batch methods are more flexible and can handle both formats through configuration.
How do I handle large Excel files in Spring Boot?
+
For large files, consider using streaming APIs like those provided by Apache POI or SAX parsing for minimal memory usage, or leverage Spring Batch for processing data in chunks.
Which method is the simplest for beginners?
+
Using Apache POI or OpenCSV might be the simplest, as they offer straightforward ways to read Excel files with well-documented examples and community support.
