Spring Boot : API to upload and process Excel file.

0

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 Boot
2, Java 21
3, 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"'


Tags:

Post a Comment

Previous Post Next Post