文件excel导入问题_excel大文件导入问题-优快云博客

本文链接：https://blog.youkuaiyun.com/answero/article/details/128567323

该代码示例展示了如何使用EasyExcel库在Java后端处理Excel导入功能。Controller接收MultipartFile文件，通过EasyExcel读取并解析数据，监听器处理分析过程，转换数据并批量保存到数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

先创建一个excel表格
在这里插入图片描述
利用easyExcel进行导入功能
controller

package com.lzy.project.easyexcel.ExcelController;
import com.alibaba.excel.EasyExcel;
import com.lzy.framework.security.service.TokenService;
import com.lzy.framework.web.domain.AjaxResult;
import com.lzy.project.easyexcel.ExcelAnalysisEventListener.ProjectZbfxgzListener;
import com.lzy.project.project.service.IProjectZbfxgzService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@Api(tags = "项目利润管理Excel处理")
@RequestMapping("/projectZbfxgz/excel")
@RestController
@Slf4j
public class ProjectZbfxgzExcelController {
    @Autowired
    private IProjectZbfxgzService projectZbfxgzService;
    @Autowired
    private TokenService tokenService;

    @PostMapping("/importExcel")
    @ApiOperation(notes = "导入数据", value = "导入数据", httpMethod = "POST")
    public AjaxResult importExcel(@RequestParam("file") MultipartFile file, @RequestParam("xmid")Long xmid) {
        try {
//            解析excel
            EasyExcel.read(file.getInputStream(),new ProjectZbfxgzListener(projectZbfxgzService, xmid ,tokenService)).sheet(0).headRowNumber(1).doRead();
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error(e.getMessage());
        }
        return AjaxResult.success();

    }

}

监听器

package com.lzy.project.easyexcel.ExcelAnalysisEventListener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.google.common.collect.Lists;
import com.lzy.common.utils.DateUtils;
import com.lzy.common.utils.ServletUtils;
import com.lzy.framework.security.LoginUser;
import com.lzy.framework.security.service.TokenService;
import com.lzy.project.easyexcel.ExcelEntity.ProjectProfitExcelAttribute;
import com.lzy.project.project.domain.ProjectZbfxgz;
import com.lzy.project.project.service.IProjectZbfxgzService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
@Slf4j
public class ProjectZbfxgzListener extends AnalysisEventListener<Object> {

    private final IProjectZbfxgzService projectZbfxgzService;
    /**
     * 通过实体类获取字段类型
     */
    private final Map<Integer, Field> excelEntityFieldMap;

    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<ProjectZbfxgz> cachedDataList = Lists.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 保存实体类
     */
    private ProjectZbfxgz projectZbfxgz;

    private Long xmid;

    private TokenService tokenService;

    /**
     * 监听器构造器
     *
     * @param projectZbfxgzService
     */
    public ProjectZbfxgzListener(IProjectZbfxgzService projectZbfxgzService, Long xmid, TokenService tokenService) {
//        excelEntityFieldMap 对应Excel中的key值与实体类中字段的索引保持一致 拿到excel中的值 key value 都拿到了
        excelEntityFieldMap = getExcelAttributeFields(ProjectZbfxgz.class);
        this.projectZbfxgzService = projectZbfxgzService;
        this.projectZbfxgz = new ProjectZbfxgz();
        this.xmid = xmid;
        this.tokenService = tokenService;

    }

    /**
     * 获取体类字段中的字段索引值对应excel中的key值
     *
     * @param projectZbfxgz
     * @return
     */
    private Map<Integer, Field> getExcelAttributeFields(Class<ProjectZbfxgz> projectZbfxgz) {
        return Arrays.stream(projectZbfxgz.getDeclaredFields()).
                filter(a -> a.getAnnotation(ProjectProfitExcelAttribute.class) != null).
//                通过注解获取到ProjectZbfxgz里面的字段对应的excel的列 返回的是一个index索引
        collect(Collectors.toMap(b -> b.getAnnotation(ProjectProfitExcelAttribute.class).index(), c -> {
    c.setAccessible(true);
    return c;
}));
    }

    /**
     * 解析excel内容
     *
     * @param o
     * @param analysisContext
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
//        o获取excel中的值
        log.info("fillEntity begin map:{}", o);
        fillEntity(projectZbfxgz, (Map) o, excelEntityFieldMap);
        cachedDataList.add(projectZbfxgz);
        if (cachedDataList.size() >= BATCH_COUNT) {
            save();
            // 存储完成清理 list
            cachedDataList = Lists.newArrayListWithExpectedSize(BATCH_COUNT);
        }
        log.info("值执行结束");

    }

    /**
     * 将上边的实体参数传给下边的形式参数 获取里边的值
     *
     * @param projectZbfxgz
     * @param o
     * @param excelEntityFieldMap
     */
    private void fillEntity(ProjectZbfxgz projectZbfxgz, Map o, Map<Integer, Field> excelEntityFieldMap) {
//        excelEntityFieldMap放的是Excel中的key值
        excelEntityFieldMap.forEach((key, value) -> {
            log.info(key + "->>>>>>>>>" + value);
            try {
                Object kv = o.get(key);
                if (StringUtils.isEmpty(kv)) {
                    throw new ExcelAnalysisException("请输入字段！");
                } else {
                    kv = changeName(key, (String) kv);

                    value.set(projectZbfxgz, kv);
                }
            } catch (Exception e) {
                e.printStackTrace();
                log.info("解析错误");
            }
        });
    }

    /**
     * 解析数据完成
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("输出 end ***********************************************");
        save();
    }

    private Boolean save() {
        try{
            for (ProjectZbfxgz c : cachedDataList) {
                LoginUser loginUser = tokenService.getLoginUser(ServletUtils.getRequest());
                c.setAddUser(loginUser.getUser().getUserId());
                c.setAddTime(DateUtils.getTime());
                c.setXmid(xmid);
                projectZbfxgzService.insertProjectZbfxgz(c);
            }
        }catch (Exception e){
            log.info("数据插入异常！");
            return false;
        }
        return true;
    }
    private static String changeName(Integer key, String kv){
        if(key == 1){ return kv.equals("紧急") ? "0" : (kv.equals("一般") ? "1" : "2"); }
        if(key == 2){ return kv.equals("内部") ? "1" : (kv.equals("外部") ? "2" : "3"); }
        if(key == 4){ return kv.equals("转移") ? "1" : (kv.equals("接受") ? "2" : kv.equals("回避") ? "3" : "4"); }
        return kv;
    }
//    a = 条件1 ? 值1 : (条件2 ? 值2 : (条件3 ? 值3 : (条件4 ? 值4 : 值5)));

}
//   if(key == 1){
//            switch (kv){
//                case "紧急":
//                    return "0";
//                case "一般":
//                    return "1";
//                case "缓慢":
//                    return "2";
            // a = 条件1 ? 值1 : (条件2 ? 值2 : (条件3 ? 值3 : (条件4 ? 值4 : 值5)));

//            }

//        if (key == 2) {
//            switch (kv){
//                case "内部":
//                    return "1";
//                case "外部":
//                    return "2";
//                case "不可规避":
//                    return "3";
//            }
//        }
//        if (key == 4) {
//            switch (kv){
//                case "转移":
//                    return "1";
//                case "接受":
//                    return "2";
//                case "回避":
//                    return "3";
//                case "减轻":
//                    return "4";
//            }
//        }
//        if(key == 2) {
//            switch ((String) kv) {
//                case "内部":
//                    kv = "1";
//                    break;
//                case "外部":
//                    kv = "2";
//                    break;
//                case "不可规避":
//                    kv = "3";
//                    break;
//            }
//        }
//        if(key == 4) {
//            switch ((String) kv) {
//                case "转移":
//                    kv = "1";
//                    break;
//                case "接受":
//                    kv = "2";
//                    break;
//                case "回避":
//                    kv = "3";
//                    break;
//                case "减轻":
//                    kv = "4";
//                    break;
//            }
//        }