springboot实现读取excel插入数据库

html:

<input type="file" name="fileName1" id="fileName1"/>
<input type="button" id="sendToUser" value="提交" />

JS

$('#sendToUser').click(function () {
        var $file1 = $("input[name='fileName1']").val();//用户文件内容(文件)
        // 判断文件是否为空
        if ($file1 == "") {
            alert("请选择上传的目标文件! ")
            return false;
        }
        //判断文件类型,我这里根据业务需求判断的是Excel文件
        var fileName1 = $file1.substring($file1.lastIndexOf(".") + 1).toLowerCase();
        if(fileName1 != "xls" && fileName1 !="xlsx"){
            alert("请选择Execl文件!");
            return false;
        }
        //判断文件大小
        var size1 = $("input[name='fileName1']")[0].files[0].size;
        if (size1>104857600) {
            alert("上传文件不能大于100M!");
            return false;
        }

        boo1 = true;
        var type = "file";
        var formData = new FormData();//这里需要实例化一个FormData来进行文件上传
        formData.append(type,$("#fileName1")[0].files[0]);
        //多文件上传在这里继续append
        //eg :
        //formData.append(type,$("#fileName1")[0].files[0]);
        $.ajax({
            type : "post",
            url : zzu+"/roughnes/importByExcel",
            data : formData,
            processData : false,
            contentType : false,
            success : function(data){
                if (data=="error") {
                    alert("文件提交失败!");
                }else{
                    $("input[name='userUrl']").val(data);
                    alert("文件上传成功!");
                }}
        });
    })

Controller

@PostMapping("importByExcel")
    @ResponseBody
    public String importByExcel(@RequestParam("file") MultipartFile file){

        roughnesService.importByExcel(file);
        return "success";
    }

Service

	@Override
    @Transactional(propagation = Propagation.REQUIRED)
    public void importByExcel(MultipartFile file) {
        //获取从excel中读取的数据转成的map集合
        Map<Integer, Map<Object,Object>> map = new HashMap<>();
        try {
            map = OfficeUtils.readExcelContentz(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //将class.toString() 返回的字符串转为列表,用作对map中的数据进行验证
        Roughnes roughnes = new Roughnes();
        String classParamStr = roughnes.toString();
        String finalStr = classParamStr.substring(classParamStr.indexOf("(") + 1)
                .replace("=null", "")
                .replace(")","")
                .replace(" ", "");
        System.out.println(finalStr);
        String[] split = finalStr.split(",");
        List<String> paramList = Arrays.asList(split);

        //初始化最终接收数据的列表
        ArrayList<Roughnes> roughnesList = new ArrayList<>();
        //对从excel中读取的数据进行遍历
        map.forEach((key,val) -> {
            //接收数据的实体对象 - 此处表示的是excel中的一行
            Roughnes finalRoughnes = new Roughnes();
            val.forEach((key1,val1) -> {
                //此处才是一行中的每个单元格
                //先对k,v进行字符串转换，方便数据验证和实体类类型转换
                String keyStr = String.valueOf(key1);
                String valStr = String.valueOf(val1);
                if (StringUtils.isEmpty(keyStr) || StringUtils.isEmpty(valStr)) {
                    return;
                }

                //对id的格式进行加工
                if ("id".equals(keyStr)) {
                    valStr = "X-" + valStr;
                }
                //数据验证
                if (paramList.contains(keyStr)) {
                    switch (keyStr) {
                        case "id" :
                            //向实体类set数据
                            finalRoughnes.setId(valStr);
                            break;
                        case "angle":
                            finalRoughnes.setAngle(Double.valueOf(valStr));
                            break;
                            case "p1Roughness" :
                            finalRoughnes.setP1Roughness(Double.valueOf(valStr));
                            break;
                        case "p2Roughness":
                            finalRoughnes.setP2Roughness(Double.valueOf(valStr));
                            break;
                            case "p3Roughness" :
                            finalRoughnes.setP3Roughness(Double.valueOf(valStr));
                            break;
                        case "p4Roughness":
                            finalRoughnes.setP4Roughness(Double.valueOf(valStr));
                            break;
                            case "p5Roughness" :
                            finalRoughnes.setP5Roughness(Double.valueOf(valStr));
                            break;
                        case "averageRoughne":
                            finalRoughnes.setAverageRoughne(Double.valueOf(valStr));
                            break;
                    }

                }
            });
            //将一行数据(一个实体)追加进列表
            roughnesList.add(finalRoughnes);
        });

        //迭代器移除操作
        /*Iterator<Roughnes> iterator = roughnesList.iterator();
        while (iterator.hasNext()) {
            if (iterator.next().getId() == null) {
                iterator.remove();
            }
        }*/

        //将空行移出，数据清洗后进入数据库
        roughnesList.removeIf(roughnes1 -> null == roughnes1.getId());
        roughnesMapper.insertBatch(roughnesList);
    }

Mapper

<insert id="insertBatch" parameterType="list">
    insert into roughnes
    (id, angle, p1_roughness, p2_roughness, p3_roughness, p4_roughness, p5_roughness,average_roughne)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.id},#{item.angle},#{item.p1Roughness},#{item.p2Roughness},#{item.p3Roughness},#{item.p4Roughness},#{item.p5Roughness},#{item.averageRoughne})
    </foreach>
  </insert>

OfficeUtils

public class OfficeUtils {
    protected static final Logger logger = LoggerFactory.getLogger(OfficeUtils.class);

    public static Map<Integer, Map<Object, Object>> readExcelContentz(MultipartFile file) throws Exception {
        Map<Integer, Map<Object, Object>> content = new HashMap<Integer, Map<Object, Object>>();
        // 上传文件名
        Workbook wb = getWb(file);
        if (wb == null) {
            throw new RuntimeException("文件上传失败");
        }
        Sheet sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        Row firstRowCell = sheet.getRow(0);
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Object, Object> cellValue = new HashMap<Object, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));

                //excel的表头是下划线命名，方便进行数据清洗，将表头命名改为驼峰命名
                StringBuilder str = new StringBuilder(String.valueOf(firstRowCell.getCell(j)));
                int o = 0;
                StringBuilder replaced = str;
                //递归实现，出现多个_组合也可
                while ((o = str.indexOf("_",o)) != -1) {
                    String replaceStr = String.valueOf(str.charAt(o + 1)).toUpperCase();
                    replaced = str.replace(o+1,o+2,replaceStr);
                    str.replace(o, o+1, "");
                    o+=1;
                }
                cellValue.put(replaced, obj);
                j++;
            }
            content.put(i, cellValue);

        }
        return content;
    }

    //根据Cell类型设置数据
    private static Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                    break;
                case FORMULA: {
                    cellvalue = cell.getDateCellValue();
                    break;
                }
                case STRING:
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    private static Workbook getWb(MultipartFile mf) {
        String filepath = mf.getOriginalFilename();
        String ext = filepath.substring(filepath.lastIndexOf("."));
        Workbook wb = null;
        try {
            InputStream is = mf.getInputStream();
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
        return wb;
    }
}