java 使用POI导入复杂excel表格

 图片不清晰，请多担待

 

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:不都为空
    }


}