栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

Easyexcel的简单使用——Excel表格导入到数据库

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

Easyexcel的简单使用——Excel表格导入到数据库

文章目录
      • jar包
      • controller层
      • QuestionbankDAO
      • QuestionbankData
      • QuestionExcelListener
      • questionbankServiceImpl

官网地址:https://www.yuque.com/easyexcel/doc/read

jar包

        
            com.alibaba
            easyexcel
            3.1.1
        
controller层
    @ApiOperation("新增题目Excel")
    @PostMapping("/insertQuestionExcel")
    public CommonResult insertQuestionExcel(@RequestParam MultipartFile file){
        try {
            EasyExcel.read(file.getInputStream(), QuestionbankData.class, new QuestionExcelListener(questionbankService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return CommonResult.success("Excel成功插入数据库!!✿✿ヽ(°▽°)ノ✿撒花");
    }
QuestionbankDAO
package com.qx.answer.pojo.dao;

import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;


@Getter
@Setter
@EqualsAndHashCode
public class QuestionbankDAO {
    private String Content;
    private String ChildContent;
    private Integer Type;
    private String AnswerContent;
}

QuestionbankData
package com.qx.answer.pojo.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

import java.util.List;


@Getter
@Setter
@EqualsAndHashCode
public class QuestionbankData {
    @ExcelProperty(value = "题目",index = 0)
    private String Content;

    @ExcelProperty(value = "选择题选项",index = 1)
    private String ChildContent;

    @ExcelProperty(value = "类型",index = 3)
    private Integer Type;

    @ExcelProperty(value = "答案",index = 2)
    private String AnswerContent;

}
QuestionExcelListener
package com.qx.answer.config;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.qx.answer.pojo.dao.QuestionbankDAO;
import com.qx.answer.pojo.dto.QuestionbankData;
import com.qx.answer.service.IQuestionbankService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.util.List;


@Component
@Scope("prototype")
@Slf4j
public class QuestionExcelListener extends AnalysisEventListener {

    @Autowired
    private IQuestionbankService questionbankService;

    private static final int BATCH_COUNT = 100;
    //缓存数据
    private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private QuestionbankDAO questionbankDAO;

    public QuestionExcelListener() {
        questionbankDAO = new QuestionbankDAO();
    }

    public QuestionExcelListener(IQuestionbankService qbService) {
        questionbankService = qbService;
    }

    @Override
    public void invoke(QuestionbankData questionbankData, AnalysisContext analysisContext) {
        // 每解析一行数据,该方法会被调用一次
        System.out.println("解析到一条数据:"+ JSON.toJSONString(questionbankData));
        cachedDataList.add(questionbankData);
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //存入到数据库中
        saveData();
        // 全部解析完成被调用
        System.out.println("所有数据解析完成!!");
    }

    //存储到数据库
    private void saveData() {
        log.info(": 共计{}条数据,开始存储数据库!", cachedDataList.size());
        questionbankService.insertSelective(cachedDataList);
        System.out.println("数据插入数据库成功!!");
    }
}

questionbankServiceImpl
@Override
    public void insertSelective(List list) {
        for (QuestionbankData qbD:list) {
            Questionbank qb = new Questionbank();
            qb.setType(qbD.getType());
            qb.setAnswerContent(qbD.getAnswerContent());
            qb.setContent(qbD.getContent());
            questionbankMapper.insert(qb);
            Questionchildcontent qcc = new Questionchildcontent();
            if(qbD.getChildContent()!=null){
                qcc.setQuerstionBankId(qb.getId());
                qcc.setContent(qbD.getChildContent());
                questionchildcontentMapper.insert(qcc);
            }
            System.out.println("数据插入数据库成功!!"+qb);
        }
    }
转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/981207.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号