大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

SpringBoot实现Excel文件批量上传导入数据库

Spring boot + Spring data jpa + Thymeleaf
批量插入 + POI读取 + 文件上传

创新互联公司专业提供四川雅安服务器托管服务,为用户提供五星数据中心、电信、双线接入解决方案,用户可自行在线购买四川雅安服务器托管服务,并享受7*24小时金牌售后服务。

pom.xml:



 org.apache.poi
 poi
 3.17



 org.apache.poi
 poi-ooxml
 3.17

upload.html:

文件

如果自己的项目中使用了Spring  security,页面提交文件之后,会出现403的错误,最快的解决办法,如下:

http.csrf().ignoringAntMatchers("/upload/**").

在security的配置文件中,加入上边的代码即可。当然还有其他的办法,大家可在网上查找。 

Controller:

package org.meng.project.controller;
 
import org.meng.project.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.http.HttpServletRequest;
 
/**
 *

上传Controller类

*

上传文件的Controller

* @Author MengMeng * @Date 2018/10/6

* @version: 0.1 * @since JDK 1.80_144 */ @Controller @RequestMapping("/upload") public class UploadController { @Autowired private HttpServletRequest request; @Autowired private ExcelService excelService; //跳转到上传文件的页面 @RequestMapping(value = "", method = RequestMethod.GET) public String goUpload() { //跳转到 templates 中tools目录下的 upload.html return "tools/upload"; } @RequestMapping(value = "/excel",method = RequestMethod.POST) public String upload(MultipartFile file, Model model) throws Exception { boolean flag = excelService.getExcel(file); if(flag){ model.addAttribute("Message", "上传成功"); }else{ model.addAttribute("Message", "上传失败"); } return "tools/upload"; } }

Excel实体:

package org.meng.project.entity;
 
import javax.persistence.*;
import java.io.Serializable;
import java.util.Objects;
 
/**
 * 

用户实体类

* @ClassName User * @Author MengMeng * @Date 2018/10/6

* @Version: 0.1 * @Since JDK 1.80_171 */ @Entity @Table(name = "test", schema = "project") public class Excel implements Serializable { private static final long serialVersionUID = 1L; @Id @Column(length=36) private String id; @Column(length=45,nullable=false,unique=true) private String username; @Column(length=100,nullable=false,unique=true) private String email; @Column(length=45,nullable=false) private String password; @Column(length=45) private String role; public Excel() { } public Excel(Excel user){ this.id = user.getId(); this.username = user.getUsername(); this.role = user.getRole(); this.email = user.getEmail(); this.password = user.getPassword(); } //get 和 set }

Service:

package org.meng.project.service;
 
import com.alibaba.fastjson.JSON;
 
import org.meng.project.entity.*;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.transaction.Transactional;
 
/**
 *

Excel的Service类接口

*

Excel的Service类接口,与Excel有关的业务逻辑方法

* @Author MengMeng * @Date 2018/10/6

* @version: 0.1 * @since JDK 1.80_144 */ public interface ExcelService { boolean getExcel(MultipartFile file) throws Exception; }

ServiceImpl:

package org.meng.project.service;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.meng.project.entity.*;
import org.meng.project.repository.ExcelRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
 
import java.io.IOException;
import java.util.*;
 
/**
 * 

Excel的Service类

*

Excel的Service类,与User有关的业务逻辑方法

* @Author MengMeng * @Date 2018/10/6 * @version: 0.1 * @since JDK 1.80_144 */ @Service public class ExcelServiceImpl implements ExcelService { @Autowired private ExcelRepository excelRepository; @Override public boolean getExcel(MultipartFile file) throws Exception { // TODO Auto-generated method stub List list = new ArrayList(); //1.得到上传的表 Workbook workbook2 = WorkbookFactory.create(file.getInputStream()); //2、获取test工作表 Sheet sheet2 = workbook2.getSheet("test"); //获取表的总行数 int num = sheet2.getLastRowNum(); //System.out.println(num); //总列数 int col = sheet2.getRow(0).getLastCellNum(); //遍历excel每一行 for (int j = 0; j <= num; j++) { Row row1 = sheet2.getRow(j); //如果单元格中有数字或者其他格式的数据,则调用setCellType()转换为string类型 Cell cell1 = row1.getCell(0); cell1.setCellType(CellType.STRING); //获取表中第i行,第2列的单元格 Cell cell2 = row1.getCell(1); //excel表的第i行,第3列的单元格 Cell cell3 = row1.getCell(2); cell3.setCellType(CellType.STRING); Cell cell4 = row1.getCell(3); Cell cell5 = row1.getCell(4); //这里new 一个对象,用来装填从页面上传的Excel数据,字段根据上传的excel决定 Excel excel= new Excel(); excel.setId(cell1.getStringCellValue()); excel.setEmail(cell2.getStringCellValue()); excel.setPassword(cell3.getStringCellValue()); excel.setRole(cell4.getStringCellValue()); excel.setUsername(cell5.getStringCellValue()); list.add(excel); } excelRepository.saveAll(list); return true; } }

注意:需统一数据库和Excel表中数据值的类型,不然会出现Cannot get a STRING value from a NUMERIC cell等错误。

Repository:

package org.meng.project.repository;
 
import org.meng.project.entity.Excel;
import org.meng.project.repository.base.BaseRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
 
/**
 *

Excel的DAO类接口

*

Excel的DAO类接口,与Excel有关的持久化操作方法

* @Author MengMeng * @Date 2018/10/6

* @version: 0.1 * @since JDK 1.80_144 */ @Repository public interface ExcelRepository extends BaseRepository { }

即可实现文件上传:

SpringBoot实现Excel文件批量上传导入数据库

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持创新互联。


网站标题:SpringBoot实现Excel文件批量上传导入数据库
文章分享:http://dzwzjz.com/article/ihedee.html
在线咨询
服务热线
服务热线:028-86922220
TOP