Home  ›  Java Program to Read Data From Excel Sheet

Java Program to Read Data From Excel Sheet

Written By Saturday, March 5, 2022 Add Comment Edit

How to Upload Excel data into Database Using Spring Boot ? Many clients provide their data in the form of excel sheet and request programmer to salvage it in your database as it is . If yous ever take implemented 'How to Upload excel information into Database using Spring Kicking', it's skillful. If not, no worries!, nosotros volition learn the same in this commodity. In the finish, you will find it very piece of cake & fast fashion to become it done. In fact, once you go through the complete article you volition observe that you only demand to provide the location of your excel file in awarding.backdrop file . You will get the data uploaded into database past making small changes in your own entity classes but.

Nosotros will not promote whatsoever hard-coding in our implementation. Even if you lot are working kickoff fourth dimension in Spring Boot webapp, you volition easily implement the functionality by post-obit the steps mentioned here. Farther to escape difficult-coding nosotros will take some of the values from backdrop file then that we don't modify the java file in case nosotros have any change request in hereafter. Also, nosotros will use the near popular Apache POI: The Java API for Microsoft Documents in our implementation. Now let'south get into the topic "How to Upload Excel information into Database Using Jump Boot".

Table of Contents (Click on links below to navigate)

  • 1 What all functionality/features volition you get from this article?
  • 2 What will you learn later on implementing this application?
  • 3 What all implementations can y'all exercise yourself after going through this example?
  • 4 Software Used in this projection
    • 4.1 External Dependencies
  • v Prerequisites
  • six Coding Steps
    • 6.i — Create Projection in STS
      • 6.ane.i Where to identify JSP files ?
    • half-dozen.two — Writing Java classes, JSPs & Updating application.backdrop
      • six.2.1 InvoiceController.java
      • half dozen.2.2 Invoice.coffee
      • 6.2.3 InvoiceRepository.java
      • 6.2.4 IExcelDataService.java
      • 6.2.5 IFileUploaderService.java
      • half-dozen.2.6 ExcelDataServiceImpl.java
      • 6.2.vii FileUploaderServiceImpl.java
      • 6.2.eight awarding.properties
      • 6.ii.nine uploadPage.jsp
      • 6.2.10 success.jsp
  • 7 Running application
  • 8 Testing Results
  • 9 How to do our own implementation from this Example with minimal changes?
  • 10 Can we use this implementation in a real project ?
  • 11 What are the changes do nosotros need to do if we want to use this functionality in our real project ?
  • 12 Determination

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

1) How to relieve data into the database if input data is in the grade of excel sail.
ii) How to upload any file (apart from excel) using a web browser.
3) In a higher place ii 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 projection.
4) Additionally, code is successfully tested on JDK8, JDK9 and even JDK14 versions.
v) How to read data from excel sheet and insert into database table in spring kicking?

What volition you learn after implementing this application?

one) How to create a Jump Boot web application that incorporates manufacture level project design?
two) How to design a Coffee web awarding, including all layers using Controller, Service, Repository, UI etc. as in real time projection designs?
iii) As important, Where and how to use Annotations like @Value, @Autowired, @Controller, @Service, @Repository, @Entity, @Id, @GeneratedValue, @GetMapping, @PostMapping etc.
4) Besides, Working with Spring Kicking Data JPA repository interface.
5) Implementation of the most popular Apache POI (Java API for Microsoft Documents)
six) And then, How to work with application.properties file?

vii) How to upload information as a batch into database using Spring Boot?
8) Additionally, How to write modular & reusable code?
9) How to implement dynamic lawmaking with minimal changes, keeping future modify requests in listen?
10) How to write code without hard-coding the values using Spring Kick?
11) Last just not the least, y'all will learn "How to Upload Excel information into Database Using Spring Kicking".
12) Particularly, How to read data from excel sheet and insert into database tabular array in spring boot.

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

1) How to read data from excel canvas and insert into database tabular array in spring mvc?
2) How to upload excel file to database using leap boot?
3) bound kicking excel file upload case
4) How to read information 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 information from excel and store in datatable in java?
9) How to upload and read excel file in leap boot?
10) How to write coffee code to read excel file and insert into database?
11) How to import excel information into database using java?
12) How to upload Excel file into database using Apache POI and Jump Framework?

Software Used in this projection

♦ STS (Spring Tool Suite): Version-> 4.seven.one. RELEASE
⇒ Dependent Starters : Spring Spider web, Jump Data JPA, MySql Commuter
♦ MySQL Database : Version ->8.0.19 MySQL Community Server
♦ JDK8 or after versions (Extremely tested on JDK8, JDK9 and JDK14)

External Dependencies

We will add following apache 'poi-ooxml' dependency in pom.xml as information technology is an external jar to go the features of Microsoft Excel.

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

Prerequisites

You should take a database ready to store data from excel file. If not, create a MySQL database to salve the data. Still, if y'all just want to test the functionality y'all tin 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 get to Internal Link to create a sample project in jump boot. While creating a projection in STS, add together 3 starters 'MySql Driver', 'Bound Data JPA' and 'Leap Web'. Yous can also add 'Spring Boot DevTools' optionally.

Where to place JSP files ?

In order to suit JSP files,

1) create folder 'webapps' under src/main/
2) nether 'webapps' create folder 'Spider web-INF'
iii) Under 'Spider web-INF' create binder 'pages' to arrange your JSP files equally view part.
4) Now your Binder structure bureaucracy should expect like "src/main/webapps/WEB-INF/pages".

— Writing Java classes, JSPs & Updating application.properties

Package/Location Course/Interface name Create/Update Purpose
com.dev.springboot.controller InvoiceController.java create Controller course
com.dev.springboot.entity Invoice.java create Model/Entity grade
com.dev.springboot.repository InvoiceRepository.java create Repository Interface
com.dev.springboot.service IExcelDataService.java create Service Interface for DB operations
com.dev.springboot.service IFileUploaderService.coffee create Service Interface to upload whatever file
com.dev.springboot.service.impl ExcelDataServiceImpl.coffee create Service implementation grade for DB operations
com.dev.springboot.service.impl FileUploaderServiceImpl.java create Service implementation form to upload whatever file
src/main/resources application.backdrop update backdrop file to declare common properties in the projection
src/primary/webapp/Spider web-INF/pages uploadPage.jsp create UI page to upload a file
src/principal/webapp/Spider web-INF/pages success.jsp create UI page to success message when data is saved into database

Beneath are the codes for each file

InvoiceController.java

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.spider web.bind.annotation.GetMapping; import org.springframework.spider web.bind.annotation.PostMapping; import org.springframework.web.bind.note.RequestParam; import org.springframework.spider web.multipart.MultipartFile; import org.springframework.spider web.servlet.mvc.back up.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 course InvoiceController { 	 	@Autowired 	IFileUploaderService fileService; 	 	@Autowired 	IExcelDataService excelservice; 	 	@Autowired 	InvoiceRepository repo; 	 	@GetMapping("/")     public Cord index() {         return "uploadPage";     }      @PostMapping("/uploadFile")     public Cord 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 Machine-generated take hold of block 			e.printStackTrace(); 		}         return "redirect:/";     }          @GetMapping("/saveData")     public String saveExcelData(Model model) {     	     	Listing<Invoice> excelDataAsList = excelservice.getExcelDataAsList();     	int noOfRecords = excelservice.saveExcelData(excelDataAsList);     	model.addAttribute("noOfRecords",noOfRecords);     	return "success";     } }

Invoice.java

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; 	individual String name; 	private Double corporeality; 	individual String number; 	private Cord receivedDate; 	 	public Invoice() { 		 	} 	 	public Long getId() { 		return id; 	} 	public void setId(Long id) { 		this.id = id; 	} 	public Cord getName() { 		return name; 	} 	public void setName(String proper noun) { 		this.proper noun = proper name; 	} 	public Double getAmount() { 		return amount; 	} 	public void setAmount(Double corporeality) { 		this.amount = amount; 	} 	public String getNumber() { 		return number; 	} 	public void setNumber(String number) { 		this.number = number; 	} 	public String getReceivedDate() { 		return receivedDate; 	} 	public void setReceivedDate(Cord receivedDate) { 		this.receivedDate = receivedDate; 	} 	 	public Invoice(Long id, String proper noun, Double corporeality, String number, Cord receivedDate) { 		super(); 		this.id = id; 		this.name = name; 		this.amount = amount; 		this.number = number; 		this.receivedDate = receivedDate; 	} }

InvoiceRepository.java

Repository class equally 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

IExcelDataService.coffee 

            packet com.dev.springboot.service;  import coffee.util.List;  import com.dev.springboot.entity.Invoice;  public interface IExcelDataService {  	List<Invoice> getExcelDataAsList(); 	 	int saveExcelData(List<Invoice> invoices); }

IFileUploaderService.java

IFileUploaderService.java 

            package com.dev.springboot.service;  import org.springframework.web.multipart.MultipartFile;  public interface IFileUploaderService {  	public void uploadFile(MultipartFile file); }

ExcelDataServiceImpl.coffee

ExcelDataServiceImpl.java 

            package com.dev.springboot.service.impl;  import java.io.File; import java.io.</yoastmark>IOException; import java.util.ArrayList; import coffee.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.manufactory.annotation.Autowired; import org.springframework.beans.manufacturing plant.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.dwelling}}") 	public Cord EXCEL_FILE_PATH;  	@Autowired 	InvoiceRepository repo;  	Workbook workbook;  	public Listing<Invoice> getExcelDataAsList() {  		List<String> list = new ArrayList<Cord>();  		// Create a DataFormatter to format and get each prison cell's value every bit String 		DataFormatter dataFormatter = new DataFormatter();  		// Create the Workbook 		attempt { 			workbook = WorkbookFactory.create(new File(EXCEL_FILE_PATH)); 		} catch (EncryptedDocumentException | IOException due east) { 			e.printStackTrace(); 		}  		// Retrieving the number of sheets in the Workbook 		System.out.println("-------Workbook has '" + workbook.getNumberOfSheets() + "' Sheets-----");  		// Getting the Sheet at index zilch 		Sheet canvass = workbook.getSheetAt(0);  		// Getting number of columns in the Canvas 		int noOfColumns = sheet.getRow(0).getLastCellNum(); 		System.out.println("-------Canvass has '"+noOfColumns+"' columns------");  		// Using for-each loop to iterate over the rows and columns 		for (Row row : canvass) { 			for (Cell cell : row) { 				Cord cellValue = dataFormatter.formatCellValue(cell); 				listing.add together(cellValue); 			} 		}  		// filling excel information and creating list as List<Invoice> 		List<Invoice> invList = createList(list, noOfColumns);  		// Endmost the workbook 		try { 			workbook.shut(); 		} take hold of (IOException e) { 			// TODO Auto-generated catch cake 			e.printStackTrace(); 		}  		render invList; 	}  	private Listing<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 + ane))); 			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

FileUploaderServiceImpl.coffee 

            packet com.dev.springboot.service.impl;  import coffee.io.File; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.nio.file.StandardCopyOption; import coffee.util.List;  import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import org.springframework.util.StringUtils; import org.springframework.spider 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 goose egg; 	} 	 	@Value("${app.upload.dir:${user.abode}}")     public String uploadDir;      public void uploadFile(MultipartFile file) {          endeavour {             Path copyLocation = Paths                 .get(uploadDir + File.separator + StringUtils.cleanPath(file.getOriginalFilename()));             Files.copy(file.getInputStream(), copyLocation, StandardCopyOption.REPLACE_EXISTING);         } grab (Exception eastward) {             due east.printStackTrace();             throw new RuntimeException("Could non store file " + file.getOriginalFilename()                 + ". Please try again!");         }     } }

application.properties 

                      #DB Connexion Properties                    bound.datasource.driver-form-name=com.mysql.cj.jdbc.Driver                    jump.datasource.url=jdbc:mysql://localhost:3306/exceldata                    jump.datasource.username=root                    spring.datasource.password=devs                                # Data JPA Properties                    bound.jpa.database-platform=org.hide.dialect.MySQL8Dialect                    spring.jpa.show-sql=true                    spring.jpa.hibernate.ddl-automobile=update                                # MVC view properties                    spring.mvc.view.prefix=/Web-INF/pages/                    leap.mvc.view.suffix=.jsp                                # upload file                    app.upload.dir=D:/ExcelUploadRepo                                # Excel file equally input for storing it's data                    app.upload.file=D:/ExcelUploadRepo/InvoiceDetailsSheet.xlsx

uploadPage.jsp

uploadPage.jsp 

            <%@ page language="coffee" contentType="text/html; charset=ISO-8859-1"     pageEncoding="ISO-8859-1"%> <!DOCTYPE html> <html>    <head>    <meta charset="ISO-8859-1">       <championship>File Upload Page</title>    </caput>        <trunk>    	  <h2>File Upload & Data Salvage Page</h2>    	         <grade   action ="uploadFile" method = "POST" enctype = "multipart/grade-data">       <br /> <br />          Delight select a file to upload :           <input blazon = "file" proper noun = "file" value = "Scan File" /> <br /> <br />          Press here to upload the file :          <input type = "submit" value = "upload" /> <br /> <br />                     <h4 manner="color: green">${message}</h4> <br />                     Do you want to save excel information into database ? <a href="saveData"><b>Yes</b></a> &nbsp &nbsp <a href="/"><b>No</b></a>       </form>    </body> </html>

success.jsp

success.jsp 

            <%@ folio contentType = "text/html; charset = UTF-viii" %> <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 projection construction should await something similar below screenshot:

img_33_excel1

.

Running awarding

In guild to run the application, right click on Project, so select Run Equally >> Spring Boot App.

Testing Results

Finally, in order to test the results paste url in your browser : http://localhost:8080/
Appropriately, y'all should see the below page :

img_33_excel2

Furthermore, in order to upload an excel file, click on 'Choose File' which will browse to your file organisation, then select an excel file to be uploaded and click 'OK/Open'. Now click on upload in society to upload the file at the location you lot provided in awarding.properties accordingly. Note: Besides excel files, this functionality will likewise piece of work for any file having other extensions. Consequently, once the file is uploaded, yous will see the success message with file proper name you lot uploaded as below.

How to Upload Excel data into Database Using Spring Boot ?

In order to upload excel records into database click on 'Yes' link. Consequently, you will encounter the success page immediately with number of records uploaded into database. In contrast, if you click no, aught will happen, it will only redirect to the same page.

Uploading Excel data into Database Using Spring Boot

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

img_33_excel5
♥ Note: If you provide the input file proper noun every bit a value of 'app.upload.file' in application.backdrop and put the excel file in the aforementioned folder, You lot can directly salve the excel records into database only by clicking on the 'Aye' link. In this case, Even Yous don't accept to upload the file.

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

In fact, y'all need to change values of properties included in the application.properties file
1) Update the values of starting time iv backdrop according to your database
ii) Besides 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 :
iv) To store excel records into database yous need to create new Entity grade, new Repository class in place of 'Invoice.java' & 'InvoiceRepository.coffee' accordingly as per your requirements.
5) Consequently, you need to change all the occurrences of entity class name in 'IExcelDataService.coffee' and 'ExcelDataServiceImpl.coffee'. It's 'Invoice' in our example.
6) In addition, you demand to update entity class method names in createList() method of 'ExcelDataServiceImpl.java'.

Can nosotros use this implementation in a real project ?

Of form, you can utilize this implementation in a real project with modification described to a higher place as per your requirement.

What are the changes do nosotros need to practice if we want to utilise this functionality in our existent projection ?

You tin can go through the minimal modifications suggested in the higher up FAQ section 'How to do our ain implementation from this Example with minimal changes?' of this article accordingly .

Conclusion

After going through all the points mentioned in the article, Y'all should be able to piece of work on 'How to read data from excel sail and insert into database table in spring boot ?'. However, If yous follow all the steps as mentioned in the article, you will implement it successfully in ane become 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, nosotros can look from yous that you lot will employ this knowledge in your real time project when required to exercise and so. Furthermore, if y'all confront whatsoever issue on running this projection, don't hesitate to put your comments below.

♥ Besides, if you lot want to learn 'How to generate dynamic PDF report using Leap Boot', Kindly visit internal commodity. Additionally, if you want to learn more than on Spring Kicking trending topics kindly visit our blog.

halesavelys.blogspot.com

Source: https://javatechonline.com/how-to-upload-excel-data-into-database-using-spring-boot/

0 Response to "Java Program to Read Data From Excel Sheet"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel