Import Excel file via JPA Spring boot

Question

I need to import data from an Excel file to a MySQL database to fill a table by this entity.

I have prepared a html file (thymeleaf), I have my class and I created the repository to save new entities, but when I try to import from an Excel file I can't find a way to do it, if any one has an idea how to do that please help.

My Entity:

package abdou.entities;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id; 
import javax.persistence.Table;

/**
 * @author Abderrahmane B
 *
 */
@Entity
@Table(name="table")
public class Table
{
 @Id 
 @GeneratedValue(strategy=GenerationType.AUTO)
private Integer id;

private String col1;
 
private String col2;
 
private String col3;

private String col4;

private String col5;

private String col6;

private String col7;

@Column(name="datesaisie")
private Date col8; 


private Integer col9;

private Integer col10;

public Table(String col1, String col2, String col3, String col4, String col5, String col6, String col7, 
    Date col8, Integer col9, Integer col10) {
    super();
    // here the fields =arguments except the id because it is auto generated
}

public Table() {
    
}

//Getters and Setters

 
}

Here is my thymeleaf test page:

                    <form method="post" enctype="multipart/form-data" th:action="@{/importer}" >
                        <input type="file" name="file">
                        <br>
                        <input type="submit" value="Import">
                    </form>

What is the simplest way to do it? and which format of Excel cells must I have?

Note: HERE IS MY CONTROLLER IMPORTS:

import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

 
 @RequestMapping(value = "/importer", method = RequestMethod.POST)
 public String process(@RequestParam("file") MultipartFile file,Model model,HttpServletRequest 
  request) throws Exception {
 //What should I write here?
  }

you have to write new method to process the Excel file where using apache-poi you can create workbook object and then iterate rows and insert it into database. `InputStream inputStream = new BufferedInputStream( file.getInputStream() ); XSSFWorkbook workbook = new XSSFWorkbook(inputStream);` — Kapil, Dec 14 '19 at 10:40
@Kapil i just want to know the type of excel data cells please and value exemples — Abderrahmane BECHIKH, Dec 14 '19 at 10:55
Here's an example to start with https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/ — Kapil, Dec 14 '19 at 12:08

Import Excel file via JPA Spring boot

0 Answers0