Java导入excel

需要先倒入POI的jar

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>

public String exportCharge(MultipartHttpServletRequest request) {
        try {
            //得到上传的文件
            MultipartFile fileFile = request.getFile("file");
            //转换成输入流
            InputStream in = fileFile.getInputStream();
            XSSFWorkbook readWb = new XSSFWorkbook(in);
            //遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数

            XSSFSheet sheet = readWb.getSheetAt(0);
             //循环行Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                System.out.println("当前插入第"+rowNum);
                XSSFRow hssfRow = sheet.getRow(rowNum);
                AllPhone allPhone=new AllPhone();
                if (hssfRow != null) {
                    for (int colNum = 0; colNum < hssfRow.getPhysicalNumberOfCells(); colNum++) {
                        String tmp = hssfRow.getCell(colNum).toString();
                        if (colNum == 0) {
                            int begin = tmp.indexOf(".");
                        } else if (colNum == 1) {
                            allPhone.setStage(tmp);
                        }else if (colNum == 2) {

                        }else if (colNum == 3) {
                            allPhone.setCardtype(tmp);
                        }else if (colNum == 4) {


                        }else if (colNum == 5) {
                            if(StringUtil.isNotEmpty(tmp)){
                                allPhone.setPostcode(tmp);
                            }
                        }
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return "ok";
    }


方法二EasyExcel

导包：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
</dependency>

Controller

EasyExcel.read(file.getInputStream(), GradeCoverDataBean.class,
        new GradeCoverDataListener(gradeInfoMapper,matchRegisterMapper)).sheet().doRead();
实体类：
名字就是  GradeCoverDataBean  字段就是excel一一对应

package com.erp.match.common.data;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.erp.match.common.enums.ExcelValueType;
import lombok.Data;

/**
 * 功能描述：团队成绩导入
 *
 * @Author hsy
 * @createDate 2022/1/14
 */
@Data
public class TeamGradeCoverDataBean {
    @ExcelProperty(value = "赛事名称",index = 1)
    private String matchName;
    @ExcelProperty(value = "赛事编码",index = 2)
    private String matchCode;
    @ExcelProperty(value = "组别",index = 3)
    private String groupName;
    @ExcelProperty(value = "队伍名称",index = 4)
    private String teamName;
    @ExcelProperty(value = "队伍码",index = 5)
    private String teamCode;
    @ExcelProperty(value = "领队姓名",index = 6)
    private String leaderName;
    @ExcelProperty(value = "领队身份证号",index = 7)
    private String leaderIdNumber;
    @ExcelProperty(value = "成绩",index = 8)
    private String teamGrade;
    @ExcelProperty(value = "名次",index = 9)
    private String teamRanking;
    @ExcelIgnore
    private String errMessage;
}

然后就是监听器：GradeCoverDataListener

package com.erp.match.service.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.erp.match.common.data.TeamGradeCoverDataBean;
import com.erp.match.dal.entity.MatchTeam;
import com.erp.match.manager.mapper.team.TeamGradeMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

import static java.util.Objects.isNull;

/**
 * 功能描述：
 *
 * @Author hsy
 * @createDate 2022/1/14
 */
@Slf4j
public class TeamGrageCoverDataListener extends AnalysisEventListener<TeamGradeCoverDataBean> {
    /**
     * 每隔10条存数据库，然后清理list，方便内存回收
     */
    private static final int BATCH_COUNT = 10;

    private List<MatchTeam> matchTeams = new ArrayList<>(BATCH_COUNT);

    private List<TeamGradeCoverDataBean> errorTeams = new ArrayList<>();

    private TeamGradeMapper teamGradeMapper;

    public TeamGrageCoverDataListener(TeamGradeMapper teamGradeMapper) {
        this.teamGradeMapper = teamGradeMapper;
    }

    @Override
    public void invoke(TeamGradeCoverDataBean teamGradeCoverDataBean, AnalysisContext analysisContext) {
        log.info("导入一条数据：{}",teamGradeCoverDataBean.toString());
        // 参数检验
        String msg=checkTeamGradeBeanValid(teamGradeCoverDataBean);
        if(StringUtils.isEmpty(msg)){
            QueryWrapper<MatchTeam> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("team_code", teamGradeCoverDataBean.getTeamCode());
            MatchTeam team = teamGradeMapper.selectOne(queryWrapper);
            if (isNull(team)) {
                teamGradeCoverDataBean.setErrMessage("队伍编码不存在");
                errorTeams.add(teamGradeCoverDataBean);
            }else {
                team.setTeamGrade(teamGradeCoverDataBean.getTeamGrade());
                team.setTeamRanking(Integer.valueOf(teamGradeCoverDataBean.getTeamRanking()));
                teamGradeMapper.updateById(team);
            }
        }else{
            teamGradeCoverDataBean.setErrMessage(msg);
            errorTeams.add(teamGradeCoverDataBean);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("导入结束!");
        log.info("错误条数：{}",errorTeams.size());
        errorTeams.clear();

    }

    private String checkTeamGradeBeanValid(TeamGradeCoverDataBean teamGradeCoverDataBean) {
        if(StringUtils.isBlank(teamGradeCoverDataBean.getTeamCode())){
            return "队伍码为空";
        }
        if(StringUtils.isBlank(teamGradeCoverDataBean.getTeamRanking())){
            return "名次为空";
        }
        if(StringUtils.isBlank(teamGradeCoverDataBean.getTeamGrade())){
            return "成绩为空";
        }
        return null;
    }
}

}