SpringBoot Upload Parse Data from Excel Files to MongoDB

SpringBoot Upload Excel file to MongoDB

Tutorial: [SpringBoot RestAPIs + MongoDB + Excel] How to read data from Excel sheet and insert into MongoDB database in SpringBoot RestAPI Controller (Parse Excel function & Upload RestAPI) – (also Write Excel file Download RestAPI).

Creating SpringBoot RestAPIs to upload and download multiple Excel files to (Atlas ) MongoDB is one of the most common question in the development world. Going through the tutorial post”SpringBoot Upload Download Multiple Excel files to MySQL/PostgreSQL”, I explain details how to do it by step to step with coding and give you 100% running source code. What we will do?

– I draw an overview diagram architecture of SpringBoot RestAPI Upload Excel Files.
– I use Spring Web to development Spring RestApis.
– I use ApacheCommon or Open Excel libraries to parse and read Excel files.
– I use SpringData MongoDB to save data from Excel files to MongoDB.
– I implement a SpringBoot Global Exception Handler when uploading with a very big files and fail.
– I use Ajax and Bootstrap to implement a frontend client to upload/download Excel files.

Related posts:

Here is the to-do-list:

To do the tutorial “SpringBoot upload download Excel files to MongoDB database”, we need prepare Java >= 8, Eclipse with SpringToolSuite, MongoDB and MongoDBCompass a RestClient (I use Postman).

Now let’s go!

Overview – Springboot Upload Download Excel files to MongoDB

Here is an overview about workflow data of SpringBoot Upload/Download multiple Excel Files project:

Overview Fullstack Architecture Diagram of SpringBoot MongoDB upload parse Excel file
Overview Fullstack Architecture Diagram of SpringBoot MongoDB upload parse Excel file

– We implement an Ajax or use a Rest client to upload/download Excel files to/from SpringBoot application.
– For manipulating Excel files, we develop an Excel Utils class to write and read data from them.
– We implement a Excel File Service to do CRUD operations with MongoDB that supported by SpringData MongoDB Repository.

Here is an overview of “SpringBoot Upload Download multiple Excel files to MongoDB database” project that we will implement in the tutorial:

SpringBoot MongoDB upload Excel file - Project Structure
SpringBoot MongoDB upload Excel file – Project Structure
  • controller package implements Controller RestAPIs classes to upload/download Excel files from Ajax or a Rest-Client
  • repository package defines a CRUD MongoDB Repository: CustomerRepository
  • services package implements a class CsvFileServices with functions to store and retrieve Excel File’s data
  • utils package implements a class CsvUtils with common functions for reading or writing data to/from Excel files
  • model package defines a class Customer for mapping data between each Excel’s row with each database’s record
  • errorhandler package implements a class RestExceptionHandler to handle an exception: file size exceeded

Create Project – SpringBoot RestAPIs Upload Download Excel file

We use Eclipse to create a “SpringBoot upload download file” project with a set of below dependencies:

  • spring-boot-starter-data-mongodb dependency is used to interact with MongoDB database
  • spring-boot-starter-web dependency is used to implement Spring RestAPIs Controller

For writting and reading Excel files, we use dependency poi-ooxml of org.apache.poi.

In the tutorial, we mainly do a demo with MongoDB database, so here is the details of necessary dependencies in pom.xml file:

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

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

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

Implement Utils SpringBoot Write and Reade Excel files

We create class ExcelUtils.java to read/write Excel file with 2 methods:

  • parseExcelFile(InputStream is) is used to read Excel file
  • customersToExcel(Writer writer, List customers) is used to write object list to Excel writer

package com.loizenjava.springboot.mongodb.excel.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.loizenjava.springboot.mongodb.excel.document.Customer;

public class ExcelUtils {
	
	public static String EXCELTYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
	
	public static ByteArrayInputStream customersToExcel(List<Customer> customers) throws IOException {
		String[] COLUMNs = { "Id", "Name", "Address", "Age" };
		try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
			CreationHelper createHelper = workbook.getCreationHelper();
	
			Sheet sheet = workbook.createSheet("Customers");
	
			Font headerFont = workbook.createFont();
			headerFont.setBold(true);
			headerFont.setColor(IndexedColors.BLUE.getIndex());
	
			CellStyle headerCellStyle = workbook.createCellStyle();
			headerCellStyle.setFont(headerFont);
	
			// Row for Header
			Row headerRow = sheet.createRow(0);
	
			// Header
			for (int col = 0; col < COLUMNs.length; col++) {
				Cell cell = headerRow.createCell(col);
				cell.setCellValue(COLUMNs[col]);
				cell.setCellStyle(headerCellStyle);
			}
	
			// CellStyle for Age
			CellStyle ageCellStyle = workbook.createCellStyle();
			ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
	
			int rowIdx = 1;
			for (Customer customer : customers) {
				Row row = sheet.createRow(rowIdx++);
	
				row.createCell(0).setCellValue(customer.getId());
				row.createCell(1).setCellValue(customer.getName());
				row.createCell(2).setCellValue(customer.getAddress());
	
				Cell ageCell = row.createCell(3);
				ageCell.setCellValue(customer.getAge());
				ageCell.setCellStyle(ageCellStyle);
			}
	
			workbook.write(out);
			return new ByteArrayInputStream(out.toByteArray());
		}
	}

	public static List<Customer> parseExcelFile(InputStream is) {
		try {
			Workbook workbook = new XSSFWorkbook(is);
	
			Sheet sheet = workbook.getSheet("Customers");
			Iterator<Row> rows = sheet.iterator();
	
			List<Customer> lstCustomers = new ArrayList<Customer>();
	
			int rowNumber = 0;
			while (rows.hasNext()) {
				Row currentRow = rows.next();
	
				// skip header
				if (rowNumber == 0) {
					rowNumber++;
					continue;
				}
	
				Iterator<Cell> cellsInRow = currentRow.iterator();
	
				Customer cust = new Customer();
	
				int cellIndex = 0;
				while (cellsInRow.hasNext()) {
					Cell currentCell = cellsInRow.next();
	
					if (cellIndex == 0) { // ID
						cust.setId((long) currentCell.getNumericCellValue());
					} else if (cellIndex == 1) { // Name
						cust.setName(currentCell.getStringCellValue());
					} else if (cellIndex == 2) { // Address
						cust.setAddress(currentCell.getStringCellValue());
					} else if (cellIndex == 3) { // Age
						cust.setAge((int) currentCell.getNumericCellValue());
					}
	
					cellIndex++;
				}
	
				lstCustomers.add(cust);
			}
	
			// Close WorkBook
			workbook.close();
	
			return lstCustomers;
		} catch (IOException e) {
			throw new RuntimeException("FAIL! -> message = " + e.getMessage());
		}
	}
	
	public static boolean isExcelFile(MultipartFile file) {
		
		if(!EXCELTYPE.equals(file.getContentType())) {
			return false;
		}
		
		return true;
	}
}

Define Spring Data MongoDB repository to do CRUD operation with MongoDB database

In the tutorial “SpringBoot RestAPIs Upload Download Excel file”, to do CRUD operations with MongoDB database, we define a simple CustomerRepository interface that extends the org.springframework.data.repository.CrudRepository:

package com.loizenjava.springboot.mongodb.excel.repository;

import org.springframework.data.repository.CrudRepository;

import com.loizenjava.springboot.mongodb.excel.document.Customer;


/**
 * 
 * Copyright by https://loizenjava.com
 * @author loizenjava.com
 *
 */
@Repository
public interface CustomerRepository extends CrudRepository<Customer, Long>{
}

See related article: How to integrate SpringBoot 2.x with PostgreSQL database using Spring JPA

Implement a Excel File Service

We create a Excel File Service ExcelFileServices to do 2 tasks:

  • Store data to database with supported API: store(MultipartFile file)
  • Load data from database to a Excel file with supported API ByteArrayInputStream loadFile()

ExcelFileServices class uses the Spring Data MongoDB repository CustomerRepository to interact with database and use the utility functions of ExcelUtils class to manipulate data with Excel files.

Here is coding details:

package com.loizenjava.springboot.mongodb.excel.services;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.loizenjava.springboot.mongodb.excel.document.Customer;
import com.loizenjava.springboot.mongodb.excel.repository.CustomerRepository;
import com.loizenjava.springboot.mongodb.excel.utils.ExcelUtils;

@Service
public class ExcelFileServices {

	@Autowired
	CustomerRepository customerRepository;

	// Store File Data to Database
	public void store(MultipartFile file) {
		try {
			List<Customer> lstCustomers = ExcelUtils.parseExcelFile(file.getInputStream());
			// Save Customers to DataBase
			customerRepository.saveAll(lstCustomers);
		} catch (IOException e) {
			throw new RuntimeException("FAIL! -> message = " + e.getMessage());
		}
	}

	// Load Data to Excel File
	public ByteArrayInputStream loadFile() {
		List<Customer> customers = (List<Customer>) customerRepository.findAll();

		try {
			ByteArrayInputStream in = ExcelUtils.customersToExcel(customers);
			return in;
		} catch (IOException e) {}

		return null;
	}
}

* Note: org.springframework.stereotype.Service indicates that an annotated class is a Service

Implement SpringBoot Upload Excel files RestApi

We create a UploadExcelFileRestApi.java controller class to do upload a single and multiple excel files with 2 api methods:

– Upload Single Excel file: uploadSingleExcelFile(@RequestParam("excelfile") MultipartFile uploadfile)
– Upload Multiple Excel file: uploadFileMulti(@RequestParam("excelfiles") MultipartFile[] uploadfiles)

Upload single Excel file

We create a posting API /api/upload/csv/single for uploading a single Excel file, and here is a signature detail:

@PostMapping("/single")
public Response uploadSingleExcelFile(@RequestParam("excelfile") MultipartFile csvfile) {
...
}

The function uploadSingleExcelFile does the 2 main taks:

  • Validated task: check name and type of requested file before processing
  • Store Excel file data to database
@PostMapping("/api/upload/excel/single")
public Response uploadSingleExcelFile(@RequestParam("excelfile") MultipartFile uploadfile) {

	Response response = new Response();
	
    if (StringUtils.isEmpty(uploadfile.getOriginalFilename())) {
    	response.addMessage(new Message(uploadfile.getOriginalFilename(),
				"No selected file to upload! Please do the checking", "fail"));

		return response;
    }
    
	if(!ExcelUtils.isExcelFile(uploadfile)) { 
	    response.addMessage(new Message(uploadfile.getOriginalFilename(), "Error: this is not a Excel file!", "fail")); 
        return response; 
	}
    
	try {
		// save file data to MongoDB
		fileServices.store(uploadfile);
		response.addMessage(new Message(uploadfile.getOriginalFilename(), "Upload File Successfully!", "ok"));
	} catch (Exception e) {
		response.addMessage(new Message(uploadfile.getOriginalFilename(), e.getMessage(), "fail"));
	}

	return response;
}

Upload multiple Excel files

We create a posting API /api/upload/excel/multiple for uploading multiple Excel files, and here is a signature detail:

@PostMapping("/api/upload/excel/multiple")
public Response uploadFileMulti(@RequestParam("excelfiles") MultipartFile[] excelfiles) {
...
}

The function uploadMultipleFiles does the 2 main taks:

  • Validated task: check names and type of all requested files before processing them
  • Store Excel files’ data to database
@PostMapping("/api/upload/excel/multiple")
public Response uploadFileMulti(
        @RequestParam("excelfiles") MultipartFile[] uploadfiles) {

	Response response = new Response();
	
    MultipartFile[] readyUploadedFiles = Arrays.stream(uploadfiles)
			.filter(x -> !StringUtils.isEmpty(x.getOriginalFilename())).toArray(MultipartFile[]::new);
    
    
    /*
	 * Checking whether having at least one file had been selected for uploading
	 */
	if (readyUploadedFiles.length == 0) {
		response.addMessage(new Message("", "No selected file to upload!", "fail"));
		return response;
	}
	
	/*
	 * Checking uploaded files are Excel files or NOT
	 */
    String notExcelFiles = Arrays.stream(uploadfiles).filter(x -> !ExcelUtils.isExcelFile(x))
    							.map(x -> x.getOriginalFilename())
    							.collect(Collectors.joining(" , "));

	if (!StringUtils.isEmpty(notExcelFiles)) {
		response.addMessage(new Message(notExcelFiles, "Not Excel Files", "fail"));
		return response;
	}

	for(MultipartFile file: uploadfiles) {
        try {
    		fileServices.store(file);
    		response.addMessage(new Message(file.getOriginalFilename(), "Upload Successfully!", "ok"));
        } catch (Exception e) {
        	response.addMessage(new Message(file.getOriginalFilename(), e.getMessage(), "fail"));
        }
	}
	
	return response;
}

The UploadExcelFileRestApi uses the ExcelFileServices service to store Excel files’ data to MongoDB database.

@RestController is a convenience annotation that is itself annotated with @Controller and @ResponseBody

The returned class Response contains a set of necessary information to return back to requested client.

Here is coding details of Response class:

package com.loizenjava.springboot.mongodb.excel.message;

import java.util.ArrayList;
import java.util.List;

/**
 * Copyright by https://loizenjava.com
 * @author loizenjava.com
 *
 */

public class Response {
	private List<Message> messages = null;
	private List<FileInfo> fileInfos = null;
	private Error error = null;
	private String errStatus = "";
	
	public Response() {
		this.messages = new ArrayList<Message>();
	}
	
	public Response(List<FileInfo> fileInfos) {
		this.fileInfos = fileInfos; 
	}
	
	public Response(String errStatus, Error err) {
		this.errStatus = errStatus;
		this.error = err;
	}

	public void addFileInfo(FileInfo file) {
		this.fileInfos.add(file);
	}

Implement SpringBoot Download Excel file RestApi

We implement a rest controller DownloadExcelRestAPI having a http GET downloadFile() API method with URL /api/download/excel/ to load all entities from database and serve them as a Excel file back to requested client.

Here is coding:

package com.loizenjava.springboot.mongodb.excel.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.loizenjava.springboot.mongodb.excel.services.ExcelFileServices;

@RestController
public class DownloadFileRestAPIs {

	@Autowired
	ExcelFileServices fileServices;

	/*
	 * Download Files
	 */
	@GetMapping("/api/download/excel/")
	public ResponseEntity<InputStreamResource> downloadFile() {

		HttpHeaders headers = new HttpHeaders();
		headers.add("Content-Disposition", "attachment; filename=customers.xlsx");
		
		return ResponseEntity.ok().headers(headers)
									.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
									.body(new InputStreamResource(fileServices.loadFile()));
	}
}

With above coding, the API returns a file that has name is customers.xlsx, type is application/vnd.ms-excel (Excel file type) and status code is 200 (.ok()) for successfully processing.

Configure MongoDB database connection

In the SpringBoot project, we need interact with database to do CRUD operation to save/retrieve data from Excel file to database, so now we need to add database configuration in application.properties file.

– For MongoDB connection, you follow below configuration formatting:


spring.data.mongodb.uri=mongodb+srv://loizenjava:12345@cluster0.uhqpv.mongodb.net/loizenjavadb?retryWrites=true&w=majority

Test SpringBoot Upload Download RestAPIs with Rest-Client

We use PostMan Client to test the Upload/Download RestAPIs.
For testing, we prepare 3 Excel files as below content:

file excel 1
file excel 1
file excel 2
file excel 2
file excel 3
file excel 3

SpringBoot Upload a Single Excel File

We create a POST request to URL http://localhost:8080/api/upload/excel/single that has a body with multipart/form-data content type and includes 1 Excel file as below:

SpringBoot RestAPI Upload Single Excel file to MongoDB
SpringBoot RestAPI Upload Single Excel file to MongoDB

Checking database, we get a customer table with 5 records as below:

Check MongoDB documents after uploading the single excel files
Check MongoDB documents after uploading the single excel files

SpringBoot Upload Multiple Excel Files

We create a POST request to URL http://localhost:8080/api/upload/excel/multiple that has a body with multipart/form-data content type and includes 2 Excel files as below:

SpringBoot RestAPI Upload Multiple Excel files to MongoDB
SpringBoot RestAPI Upload Multiple Excel files to MongoDB

Checking MongoDB database, now customer table has 15 records as below:

Check MongoDB documents after uploading multiple excel files
Check MongoDB documents after uploading multiple excel files

SpringBoot Download Data as a Single Excel file

Do a GET request to retrieve a Excel file from URL http://localhost:8080/api/download/excel/:

SpringBoot MongoDB download data as Excel files
SpringBoot MongoDB download data as Excel files

SpringBoot test a big Excel File Uploading

We do a big Excel file uploading to SpringBoot RestApi:

Test Upload a Big File to SpringBoot Server and get Exception Error
Test Upload a Big File to SpringBoot Server and get Exception Error

We get a 500 error response with an exception message throwed from SpringBoot server:

org.apache.tomcat.util.http.fileupload.impl.SizeLimitExceededException: the request was rejected because its size (45422988) exceeds the configured maximum (10485760)
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.init(FileItemIteratorImpl.java:150) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.getMultiPartStream(FileItemIteratorImpl.java:194) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.findNextItem(FileItemIteratorImpl.java:213) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.(FileItemIteratorImpl.java:131) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.FileUploadBase.getItemIterator(FileUploadBase.java:255) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.FileUploadBase.parseRequest(FileUploadBase.java:279) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.Request.parseParts(Request.java:2870) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.Request.getParts(Request.java:2772) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.RequestFacade.getParts(RequestFacade.java:1098) ~[tomcat-embed-core-9.0.33.jar:9.0.33]

This is a Maximum upload size exceeded exception. How to catch and handle the exception? We go to the next session for a right solution.

Implement SpringBoot Rest Exception Handler

For handling the Maximum upload size exceeded exception, we create a RestExceptionHandler class that extends the ResponseEntityExceptionHandler class.

package com.loizenjava.springboot.mongodb.excel.errorhandler;

import javax.servlet.http.HttpServletRequest;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartException;
import org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler;

/**
 * 
 * Copyright by https://loizenjava.com
 * @author loizenjava.com
 *
 */

@ControllerAdvice
public class RestExceptionHandler extends ResponseEntityExceptionHandler {

    // Catch file size exceeded exception!
    @SuppressWarnings({ "rawtypes", "unchecked" })
	@ExceptionHandler(MultipartException.class)
    @ResponseBody
    ResponseEntity<Response> handleControllerException(HttpServletRequest request, Throwable ex) {
        HttpStatus status = getStatus(request);
        Error err = new Error("0x123", ex.getMessage());
        Response res = new Response("error", err);
        
        return new ResponseEntity(res, status);
    }

    private HttpStatus getStatus(HttpServletRequest request) {
        Integer statusCode = (Integer) request.getAttribute("javax.servlet.error.status_code");
        if (statusCode == null) {
            return HttpStatus.INTERNAL_SERVER_ERROR;
        }
        return HttpStatus.valueOf(statusCode);
    }
}

org.springframework.web.bind.annotation.ControllerAdvice is a specialization of @Component for classes that declare @ExceptionHandler, @InitBinder, or @ModelAttribute methods to be shared across multiple @Controller classes.

org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler is a convenient base class for @ControllerAdvice classes that wish to provide centralized exception handling across all @RequestMapping methods through @ExceptionHandler methods. This base class provides an @ExceptionHandler method for handling internal Spring MVC exceptions. This method returns a ResponseEntity for writing to the response with a message converter. (refer Spring docs)

Tuning File Upload Limits

We can tune Spring Boot’s auto-configured MultipartConfigElement with property settings. We add the following properties to your existing properties settings (in application.properties file):

spring.servlet.multipart.max-file-size=4096KB
spring.servlet.multipart.max-request-size=4096KB

The multipart settings are constrained as follows:

  • spring.http.multipart.max-file-size is set to 4096KB, meaning total file size cannot exceed 4096KB.
  • spring.http.multipart.max-request-size is set to 4096KB, meaning total request size for a multipart/form-data cannot exceed 4096KB.
Returned Response after Handling MultipartFile Exception
Returned Response after Handling MultipartFile Exception

Implement Ajax Client

Implement Upload Download Html Form

I use Bootstrap framework to create Html view for uploadload and download Excel files as below structure:

Bootstrap html structure
Bootstrap html structure

The view clearly has 3 difference row:

  • the first row includes a html form for uploading a single Excel file
  • the second row includes a html form for uploading multiple Excel files
  • the final row includes a link used to download a Excel files

Here is coding:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Download Excel Files Example - copyright by https://loizenjava.com</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
  <script src="/js/excelfiles.js"></script>
</head>
<body>
	<div class="container"">
		<p style="margin:5xp;padding:10px;">@ Copyright by <a href="https://loizenjava.com">https://loizenjava.com</a> - youtube: <a href="https://www.youtube.com/channel/UChkCKglndLes1hkKBDmwPWA">loizenjava</a></p>
		<div class="row">
			<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
				<h3>Upload Single Excel File</h3>
				<form id="uploadSingleFileForm">
					<div class="form-group">
						<label class="control-label" for="csvfile">Choose a Excel File:</label>
						<input type="file" class="form-control" 
								placeholder="Choose a upload file" id="excelfile" name="excelfile" required></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnUploadSingleFileSubmit">Submit</button>
				</form>
				<div id="response" style="display:none">
				</div>
			</div>
		</div>
		<div class="row">
			<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
				<h3>Upload Multiple Excel Files</h3>
				<form id="uploadMultipleFilesForm">
					<div class="form-group">
						<label class="control-label" for="excelfiles">Choose Excel Files:</label>
						<input type="file" class="form-control" 
								placeholder="Choose upload files" id="excelfiles" name="excelfiles" multiple required></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnUploadMultipleFilesSubmit">Submit</button>
				</form>
				<div id="responses" style="display:none">
				</div>
			</div>
			
		</div>
		<br>
		<div class="row">
			<div class="col-sm-7">
				<a href="/api/download/excel/" class="btn btn-primary" role="button" id="downloadexcelfile">Download Excel File</a>
			</div>
		</div>
	</div>
</body>
</html>

We save the .html file in static folder under src/main/resources folder of SpringBoot project as index.html name.

Implement Ajax Upload Excel files

We continue to implement an Ajax script to upload the data-form:

/**
 * Copyright by https://loizenjava.com
 * Author: loizenjava.com 
 */

$(document).ready(function() {
	
	/**
	 * Upload single file to SpringBoot 
	 * at RestAPI: /api/upload/csv/single
	 */
	$("#uploadSingleFileForm").submit(function(evt) {
		evt.preventDefault();
		
		var formData = new FormData($(this)[0]);
		
		$.ajax({
			url : '/api/upload/excel/single',
			type : 'POST',
			data : formData,
			async : false,
			cache : false,
			contentType : false,
			enctype : 'multipart/form-data',
			processData : false,
			success : function(response) {
				$("#response").empty();
				if(response.errStatus !== "error"){
					var displayInfo = response.messages[0].filename + " : " + response.messages[0].message + "<br>"; 
					
					$("#response").append(displayInfo);
					// add some css
					$("#response").css("display", "block");
					$("#response").css("background-color", "#e6e6ff");
					$("#response").css("border", "solid 1px black");
					$("#response").css("border-radius", "3px");
					$("#response").css("margin", "10px");
					$("#response").css("padding", "10px");
				}else{
					$("#response").css("display", "none");
					var error = response.error.errDesc;
					alert(error);
				}
			},
			error: function(e){
				alert("Fail! " + e);
			}
		});
		
		return false;
	});
	

	$("#uploadMultipleFilesForm").submit(function(evt) {
		evt.preventDefault();
		
		var formData = new FormData($(this)[0]);
		
		$.ajax({
			url : '/api/upload/excel/multiple',
			type : 'POST',
			data : formData,
			async : false,
			cache : false,
			contentType : false,
			enctype : 'multipart/form-data',
			processData : false,
			success : function(response) {
				
				$("#responses").empty();
				if(response.errStatus !== "error"){
					
					var displayInfo = "<ul>";
					
					for(var i=0; i<response.messages.length; i++){
						
						displayInfo += "<li>" + response.messages[i].filename 
											+ "&nbsp; : &nbsp;" + response.messages[i].message
											+ "</li>";
					}
					$("#responses").append(displayInfo + "</ul>");
					$("#responses").css("display", "block");
					
					// add some css
					$("#responses").css("background-color", "#e6e6ff");
					$("#responses").css("border", "solid 1px black");
					$("#responses").css("border-radius", "3px");
					$("#responses").css("margin", "10px");
					$("#responses").css("padding", "10px");
				}else{
					$("#responses").css("display", "none");
					var error = response.error.errDesc;
					alert(error);
				}
			},
			error: function(e){
				alert("Fail! " + e);
			}
		});
		
		return false;
	});
})

The above script has 2 main functions:

  • $("#uploadSingleFileForm").submit(function(evt) is used to upload a single Excel file
  • $("#uploadMultipleFilesForm").submit(function(evt) is used to upload multiple Excel files

Integrative Testing SpringBoot Upload Download Excel files with Ajax Client

Testcase 1 – SpringBoot RestAPI Upload Parse Single Excel file to MongoDB

Ajax Upload Single Excel file to MongoDB through SpringBoot RestAPI
Ajax Upload Single Excel file to MongoDB through SpringBoot RestAPI

Network log:

Ajax Upload Single Excel file to MongoDB through SpringBoot RestAPI - UI successfully
Ajax Upload Single Excel file to MongoDB through SpringBoot RestAPI – UI successfully

Testcase 2 – SpringBoot RestAPI Upload Parse Multiple Excel files to MongoDB

Ajax Upload multiple excel files to MongoDB through SpringBoot RestAPIs
Ajax Upload multiple excel files to MongoDB through SpringBoot RestAPIs

Network log:

UI Bootstrap upload parse Multiple Excel files to MongoDB through SpringBoot RestAPIs successfully
UI Bootstrap upload parse Multiple Excel files to MongoDB through SpringBoot RestAPIs successfully

Testcase 3 – SpringBoot RestAPI Download Write Excel file from MongoDB

SpringBoot Download Excel Files from MongoDB - Network Log
SpringBoot Download Excel Files from MongoDB – Network Log

Network Logs:

Excel files extract all data from MongoDB via SpringBoot Download RestAPI
Excel files extract all data from MongoDB via SpringBoot Download RestAPI

Sourcecode

All features of sourcecode for the tutorial SpringBoot RestAPIs Upload Download Multiple Excel files to MongoDB with Ajax + RestClient:

  • Backend
    • Upload Single Excel File RestAPI
    • Upload Multiple Excel Files RestAPI
    • Download all data as a single Excel file
  • Frontend
    • Html Upload Single File Form
    • Html Upload Multiple Files Form
    • JQuery Ajax to download all data as a single Excel file

SpringBootUploadExcelMongoDB

– GitHub Sourcecode for “SpringBoot Upload Download Excel files to MongoDB”:

SpringBoot Upload Parse Excel File to MongoDB – Github sourcecode

Further Reading

  1. Spring Data JPA
  2. Spring MVC Tutorial
Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *