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

Spring boot + Spring data jpa + Thymeleaf

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

 

pom.xml：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

 

upload.html：

<form enctype="multipart/form-data" method="post" action="/upload/excel">
    文件
    <input type="file" name="file" /> <input type="submit" value="上传" />
</form>

 

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

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

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

 

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;

/**
 *<p><b>上传Controller类</b></p>
 *<p> 上传文件的Controller</p>
 * @Author MengMeng 
 * @Date 2018/10/6 </p>
 * @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实体：

import javax.persistence.*;
import java.io.Serializable;
import java.util.Objects;

/**
 * <p><b>用户实体类</b></p>
 * @ClassName User
 * @Author MengMeng 
 * @Date 2018/10/6 </p>
 * @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：

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;

/**
 *<p><b>Excel的Service类接口</b></p>
 *<p> Excel的Service类接口，与Excel有关的业务逻辑方法</p>
 * @Author MengMeng 
 * @Date 2018/10/6 </p>
 * @version: 0.1
 * @since JDK 1.80_144
 */
public interface ExcelService {

	boolean getExcel(MultipartFile file) throws Exception;

}

 

ServiceImpl：

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.*;

/**
 * <p><b>Excel的Service类</b></p>
 * <p>Excel的Service类，与User有关的业务逻辑方法</p>
 * @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<Excel> list = new ArrayList<Excel>();

            //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：

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;

/**
 *<p><b>Excel的DAO类接口</b></p>
 *<p> Excel的DAO类接口，与Excel有关的持久化操作方法</p>
 * @Author MengMeng 
 * @Date 2018/10/6 </p>
 * @version: 0.1
 * @since JDK 1.80_144
 */
@Repository
public interface ExcelRepository extends BaseRepository<Excel, String> {
	
    
}

 

BaseRepository：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

import java.io.Serializable;

/**
 *<p><b>所有DAO类接口的原型</b></p>
 *<p> 其他Entity的DAO类接口均继承此接口
 * @version: 0.1
 */
@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
}

 

即可实现文件上传：