Spring Boot Examples : API to upload and process Excel file.
In this post, we will write code to process excel file from API and conter that to json list of object using spring boot, apache poi and java 21.
Lets start..
we are using...
1, Spring Boot2, Java 213, Apache POI (to process excel)
Here, we will be using
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
Project Folder Structrure:
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.3.0-SNAPSHOT</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.javainspires</groupId>
<artifactId>excel-upload</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excel-upload</name>
<description>Demo project for Spring Boot + Excel Upload</description>
<properties>
<java.version>21</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/milestone</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
<repository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https://repo.spring.io/snapshot</url>
<releases>
<enabled>false</enabled>
</releases>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/milestone</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</pluginRepository>
<pluginRepository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https://repo.spring.io/snapshot</url>
<releases>
<enabled>false</enabled>
</releases>
</pluginRepository>
</pluginRepositories>
</project>
Application Class ExcelUploadApplication.java
package com.javainspires.excelupload;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class ExcelUploadApplication {
public static void main(String[] args) {
SpringApplication.run(ExcelUploadApplication.class, args);
}
}
Controller class ExcelUploadController.java
package com.javainspires.excelupload;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.List;
import java.util.Map;
@RestController
public class ExcelUploadController {
@PostMapping(value = "/upload")
public ResponseEntity<List<Map<String,String>>> uploadExcel(@RequestParam(name = "file") MultipartFile excelFile) throws IOException {
List<Map<String,String>> result = ExcelProcessHelper.convertExcelRows(excelFile);
return ResponseEntity.ok(result);
}
}
Excel helper class ExcelProcessHelper.java
package com.javainspires.excelupload;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.*;
public class ExcelProcessHelper {
public static List<Map<String,String>> convertExcelRows(MultipartFile excelFile) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook(excelFile.getInputStream());
// get current opened sheet index
int sheetIndex = workbook.getActiveSheetIndex();
// get sheet
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
List<String> headers = new ArrayList<>();
Iterator<Row> rows = sheet.rowIterator();
// process headers
rows.next().forEach(h->headers.add(h.getStringCellValue()));
List<Map<String,String>> rowsResult = new ArrayList<>();
rows.forEachRemaining(row->{
Map<String,String> rowMap = new LinkedHashMap<>();
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
rowMap.put(headers.get(i),row.getCell(i).toString());
}
rowsResult.add(rowMap);
});
return rowsResult;
}
}
application.properties
spring.application.name=excel-upload
Excel File to upload:
Application Startup log:
C:\dvb\jdk-21.0.1\bin\java.exe -agentlib:jdwp=transport=dt_socket,address=127.0.0.1:50377,suspend=y,server=n -javaagent:C:\Users\developer\AppData\Local\JetBrains\IdeaIC2023.3\captureAgent\debugger-agent.jar -Dfile.encoding=UTF-8 -Dsun.stdout.encoding=UTF-8 -Dsun.stderr.encoding=UTF-8 -classpath "C:\Users\developer\Desktop\excel-upload\excel-upload\target\classes;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot-starter-web\3.3.0-SNAPSHOT\spring-boot-starter-web-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot-starter\3.3.0-SNAPSHOT\spring-boot-starter-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot\3.3.0-SNAPSHOT\spring-boot-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot-autoconfigure\3.3.0-SNAPSHOT\spring-boot-autoconfigure-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot-starter-logging\3.3.0-SNAPSHOT\spring-boot-starter-logging-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\ch\qos\logback\logback-classic\1.4.14\logback-classic-1.4.14.jar;C:\Users\developer\.m2\repository\ch\qos\logback\logback-core\1.4.14\logback-core-1.4.14.jar;C:\Users\developer\.m2\repository\org\apache\logging\log4j\log4j-to-slf4j\2.23.1\log4j-to-slf4j-2.23.1.jar;C:\Users\developer\.m2\repository\org\apache\logging\log4j\log4j-api\2.23.1\log4j-api-2.23.1.jar;C:\Users\developer\.m2\repository\org\slf4j\jul-to-slf4j\2.0.12\jul-to-slf4j-2.0.12.jar;C:\Users\developer\.m2\repository\jakarta\annotation\jakarta.annotation-api\2.1.1\jakarta.annotation-api-2.1.1.jar;C:\Users\developer\.m2\repository\org\yaml\snakeyaml\2.2\snakeyaml-2.2.jar;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot-starter-json\3.3.0-SNAPSHOT\spring-boot-starter-json-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\com\fasterxml\jackson\core\jackson-databind\2.17.0\jackson-databind-2.17.0.jar;C:\Users\developer\.m2\repository\com\fasterxml\jackson\core\jackson-annotations\2.17.0\jackson-annotations-2.17.0.jar;C:\Users\developer\.m2\repository\com\fasterxml\jackson\core\jackson-core\2.17.0\jackson-core-2.17.0.jar;C:\Users\developer\.m2\repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.17.0\jackson-datatype-jdk8-2.17.0.jar;C:\Users\developer\.m2\repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.17.0\jackson-datatype-jsr310-2.17.0.jar;C:\Users\developer\.m2\repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.17.0\jackson-module-parameter-names-2.17.0.jar;C:\Users\developer\.m2\repository\org\springframework\boot\spring-boot-starter-tomcat\3.3.0-SNAPSHOT\spring-boot-starter-tomcat-3.3.0-20240327.093721-270.jar;C:\Users\developer\.m2\repository\org\apache\tomcat\embed\tomcat-embed-core\10.1.19\tomcat-embed-core-10.1.19.jar;C:\Users\developer\.m2\repository\org\apache\tomcat\embed\tomcat-embed-el\10.1.19\tomcat-embed-el-10.1.19.jar;C:\Users\developer\.m2\repository\org\apache\tomcat\embed\tomcat-embed-websocket\10.1.19\tomcat-embed-websocket-10.1.19.jar;C:\Users\developer\.m2\repository\org\springframework\spring-web\6.1.5\spring-web-6.1.5.jar;C:\Users\developer\.m2\repository\org\springframework\spring-beans\6.1.5\spring-beans-6.1.5.jar;C:\Users\developer\.m2\repository\io\micrometer\micrometer-observation\1.13.0-M2\micrometer-observation-1.13.0-M2.jar;C:\Users\developer\.m2\repository\io\micrometer\micrometer-commons\1.13.0-M2\micrometer-commons-1.13.0-M2.jar;C:\Users\developer\.m2\repository\org\springframework\spring-webmvc\6.1.5\spring-webmvc-6.1.5.jar;C:\Users\developer\.m2\repository\org\springframework\spring-aop\6.1.5\spring-aop-6.1.5.jar;C:\Users\developer\.m2\repository\org\springframework\spring-context\6.1.5\spring-context-6.1.5.jar;C:\Users\developer\.m2\repository\org\springframework\spring-expression\6.1.5\spring-expression-6.1.5.jar;C:\Users\developer\.m2\repository\org\apache\poi\poi-ooxml\3.9\poi-ooxml-3.9.jar;C:\Users\developer\.m2\repository\org\apache\poi\poi\3.9\poi-3.9.jar;C:\Users\developer\.m2\repository\commons-codec\commons-codec\1.16.1\commons-codec-1.16.1.jar;C:\Users\developer\.m2\repository\org\apache\poi\poi-ooxml-schemas\3.9\poi-ooxml-schemas-3.9.jar;C:\Users\developer\.m2\repository\org\apache\xmlbeans\xmlbeans\2.3.0\xmlbeans-2.3.0.jar;C:\Users\developer\.m2\repository\stax\stax-api\1.0.1\stax-api-1.0.1.jar;C:\Users\developer\.m2\repository\dom4j\dom4j\1.6.1\dom4j-1.6.1.jar;C:\Users\developer\.m2\repository\xml-apis\xml-apis\1.0.b2\xml-apis-1.0.b2.jar;C:\Users\developer\.m2\repository\org\projectlombok\lombok\1.18.30\lombok-1.18.30.jar;C:\Users\developer\.m2\repository\org\slf4j\slf4j-api\2.0.12\slf4j-api-2.0.12.jar;C:\Users\developer\.m2\repository\net\bytebuddy\byte-buddy\1.14.12\byte-buddy-1.14.12.jar;C:\Users\developer\.m2\repository\org\springframework\spring-core\6.1.5\spring-core-6.1.5.jar;C:\Users\developer\.m2\repository\org\springframework\spring-jcl\6.1.5\spring-jcl-6.1.5.jar;C:\Users\developer\AppData\Local\Programs\IntelliJ IDEA Community Edition\lib\idea_rt.jar" com.javainspires.excelupload.ExcelUploadApplication Connected to the target VM, address: '127.0.0.1:50377', transport: 'socket' . ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v3.3.0-SNAPSHOT) 2024-03-27T19:59:14.347+05:30 INFO 1988 --- [excel-upload] [ main] c.j.excelupload.ExcelUploadApplication : Starting ExcelUploadApplication using Java 21.0.1 with PID 1988 (C:\Users\developer\Desktop\excel-upload\excel-upload\target\classes started by developer in C:\Users\developer\Desktop\excel-upload\excel-upload) 2024-03-27T19:59:14.356+05:30 INFO 1988 --- [excel-upload] [ main] c.j.excelupload.ExcelUploadApplication : No active profile set, falling back to 1 default profile: "default" 2024-03-27T19:59:16.564+05:30 INFO 1988 --- [excel-upload] [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port 8080 (http) 2024-03-27T19:59:16.592+05:30 INFO 1988 --- [excel-upload] [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat] 2024-03-27T19:59:16.593+05:30 INFO 1988 --- [excel-upload] [ main] o.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/10.1.19] 2024-03-27T19:59:16.727+05:30 INFO 1988 --- [excel-upload] [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext 2024-03-27T19:59:16.730+05:30 INFO 1988 --- [excel-upload] [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2216 ms 2024-03-27T19:59:17.605+05:30 INFO 1988 --- [excel-upload] [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port 8080 (http) with context path '/' 2024-03-27T19:59:17.624+05:30 INFO 1988 --- [excel-upload] [ main] c.j.excelupload.ExcelUploadApplication : Started ExcelUploadApplication in 4.115 seconds (process running for 5.055)
Testing:
API Curl:
curl --location 'http://localhost:8080/upload' \--form 'file=@"/C:/Users/developer/Desktop/employee-details.xlsx"'