1. 新增aod数据索引，aod详细数据实体类，mapper，service 2. 新增卫星遥测数据网格数据pm2.5、aod数据导入，模板下载 3....

hcong

2024-12-27 9d4ca2e7bdfc0d634b0cc27c7fbe23740f4bb398

 src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt

@@ -1,11 +1,16 @@
package com.flightfeather.uav.common.utils

import com.alibaba.fastjson.JSONObject
import com.flightfeather.uav.common.exception.ResponseErrorException
import com.flightfeather.uav.common.exception.BizException
import com.flightfeather.uav.domain.entity.GridAodDetail
import com.flightfeather.uav.domain.entity.GridDataDetail
import com.flightfeather.uav.domain.entity.RealTimeData
import com.flightfeather.uav.domain.entity.RealTimeDataVehicle
import com.flightfeather.uav.socket.bean.AirData
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.ss.util.CellAddress
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.io.File
import java.io.FileInputStream
@@ -29,7 +34,259 @@
        private val format = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
    }

    /**
     * 转换静安区车载走航数据
     */
    fun exchangeJinanData(deviceCode: String, file: InputStream): List<RealTimeDataVehicle> {
        val headers = listOf(
            "PostTime",
            "Lon",
            "Lat",
            "PM25",
            "PM10",
            "Temperature",
            "Humidity",
            "Speed",
        )
        val result = mutableListOf<RealTimeDataVehicle>()
        try {
            ExcelUtil.readXLXS(file, headerCheck = {
                val cellIterator = it.cellIterator()
                val headIterator = headers.iterator()
                while (headIterator.hasNext()) {
                    val head = headIterator.next()
                    if (cellIterator.hasNext()) {
                        val cellText = cellIterator.next().stringCellValue
                        if (!cellText.equals(head)) {
                            throw BizException("文件格式错误, 表头[${cellText}]应该为[${head}]")
                        }
                    } else {
                        throw BizException("文件格式错误, 表头[${head}]缺失")
                    }
                }
                true
            }, onRow = {
                val data = RealTimeDataVehicle().apply {
                    this.deviceCode = deviceCode
                    dataTime = DateUtil.instance.StringToDate(
                        it.getCell(0)?.stringCellValue?.trim()?.split(".")?.get(0)
                    )
                    longitude = it.getCell(1)?.stringCellValue?.trim()?.toBigDecimal()
                    latitude = it.getCell(2)?.stringCellValue?.trim()?.toBigDecimal()
                    pm25 = it.getCell(3)?.stringCellValue?.trim()?.toFloat()?: 0F
                    pm10 = it.getCell(4)?.stringCellValue?.trim()?.toFloat()?: 0F
                    temperature = it.getCell(5)?.stringCellValue?.trim()?.toFloat()?: 0F
                    humidity = it.getCell(6)?.stringCellValue?.trim()?.toFloat()?: 0F
                    velocity = it.getCell(7)?.stringCellValue?.trim()?.toFloat()?: 0F
                    no2 = 0F
                    co = 0F
                    h2s = 0F
                    so2 = 0F
                    o3 = 0F
                    voc = 0F
                    noi = 0F
                    windSpeed = 0F
                    windDirection = 0F
                }
                result.add(data)
            })
        } catch (e: Exception) {
            e.printStackTrace()
            throw BizException("excel文件内容错误，数据转换失败！", e)
        }
        return result
    }

    /**
     * 转换PM2.5表格数据
     */
    fun exchangeGridData(file: InputStream, correctRangeGridCells: Set<Int>): List<GridDataDetail> {
        // 模仿已有代码判断表头正确性 并在错误时抛出异常
        val headers = listOf(
            "pointid",
            "PM2.5"
        )
        val result = mutableListOf<GridDataDetail>()
        try {
            ExcelUtil.readXLXS(file, headerCheck = {
                val cellIterator = it.cellIterator()
                val headIterator = headers.iterator()
                while (headIterator.hasNext()) {
                    val head = headIterator.next()
                    if (cellIterator.hasNext()) {
                        val cellText = cellIterator.next().stringCellValue
                        if (!cellText.equals(head)) {
                            throw BizException("文件格式错误, 表头[${cellText}]应该为[${head}]")
                        }
                    } else {
                        throw BizException("文件格式错误, 表头[${head}]缺失")
                    }
                }
                true
            },
                // 判断单元格数据正确性，并在报错后抛出异常
                onRow = {
                    val data = GridDataDetail().apply {
                        // 转换网格组单元格id
                        // 得到当前单元格的定位信息 （eg: B3,BA3...）
                        val cellIdCellPosStr = CellAddress(it.rowNum, 0).formatAsString()
                        // 得到当前单元格数据类型
                        val cellIdCellType = it.getCell(0).cellType
                        when (cellIdCellType) {
                            CellType.NUMERIC -> {
                                // 当值为数字类型时转化为网格单元格id
                                this.cellId = it.getCell(0)?.numericCellValue?.toInt()
                            }
                            else -> {
                                throw BizException("单元格[${cellIdCellPosStr}]不是数字类型")
                            }
                        }

                        // 转换PM2.5
                        // 得到当前单元格的定位信息 （eg: B3,BA3...）
                        val pm25CellPosStr = CellAddress(it.rowNum, 1).formatAsString()
                        // 得到当前单元格数据类型
                        val pm25CellType = it.getCell(1).cellType
                        when (pm25CellType) {
                            CellType.STRING -> {
                                // 当值为字符串时有两种情况
                                // 空白或者是‘/’字符 转化为空值
                                // 否则报错
                                val stringValue = it.getCell(1)?.stringCellValue?.trim()
                                if (stringValue != null) {
                                    if (stringValue == "/" || stringValue.isBlank()) {
                                        this.pm25 = null
                                    }
                                }else {
                                    throw BizException("单元格[${pm25CellPosStr}]不是数字类型或者文本类型")
                                }
                            }
                            CellType.NUMERIC -> {
                                // 当值为数字类型时转化为pm2.5数据
                                this.pm25 = it.getCell(1)?.numericCellValue?.toFloat()
                            }
                            else -> {
                                throw BizException("单元格[${pm25CellPosStr}]不是数字类型")
                            }
                        }
                    }
                    result.add(data)
                })
        } catch (e: BizException) {
            throw e
        }
        // 判断网格单元格编号是否在正确的范围内
        val resultCellIdSet = result.asSequence().map { it.cellId ?: -1 }.toSet()
        // 用户导入中缺少的的单元格
        val shortCells = correctRangeGridCells - resultCellIdSet
        // excel 中多出的的单元格
        val outCells = resultCellIdSet - correctRangeGridCells
        if (shortCells.isNotEmpty()) {
            throw BizException("导入数据中缺少以下网格单元格：${shortCells.joinToString(",")}")
        }
        if (outCells.isNotEmpty()) {
            throw BizException("导入数据中有多余网格单元格：${outCells.joinToString(",")}")
        }
        return result
    }

    /**
     * 转换AOD表格数据
     */
    fun exchangeGridAod(file: InputStream, correctRangeGridCells: Set<Int>): List<GridAodDetail> {
        // 模仿已有代码判断表头正确性 并在错误时抛出异常
        val headers = listOf(
            "pointid",
            "AOD"
        )
        val result = mutableListOf<GridAodDetail>()
        try {
            ExcelUtil.readXLXS(file, headerCheck = {
                val cellIterator = it.cellIterator()
                val headIterator = headers.iterator()
                while (headIterator.hasNext()) {
                    val head = headIterator.next()
                    if (cellIterator.hasNext()) {
                        val cellText = cellIterator.next().stringCellValue
                        if (!cellText.equals(head)) {
                            throw BizException("文件格式错误, 表头[${cellText}]应该为[${head}]")
                        }
                    } else {
                        throw BizException("文件格式错误, 表头[${head}]缺失")
                    }
                }
                true
            },
                // 判断单元格数据正确性，并在报错后抛出异常
                onRow = {
                    val data = GridAodDetail().apply {
                        // 转换网格组单元格id
                        // 得到当前单元格的定位信息 （eg: B3,BA3...）
                        val cellIdCellPosStr = CellAddress(it.rowNum, 0).formatAsString()
                        // 得到当前单元格数据类型
                        val cellIdCellType = it.getCell(0).cellType
                        when (cellIdCellType) {
                            CellType.NUMERIC -> {
                                // 当值为数字类型时转化为网格单元格id
                                this.cellId = it.getCell(0)?.numericCellValue?.toInt()
                            }
                            else -> {
                                throw BizException("单元格[${cellIdCellPosStr}]不是数字类型")
                            }
                        }

                        // 转换AOD
                        // 得到当前单元格的定位信息 （eg: B3,BA3...）
                        val aodCellPosStr = CellAddress(it.rowNum, 1).formatAsString()
                        // 得到当前单元格数据类型
                        val aodCellType = it.getCell(1).cellType
                        when (aodCellType) {
                            CellType.STRING -> {
                                // 当值为字符串时有两种情况
                                // 空白或者是‘/’字符 转化为空值
                                // 否则报错
                                val stringValue = it.getCell(1)?.stringCellValue?.trim()
                                if (stringValue != null) {
                                    if (stringValue == "/" || stringValue.isBlank()) {
                                        this.aod = null
                                    }
                                }else {
                                    throw BizException("单元格[${aodCellPosStr}]不是数字类型或者文本类型")
                                }
                            }
                            CellType.NUMERIC -> {
                                // 当值为数字类型时转化为pm2.5数据
                                this.aod = it.getCell(1)?.numericCellValue?.toFloat()
                            }
                            else -> {
                                throw BizException("单元格[${aodCellPosStr}]不是数字类型")
                            }
                        }
                    }
                    result.add(data)
                })
        } catch (e: BizException) {
            throw e
        }
        // 判断网格单元格编号是否在正确的范围内
        val resultCellIdSet = result.asSequence().map { it.cellId ?: -1 }.toSet()
        // 用户导入中缺少的的单元格
        val shortCells = correctRangeGridCells - resultCellIdSet
        // excel 中多出的的单元格
        val outCells = resultCellIdSet - correctRangeGridCells
        if (shortCells.isNotEmpty()) {
            throw BizException("导入数据中缺少以下网格单元格：${shortCells.joinToString(",")}")
        }
        if (outCells.isNotEmpty()) {
            throw BizException("导入数据中有多余网格单元格：${outCells.joinToString(",")}")
        }
        return result
    }

    /**
     * 转换车载走航数据
     */
    fun exchangeVehicleData(deviceCode: String, file: InputStream): List<RealTimeDataVehicle> {
        val headers = listOf(
            "data_time",
            "longitude",
@@ -51,7 +308,7 @@
        )
        val result = mutableListOf<RealTimeDataVehicle>()
        try {
            ExcelUtil.read(file, headerCheck = {
            ExcelUtil.readXLXS(file, headerCheck = {
                val cellIterator = it.cellIterator()
                val headIterator = headers.iterator()
                while (headIterator.hasNext()) {
@@ -59,10 +316,10 @@
                    if (cellIterator.hasNext()) {
                        val cellText = cellIterator.next().stringCellValue
                        if (!cellText.equals(head)) {
                            throw ResponseErrorException("文件格式错误, 表头[${head}]应该为[${cellText}]")
                            throw BizException("文件格式错误, 表头[${head}]应该为[${cellText}]")
                        }
                    } else {
                        throw ResponseErrorException("文件格式错误, 表头[${head}]缺失")
                        throw BizException("文件格式错误, 表头[${head}]缺失")
                    }
                }
                true
@@ -91,7 +348,7 @@
            })
        } catch (e: Exception) {
            e.printStackTrace()
            throw ResponseErrorException("excel文件内容错误，数据转换失败！", e)
            throw BizException("excel文件内容错误，数据转换失败！", e)
        }
        return result
    }