SpringBoot 导入excel到数据库 -- EasyPoi

controller层

    /**
     * 导入企业数据
     */
    @ResponseBody
    @RequestMapping(value = "/companyData", method = RequestMethod.POST)
    public Map<String, Object> importData(@RequestParam MultipartFile file) {
        try {
            if (!file.isEmpty()) {
                // 使用临时文件
                File newFile = new File(file.getOriginalFilename());    //如果需要生成本地文件可以直接写出具体路径
                FileUtils.copyInputStreamToFile(file.getInputStream(), newFile);
                System.out.println(newFile.getAbsolutePath());

                return dataImportService.importData(newFile);
            }
            return ToolsUtil.returnMessage(HttpStatus.ERROR, "未获取到文件");

        } catch (Exception e) {
            e.printStackTrace();
        }

        return ToolsUtil.returnMessage(HttpStatus.ERROR, "导入失败");
    }

service层

一个Excel文件存在多个sheet表，格式化日期字段

    @Override
    public Map<String, Object> importData(File file) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                String sheetName = workbook.getSheetName(sheetIndex);
                if (sheetName.contains("工商信息")) {
                    List<String> codeList = companyRepository.findCreditCodeList();
                    String[] str = new String[]{"注册时间", "营业期限起", "营业期限止", "核准日期"};
                    List<Company> list = ExcelUtil.parseExcel(file, sheetIndex, Company.class, str);
                    if (!ToolsUtil.isEmpty(list)) {
                        for (Company company : list) {
                            if (codeList.contains(company.getCreditCode())) {
                                List<Company> oldCompanys = companyRepository.findByCreditCode(company.getCreditCode());
                                if (!ToolsUtil.isEmpty(oldCompanys)) {
                                    for (Company oldCompany : oldCompanys) {
                                        if (Objects.equals(oldCompany.getCompanyName(), company.getCompanyName())) {
                                            System.out.println(company.getCreditCode());
                                            company.setCompanyId(oldCompany.getCompanyId());
                                        }
                                    }
                                }
                            }
                            codeList.add(company.getCreditCode());
                        }
                        companyRepository.saveAll(list);
                    }
                } else if (sheetName.contains("分支机构")) {
                    String[] str = new String[0];
                    List<CompanyBranch> list = ExcelUtil.parseExcel(file, sheetIndex, CompanyBranch.class, str);
                    if (!ToolsUtil.isEmpty(list)) {
                        companyBranchRepository.saveAll(list);
                    }
                } 
            }

            //删除临时文件
            if (file.exists()) {
                file.delete();
            }

            System.out.println("导入成功，临时文件删除");
            return ToolsUtil.returnMessage(HttpStatus.SUCCESS, "导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            return ToolsUtil.returnMessage(HttpStatus.ERROR, "导入失败");
        }
    }

ExcelUtil工具类

package com.test.utils;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.hibernate.TypeMismatchException;

import java.io.File;
import java.util.List;


public class ExcelUtil {
    private static final String DIAN = ".";

    private static final String XLS = "xls";

    private static final String XLSX = "xlsx";

    public static List parseExcel(File file, int sheetIndex, Class<?> clazz, String[] str) throws TypeMismatchException {
        if (checkIsExcel(file)) {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            ImportParams params = new ImportParams();
            ExcelHandler excelHandler = new ExcelHandler();
            //设置要转换的字段
            excelHandler.setNeedHandlerFields(str);
            // 第几个sheet页
            params.setStartSheetIndex(sheetIndex);
            //设置标题的行数，有标题时一定要有
            params.setTitleRows(0);
            //设置表头的行数
            params.setHeadRows(1);
            params.setDataHandler(excelHandler);
            params.setNeedSave(true);

            return ExcelImportUtil.importExcel(file, clazz, params);
        }
        throw new TypeMismatchException("文件格式错误!");

    }

    public static boolean checkIsExcel(File file) {
        if (null != file) {
            String fileName = file.getName();
            if (!fileName.contains(DIAN)) {
                return false;
            }
            String type = fileName.substring(fileName.lastIndexOf(".") + 1);
            return XLS.equalsIgnoreCase(type) || XLSX.equalsIgnoreCase(type);
        }
        throw new NullPointerException("文件为空");
    }
}

build.gradle引入easypoi包

dependencies {
	implementation 'cn.afterturn:easypoi-base:4.4.0'
    implementation 'cn.afterturn:easypoi-annotation:4.4.0'
    implementation 'cn.afterturn:easypoi-web:4.4.0'

    implementation 'commons-io:commons-io:2.5'
}

实体类举例：

package com.test.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
import java.util.List;

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })
@Table(name = "company_info")
public class Company implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "company_id")
    private Integer companyId;

    /**
     * 统一社会信用代码
     */
    @Excel(name = "统一信用代码")
    @Column(name = "credit_code")
    private String creditCode;

    /**
     * 企业名称
     */
    @Excel(name = "企业名称")
    @Column(name = "company_name")
    private String companyName;

    /**
     * 工商注册号
     */
    @Excel(name = "工商注册号")
    @Column(name = "regist_num")
    private String registNum;

    /**
     * 登记机关
     */
    @Excel(name = "登记机关")
    @Column(name = "belong_org")
    private String belongOrg;

    /**
     * 法定代表人名称
     */
    @Excel(name = "法定代表人")
    @Column(name = "oper_name")
    private String operName;

    /**
     * 成立日期
     */
    @Excel(name = "注册时间")
    @Column(name = "start_date")
    private String startDate;

    /**
     * 登记状态
     */
    @Excel(name = "经营状态")
    @Column(name = "company_status")
    private String companyStatus;

    /**
     * 注册资本
     */
    @Excel(name = "注册资本")
    @Column(name = "regist_capi")
    private String registCapi;

    /**
     * 企业类型
     */
    @Excel(name = "企业类型")
    @Column(name = "econ_kind")
    private String econKind;

    /**
     * 所属行业
     */
    @Excel(name = "所属行业")
    @Column(name = "industry")
    private String industry;

    /**
     * 注册地址
     */
    @Excel(name = "注册地址")
    @Column(name = "address")
    private String address;

    /**
     * 经营范围
     */
    @Excel(name = "经营范围")
    @Column(name = "scope")
    private String scope;

    /**
     * 营业期限始
     */
    @Excel(name = "营业期限起")
    @Column(name = "term_start")
    private String termStart;

    /**
     * 营业期限至
     */
    @Excel(name = "营业期限止")
    @Column(name = "term_end")
    private String termEnd;

    /**
     * 核准日期
     */
    @Excel(name = "核准日期")
    @Column(name = "check_date")
    private String checkDate;

    /**
     * 组织机构代码
     */
    @Excel(name = "组织机构代码")
    @Column(name = "org_num")
    private String orgNum;


}