Many clients provide their data in the form of excel sheet and request developer to save it in your database as it is. If you ever have implemented ‘How to Upload excel data into Database using Spring Boot’, it’s good. If not, no worries!, we will learn the same in this article. In the end, you will find it very easy & fast way to get it done. In fact, once you go through the complete article you will observe that you only need to provide the location of your excel file in application.properties file. You will get the data uploaded into database by making small changes in your own entity classes only.

We will not promote any hard-coding in our implementation. Even if you are working first time in Spring Boot webapp, you will easily implement the functionality by following the steps mentioned here. Further to escape hard-coding we will take some of the values from properties file so that we don’t modify the java file in case we have any change request in future. Also, we will use the most popular Apache POI: The Java API for Microsoft Documents in our implementation. Now let’s get into the topic “How to Upload Excel data into Database Using Spring Boot”.

What all functionality/features will you get from this article?

1) How to save data into the database if input data is in the form of excel sheet.
2) How to upload any file (apart from excel) using a web browser.
3) Above two functionalities are not interdependent. We can execute each functionality separately.
3) Apache POI Latest version (currently 4.1.2) has been used to implement the project.
4) Additionally, code is successfully tested on JDK8, JDK9 and even JDK14 versions.
5) How to read data from excel sheet and insert into database table in spring boot?

What will you learn after implementing this application?

1) How to create a Spring Boot web application that incorporates industry level project design?

2) How to design a Java web application, including all layers using Controller, Service, Repository, UI etc. as in real time project designs?

3) Equally important, Where and how to use Annotations like @Value, @Autowired, @Controller, @Service, @Repository, @Entity, @Id, @GeneratedValue, @GetMapping, @PostMapping etc.

4) Also, Working with Spring Boot Data JPA repository interface.

5) Implementation of the most popular Apache POI (Java API for Microsoft Documents)

6) Then, How to work with application.properties file?

7) How to upload data as a batch into database using Spring Boot?

8) Additionally, How to write modular & reusable code?

9) How to implement dynamic code with minimal changes, keeping future change requests in mind?

10) How to write code without hard-coding the values using Spring Boot?

11) Last but not the least, you will learn “How to Upload Excel data into Database Using Spring Boot”.

12) Particularly, How to read data from excel sheet and insert into database table in spring boot.

What all implementations can you do yourself after going through this example?

1) How to read data from excel sheet and insert into database table in spring mvc?

2) How to upload excel file to database using spring boot?

3) spring boot excel file upload example

4) How to read data from excel sheet and insert into database table in spring boot?

5) How to import data from excel to mysql using spring boot?

6) How to upload excel file into database using java?

7) How to read excel file in spring boot example?

8) How to read data from excel and store in the datatable in java?

9) How to upload and read excel file in spring boot?

10) How to write java code to read excel file and insert into database?

11) How to import excel data into database using java?

12) How to upload Excel file into database using Apache POI and Spring Framework?

Software Used in this project

♦ STS (Spring Tool Suite): Version-> 4.7.1. RELEASE
⇒ Dependent Starters : Spring Web, Spring Data JPA, MySql Driver
♦ MySQL Database : Version ->8.0.19 MySQL Community Server
♦ JDK8 or later versions (Extremely tested on JDK8, JDK9 and JDK14)

External Dependencies

We will add following apache ‘poi-ooxml’ dependency in pom.xml as it is an external jar to get the features of Microsoft Excel.

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

Prerequisites

You should have a database ready to store data from excel file. If not, create a MySQL database to save the data. Still, if you just want to test the functionality you can use our configurations as database name ‘exceldata’ and table name ‘invoice’ accordingly.

Coding Steps

— Create Project in STS

If you are new in Spring Boot go to Internal Link to create a sample project in spring boot. While creating a project in STS, add 3 starters ‘MySql Driver’, ‘Spring Data JPA‘ and ‘Spring Web’. You can also add ‘Spring Boot DevTools’ optionally.

Where to place JSP files ?

In order to accommodate JSP files,

1) create folder ‘webapps’ under src/main/
2) under ‘webapps’ create folder ‘WEB-INF’
3) Under ‘WEB-INF’ create folder ‘pages’ to accommodate your JSP files as view part.
4) Now your Folder structure hierarchy should look like “src/main/webapps/WEB-INF/pages”.

— Writing Java classes, JSPs & Updating application.properties

Below are the codes for each file

InvoiceController.java

package com.dev.springboot.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import com.dev.springboot.entity.Invoice;
import com.dev.springboot.repository.InvoiceRepository;
import com.dev.springboot.service.IExcelDataService;
import com.dev.springboot.service.IFileUploaderService;

@Controller
public class InvoiceController {
	
	@Autowired
	IFileUploaderService fileService;
	
	@Autowired
	IExcelDataService excelservice;
	
	@Autowired
	InvoiceRepository repo;
	
	@GetMapping("/")
    public String index() {
        return "uploadPage";
    }

    @PostMapping("/uploadFile")
    public String uploadFile(@RequestParam("file") MultipartFile file, RedirectAttributes redirectAttributes) {

        fileService.uploadFile(file);

        redirectAttributes.addFlashAttribute("message",
            "You have successfully uploaded '"+ file.getOriginalFilename()+"' !");
        try {
			Thread.sleep(3000);
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return "redirect:/";
    }
    
    @GetMapping("/saveData")
    public String saveExcelData(Model model) {
    	
    	List<Invoice> excelDataAsList = excelservice.getExcelDataAsList();
    	int noOfRecords = excelservice.saveExcelData(excelDataAsList);
    	model.addAttribute("noOfRecords",noOfRecords);
    	return "success";
    }
}

Invoice.java

package com.dev.springboot.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Invoice {

	@Id
	@GeneratedValue
	private Long id;
	private String name;
	private Double amount;
	private String number;
	private String receivedDate;
	
	public Invoice() {
		
	}
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Double getAmount() {
		return amount;
	}
	public void setAmount(Double amount) {
		this.amount = amount;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public String getReceivedDate() {
		return receivedDate;
	}
	public void setReceivedDate(String receivedDate) {
		this.receivedDate = receivedDate;
	}
	
	public Invoice(Long id, String name, Double amount, String number, String receivedDate) {
		super();
		this.id = id;
		this.name = name;
		this.amount = amount;
		this.number = number;
		this.receivedDate = receivedDate;
	}
}

InvoiceRepository.java

package com.dev.springboot.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.dev.springboot.entity.Invoice;

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {

}

IExcelDataService.java

package com.dev.springboot.service;

import java.util.List;

import com.dev.springboot.entity.Invoice;

public interface IExcelDataService {

	List<Invoice> getExcelDataAsList();
	
	int saveExcelData(List<Invoice> invoices);
}

IFileUploaderService.java

package com.dev.springboot.service;

import org.springframework.web.multipart.MultipartFile;

public interface IFileUploaderService {

	public void uploadFile(MultipartFile file);
}

ExcelDataServiceImpl.java

package com.dev.springboot.service.impl;

import java.io.File;
import java.io.</yoastmark>IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.dev.springboot.entity.Invoice;
import com.dev.springboot.repository.InvoiceRepository;
import com.dev.springboot.service.IExcelDataService;

@Service
public class ExcelDataServiceImpl implements IExcelDataService {

	@Value("${app.upload.file:${user.home}}")
	public String EXCEL_FILE_PATH;

	@Autowired
	InvoiceRepository repo;

	Workbook workbook;

	public List<Invoice> getExcelDataAsList() {

		List<String> list = new ArrayList<String>();

		// Create a DataFormatter to format and get each cell's value as String
		DataFormatter dataFormatter = new DataFormatter();

		// Create the Workbook
		try {
			workbook = WorkbookFactory.create(new File(EXCEL_FILE_PATH));
		} catch (EncryptedDocumentException | IOException e) {
			e.printStackTrace();
		}

		// Retrieving the number of sheets in the Workbook
		System.out.println("-------Workbook has '" + workbook.getNumberOfSheets() + "' Sheets-----");

		// Getting the Sheet at index zero
		Sheet sheet = workbook.getSheetAt(0);

		// Getting number of columns in the Sheet
		int noOfColumns = sheet.getRow(0).getLastCellNum();
		System.out.println("-------Sheet has '"+noOfColumns+"' columns------");

		// Using for-each loop to iterate over the rows and columns
		for (Row row : sheet) {
			for (Cell cell : row) {
				String cellValue = dataFormatter.formatCellValue(cell);
				list.add(cellValue);
			}
		}

		// filling excel data and creating list as List<Invoice>
		List<Invoice> invList = createList(list, noOfColumns);

		// Closing the workbook
		try {
			workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return invList;
	}

	private List<Invoice> createList(List<String> excelData, int noOfColumns) {

		ArrayList<Invoice> invList = new ArrayList<Invoice>();

		int i = noOfColumns;
		do {
			Invoice inv = new Invoice();

			inv.setName(excelData.get(i));
			inv.setAmount(Double.valueOf(excelData.get(i + 1)));
			inv.setNumber(excelData.get(i + 2));
			inv.setReceivedDate(excelData.get(i + 3));

			invList.add(inv);
			i = i + (noOfColumns);

		} while (i < excelData.size());
		return invList;
	}

	@Override
	public int saveExcelData(List<Invoice> invoices) {
		invoices = repo.saveAll(invoices);
		return invoices.size();
	}
}

FileUploaderServiceImpl.java

package com.dev.springboot.service.impl;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.List;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.dev.springboot.entity.Invoice;
import com.dev.springboot.service.IFileUploaderService;

@Service
public class FileUploaderServiceImpl implements IFileUploaderService {

	
	public List<Invoice> invoiceExcelReaderService() {
		return null;
	}
	
	@Value("${app.upload.dir:${user.home}}")
    public String uploadDir;

    public void uploadFile(MultipartFile file) {

        try {
            Path copyLocation = Paths
                .get(uploadDir + File.separator + StringUtils.cleanPath(file.getOriginalFilename()));
            Files.copy(file.getInputStream(), copyLocation, StandardCopyOption.REPLACE_EXISTING);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("Could not store file " + file.getOriginalFilename()
                + ". Please try again!");
        }
    }
}

application.properties

#DB Connection Properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/exceldata
spring.datasource.username=root
spring.datasource.password=devs

# Data JPA Properties
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

# MVC view properties
spring.mvc.view.prefix=/WEB-INF/pages/
spring.mvc.view.suffix=.jsp

# upload file 
app.upload.dir=D:/ExcelUploadRepo

# Excel file as input for storing it's data
app.upload.file=D:/ExcelUploadRepo/InvoiceDetailsSheet.xlsx

uploadPage.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
   <head>
   <meta charset="ISO-8859-1">
      <title>File Upload Page</title>
   </head>
   
   <body>
   	  <h2>File Upload & Data Save Page</h2>
   	  
      <form   action ="uploadFile" method = "POST" enctype = "multipart/form-data">
      <br /> <br />
         Please select a file to upload : 
         <input type = "file" name = "file" value = "Browse File" /> <br /> <br />
         Press here to upload the file :
         <input type = "submit" value = "upload" /> <br /> <br /> 
         
         <h4 style="color: green">${message}</h4> <br /> 
         
         Do you want to save excel data into database ? <a href="saveData"><b>Yes</b></a> &nbsp &nbsp <a href="/"><b>No</b></a>
      </form>
   </body>
</html>

success.jsp

<%@ page contentType = "text/html; charset = UTF-8" %>
<html>
   <head>
      <title>Data Save Example</title>
   </head>
   <body>
      
    <b> Total ${noOfRecords} records uploaded !</b> 
   </body>
</html>

Once all above files created in STS, your project structure should look something like below screenshot:

.

Running application

In order to run the application, right click on Project, then select Run As >> Spring Boot App.

Testing Results

Finally, in order to test the results paste url in your browser : http://localhost:8080/
Accordingly, you should see the below page :

Furthermore, in order to upload an excel file, click on ‘Choose File’ which will browse to your file system, then select an excel file to be uploaded and click ‘OK/Open’. Now click on upload in order to upload the file at the location you provided in application.properties accordingly. Note: Besides excel files, this functionality will also work for any file having other extensions. Consequently, once the file is uploaded, you will see the success message with file name you uploaded as below.

In order to upload excel records into database click on ‘Yes’ link. Consequently, you will see the success page immediately with number of records uploaded into database. In contrast, if you click no, nothing will happen, it will just redirect to the same page.

Furthermore, you can check the saved records into database as below:

♥ Note: If you provide the input file name as a value of ‘app.upload.file’ in application.properties and put the excel file in the same folder, You can directly save the excel records into database just by clicking on the ‘Yes’ link. In this case, Even You don’t have to upload the file.

How to do our own implementation from this Example with minimal changes?

In fact, you need to change values of properties included in the application.properties file
1) Update the values of first 4 properties according to your database
2) Also update the file storage directory path in the value of property ‘app.upload.dir’
3) Then update the upload file path with file name in the value of property ‘app.upload.file’

Moreover, Below are the changes in java files :
4) To store excel records into database you need to create new Entity class, new Repository class in place of ‘Invoice.java’ & ‘InvoiceRepository.java’ accordingly as per your requirements.
5) Consequently, you need to change all the occurrences of entity class name in ‘IExcelDataService.java’ and ‘ExcelDataServiceImpl.java’. It’s ‘Invoice’ in our case.
6) In addition, you need to update entity class method names in createList() method of ‘ExcelDataServiceImpl.java’.

Can we use this implementation in a real project ?

Of course, you can use this implementation in a real project with modification described above as per your requirement.

What are the changes do we need to do if we want to use this functionality in our real project ?

You can go through the minimal modifications suggested in the above FAQ section ‘How to do our own implementation from this Example with minimal changes?’ of this article accordingly.

Conclusion

After going through all the points mentioned in the article, You should be able to work on ‘How to read data from excel sheet and insert into database table in spring boot ?’. However, If you follow all the steps as mentioned in the article, you will implement it successfully in one go without facing any issue. Additionally, you will also be able to work on ‘How to upload any file (apart from excel) using web browser’ confidently. Now, we can expect from you that you will apply this knowledge in your real time project when required to do so. Furthermore, if you face any issue on running this project, don’t hesitate to put your comments below.

♥ Also, if you want to learn ‘How to generate dynamic PDF report using Spring Boot’, Kindly visit internal article. Additionally, if you want to learn more on Spring Boot trending topics kindly visit our blog.