SpringBootexcel表格固定格式数据导入数据库

SpringBootexcel表格固定格式数据导入数据库（.xls格式）

1.创建文件导入的controller类

@ApiOperation(value = "文件上传", notes = "传入文件")
    @RequestMapping(value = "/eye", method = RequestMethod.POST)
    public CommonResult<String> eyeSight(@RequestParam MultipartFile file) {
        if (file.isEmpty()) {
            return CommonResult.validateFailed("上传为空！");
        }

        String url = null;
        try {
            url = ylwComponent.eyeComponent(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (!StringUtils.isEmpty(url)) {
            return CommonResult.success(url);
        }

        return CommonResult.failed("服务器错误，请联系管理员！");
    }

2. excel表格数据处理eyeComponent中

@Component
public class YlwComponent {
 @Resource
   private IEyeSightService eyeSightService;
   
public String eyeComponent(InputStream is) {

        try {
            HSSFWorkbook wb = new HSSFWorkbook(is);
            HSSFSheet sheet = wb.getSheetAt(0);
            int startRowIndex = 1;
            int endRowIndex = sheet.getLastRowNum();

            for(int i = startRowIndex;i<=endRowIndex;i++){
                //获取一行数据
                HSSFRow row = sheet.getRow(i);
                String name =  getCellValueByCell(row.getCell(0));
                String idcard = getCellValueByCell(row.getCell(1));
                String left_eye = getCellValueByCell(row.getCell(2));
                String right_eye = getCellValueByCell(row.getCell(3));
                String remark = getCellValueByCell(row.getCell(4));
                //数据更新
                EyeSightParam param = new EyeSightParam();
                param.setName(name);
                param.setIdcard(idcard);
                param.setLeft_eye(left_eye);
                param.setRight_eye(right_eye);
                param.setRemark(remark);
                //插入或者及更新数据接口自己不全能看到这里的人懂得都懂，再次强调.xls
                //数据插入更新（数据库里面有数据，可以根据表格中的字段进行选择对应的数据进行插入，这里插入语句选择的值是idcard）
                eyeSightService.update(idcard, param);
                //数据插入
//                eyeSightService.insert(param);

            }
            return "数据导入成功！";
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";;
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
                break;
        }
        //返回单元格值并去除首尾空格和去除空格
        return cellValue.trim().replace(" ","");
    }
}

3.表格示例模板

对应这五列数据，第一行数据没有取到（方便用户查看）

  String name =  getCellValueByCell(row.getCell(0));
  String idcard = getCellValueByCell(row.getCell(1));
  String left_eye = getCellValueByCell(row.getCell(2));
  String right_eye = getCellValueByCell(row.getCell(3));
  String remark = getCellValueByCell(row.getCell(4));

4. 提示文件内容

public class CommonResult<T> implements Serializable {

    /**
     * 结果码
     */
    @ApiModelProperty(value = "结果码")
    private Long code;

    /**
     * 提示信息
     */
    @ApiModelProperty(value = "提示信息")
    private String message;

    /**
     * 返回数据
     */
    @ApiModelProperty(value = "返回数据")
    private T data;

    protected CommonResult() {
    }

    private CommonResult(long code, String message, T data) {
        this.code = code;
        this.message = message;
        this.data = data;
    }

    /**
     * 返回成功结果
     */
    public static <T> CommonResult<T> success() {
        return new CommonResult<T>(ResultCodeEnum.SUCCESS.getCode(),
                ResultCodeEnum.SUCCESS.getMessage(), null);
    }

    /**
     * 成功返回结果
     *
     * @param data 获取的数据
     */
    public static <T> CommonResult<T> success(T data) {
        return new CommonResult<T>(ResultCodeEnum.SUCCESS.getCode(),
                ResultCodeEnum.SUCCESS.getMessage(), data);
    }

    /**
     * 成功返回结果
     *
     * @param data    获取的数据
     * @param message 提示信息
     */
    public static <T> CommonResult<T> success(T data, String message) {
        return new CommonResult<T>(ResultCodeEnum.SUCCESS.getCode(), message, data);
    }

    /**
     * 失败返回结果
     *
     * @param errorCode 错误码
     */
    private static <T> CommonResult<T> failed(IErrorCode errorCode) {
        return new CommonResult<T>(errorCode.getCode(), errorCode.getMessage(), null);
    }

    /**
     * 失败返回结果
     *
     * @param message 提示信息
     */
    public static <T> CommonResult<T> failed(String message) {
        return new CommonResult<T>(ResultCodeEnum.FAILED.getCode(),
                message, null);
    }

    /**
     * 失败返回结果
     */
    public static <T> CommonResult<T> failed() {
        return failed(ResultCodeEnum.FAILED);
    }

    /**
     * 参数验证失败返回结果
     */
    public static <T> CommonResult<T> validateFailed() {
        return failed(ResultCodeEnum.VALIDATE_FAILED);
    }

    /**
     * 参数验证失败返回结果
     *
     * @param message 提示信息
     */
    public static <T> CommonResult<T> validateFailed(String message) {
        return new CommonResult<T>(ResultCodeEnum.VALIDATE_FAILED.getCode(),
                message, null);
    }

    /**
     * 未登录返回结果
     */
    public static <T> CommonResult<T> unauthorized(T data) {
        return new CommonResult<T>(ResultCodeEnum.UNAUTHORIZED.getCode(),
                ResultCodeEnum.UNAUTHORIZED.getMessage(), data);
    }

    /**
     * 未授权返回结果
     */
    public static <T> CommonResult<T> forbidden(T data) {
        return new CommonResult<T>(ResultCodeEnum.FORBIDDEN.getCode(),
                ResultCodeEnum.FORBIDDEN.getMessage(), data);
    }

    public Long getCode() {
        return code;
    }

    public void setCode(Long code) {
        this.code = code;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "CommonResult{" +
                "code=" + code +
                ", message='" + message + '\'' +
                ", data=" + data +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }

        if (o == null || getClass() != o.getClass()) {
            return false;
        }

        CommonResult<?> that = (CommonResult<?>) o;
        return Objects.equals(code, that.code) &&
                Objects.equals(message, that.message) &&
                Objects.equals(data, that.data);
    }

    @Override
    public int hashCode() {
        return Objects.hash(code, message, data);
    }
}