使用Excel批量添加数据

使用XSSF Excel批量导入

大致的思路：先使用XSSF读取到数据，保存在list中的对象，验证数据，循环添加
XSSF的过程：读取所有行数–》开始循环（第一层）—》读取列数—》开始循环（第二层）–》读取当前行列的数据添加到对象中—》循环结束—》将对象add到list中

HSSF的思路差不多
HSSF：读取.xsl文件也就是03版的Excel
XSSF：读取.xslx文件也就是07版的Excel

使用HSSF/XSSF会非常耗费内存建议使用EasyExcel
EasyExcel的使用

public Result<?> dataImport(String path) {
        Result<?> result = new Result<>();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
        List<BizCardquality> bizCardqualities = new ArrayList<>();
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        XSSFCell cell=null;
        // 读取数据
        try {
            //开始读取
            workbook = new XSSFWorkbook(new FileInputStream(new File(uploadpath +"//"+path)));
            sheet = workbook.getSheet("damage tracing");
            int lastRowNum = ExcelUtils.getRowNums(sheet);
            for (int i = 1; i <= lastRowNum; i++) {
                BizCardquality bizCardquality = new BizCardquality();
                XSSFRow row = sheet.getRow(i);
                List list = new ArrayList();
                short lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    //获取数据
                    cell = row.getCell(j);
                    if (null == cell) continue;
                    cell.setCellType(1);
                    String cellValue = ExcelUtils.getXCellValue(cell);
                    cellValue = cellValue.trim();
                    list.add(cellValue);
                    if (cellValue != null) {
                        if (j == 0) {
                            // 报告编号
                            bizCardquality.setCqReportno(cellValue);
                            continue;
                        }
                        if (j == 1) {
                            // VIN码
                            bizCardquality.setCqVehicleVin(cellValue);
                            continue;
                        }
                        if (j == 2) {
                            // 车型
                            bizCardquality.setCqVehicleModel(cellValue);
                            continue;
                        }
                        if (j == 3) {
                            // 点检地点
                            bizCardquality.setCqCheckingPoint(cellValue);
                            //填充质损场站
                            String station  = getStationIdBystsAbbrename(cellValue.substring(0,cellValue.indexOf("-")));
                            if (station==null){
                                return Result.error("点检地点未查询到");
                            }
                            bizCardquality.setCqDamageplace(station);
                            continue;
                        }
                        if (j == 4) {
                            // 发动机缸数
                            bizCardquality.setCqTerminal(cellValue);
                            continue;
                        }
                        if (j == 5) {
                            // 方向
                            bizCardquality.setCqDerection(cellValue);
                            continue;
                        }
                        if (j == 6) {
                            // 班次号
                            bizCardquality.setCqTrainNumber(cellValue);
                            continue;
                        }
                        if (j == 7) {
                            //实际发运时间ATD
                            if(StringUtils.isEmpty(cellValue)){
                                bizCardquality.setCqAtd(null);
                                continue;
                            }
                            bizCardquality.setCqAtd((dateFormat.parse(cellValue)));
                            continue;
                        }
                        if (j == 8) {
                            //"ATD WK
                            //发运周数"
                            bizCardquality.setCqAtdWk(cellValue);
                            continue;
                        }
                        if (j == 9) {
                            //"ATA
                            //实际到达时间"
                            if(StringUtils.isEmpty(cellValue)){
                                bizCardquality.setCqAta(null);
                                continue;
                            }
                            bizCardquality.setCqAta((dateFormat.parse(cellValue)));
                            continue;
                        }
                        if (j == 10) {
                            //"ATA WK
                            //到达周数"
                            bizCardquality.setCqAtaWk(cellValue);
                            continue;
                        }
                        if (j == 11) {
                            // 损坏编码
                            bizCardquality.setCqDamagecode(cellValue);
                            continue;
                        }
                        if (j == 12) {
                            // 损坏部位编码
                            bizCardquality.setCqDamagepartcode(cellValue);
                            continue;
                        }
                        // 损坏类型编码
                        if (j == 13) {
                            bizCardquality.setCqDamagetypecode(cellValue);
                            continue;
                        }
                        if (j == 14) {
                            // 损坏部位
                            bizCardquality.setCqDamagepart(cellValue);
                            continue;
                        }
                        if (j == 15) {
                            // 损坏类型
                            bizCardquality.setCqDamagetype(cellValue);
                            continue;
                        }
                        if (j == 16) {
                            //Instruction from vv vv指令
                            bizCardquality.setCqInstructions(cellValue);
                        }
                        if (j == 17) {
                            //Qualitative nature 质损性质（PDI)
                            bizCardquality.setCqQualitativeNature(cellValue);
                        }
                        if (j == 18) {
                            //Damgede typetwo
                            bizCardquality.setCqDamagetypeTwo(cellValue);
                        }
                        if (j == 19) {
                            // 损坏初始
                            bizCardquality.setCqDamagefirstseen(cellValue);
                            String station  = getStationIdBystsAbbrename(cellValue.substring(0,cellValue.indexOf("-")));
                            if (station==null){
                                return Result.error("损坏初始地未查询到");
                            }
                            bizCardquality.setCqFirstdamageplace(station);
                            continue;
                        }
                        if (j == 20) {
                            //责任方
                            bizCardquality.setCqResponsibleparty(cellValue);
                            continue;
                        }
                        if (j == 21) {
                            //检验方
                            bizCardquality.setCqInspectionGroup(cellValue);
                            continue;
                        }
                        if (j == 22) {
                            //检验日期
                            if(StringUtils.isEmpty(cellValue)){
                                bizCardquality.setCqInspectionDate(null);
                                continue;
                            }
                            bizCardquality.setCqInspectionDate((dateFormat.parse(cellValue)));
                            continue;
                        }
                        if (j == 23) {
                            //文件地址
                            bizCardquality.setCqPath(cellValue);
                            continue;
                        }
                        if (j == 24) {
                            // 备注
                            bizCardquality.setCqRemark(cellValue);
                            continue;
                        }

                    }
                }
                bizCardqualities.add(bizCardquality);
            }

        }
        catch (IOException | ParseException e) {
            log.error("读取数据失败",e);
            e.printStackTrace();
        }

        if (bizCardqualities != null) {
            for (BizCardquality bizCardquality : bizCardqualities) {
                //校验是否有重复数据
                Boolean check =bizCardqualityMapper.insertCheck(bizCardquality)==null?false:true;
                //校验Vin是否有效
                Boolean checkVin =bizCardqualityMapper.VinCheck(bizCardquality.getCqVehicleVin())==null?true:false;
                if(checkVin){
                    result.setSuccess(false);
                    result.setMessage("未找到该车辆VIN码："+bizCardquality.getCqVehicleVin()+"不存在!");
                    return result;
                }
                if(check){
                    result.setSuccess(false);
                    result.setMessage("添加失败，"+bizCardquality.getCqDamagecode()+"或"+bizCardquality.getCqVehicleVin()+"重复!");
                    return result;
                }
            }
            for (BizCardquality bizCardquality : bizCardqualities) {
                // 添加
                int insert = bizCardqualityMapper.insert(bizCardquality);
                //通过vin修改车辆信息为质损
                bizCardqualityMapper.updateCar(1,bizCardquality.getCqVehicleVin());
                if (insert == 0) {
                    result.setSuccess(false);
                    result.setMessage("导入失败!");
                    return result;
                }
                try {
                    // 文件保存
                    BizUpfileinfo upfileinfo = new BizUpfileinfo();
                    // 相对路径
                    upfileinfo.setUfiPath(path);
                    // 业务id
                    upfileinfo.setUfiBizid(bizCardquality.getId());
                    // 业务名称
                    upfileinfo.setUfiBizname("车辆质损记录导入");
                    upfileinfoService.insertNewPosition(upfileinfo);
                } catch (IOException ioException) {
                    ioException.printStackTrace();
                }
            }
        }
        return result;
    }