使用mybatisplus快速开发springboot项目（五）--Excel导入

使用mybatisplus快速开发springboot项目（四）--PageHelper-CSDN博客

很多情况下，数据需要一条一条的添加，使用excel表格的方式会大大提高操作的效率~

现在引入poi库帮我们实现excel文件的导入

pom.xml导入依赖

    <!--使用POI读取文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

ImportExcelUtil（导入Excel工具类）

package com.example.examservice.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 导入Excel表工具类
 */
public class ImportExcelUtil {
    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws Exception
     */
    public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = ImportExcelUtil.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(ImportExcelUtil.getCellValue(cell));
                }
                list.add(li);
            }

        }
//        work.close();
        return list;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003L.equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     */
    public  static Object getCellValue(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
//        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        if (cell!=null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if("General".equals(cell.getCellStyle().getDataFormatString())){
                        value = df.format(cell.getNumericCellValue());
                    }
                    else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                        value = sdf.format(cell.getDateCellValue());
                    }
                    else{
                        value = sdf.format(cell.getDateCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
        }

        return value;
    }
}

导入业务实现

package com.example.examservice.comprehensiveService;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.example.examservice.common.CustomException;
import com.example.examservice.common.R;
import com.example.examservice.entity.Course;
import com.example.examservice.entity.Question;
import com.example.examservice.service.CourseService;
import com.example.examservice.service.QuestionService;
import com.example.examservice.utils.ImportExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * <p>
 *  综合服务类
 * </p>
 *
 * @author liujianchen
 * @since 2024-03-19
 */
@Slf4j
@Transactional
@Service
public class QuestionBankService {

    @Autowired
    private QuestionService questionService;

    @Autowired
    private CourseService courseService;

    public R importExcelQuestion(MultipartFile file) {
        int j = 0;
        LambdaQueryWrapper<Course> queryWrapper = new LambdaQueryWrapper();
        queryWrapper.eq(Course::getIsDeleted, 0);
        List<Course> courseList = courseService.list(queryWrapper);
        try {
            //验证文件类型
            if (!file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xls")
                    &&!file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xlsx")){
                throw new CustomException("文件类型有误！请上传Excle文件");
            }
            //获取数据
            List<List<Object>> olist = ImportExcelUtil.getListByExcel(file.getInputStream(), file.getOriginalFilename());
            List<Question> questionList = new ArrayList<>();
            for (int i = 0; i < olist.size(); i++) {
                List<Object> list = olist.get(i);
                Question question = new Question();
                question.setId(IdWorker.getId());
                //根据下标获取每一行的每一条数据
                if (StringUtils.isBlank(String.valueOf(list.get(0))))
                    throw new CustomException("第" + j + "题题目不能为空");
                question.setQuestion(String.valueOf(list.get(0)));

                if (StringUtils.isBlank(String.valueOf(list.get(1))))
                    throw new CustomException("第" + j + "题类型不能为空");
                String type = String.valueOf(list.get(1));

                if (type.contains("填空")) {
                    question.setType("cloze");
                } else if (type.contains("单选")){
                    question.setType("multiple_choice");
                } else if (type.contains("多选")){
                    question.setType("multiple_choice_questions");
                } else if (type.contains("判断")){
                    question.setType("true_or_false");
                } else {
                    throw new CustomException("第" + j + "题类型错误");
                }

                Object optionAValue = list.get(2);
                if (optionAValue != null)
                    question.setOptionA(String.valueOf(optionAValue));

                Object optionBValue = list.get(3);
                if (optionBValue != null)
                    question.setOptionB(String.valueOf(optionBValue));

                Object optionCValue = list.get(4);
                if (optionCValue != null)
                    question.setOptionC(String.valueOf(optionCValue));

                Object optionDValue = list.get(5);
                if (optionDValue != null)
                    question.setOptionD(String.valueOf(optionDValue));

                if (StringUtils.isBlank(String.valueOf(list.get(6))))
                    throw new CustomException("第" + j + "题答案不能为空");
                question.setAnswer(String.valueOf(list.get(6)));

                if (StringUtils.isBlank(String.valueOf(list.get(7))))
                    throw new CustomException("第" + j + "题分数不能为空");
                question.setScore(Integer.parseInt((String) list.get(7)));

                if (StringUtils.isBlank(String.valueOf(list.get(8))))
                    throw new CustomException("第" + j + "题难度不能为空");
                question.setDifficulty(Integer.parseInt((String) list.get(8)));

                String courseName = (String) list.get(9);
                if (StringUtils.isBlank(String.valueOf(list.get(9))))
                    throw new CustomException("第" + j + "题课程不能为空");
                question.setCourseName(courseName);
                // 查找与指定课程名称相同的课程，并获取其ID
                Optional<Long> courseIdOptional = courseList.stream()
                        .filter(course -> course.getName().equals(courseName))
                        .map(Course::getId)
                        .findFirst();
                Long courseId;
                // 如果不存在相同名称的课程ID，则执行操作
                if (!courseIdOptional.isPresent()) {
                    Course course = new Course();
                    courseId = IdWorker.getId();
                    course.setId(courseId);
                    course.setName(courseName);
                    course.setIsDeleted(0);
                    courseService.save(course);
                    // 保存课程后立即获取最新的课程列表
                    courseList = courseService.list(queryWrapper);
                } else
                    courseId = courseIdOptional.get(); // 获取课程ID

                question.setCourseId(courseId);
                if (questionList.add(question))
//                if (questionService.save(question))
                    j++;
                else
                    throw new CustomException("第" + j + "题保存失败");
            }
            questionService.saveBatch(questionList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return R.ok().message("成功生成"+ j +"条问题");
        }
    }
}

接口需要接收一个MultipartFile类型的数据

用postman做示例

test.xls值就是我们的excel文件

搞定~