upload excel files to postgresql database via php

How to Upload Excel data into Database Using Spring Boot ? Many clients provide their data in the grade of excel sheet and asking developer to salvage it in your database every bit it is . If you e'er have implemented 'How to Upload excel information into Database using Spring Boot', it's good. If not, no worries!, we will larn the same in this article. In the terminate, you will find information technology very like shooting fish in a barrel & fast manner to get information technology done. In fact, one time you go through the complete article you volition discover that yous only need to provide the location of your excel file in application.properties file . You volition 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 commencement time in Spring Boot webapp, you volition easily implement the functionality by following the steps mentioned here. Further to escape difficult-coding we volition take some of the values from properties file so that we don't modify the java file in case we have whatever change request in future. Also, we will use the nearly popular Apache POI: The Java API for Microsoft Documents in our implementation. At present let'southward get into the topic "How to Upload Excel data into Database Using Leap Boot".

Table of Contents (Click on links below to navigate)

  • ane What all functionality/features volition you go from this article?
  • two What will you learn after implementing this application?
  • 3 What all implementations tin you lot practise yourself afterward going through this example?
  • 4 Software Used in this projection
    • 4.1 External Dependencies
  • v Prerequisites
  • 6 Coding Steps
    • 6.1 — Create Projection in STS
      • 6.1.1 Where to place JSP files ?
    • six.2 — Writing Java classes, JSPs & Updating application.properties
      • half dozen.two.1 InvoiceController.java
      • vi.two.2 Invoice.java
      • vi.2.3 InvoiceRepository.java
      • 6.two.4 IExcelDataService.java
      • 6.2.5 IFileUploaderService.coffee
      • 6.2.half dozen ExcelDataServiceImpl.java
      • 6.two.seven FileUploaderServiceImpl.java
      • half dozen.2.8 application.backdrop
      • 6.2.nine uploadPage.jsp
      • 6.two.ten success.jsp
  • 7 Running application
  • 8 Testing Results
  • 9 How to do our own implementation from this Example with minimal changes?
  • ten Tin can we use this implementation in a existent project ?
  • 11 What are the changes practise we need to practise if we want to use this functionality in our real project ?
  • 12 Conclusion

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

1) How to relieve 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.
iii) Above ii functionalities are not interdependent. Nosotros can execute each functionality separately.
3) Apache POI Latest version (currently iv.1.ii) has been used to implement the project.
4) Additionally, lawmaking is successfully tested on JDK8, JDK9 and even JDK14 versions.
five) How to read data from excel sheet and insert into database table in spring kicking?

What will you lot learn after implementing this application?

i) How to create a Spring Boot web awarding that incorporates industry level project design?
2) How to design a Java spider web application, including all layers using Controller, Service, Repository, UI etc. every bit in real fourth dimension projection designs?
3) Every bit important, Where and how to use Annotations like @Value, @Autowired, @Controller, @Service, @Repository, @Entity, @Id, @GeneratedValue, @GetMapping, @PostMapping etc.
4) Likewise, 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 equally a batch into database using Spring Boot?
8) Additionally, How to write modular & reusable lawmaking?
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 Kick?
11) Last just not the to the lowest degree, you lot will learn "How to Upload Excel data into Database Using Leap Boot".
12) Particularly, How to read data from excel canvas and insert into database table in jump boot.

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

i) How to read data from excel canvass and insert into database table in spring mvc?
two) How to upload excel file to database using spring boot?
iii) spring kick excel file upload case
iv) How to read data from excel sail and insert into database table in spring boot?
v) How to import data from excel to mysql using spring kicking?
vi) How to upload excel file into database using java?

7) How to read excel file in leap boot example?
8) How to read data from excel and store in 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 (Bound Tool Suite): Version-> iv.vii.1. RELEASE
⇒ Dependent Starters : Spring Web, Bound Data JPA, MySql Driver
♦ MySQL Database : Version ->8.0.nineteen MySQL Customs Server
♦ JDK8 or afterward versions (Extremely tested on JDK8, JDK9 and JDK14)

External Dependencies

Nosotros will add following apache 'poi-ooxml' dependency in pom.xml every bit 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 set to store information from excel file. If not, create a MySQL database to salve the data. Still, if you just desire to test the functionality you can use our configurations equally database name 'exceldata' and table name 'invoice' accordingly.

Coding Steps

— Create Project in STS

If you are new in Spring Kicking go to Internal Link to create a sample projection in spring boot. While creating a projection in STS, add three starters 'MySql Driver', 'Leap Data JPA' and 'Leap Spider web'. Yous tin can also add 'Spring Boot DevTools' optionally.

Where to place JSP files ?

In lodge to arrange JSP files,

ane) create folder 'webapps' under src/chief/
2) under 'webapps' create binder 'Web-INF'
iii) Nether 'WEB-INF' create folder 'pages' to accommodate your JSP files as view function.
iv) Now your Folder structure hierarchy should expect like "src/chief/webapps/Web-INF/pages".

— Writing Coffee classes, JSPs & Updating application.properties

Package/Location Course/Interface name Create/Update Purpose
com.dev.springboot.controller InvoiceController.java create Controller class
com.dev.springboot.entity Invoice.coffee create Model/Entity class
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.java create Service implementation class for DB operations
com.dev.springboot.service.impl FileUploaderServiceImpl.java create Service implementation class to upload any file
src/principal/resources awarding.properties update properties file to declare common backdrop in the project
src/main/webapp/Spider web-INF/pages uploadPage.jsp create UI folio to upload a file
src/chief/webapp/WEB-INF/pages success.jsp create UI page to success message when information is saved into database

Below are the codes for each file

InvoiceController.java

InvoiceController.java

            packet com.dev.springboot.controller;  import java.util.Listing;  import org.springframework.beans.factory.note.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.spider web.demark.notation.GetMapping; import org.springframework.web.demark.notation.PostMapping; import org.springframework.spider web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import org.springframework.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 class InvoiceController { 	 	@Autowired 	IFileUploaderService fileService; 	 	@Autowired 	IExcelDataService excelservice; 	 	@Autowired 	InvoiceRepository repo; 	 	@GetMapping("/")     public Cord index() {         render "uploadPage";     }      @PostMapping("/uploadFile")     public String uploadFile(@RequestParam("file") MultipartFile file, RedirectAttributes redirectAttributes) {          fileService.uploadFile(file);          redirectAttributes.addFlashAttribute("message",             "You lot have successfully uploaded '"+ file.getOriginalFilename()+"' !");         endeavor { 			Thread.slumber(3000); 		} catch (InterruptedException e) { 			// TODO Auto-generated catch cake 			eastward.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

Invoice.java

            package com.dev.springboot.entity;  import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id;  @Entity public course Invoice {  	@Id 	@GeneratedValue 	private Long id; 	private String proper 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 proper noun) { 		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 Cord getReceivedDate() { 		return receivedDate; 	} 	public void setReceivedDate(String receivedDate) { 		this.receivedDate = receivedDate; 	} 	 	public Invoice(Long id, Cord proper noun, Double corporeality, String number, String receivedDate) { 		super(); 		this.id = id; 		this.proper name = name; 		this.amount = amount; 		this.number = number; 		this.receivedDate = receivedDate; 	} }          

InvoiceRepository.java

Repository grade as InvoiceRepository.coffee

            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.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.coffee

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

            packet 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.Canvas; 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.mill.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 Cord 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 		Organization.out.println("-------Workbook has '" + workbook.getNumberOfSheets() + "' Sheets-----");  		// Getting the Canvass at index aught 		Canvas sheet = workbook.getSheetAt(0);  		// Getting number of columns in the Sail 		int noOfColumns = canvass.getRow(0).getLastCellNum(); 		Organization.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 every bit List<Invoice> 		List<Invoice> invList = createList(list, noOfColumns);  		// Closing the workbook 		endeavour { 			workbook.close(); 		} grab (IOException e) { 			// TODO Machine-generated catch block 			due east.printStackTrace(); 		}  		return invList; 	}  	individual 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 + one))); 			inv.setNumber(excelData.get(i + 2)); 			inv.setReceivedDate(excelData.go(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.java

            bundle com.dev.springboot.service.impl;  import java.io.File; import java.nio.file.Files; import java.nio.file.Path; import coffee.nio.file.Paths; import coffee.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);         } grab (Exception e) {             e.printStackTrace();             throw new RuntimeException("Could not store file " + file.getOriginalFilename()                 + ". Please effort again!");         }     } }          

application.backdrop

                      #DB Connectedness Properties                    spring.datasource.driver-course-name=com.mysql.cj.jdbc.Driver                    leap.datasource.url=jdbc:mysql://localhost:3306/exceldata                    leap.datasource.username=root                    bound.datasource.password=devs                                # Data JPA Backdrop                    spring.jpa.database-platform=org.hide.dialect.MySQL8Dialect                    leap.jpa.bear witness-sql=truthful                    leap.jpa.hibernate.ddl-auto=update                                # MVC view properties                    bound.mvc.view.prefix=/Web-INF/pages/                    spring.mvc.view.suffix=.jsp                                # upload file                    app.upload.dir=D:/ExcelUploadRepo                                # Excel file every bit input for storing it'due south data                    app.upload.file=D:/ExcelUploadRepo/InvoiceDetailsSheet.xlsx                  

uploadPage.jsp

uploadPage.jsp

            <%@ page linguistic communication="java" contentType="text/html; charset=ISO-8859-one"     pageEncoding="ISO-8859-1"%> <!DOCTYPE html> <html>    <head>    <meta charset="ISO-8859-1">       <championship>File Upload Page</title>    </head>        <body>    	  <h2>File Upload & Data Salvage Page</h2>    	         <class   action ="uploadFile" method = "POST" enctype = "multipart/form-data">       <br /> <br />          Please select a file to upload :           <input type = "file" proper noun = "file" value = "Browse File" /> <br /> <br />          Printing hither to upload the file :          <input blazon = "submit" value = "upload" /> <br /> <br />                     <h4 style="color: dark-green">${message}</h4> <br />                     Do you desire to salvage excel data into database ? <a href="saveData"><b>Yes</b></a> &nbsp &nbsp <a href="/"><b>No</b></a>       </form>    </torso> </html>          

success.jsp

success.jsp

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

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

img_33_excel1

.

Running awarding

In order to run the application, correct click on Project, so select Run As >> Spring Kicking App.

Testing Results

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

img_33_excel2

Furthermore, in club to upload an excel file, click on 'Choose File' which volition browse to your file arrangement, 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 y'all provided in awarding.properties accordingly. Note: Besides excel files, this functionality volition as well work for any file having other extensions. Consequently, once the file is uploaded, you lot will meet the success message with file name you uploaded as beneath.

How to Upload Excel data into Database Using Spring Boot ?

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

Uploading Excel data into Database Using Spring Boot

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

img_33_excel5
♥ Annotation: If you provide the input file proper noun as a value of 'app.upload.file' in application.properties and put the excel file in the same folder, You lot can directly save the excel records into database simply by clicking on the 'Yes' link. In this case, Fifty-fifty Yous don't have to upload the file.

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

In fact, yous need to change values of properties included in the application.properties file
1) Update the values of commencement 4 properties according to your database
2) Besides update the file storage directory path in the value of holding 'app.upload.dir'
iii) Then update the upload file path with file proper noun in the value of belongings 'app.upload.file'

Moreover, Beneath are the changes in coffee files :
4) To store excel records into database you demand to create new Entity grade, new Repository class in identify of 'Invoice.coffee' & 'InvoiceRepository.java' appropriately as per your requirements.
5) Consequently, you need to modify all the occurrences of entity class name in 'IExcelDataService.java' and 'ExcelDataServiceImpl.java'. It's 'Invoice' in our case.
6) In addition, y'all need to update entity grade method names in createList() method of 'ExcelDataServiceImpl.coffee'.

Can nosotros use this implementation in a real project ?

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

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

Y'all can go through the minimal modifications suggested in the above FAQ section 'How to practise our own implementation from this Instance with minimal changes?' of this article appropriately .

Conclusion

After going through all the points mentioned in the commodity, Yous should be able to work on 'How to read data from excel sheet and insert into database table in leap kick ?'. Even so, If you follow all the steps equally mentioned in the article, you lot volition implement it successfully in 1 become without facing any issue. Additionally, you volition likewise be able to work on 'How to upload any file (autonomously from excel) using spider web browser' confidently. At present, we can look from you that y'all volition apply this cognition in your real time project when required to do so. Furthermore, if yous face any issue on running this projection, don't hesitate to put your comments below.

♥ Also, if you desire to learn 'How to generate dynamic PDF written report using Jump Boot', Kindly visit internal commodity. Additionally, if you desire to larn more on Spring Kick trending topics kindly visit our web log.

greentrigive.blogspot.com

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

0 Response to "upload excel files to postgresql database via php"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel