后台导入功能实现

需求:项目是前后台分离,前台直接上传附件,后台解析并存储数据,持久层采用jdbc批量存储,性能更优

pom包依赖:

        <dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.16</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.16</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.16</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.16</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>ooxml-schemas</artifactId>
			<version>1.3</version>
		</dependency>

代码实现:

//结果返回集,用的我之前博文里的result返回,可以查看我的其他博文
public Result<Map<String,Object>> importclazz(@RequestParam("file") MultipartFile file){
        if (!this.isExcel(Objects.requireNonNull(file.getOriginalFilename(), "请上传文件！")))
            return ResultUtil.error("文件格式不正确！");
        try {
            List<Map<String, Object>> dataList = this.excelToList(file.getInputStream());
            if (CollectionUtils.isEmpty(dataList))
                return ResultUtil.error("上传内容不能为空！");
            List<ClassScore> batchclassScore = new ArrayList<ClassScore>();
            DecimalFormat df = new DecimalFormat("0.0");

            for (Map<String, Object> map : dataList) {
                ClassScore classScore = new ClassScore();
                classScore.setIfRegular(MapUtils.getString(map, "column1"));
                classScore.setClassName(MapUtils.getString(map, "column2"));
                //时间格式处理                classScore.setExamTime(HSSFDateUtil.getJavaDate(Double.valueOf(MapUtils.getString(map, "column3"))));
                batchclassScore.add(classScore);
            }
            scoreService.batchSaveClazz(batchclassScore);
        } catch (IOException e) {
            logger.error("ScoreImportController-->importclazz: 获取文件失败.", e);
            return ResultUtil.error("请检查内容格式是否正确！");
        }

        return ResultUtil.success("导入成绩成功");
    }

附件处理工具:

/**
*判断是否是excel表格
*/
public static boolean isExcel(String fileName) {
        // 匹配.xls|.xlsx正则
        String regex = "^.*\\.(?:xlsx|xls)$";
        return fileName.matches(regex);
    }

    /**
     * @param input 文件流
     * @return List<Map < ( column + 列数 ), 列值>>
     * @description 将excel数据转换为List，限一个sheet
     */
    public  List<Map<String, Object>> excelToList(InputStream input) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(input);
            if (null == workbook)
                throw new Exception("创建Excel工作薄为空！");
            Sheet sheet = workbook.getSheetAt(0);
            // 得到标题行
            Row titleRow = sheet.getRow(0);
            int lastRowNum = sheet.getLastRowNum();
            int lastCellNum = titleRow.getLastCellNum();
            for (int i = 1; i <= lastRowNum; i++) {
                Map<String, Object> map = new HashMap<String, Object>();
                Row row = sheet.getRow(i);
                for (int j = 0; j < lastCellNum; j++) {
                    // 得到列名
                    String key = titleRow.getCell(j).getStringCellValue();
                    Cell cell = row.getCell(j);
                    cell.setCellType(CellType.STRING);
                    map.put("column" + (j + 1), cell.getStringCellValue());
                }
                list.add(map);
            }
        } catch (Exception e) {
            logger.error("ExcelUtil-->excelToList: Excel conversion failed .", e);
        } finally {
            try {
                if (null != input)
                    input.close();
                if (null != workbook)
                    workbook.close();
            } catch (Exception e) {
                logger.error("ExcelUtil-->excelToList: stream close failed .", e);
            }
        }
        return list;
    }

数据持久层处理方式:

//批量插入的关键是把list数组转化为Object类型

public static final int BATCH_SIZE = 5000;

 private List<Object[]> transformclassScoreToObjects(List<ClassScore> classScores) {

        List<Object[]> list = new ArrayList<>();

        Object[] object = null;
        for(ClassScore classScore :classScores){
            object = new Object[]{
                    classScore.getClassName(),
                    classScore.getExamTime(),
                    classScore.getNumber(),
                    classScore.getTeacherId(),
                    classScore.getTeacherName(),
                    classScore.getSubject(),
                    classScore.getQuality(),
                    classScore.getQualityGrade(),
                    classScore.getAverage(),
                    classScore.getAverageGrade(),
                    classScore.getExcellent(),
                    classScore.getExcelentGrade(),
                    classScore.getPass(),
                    classScore.getPassGrade(),
                    classScore.getIfRegular(),
            };
            list.add(object);
        }

        return list ;
    }

然后存储入表操作:

 public void batchSaveClazz(List<ClassScore> batchclassScore) {
        String sql =" INSERT INTO class_score (className, examTime, number, teacherId ,teacherName, subject, " +
                " quality, qualityGrade, average, averageGrade, excellent, excelentGrade, " +
                " pass, passGrade,ifRegular ) " +
                " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?) ";
        List<Object[]> args = transformclassScoreToObjects(batchclassScore);
        int fromIndex = 0; int toIndex = BATCH_SIZE;
        while (fromIndex != args.size()) {
            if (toIndex > args.size()) {
                toIndex = args.size();
            }
            this.jdbc.batchUpdate(sql,args.subList(fromIndex, toIndex));
            fromIndex = toIndex;
            toIndex += BATCH_SIZE;
            if (toIndex > args.size())
                toIndex = args.size();
        }
    }

以上,如有不清楚错误地方,欢迎指出交流