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

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

Spring boot + Spring data jpa + Thymeleaf

批量插入 + POI读取 + 文件上传

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

* @http://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.fasthttp://json.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.dhttp://omain.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 {

}

即可实现文件上传：

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。