Java将Excel表格数据上传数据库

Java将Excel表格数据上传数据库 Java将Excel表格数据上传数据库

数据库采用jpa框架

1. 数据库格式
2. 实现上传数据方法代码

@Entity
@Table(name = "d1_device")
public class DeviceEntity extends baseEntity {
    
    @Column(length = 32)
    private String deviceNum;
    
    @Column(length = 215)
    private String deviceId;
    
    @Column(length = 216)
    private String entranceGuardIP;
    
    @Column(length = 215)
    private String gatewayIP;
    
    @Column(length = 215)
    private String subnetMask;
    
    @Column(length = 215)
    private String DNS;
    
    @Column(length = 215)
    private String serverIP;
    
    @Column(length = 10)
    private String configStatus;
    
    @Column(length = 32)
    private String buildingId;
    
    @Column(length = 32)
    private String roomId;

    public DeviceEntity() {

    }

    public String getDeviceNum() {
        return deviceNum;
    }

    public void setDeviceNum(String deviceNum) {
        this.deviceNum = deviceNum;
    }

    public String getDNS() {
        return DNS;
    }

    public void setDNS(String DNS) {
        this.DNS = DNS;
    }

    public String getDeviceId() {
        return deviceId;
    }

    public void setDeviceId(String deviceId) {
        this.deviceId = deviceId;
    }

    public String getEntranceGuardIP() {
        return entranceGuardIP;
    }

    public void setEntranceGuardIP(String entranceGuardIP) {
        this.entranceGuardIP = entranceGuardIP;
    }

    public String getGatewayIP() {
        return gatewayIP;
    }

    public void setGatewayIP(String gatewayIP) {
        this.gatewayIP = gatewayIP;
    }

    public String getSubnetMask() {
        return subnetMask;
    }

    public void setSubnetMask(String subnetMask) {
        this.subnetMask = subnetMask;
    }

    public String getServerIP() {
        return serverIP;
    }

    public void setServerIP(String serverIP) {
        this.serverIP = serverIP;
    }

    public String getConfigStatus() {
        return configStatus;
    }

    public void setConfigStatus(String configStatus) {
        this.configStatus = configStatus;
    }

    public String getBuildingId() {
        return buildingId;
    }

    public void setBuildingId(String buildingId) {
        this.buildingId = buildingId;
    }

    public String getRoomId() {
        return roomId;
    }

    public void setRoomId(String roomId) {
        this.roomId = roomId;
    }


    public DeviceEntity(String deviceNum, String deviceId, String entranceGuardIP, String gatewayIP, String subnetMask, String DNS, String serverIP, String configStatus, String buildingId, String roomId) {
        this.deviceNum = deviceNum;
        this.deviceId = deviceId;
        this.entranceGuardIP = entranceGuardIP;
        this.gatewayIP = gatewayIP;
        this.subnetMask = subnetMask;
        this.DNS = DNS;
        this.serverIP = serverIP;
        this.configStatus = configStatus;
        this.buildingId = buildingId;
        this.roomId = roomId;
    }
}

public void checkDeviceExcel(MultipartFile file) throws Exception {
    Workbook wb = ExportExcelUtils.chooseExcelFormat(file);
    Sheet sheet = wb.getSheetAt(0);
    String name = sheet.getRow(0).getCell(1).getStringCellValue();
    if (!"设备ID".equals(name)) {
        throw new DoValidException("您所要导入的数据结构不对，请下载模板");
    }

    //int lastColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
    //获取行数
    int lastRowNum = sheet.getLastRowNum();
    if (lastRowNum < 1) {
        throw new DoValidException("导入表为空表");
    }
    //遍历表格检查是否有空值；
    for (int rowMin = 1; rowMin <= lastRowNum; rowMin++) {
        Row row = sheet.getRow(rowMin);
        //导入的表的前五列为非空项
        int columnMax = 9;
        for (int columnMin = 0; columnMin < columnMax; columnMin++) {
            Cell cell = row.getCell(columnMin);
            if (cell == null || cell.getCellType() != 0 && cell.getStringCellValue().equals("")) {
                throw new DoValidException("第" + rowMin + "行，第" + columnMin + "列的数据不可为空");
            }

        }
    }
}

public List changeExcelToDevice(MultipartFile file) throws Exception {
    Workbook wb = ExportExcelUtils.chooseExcelFormat(file);
    Sheet sheet = wb.getSheetAt(0);
    List devicesFromExcel = new linkedList<>();
    int lastRowNum = sheet.getLastRowNum();
    for (int rowMin = 1; rowMin <= lastRowNum; rowMin++) {
        Row row = sheet.getRow(rowMin);
        DeviceEntity deviceFromExcel = new DeviceEntity(
                row.getCell(0).getCellType()==0? String.valueOf((int)row.getCell(0).getNumericCellValue()):row.getCell(0).getStringCellValue(),
                row.getCell(1).getCellType()==0? String.valueOf((int)row.getCell(1).getNumericCellValue()):row.getCell(1).getStringCellValue(),
                row.getCell(2).getCellType()==0? String.valueOf((int)row.getCell(2).getNumericCellValue()):row.getCell(2).getStringCellValue(),
                row.getCell(3).getCellType()==0? String.valueOf((int)row.getCell(3).getNumericCellValue()):row.getCell(3).getStringCellValue(),
                row.getCell(4).getCellType()==0? String.valueOf((int)row.getCell(4).getNumericCellValue()):row.getCell(4).getStringCellValue(),
                row.getCell(5).getCellType()==0? String.valueOf((int)row.getCell(5).getNumericCellValue()):row.getCell(5).getStringCellValue(),
                row.getCell(6).getCellType()==0? String.valueOf((int)row.getCell(6).getNumericCellValue()):row.getCell(6).getStringCellValue(),
                row.getCell(7).getCellType()==0? String.valueOf((int)row.getCell(7).getNumericCellValue()):row.getCell(7).getStringCellValue(),
                row.getCell(8).getCellType()==0? String.valueOf((int)row.getCell(8).getNumericCellValue()):row.getCell(8).getStringCellValue(),
                row.getCell(9).getCellType()==0? String.valueOf((int)row.getCell(9).getNumericCellValue()):row.getCell(9).getStringCellValue()

        );
        deviceFromExcel.setId(baseUtils.generate32Id());
        devicesFromExcel.add(deviceFromExcel);
    }
    return devicesFromExcel;
}

public void saveAll(List Devices) throws DoValidException {
    for(DeviceEntity deviceEntity:Devices) {
        if (deviceEntityDao.existsByDeviceId(deviceEntity.getDeviceId())) {
            throw new DoValidException("设备ID："+deviceEntity.getDeviceId()+" 已存在");
        }
        if (deviceEntityDao.existsByBuildingIdAndRoomId(deviceEntity.getBuildingId(),deviceEntity.getRoomId())) {
            throw new DoValidException("楼栋号："+deviceEntity.getBuildingId()+" "+"房间号："+deviceEntity.getRoomId()+"已存在设备");
        }

    }
    log.info("save={}", JSON.toJSONString(Devices));
    //设备ID和楼栋号房间号不存在重复再存入Excel表格中所有数据
   deviceEntityDao.saveAll(Devices);
}

Controller层

• 导入设备
• @param file 文件模型

@PostMapping(value = "/importData")
public Result importData(MultipartFile file) {
    try {
        //检查表
        DeviceService.checkDeviceExcel(file);
        //读取表
        List devices = DeviceService.changeExcelToDevice(file);
        //数据存储
        DeviceService.saveAll(devices);
        return ResultUtil.success("导入成功");
    } catch (Exception e) {
        return ResultUtil.fail("导入失败:" + e.getMessage());
    }
}