Java 导入复杂excel,多表格中有合并以及描述

接口返回类

package com.hs.report.utils;

import com.hs.report.enums.ResultCode;
import org.apache.commons.lang.StringUtils;

import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;

public class Result <T> extends BaseDomain implements Serializable {
    private static final long serialVersionUID = -1117047807265427246L;
    private int status;
    private String msg;
    private T data;

    public Result() {
        this.status = ResultCode.SUCCESS.getStatus();
        this.msg = ResultCode.SUCCESS.getMsg();
    }

    public Result(T data) {
        this.setData(data);
        this.status = ResultCode.SUCCESS.getStatus();
        this.msg = ResultCode.SUCCESS.getMsg();
    }

    public Result(int status, String msg) {
        this.setStatus(status);
        this.setMsg(msg);
    }

    public Result(int status, String msg, T data) {
        this.setStatus(status);
        this.setMsg(msg);
        this.setData(data);
    }

    public Result<T> setError(int status, String msg) {
        this.setStatus(status);
        this.setMsg(msg);
        return this;
    }

    public Result<T> setError(ResultCode errorCode) {
        this.setStatus(errorCode.getStatus());
        this.setMsg(errorCode.getMsg());
        return this;
    }

    public boolean isSuccess() {
        return this.getStatus() == ResultCode.SUCCESS.getStatus();
    }

    public static  Result instance() {
        return new Result();
    }

    public static <T> Result instance(T data) {
        return new Result(data);
    }

    public static <T> Result instance(int status, String msg) {
        return new Result(status, msg);
    }

    public static <T> Result instance(int status, String msg, T data) {
        return new Result(status, msg, data);
    }

    public int getStatus() {
        return this.status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getMsg() {
        return this.msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public void setPeriodIdMsg(Integer periodId){
        this.msg = ResultCode.SUCCESS.getMsg() + "_" + periodId;
    }

    public T getData() {
        return this.data;
    }

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

    public Map<String,Object> toJsonMap(){
        Map<String,Object> map = new HashMap<>();
        map.put("data",this.data);
        map.put("msg",this.msg);
        map.put("status",this.status);
        return map;
    }

}

实体类

package com.hs.report.entity.week;
import lombok.Builder;
import lombok.Data;
import java.io.Serializable;
@Data
@Builder
public class SnapshotReq<T> implements Serializable {
    private static final long serialVersionUID = 4403232919132430023L;
    private String type;
    private Integer offset;
	/**
	*json字符串
	**/
    private T content;

    private Integer subNo;

    private Integer periodId;

    private String flowId;
}

Controller层

package com.hs.report.controller.week;

import com.hs.report.entity.week.SnapshotReq;
import com.hs.report.service.week.ParseExcelForGSService;
import com.hs.report.service.week.ParseExcelForOTCService;
import com.hs.report.service.week.ParseExcelForZTService;
import com.hs.report.utils.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.List;

@Slf4j
@Api(tags = "导入Excel模板")
@RestController
@RequestMapping("/investmentReport/week")
public class ImportWeekExcelController {

    @Autowired
    private ParseExcelForZTService parseExcelForZTService;

    @Autowired
    private ParseExcelForGSService parseExcelForGSService;

    @Autowired
    private ParseExcelForOTCService parseExcelForOTCService;

    @ApiOperation(value = "导入")
    @PostMapping("/import")
    public Result<List<SnapshotReq>> importExcel(@RequestParam("file") MultipartFile file, @RequestParam(value = "type") Integer type) throws Exception {
        log.info("开始解析Excel, type==={}", type);

        Result<List<SnapshotReq>> result = new Result<>();
        List<SnapshotReq> snapshotReqs = null;

        InputStream inputStream = null;
        Workbook wb = null;
        try{
            inputStream = file.getInputStream();
            String fileName = file.getOriginalFilename();

            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if(".xls".equals(fileType)){
                wb= new HSSFWorkbook(inputStream);
            }else if(".xlsx".equals(fileType)) {
                wb = WorkbookFactory.create(inputStream);
            }

            switch(type){
                //证投
                case 1:
                    snapshotReqs = parseExcelForZTService.parseExcelForZT(wb);
                    break;
                //固收
                case 2:
                    snapshotReqs = parseExcelForGSService.parseExcelForGS(wb);
                    break;
                //OTC
                case 3:
                    snapshotReqs = parseExcelForOTCService.parseExcelForOTC(wb);
                    break;
            }

            inputStream.close();
            result.setData(snapshotReqs);
            log.info("结束解析Excel, type==={}", type);
        }catch (Exception ex){
            throw new Exception("导入Excel出现异常，异常信息：" + ex.getMessage());
        }

        return result;
    }


}

Service层

package com.hs.report.service.impl.week;

import com.hs.report.entity.week.BusinessTypeDO;
import com.hs.report.entity.week.LossPayfDO;
import com.hs.report.entity.week.SnapshotReq;
import com.hs.report.entity.week.WeekLossPayfDO;
import com.hs.report.service.week.ParseExcelForGSService;
import org.apache.commons.lang.StringUtils;
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.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Service
public class ParseExcelForGSServiceImpl implements ParseExcelForGSService {
    @Override
    public List<SnapshotReq> parseExcelForGS(Workbook wb) throws InstantiationException, IllegalAccessException  {
        Sheet sheet = wb.getSheetAt(0);
        List<SnapshotReq> result = new ArrayList<>();
        int rowIndex = 0;

        //本周重点推进工作：描述
        int weekRowNum = parseTextValue(sheet, 1,rowIndex + 2, 0, result);

        //本周重点推进工作：描述
        int lastWeekRowNum = parseTextValue(sheet, 2, weekRowNum + 3, 0, result);

        //T0周损益对比：描述
        int weekLossRowNum = parseTextValue(sheet, 3, lastWeekRowNum + 3, 0, result);

        //第一个表格内容
        int oneTitleText = parseTableValue(sheet, 4, weekLossRowNum + 4, result, WeekLossPayfDO.class);

        //市场状况及交易部门损益
        int marketRowNum = parseTextValue(sheet, 5, weekLossRowNum + 3 + oneTitleText + 3, 0, result);

        //第二个表格内容
        int twoTitleText = parseTableValue(sheet, 7, marketRowNum + 4, result, LossPayfDO.class);

        //固定收益部损益分析
        int fixedRowNum = parseTextValue(sheet, 10, marketRowNum + 3 + twoTitleText + 4, 0, result);

        //第三个表格内容
        int threeTitleText = parseThreeTableValue(sheet, 11, fixedRowNum + 3, result);

        //固定收益部
        int dailyRowNum = parseTextValue(sheet, 12, fixedRowNum + 3 + threeTitleText + 3, 0, result);

        return result;
    }

    //解析文本框内容
    private int parseTextValue(Sheet sheet, int offset, int rowIndex, int cellIndex, List<SnapshotReq> result){
        SnapshotReq snapshotReq = null;
        Cell cell = sheet.getRow(rowIndex).getCell(cellIndex);
        snapshotReq = SnapshotReq.builder().offset(offset).content(cell.getStringCellValue()).build();
        result.add(snapshotReq);
        return rowIndex;
    }

    //解析第三表格部分 有合并列
    private <T> int parseThreeTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result) {
        Row row = null;
        Cell cell = null;
        SnapshotReq snapshotReq = null;
        String perPurpose = "";//上一个数据描述
        String purpose = "";//当前数据描述
        boolean flag = true;//用来记录是否是第一次循环
        //判断有几条数据
        int oneTitleText = 0;
        List<Object> dataList = new ArrayList<>();
        for(int i = rowIndex;i < sheet.getLastRowNum();i++){
            BusinessTypeDO businessTypeDO = new BusinessTypeDO();
            row = sheet.getRow(i);
            //判断此行的每一列是否都为空
            if(judgeWhetherNull(row)){
                oneTitleText++;
                break;
            }

            if(row != null){
                for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                    cell = row.getCell(j);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    purpose = cell.getStringCellValue();
                    if(j == 0){

                        if(StringUtils.isNotEmpty(purpose) && !purpose.equals(perPurpose)){
                            flag = true;
                        }

                        if(flag){//如果是第一次循环，将perPurpose初始化，以便后面的第一次判断
                            perPurpose = purpose;
                            flag = false;
                        }

                        if(StringUtils.isEmpty(purpose) || purpose.equals(perPurpose)){
                            purpose = perPurpose;
                        }else{
                            purpose = cell.getStringCellValue();
                        }
                    }

                    getThreeTitleText(businessTypeDO, purpose, j);
                }
                dataList.add(businessTypeDO);

            }else{
                break;
            }
            oneTitleText++;
        }
        snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
        result.add(snapshotReq);

        return oneTitleText - 1;
    }


    //解析表格内容
    private <T> int parseTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result, Class<T> tClass)throws IllegalAccessException, InstantiationException{
        Row row = null;
        Cell cell = null;
        SnapshotReq snapshotReq = null;
        //判断有几条数据
        int oneTitleText = 0;
        List<Object> dataList = new ArrayList<>();
        for(int i = rowIndex;i < sheet.getLastRowNum();i++){
            Object object = tClass.newInstance();
            row = sheet.getRow(i);
            //判断此行的每一列是否都为空
            if(judgeWhetherNull(row)){
                if(i == rowIndex){
                    oneTitleText = 2;
                }else{
                    oneTitleText++;
                }
                break;
            }

            if(row != null){
                for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                    cell = row.getCell(j);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    generateData(object, cell.getStringCellValue(), j);
                }
                dataList.add(object);

            }else{
                break;
            }
            oneTitleText++;
        }
        snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
        result.add(snapshotReq);

        return oneTitleText - 1;
    }

    private <T> void generateData(T object, String value, int cellIndex){
        if(object instanceof WeekLossPayfDO){
            getOneTitleText((WeekLossPayfDO) object, value, cellIndex);
        }else if(object instanceof LossPayfDO){
            getTwoTitleText((LossPayfDO) object, value, cellIndex);
        }
    }

    private void getThreeTitleText(BusinessTypeDO businessTypeDO, String value, int cellIndex) {
        switch(cellIndex){
            case 0:
                businessTypeDO.setTitle(value);
                break;
            case 2:
                businessTypeDO.setSubtitle(getContentStr(value));
                break;
            case 4:
                businessTypeDO.setValue(getContentStr(value));
                break;
        }
    }

    private String getContentStr(String content) {
        return StringUtils.isNotEmpty(content) ? content : "";
    }

    private boolean judgeWhetherNull(Row row) {
    	if(row == null){
            return true;
        }
        int count = 0;
        //单元格数量
        int rowCount = row.getLastCellNum() - row.getFirstCellNum();
        for (int c = 0; c < rowCount; c++) {
            Cell cell = row.getCell(c);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtils.isEmpty((cell+"").trim())){
                count += 1;
            }
        }

        if (count == rowCount) {
            return true;
        }
        return  false;
    }

    private void getTwoTitleText(LossPayfDO lossPayfDO, String value, int cellIndex) {
        Double data = null;
        if(cellIndex != 0){
            data = StringUtils.isNotEmpty(value) ? Double.valueOf(value) : 0.0;
        }
        switch (cellIndex){
            case 0:
                lossPayfDO.setDepName(getContentStr(value));
                break;
            case 1:
                lossPayfDO.setRiskExpo(data);
                break;
            case 2:
                lossPayfDO.setGsck(data);
                break;
            case 3:
                lossPayfDO.setActlMval(data);
                break;
            case 4:
                lossPayfDO.setFutrNetPos(data);
                break;
            case 5:
                lossPayfDO.setWeekLossPayf(data);
                break;
            case 6:
                lossPayfDO.setMthLossPayf(data);
                break;
            case 7:
                lossPayfDO.setYearLossPayf(data);
                break;
        }
    }

    private void getOneTitleText(WeekLossPayfDO weekLossPayfDO, String value, int cellIndex) {
        BigDecimal data = null;
        if(cellIndex != 0){
            data = StringUtils.isNotEmpty(value) ? new BigDecimal(value) : null;
        }
        switch (cellIndex){
            case 0:
                weekLossPayfDO.setDeptName(getContentStr(value));
                break;
            case 2:
                weekLossPayfDO.setWeekLoss(data);
                break;
            case 4:
                weekLossPayfDO.setLastWeekLoss(data);
                break;
            case 6:
                weekLossPayfDO.setYearLoss(data);
                break;
        }
    }
}

一般常规办法：先使用POI或者HSSFWorkbook等第三方类库对其表格数据结构化，再用SQL语句写入数据库。由于Java并没有表格对象，总要利用集合加实体类去实现(硬编码)，如果碰到格式复杂的表格，解析难度大，工作量会成倍增加，代码不仅冗长、且很难通用。

比如要处理这么个场景:数据库表Logistics有3个字段：Shippers、Region、Quantity。解析如下Excel表格，并入库:

入库后的效果：

Java代码大概要写成这样子：

如果有了集算器，这样的问题则会简单很多，它是专业处理结构化数据的语言，能够轻松读取Excel数据，结构化成“序表”后导入数据库。以往需要编写数千行代码才能完成的Excel数据结构化入库工作，现在只需简单的几行就搞定了。比如上面的问题，集算器SPL仅3行：

其实还有很多类似的结构化问题不太方便，但有集算器SPL的辅助却很简单，感兴趣可以参考：复杂Excel表格导入导出的最简方法、10行代码提取复杂Excel数据

集算器还很容易嵌入到Java应用程序中，Java如何调用SPL脚本有使用和获得它的方法。

关于集算器安装使用、获得免费授权和相关技术资料，可以参见如何使用集算器。

 图片不清晰，请多担待

pom.xml依赖

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>${poi.version}</version>
</dependency>

上代码：

import com.ruoyi.common.exception.CustomException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileUtils;
import com.ruoyi.system.domain.VehicleInfo;
import com.ruoyi.system.domain.VehicleInsurance;
import com.ruoyi.system.domain.VehicleTotalmile;
import com.ruoyi.system.domain.vo.VehicleInfoImportVo;
import com.ruoyi.system.mapper.sysdict.DictDataMapper;
import com.ruoyi.system.mapper.vehicle.VehicleInfoMapper;
import com.ruoyi.system.mapper.vehicle.VehicleInsuranceMapper;
import com.ruoyi.system.mapper.vehicle.VehicleTotalmileMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Objects;
import java.util.UUID;

import static org.apache.poi.ss.usermodel.CellType.*;

@Slf4j
@Service
public class VehicleImportService {

    @Autowired
    private DictDataMapper dictDataMapper;
    @Autowired
    private VehicleInfoMapper vehicleInfoMapper;
    @Autowired
    private VehicleInsuranceMapper vehicleInsuranceMapper;
    @Autowired
    private VehicleTotalmileMapper vehicleTotalmileMapper;


    /**
     * 读取excel文件
     *
     * @param path 文件流
     * @param operName 操作人名称
     * @param sheetIndex    sheet页下标：从0开始
     * @param startReadLine 开始读取的行:从0开始
     * @param tailLine      结束行
     */
    public String importVehicleInfo(MultipartFile path, String operName, int sheetIndex, int startReadLine, int tailLine) {
        Workbook wb = null;
        File file = null;
        try {
            file=File.createTempFile(path.getOriginalFilename(), null);
            path.transferTo(file);
            file.deleteOnExit();
            wb = WorkbookFactory.create(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //读取excel表中的sheet, 参数为sheet的索引值（从0开始）
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        Boolean save = false;
        //获取该sheet的最后一行数据的索引
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 2){
            throw new CustomException("导入模板不正确！");
        }
        if (lastRowNum == 2){
            throw new CustomException("导入车辆数据不能为空！");
        }

        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        ArrayList<VehicleInfoImportVo> list = new ArrayList<>();
        //外循环是循环行，内循环是循环每行的单元格
        for (int i = 3; i <= lastRowNum; i++) {
            //车辆信息导入对象
            VehicleInfoImportVo vehicleInfoImportVo = new VehicleInfoImportVo();

            //车辆保险对象
            VehicleInsurance vehicleInsurance = new VehicleInsurance();
            //商业保险费
            BigDecimal commercialInsurPrice = BigDecimal.ZERO;
            //承运险
            BigDecimal carrierRisk = BigDecimal.ZERO;
            //交强险保费
            BigDecimal compulsoryInsurPrice = BigDecimal.ZERO;
            //商业保险期限
            String commercialInsurDate = "";
            //承运险期限
            String carrierRiskDate = "";
            //交强险期限
            String compulsoryInsurDate = "";
            //保险号数组
            String[] insurNums = null;

            //车辆里程对象
            VehicleTotalmile vehicleTotalmile = new VehicleTotalmile();
            
            //根据行索引获取行对象（单元格集合）
            row = sheet.getRow(i);
            //遍历行的单元格，并解析
            for (Cell c : row) {
                String returnStr = "";
                String value = "";
                //设置该单元格的数据的类型为String
                c.setCellType(STRING);
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                // 判断是否具有合并单元格
                if (isMerge) {
                    //如果是合并单元格，就获取合并单元格的值
                    returnStr = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()).toString();
                } else {
                    //不是合并单元格直接获取单元格的值
                    returnStr = getCellValue(c).toString();
                }
                if (StringUtils.isNotEmpty(returnStr)) {
                    value = returnStr.trim();
                    
                    //封装结果集，一行数据封装为一个对象
                    if (c.getColumnIndex() == 0) {
                        System.out.println("序号："+value);
                    } else if (c.getColumnIndex() == 1) {
                        System.out.println("车牌号："+value);
                        vehicleInfoImportVo.setVehicleNo(value);
                    } else if (c.getColumnIndex() == 2) {
                        System.out.println("车牌颜色："+value);
                        vehicleInfoImportVo.setPlateColor(value);
                    } else if (c.getColumnIndex() == 3) {
                        System.out.println("核定载客数："+value.replaceAll("人", ""));
                    } else if (c.getColumnIndex() == 4) {
                        System.out.println("车辆厂牌："+value);
                        vehicleInfoImportVo.setBrand(value);
                    } else if (c.getColumnIndex() == 5) {
                        System.out.println("车辆型号："+value);
                        vehicleInfoImportVo.setModel(value);
                    } else if (c.getColumnIndex() == 6) {
                        System.out.println("车辆类型："+value);
                    } else if (c.getColumnIndex() == 7) {
                        System.out.println("车辆所有人："+value);
                        vehicleInfoImportVo.setOwnerName(value);
                    } else if (c.getColumnIndex() == 8) {
                        System.out.println("车身颜色："+value);
                        vehicleInfoImportVo.setVehicleColor(value);
                    } else if (c.getColumnIndex() == 9) {
                        System.out.println("发动机号："+value);
                        vehicleInfoImportVo.setEngineId(value);
                    } else if (c.getColumnIndex() == 10) {
                        System.out.println("车架号："+value);
                        vehicleInfoImportVo.setVin(value);
                    } else if (c.getColumnIndex() == 11) {
                        System.out.println("保险公司名称："+value);
                        vehicleInsurance.setInsurCom(value);
                        vehicleInsurance.setVehicleNo(vehicleInfoImportVo.getVehicleNo());
                    } else if (c.getColumnIndex() == 12) {
                        System.out.println("保险号："+value);
                        insurNums = value.split("；");
                    } else if (c.getColumnIndex() == 13) {
                        System.out.println("车船税："+value);
                    } else if (c.getColumnIndex() == 14) {
                        System.out.println("商业保险费："+value);
                        commercialInsurPrice = new BigDecimal(value);
                    } else if (c.getColumnIndex() == 15) {
                        System.out.println("承运险保费："+value);
                        carrierRisk = new BigDecimal(value);
                    } else if (c.getColumnIndex() == 16) {
                        System.out.println("交强险保费："+value);
                        compulsoryInsurPrice = new BigDecimal(value);
                    } else if (c.getColumnIndex() == 17) {
                        System.out.println("保险总金额(人民币元）："+value);
                    } else if (c.getColumnIndex() == 18) {
                        System.out.println("承运险保险期限："+value);
                        carrierRiskDate = value;
                    } else if (c.getColumnIndex() == 19) {
                        System.out.println("交强险保险期限："+value);
                        compulsoryInsurDate = value;
                    } else if (c.getColumnIndex() == 20) {
                        System.out.println("商业险保险期限："+value);
                        commercialInsurDate = value;
                    } else if (c.getColumnIndex() == 21) {
                        System.out.println("年审时间："+value);
                    } else if (c.getColumnIndex() == 22) {
                        System.out.println("行驶公里数："+value);
                        vehicleTotalmile.setVehicleNo(vehicleInfoImportVo.getVehicleNo());
                        vehicleTotalmile.setTotalMile(new BigDecimal(value));
                    }
                }
            }
            //判断一个对象的属性是否都为空， true:都为空  ， false: 不都为空
            if (!allFieldIsNULL(vehicleInfoImportVo)) {
                Date date = DateUtils.getNowDate();
                vehicleInsurance.setUpdateTime(date);
                vehicleTotalmile.setUpdateTime(date);
                vehicleInfoImportVo.setUpdateBy(operName);
                vehicleInfoImportVo.setId(UUID.randomUUID().toString().replaceAll("-",""));
                try {
                    // 验证是否存在这个车辆
                    VehicleInfo vehicle = vehicleInfoMapper.selectVehicleInfoByVehicleNo(vehicleInfoImportVo.getVehicleNo());
                    if (vehicle == null){
                        vehicleInfoImportVo.setCreateTime(date);
                        VehicleInfo vehicleInfo = new VehicleInfo();
                        // 复制 vehicleInfoImportVo 到 vehicleInfo
                        BeanUtils.copyProperties(vehicleInfoImportVo, vehicleInfo);
                        vehicleInfo.setSeats(Integer.valueOf(vehicleInfoImportVo.getSeats()));
                        if (vehicleInfoMapper.insertVehicleInfo(vehicleInfo) > 0){
                            successNum++;
                            successMsg.append("<br/>" + successNum + "、车辆 " + vehicleInfoImportVo.getVehicleNo() + " 导入成功");
                        } else {
                            log.info("导入车辆信息，插入车辆信息失败，车牌号：{}", vehicleInfoImportVo.getVehicleNo());
                        }
                    } else {
                        vehicleInfoImportVo.setUpdateTime(date);
                        VehicleInfo vehicleInfo = new VehicleInfo();
                        // 复制 vehicleInfoImportVo 到 vehicleInfo
                        BeanUtils.copyProperties(vehicleInfoImportVo, vehicleInfo);
                        vehicleInfo.setSyncflag(0);
                        if (vehicleInfoMapper.updateVehicleInfo(vehicleInfo) > 0){
							successNum++;
                            successMsg.append("<br/>" + successNum + "、车辆 " + vehicleInfoImportVo.getVehicleNo() + " 更新成功");
                        } else {
                            log.info("导入车辆信息，更新车辆信息失败，车牌号：{}", vehicleInfoImportVo.getVehicleNo());
                        }
                    }
                } catch (Exception e) {
                    failureNum++;
                    String msg = "<br/>" + failureNum + "、车辆 " + vehicleInfoImportVo.getVehicleNo() + " 导入失败：";
                    failureMsg.append(msg + e.getMessage());
                    log.error(msg, e);
                }
            }
        }
        if (failureNum > 0) {
            failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
            throw new CustomException(failureMsg.toString());
        } else {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
        }
        return successMsg.toString();
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        //获取该sheet所有合并的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //循环判断 该单元格属于哪个合并单元格， 如果能找到对应的，就表示该单元格是合并单元格
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet  sheet索引 从0开始
     * @param row    行索引 从0开始
     * @param column 列索引  从0开始
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 获取单元格的值  先确定单元格的类型，然后根据类型 取值
     *
     * @param cell 单元格
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) return "";
        if (cell.getCellType() == STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }

    /**
     * 判断一个对象的属性是否都为空，
     *
     * @param obj 对象
     * @return false : 至少有一个属性不为空， true: 该对象的属性全为空
     */
    public static boolean allFieldIsNULL(Object obj) {
        Boolean flag = true;//都为空
        if (null == obj || "".equals(obj)) return flag;
        try {
            Field[] declaredFields = obj.getClass().getDeclaredFields();
            for (Field field : declaredFields) { // 循环该类，取出类中的每个属性
                field.setAccessible(true);// 把该类中的所有属性设置成 public
                Object object = field.get(obj); // object 是对象中的属性
                if (object instanceof CharSequence) { // 判断对象中的属性的类型，是否都是CharSequence的子类
                    if (!Objects.isNull(object)) { // 如果是他的子类，那么就可以用ObjectUtils.isEmpty进行比较
                        flag = false;//不为空
                    }
                } else { //如果不是那就直接用null判断
                    if (!(null == object || "".equals(object))) {
                        flag = false;//不为空
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;//false:不都为空
    }


}