SpringBoot上传Excel并导入Excel表格

pom.xml导入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
</dependency>
<dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
</dependency>
<dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
</dependency>

实体类

public class User extends BaseEntity<String>{
    @Override
    public String primaryKey() {
        return this.getOpenId();
    }

    @ApiModelProperty(value = "用户ID")
    private String id;

    @ApiModelProperty(value = "用户名")
    private String name;

    @ApiModelProperty(value = "手机号码")
    private Long phone;


    @ApiModelProperty(value = "创建日期")
    private Date createTime;

    @ApiModelProperty(value = "更新日期")
    private Date updateTime;

   //get/set方法省略，构造方法省略

控制层

	@Resource
    private IExcelService excelService;


    @ApiOperation(value = "excel导入字典信息")
    @ApiImplicitParams({@ApiImplicitParam(name = "file",
            value = "文件对象",
            dataType = "MultipartFile",
            required = true,
            allowMultiple = true)})
    @PostMapping("/import")
    public String importExcel(@RequestParam("file") MultipartFile file) {
        Boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = excelService.importExcel(fileName, file);
            return  a.toString();
        } catch (Exception e) {
            e.printStackTrace();
            return  e.getMessage();
        }
    }

工具类

public interface IExcelService {
    boolean importExcel(String fileName, MultipartFile file) throws Exception;
}

@Service("excelService")
public class ExcelService implements IExcelService{

    @Resource
    private UserMapper userMapper;

    @Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public boolean importExcel(String fileName, MultipartFile file) throws Exception {
        boolean notNull = false;
        List<User> userList = new ArrayList<User>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        //这里开始到下一个注释前，可以替换
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        User user;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
/**
		//解决poi读取大Excel文件内存溢出问题
		//将一部分行缓存到内存中，在迭代时，不断加载行到内存中，而不是一次性加载所有记录，这样可以不断读取并不影响内存
		//限制之处：只能用于读取excel内容，写入操作不可用；可以用getSheetAt()获取对应的sheet，因为当前只是加载有限的row在内存中，因此不能随便访问row，不能使用getRow(int rowNum)方法，但cell不影响。使用时建议使用迭代器
		//额外导入依赖
		<dependency>
			<groupId>com.monitorjbl</groupId>
			<artifactId>xlsx-streamer</artifactId>
			<version>2.2.0</version>
		</dependency>
		
		Workbook wb = StreamingReader.builder()
		                .rowCacheSize(100)  //缓存到内存中的行数，默认是10
		                .bufferSize(8192)  //读取资源时，缓存到内存的字节大小，默认是1024
		                .open(is);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
		Sheet sheet = wb.getSheetAt(0);
		if (sheet != null) {
		     notNull = true;
		}
		User user;
		for (Row row : sheet) {
		       int r = row.getRowNum();
**/
            if (row == null){
                continue;
            }

            user = new User ();

            if( row.getCell(0).getCellType() !=1){
                throw new MyException("导入失败(第"+(r+1)+"行,姓名请设为文本格式)");
            }
            String name = row.getCell(0).getStringCellValue();
            if(name == null || name.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,名称未填写)");
            }
            
            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            String phone = row.getCell(1).getStringCellValue();
            if(phone ==null || phone .isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,电话号码格式错误)");
            }
            Long phoneNum = Long.valueOf(phone);

            user.setName(name);
            user.setPhone(phoneNum);


            userList.add(user);
        }
        return notNull;
    }
}