Merge remote-tracking branch 'origin/hc-satellite-data-import-1227_2'

riku

2024-12-31 b7b520bfe8b35683112284861f0dca8e645cbd56

Merge remote-tracking branch 'origin/hc-satellite-data-import-1227_2'

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

@@ -2,10 +2,15 @@

import com.alibaba.fastjson.JSONObject
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
@@ -53,7 +58,7 @@
                    if (cellIterator.hasNext()) {
                        val cellText = cellIterator.next().stringCellValue
                        if (!cellText.equals(head)) {
                            throw BizException("文件格式错误, è¡¨å¤´[${head}]应该为[${cellText}]")
                            throw BizException("文件格式错误, è¡¨å¤´[${cellText}]应该为[${head}]")
                        }
                    } else {
                        throw BizException("文件格式错误, è¡¨å¤´[${head}]缺失")
@@ -93,6 +98,192 @@
    }

    /**
     * è½¬æ¢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> {

 src/main/kotlin/com/flightfeather/uav/domain/entity/GridAod.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,59 @@
package com.flightfeather.uav.domain.entity;

import javax.persistence.*;
import java.util.Date;

@Table(name = "grid_aod")
public class GridAod {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "group_id")
    private Integer groupId;

    @Column(name = "data_time")
    private Date dataTime;

    /**
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * @return group_id
     */
    public Integer getGroupId() {
        return groupId;
    }

    /**
     * @param groupId
     */
    public void setGroupId(Integer groupId) {
        this.groupId = groupId;
    }

    /**
     * @return data_time
     */
    public Date getDataTime() {
        return dataTime;
    }

    /**
     * @param dataTime
     */
    public void setDataTime(Date dataTime) {
        this.dataTime = dataTime;
    }
}

 src/main/kotlin/com/flightfeather/uav/domain/entity/GridAodDetail.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,92 @@
package com.flightfeather.uav.domain.entity;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "grid_aod_detail")
public class GridAodDetail {
    @Id
    private Integer id;

    @Column(name = "aod_id")
    private Integer aodId;

    @Column(name = "group_id")
    private Integer groupId;

    @Column(name = "cell_id")
    private Integer cellId;

    private Float aod;

    /**
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * @return aod_id
     */
    public Integer getAodId() {
        return aodId;
    }

    /**
     * @param aodId
     */
    public void setAodId(Integer aodId) {
        this.aodId = aodId;
    }

    /**
     * @return group_id
     */
    public Integer getGroupId() {
        return groupId;
    }

    /**
     * @param groupId
     */
    public void setGroupId(Integer groupId) {
        this.groupId = groupId;
    }

    /**
     * @return cell_id
     */
    public Integer getCellId() {
        return cellId;
    }

    /**
     * @param cellId
     */
    public void setCellId(Integer cellId) {
        this.cellId = cellId;
    }

    /**
     * @return aod
     */
    public Float getAod() {
        return aod;
    }

    /**
     * @param aod
     */
    public void setAod(Float aod) {
        this.aod = aod;
    }
}

 src/main/kotlin/com/flightfeather/uav/domain/entity/GridData.java

@@ -6,6 +6,7 @@
@Table(name = "grid_data")
public class GridData {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    /**

 src/main/kotlin/com/flightfeather/uav/domain/mapper/GridAodDetailMapper.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,8 @@
package com.flightfeather.uav.domain.mapper

import com.flightfeather.uav.domain.MyMapper
import com.flightfeather.uav.domain.entity.GridAodDetail
import org.apache.ibatis.annotations.Mapper

@Mapper
interface GridAodDetailMapper : MyMapper<GridAodDetail?>

 src/main/kotlin/com/flightfeather/uav/domain/mapper/GridAodMapper.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,8 @@
package com.flightfeather.uav.domain.mapper

import com.flightfeather.uav.domain.MyMapper
import com.flightfeather.uav.domain.entity.GridAod
import org.apache.ibatis.annotations.Mapper

@Mapper
interface GridAodMapper : MyMapper<GridAod?>

 src/main/kotlin/com/flightfeather/uav/domain/mapper/GridDataDetailMapper.kt

@@ -5,4 +5,6 @@
import org.apache.ibatis.annotations.Mapper

@Mapper
interface GridDataDetailMapper : MyMapper<GridDataDetail?>
interface GridDataDetailMapper : MyMapper<GridDataDetail?> {
    fun updatePM25Batch(gridDataDetails: List<GridDataDetail>)
}

 src/main/kotlin/com/flightfeather/uav/domain/repository/SatelliteGridRep.kt

@@ -1,9 +1,8 @@
package com.flightfeather.uav.domain.repository

import com.flightfeather.uav.domain.entity.GridCell
import com.flightfeather.uav.domain.entity.GridData
import com.flightfeather.uav.domain.entity.GridDataDetail
import com.flightfeather.uav.domain.entity.GridGroup
import com.flightfeather.uav.domain.entity.*
import com.flightfeather.uav.domain.mapper.GridAodDetailMapper
import com.flightfeather.uav.domain.mapper.GridAodMapper
import com.flightfeather.uav.domain.mapper.GridCellMapper
import com.flightfeather.uav.domain.mapper.GridDataDetailMapper
import com.flightfeather.uav.domain.mapper.GridDataMapper
@@ -24,6 +23,8 @@
    private val gridCellMapper: GridCellMapper,
    private val gridDataMapper: GridDataMapper,
    private val gridDataDetailMapper: GridDataDetailMapper,
    private val gridAodMapper: GridAodMapper,
    private val gridAodDetailMapper: GridAodDetailMapper,
) {

    fun fetchGridGroup(areaVo: AreaVo): List<GridGroup?> {
@@ -59,4 +60,56 @@
            orderBy("cellId")
        })
    }

    fun insertGridDataAndDetail(data: GridData, gridDataDetails: List<GridDataDetail>) {
        gridDataMapper.insert(data)
        gridDataDetails.forEach {
            it.dataId = data.id
            it.groupId = data.groupId
        }
        gridDataDetailMapper.insertList(gridDataDetails)
    }

    fun updatePM25Batch(gridDataDetails: List<GridDataDetail>) {
        gridDataDetailMapper.updatePM25Batch(gridDataDetails)
    }


    //    aod ç›¸å…³æ“ä½œ
    fun fetchGridAod(groupId: Int, dataTime: LocalDateTime?): List<GridAod?> {
        return gridAodMapper.selectByExample(Example(GridAod::class.java).apply {
            createCriteria().andEqualTo("groupId", groupId)
                .andEqualTo("dataTime", dataTime)
        })
    }

    fun fetchGridAodDetail(aodId: Int, groupId: Int?, cellId: Int?): List<GridAodDetail?> {
        return gridAodDetailMapper.selectByExample(Example(GridAodDetail::class.java).apply {
            createCriteria().andEqualTo("aodId", aodId)
                .andEqualTo("groupId", groupId)
                .andEqualTo("cellId", cellId)
            orderBy("cellId")
        })
    }

    fun insertGridAodAndDetail(aod: GridAod, gridAodDetails: List<GridAodDetail>) {
        gridAodMapper.insert(aod)
        gridAodDetails.forEach {
            it.aodId = aod.id
            it.groupId = aod.groupId
        }
        gridAodDetailMapper.insertList(gridAodDetails)
    }

    fun updateGridAodBatch(gridDataDetails: List<GridAodDetail>) {
        gridDataDetails.forEach {
            gridAodDetailMapper.updateByExample(it, Example(GridAodDetail::class.java).apply {
                createCriteria().andEqualTo("aodId", it.aodId)
                    .andEqualTo("groupId", it.groupId)
                    .andEqualTo("cellId", it.cellId)
            })
        }

    }

}

 src/main/kotlin/com/flightfeather/uav/lightshare/bean/GridDataImportResult.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,12 @@
package com.flightfeather.uav.lightshare.bean

import com.fasterxml.jackson.annotation.JsonInclude

/**
 * ç½‘格数据导入处理结果
 */
@JsonInclude(JsonInclude.Include.NON_NULL)
data class GridDataImportResult(
    val success: Boolean,
    val result: String
)

 src/main/kotlin/com/flightfeather/uav/lightshare/service/SatelliteTelemetryService.kt

@@ -1,12 +1,17 @@
package com.flightfeather.uav.lightshare.service

import com.flightfeather.uav.common.exception.BizException
import com.flightfeather.uav.domain.entity.GridAod
import com.flightfeather.uav.domain.entity.GridCell
import com.flightfeather.uav.domain.entity.GridData
import com.flightfeather.uav.domain.entity.GridDataDetail
import com.flightfeather.uav.domain.entity.GridGroup
import com.flightfeather.uav.lightshare.bean.AreaVo
import com.flightfeather.uav.lightshare.bean.DataHead
import com.flightfeather.uav.lightshare.bean.GridDataImportResult
import org.springframework.web.multipart.MultipartFile
import java.time.LocalDateTime
import javax.servlet.http.HttpServletResponse

/**
 *
@@ -22,4 +27,16 @@
    fun fetchGridData(groupId: Int, dataTime: LocalDateTime?, type: Int?): List<GridData?>

    fun fetchGridDataDetail(dataId: Int, groupId: Int?, cellId: Int?): List<GridDataDetail?>

    @Throws(BizException::class)
    fun importGridData(groupId: Int, dataTime: LocalDateTime?, update: Boolean, file: MultipartFile): GridDataImportResult?

    fun downloadTemplate(response: HttpServletResponse): Boolean

    fun fetchGridAod(groupId: Int, dataTime: LocalDateTime?): List<GridAod?>

    @Throws(BizException::class)
    fun importGridAOD(groupId: Int, dataTime: LocalDateTime?, update: Boolean, file: MultipartFile): GridDataImportResult?

    fun downloadAODTemplate(response: HttpServletResponse): Boolean
}

 src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/SatelliteTelemetryServiceImpl.kt

@@ -1,5 +1,8 @@
package com.flightfeather.uav.lightshare.service.impl

import com.flightfeather.uav.common.exception.BizException
import com.flightfeather.uav.common.utils.FileExchange
import com.flightfeather.uav.domain.entity.GridAod
import com.flightfeather.uav.domain.entity.GridCell
import com.flightfeather.uav.domain.entity.GridData
import com.flightfeather.uav.domain.entity.GridDataDetail
@@ -7,10 +10,17 @@
import com.flightfeather.uav.domain.repository.SatelliteGridRep
import com.flightfeather.uav.lightshare.bean.AreaVo
import com.flightfeather.uav.lightshare.bean.DataHead
import com.flightfeather.uav.lightshare.bean.GridDataImportResult
import com.flightfeather.uav.lightshare.service.SatelliteTelemetryService
import com.github.pagehelper.PageHelper
import org.springframework.stereotype.Service
import org.springframework.web.multipart.MultipartFile
import java.io.ByteArrayInputStream
import java.io.File
import java.time.LocalDateTime
import java.time.ZoneId
import java.util.*
import javax.servlet.http.HttpServletResponse

/**
 *
@@ -20,6 +30,7 @@
@Service
class SatelliteTelemetryServiceImpl(private val satelliteGridRep: SatelliteGridRep) : SatelliteTelemetryService {

    private val fileExchange = FileExchange()
    override fun fetchGridGroup(areaVo: AreaVo, page: Int?, perPage: Int?): Pair<DataHead, List<GridGroup?>> {
        val pageInfo = PageHelper.startPage<GridGroup>(page ?: 1, perPage ?: 100)
        val res = satelliteGridRep.fetchGridGroup(areaVo)
@@ -34,7 +45,137 @@
        return satelliteGridRep.fetchGridData(groupId, dataTime, type)
    }

    override fun fetchGridAod(groupId: Int, dataTime: LocalDateTime?): List<GridAod?> {
        return satelliteGridRep.fetchGridAod(groupId, dataTime)
    }

    override fun fetchGridDataDetail(dataId: Int, groupId: Int?, cellId: Int?): List<GridDataDetail?> {
        return satelliteGridRep.fetchGridDataDetail(dataId, groupId, cellId)
    }

    override fun importGridData(groupId: Int, dataTime: LocalDateTime?, update: Boolean, file: MultipartFile): GridDataImportResult? {
        // å› ä¸ºæ˜¯å¯¼å…¥å«æ˜Ÿé¥æµ‹æ•°æ® type始终为0
        val type = 0
        // é¦–先判断文件类型，文件类型不是xlsx直接报错
        if (!file.contentType!!.startsWith("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
            throw BizException("文件类型错误，请上传xlsx类型文件")
        }
        // æ ¡éªŒ ç”¨æˆ·æ›´æ–°æŒ‡ä»¤ å’Œ æ•°æ®åº“中网格数据存在与否 æ˜¯å¦åŒ¹é…ï¼Œä¸åŒ¹é…æŠ›å‡ºé”™è¯¯
        // è¿™ä¸ªè¿‡ç¨‹éœ€è¦æŸ¥è¯¢æ•°æ®åº“中网格组数据
        val gridData = satelliteGridRep.fetchGridData(groupId, dataTime, type)
        // å°†ç”¨æˆ·å¯¼å…¥çš„文件转换为数据
        // è½¬æ¢çš„过程需要网格单元格数据 ä»¥åˆ¤æ–­ç½‘格单元格是否在正确的范围内
        val gridCellsInDBSet = satelliteGridRep.fetchGridCell(groupId).asSequence().map { it?.id ?: -1 }.toSet()
        val importData
                = fileExchange.exchangeGridData(ByteArrayInputStream(file.bytes), gridCellsInDBSet)
        if (update) {
            // ç”¨æˆ·éœ€è¦æ‰§è¡Œæ›´æ–°
            if (gridData.isEmpty()) {
                throw BizException("指令错误，数据库对应网格组和日期下不存在遥测数据，请执行插入操作")
            }
            importData.forEach {
                it.dataId = gridData[0]?.id
                it.groupId = gridData[0]?.groupId
            }
            satelliteGridRep.updatePM25Batch(importData)
            return GridDataImportResult(true, "覆盖成功")
        }else {
            // ç”¨æˆ·éœ€è¦æ‰§è¡Œæ’å…¥
            if (gridData.isNotEmpty()) {
                throw BizException("指令错误，数据库对应网格组和日期下已存在遥测数据，请执行更新操作")
            }
            val gridDataEntity = GridData()
            gridDataEntity.groupId = groupId
            gridDataEntity.dataTime = dataTime?.atZone(ZoneId.systemDefault())?.toInstant()?.toEpochMilli()
                ?.let { Date(it) }
            gridDataEntity.type = type.toByte()
            satelliteGridRep.insertGridDataAndDetail(gridDataEntity, importData)
            return GridDataImportResult(true, "导入成功")
        }
    }

    override fun downloadTemplate(response: HttpServletResponse): Boolean {
        val fileName = "GridData-PM2.5-Template.xlsx"
        val path = (Thread.currentThread().contextClassLoader?.getResource("/")?.path
            ?: "src/main/resources") + "/templates/" + fileName
        val file = File(path)
        if (file.exists()) {
            val fName = Base64.getEncoder().encodeToString(fileName.toByteArray())
            response.apply {
                setHeader("Content-Disposition", "attachment;filename=$fName")
                setHeader("fileName", fName)
                addHeader("Access-Control-Expose-Headers", "fileName")
                contentType = "application/vnd.ms-excel;charset=UTF-8"
                setHeader("Pragma", "no-cache")
                setHeader("Cache-Control", "no-cache")
                setDateHeader("Expires", 0)
            }
            response.outputStream.write(file.readBytes())
        }
        return true
    }

    override fun importGridAOD(
        groupId: Int,
        dataTime: LocalDateTime?,
        update: Boolean,
        file: MultipartFile
    ): GridDataImportResult? {
        // é¦–先判断文件类型，文件类型不是xlsx直接报错
        if (!file.contentType!!.startsWith("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
            throw BizException("文件类型错误，请上传xlsx类型文件")
        }
        // æ ¡éªŒ ç”¨æˆ·æ›´æ–°æŒ‡ä»¤ å’Œ æ•°æ®åº“中网格数据存在与否 æ˜¯å¦åŒ¹é…ï¼Œä¸åŒ¹é…æŠ›å‡ºé”™è¯¯
        // è¿™ä¸ªè¿‡ç¨‹éœ€è¦æŸ¥è¯¢æ•°æ®åº“中网格组数据
        val gridAod = satelliteGridRep.fetchGridAod(groupId, dataTime)
        // å°†ç”¨æˆ·å¯¼å…¥çš„文件转换为数据
        // è½¬æ¢çš„过程需要网格单元格数据 ä»¥åˆ¤æ–­ç½‘格单元格是否在正确的范围内
        val gridCellsInDBSet = satelliteGridRep.fetchGridCell(groupId).asSequence().map { it?.id ?: -1 }.toSet()
        val importAod
                = fileExchange.exchangeGridAod(ByteArrayInputStream(file.bytes), gridCellsInDBSet)
        if (update) {
            // ç”¨æˆ·éœ€è¦æ‰§è¡Œæ›´æ–°
            if (gridAod.isEmpty()) {
                throw BizException("指令错误，数据库对应网格组和日期下不存在AOD数据，请执行插入操作")
            }
            importAod.forEach {
                it.aodId = gridAod[0]?.id
                it.groupId = gridAod[0]?.groupId
            }
            satelliteGridRep.updateGridAodBatch(importAod)
            return GridDataImportResult(true, "覆盖成功")
        }else {
            // ç”¨æˆ·éœ€è¦æ‰§è¡Œæ’å…¥
            if (gridAod.isNotEmpty()) {
                throw BizException("指令错误，数据库对应网格组和日期下已存在AOD数据，请执行更新操作")
            }
            val gridAodEntity = GridAod()
            gridAodEntity.groupId = groupId
            gridAodEntity.dataTime = dataTime?.atZone(ZoneId.systemDefault())?.toInstant()?.toEpochMilli()
                ?.let { Date(it) }
            satelliteGridRep.insertGridAodAndDetail(gridAodEntity, importAod)
            return GridDataImportResult(true, "导入成功")
        }
    }

    override fun downloadAODTemplate(response: HttpServletResponse): Boolean {
        val fileName = "GridData-AOD-Template.xlsx"
        val path = (Thread.currentThread().contextClassLoader?.getResource("/")?.path
            ?: "src/main/resources") + "/templates/" + fileName
        val file = File(path)
        if (file.exists()) {
            val fName = Base64.getEncoder().encodeToString(fileName.toByteArray())
            response.apply {
                setHeader("Content-Disposition", "attachment;filename=$fName")
                setHeader("fileName", fName)
                addHeader("Access-Control-Expose-Headers", "fileName")
                contentType = "application/vnd.ms-excel;charset=UTF-8"
                setHeader("Pragma", "no-cache")
                setHeader("Cache-Control", "no-cache")
                setDateHeader("Expires", 0)
            }
            response.outputStream.write(file.readBytes())
        }
        return true
    }
}

 src/main/kotlin/com/flightfeather/uav/lightshare/web/SatelliteTelemetryController.kt

@@ -6,8 +6,12 @@
import io.swagger.annotations.Api
import io.swagger.annotations.ApiOperation
import io.swagger.annotations.ApiParam
import org.springframework.format.annotation.DateTimeFormat
import org.springframework.web.bind.annotation.*
import org.springframework.web.multipart.MultipartFile
import springfox.documentation.annotations.ApiIgnore
import java.time.LocalDateTime
import javax.servlet.http.HttpServletResponse

/**
 * å«æ˜Ÿé¥æµ‹
@@ -38,7 +42,7 @@
    fun fetchGridData(
        @ApiParam("网格组id") @RequestParam groupId: Int,
        @ApiParam("遥测数据时间")
        @RequestParam(required = false) @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") dataTime: LocalDateTime?,
        @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") dataTime: LocalDateTime?,
        @ApiParam("遥测数据类型", allowableValues = "0：原始卫星遥测数据；1：融合数据") @RequestParam(required = false) type: Int?,
    ) = resPack { satelliteTelemetryService.fetchGridData(groupId, dataTime, type) }

@@ -49,4 +53,45 @@
        @ApiParam("网格组id") @RequestParam(required = false) groupId: Int?,
        @ApiParam("网格单元格id") @RequestParam(required = false) cellId: Int?,
    ) = resPack { satelliteTelemetryService.fetchGridDataDetail(dataId, groupId, cellId) }


    @ApiOperation(value = "导入卫星遥测PM2.5结果数据")
    @PostMapping("/import/grid/data")
    fun importGridData(
        @ApiParam("网格组id") @RequestParam groupId: Int,
        @ApiParam("遥测数据时间")
        @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") dateTime: LocalDateTime,
        @ApiParam("覆盖旧数据 0: ä¸è¦†ç›– 1: è¦†ç›–") @RequestParam update: Boolean,
        @RequestParam("excel") file: MultipartFile,
    ) = resPack {
        satelliteTelemetryService.importGridData(groupId, dateTime, update, file)
    }

    @ApiOperation(value = "下载卫星遥测PM2.5结果数据导入模板")
    @GetMapping("/import/grid/data/download/template")
    fun downloadTemplate(@ApiIgnore response: HttpServletResponse) = satelliteTelemetryService.downloadTemplate(response)

    @ApiOperation(value = "获取网格组下的卫星遥测aod数据")
    @GetMapping("/grid/aod")
    fun fetchGridAod(
        @ApiParam("网格组id") @RequestParam groupId: Int,
        @ApiParam("遥测数据时间")
        @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") dataTime: LocalDateTime?,
    ) = resPack { satelliteTelemetryService.fetchGridAod(groupId, dataTime) }

    @ApiOperation(value = "导入卫星遥测Aod结果数据")
    @PostMapping("/import/grid/aod")
    fun importGridAOD(
        @ApiParam("网格组id") @RequestParam groupId: Int,
        @ApiParam("遥测数据时间")
        @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") dateTime: LocalDateTime,
        @ApiParam("覆盖旧数据 0: ä¸è¦†ç›– 1: è¦†ç›–") @RequestParam update: Boolean,
        @RequestParam("excel") file: MultipartFile,
    ) = resPack {
        satelliteTelemetryService.importGridAOD(groupId, dateTime, update, file)
    }

    @ApiOperation(value = "下载卫星遥测Aod结果数据导入模板")
    @GetMapping("/import/grid/aod/download/template")
    fun downloadAODTemplate(@ApiIgnore response: HttpServletResponse) = satelliteTelemetryService.downloadAODTemplate(response)
}

 src/main/resources/mapper/GridAodDetailMapper.xml

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,20 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.flightfeather.uav.domain.mapper.GridAodDetailMapper">
  <resultMap id="BaseResultMap" type="com.flightfeather.uav.domain.entity.GridAodDetail">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="aod_id" jdbcType="INTEGER" property="aodId" />
    <result column="group_id" jdbcType="INTEGER" property="groupId" />
    <result column="cell_id" jdbcType="INTEGER" property="cellId" />
    <result column="aod" jdbcType="REAL" property="aod" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
    -->
    id, aod_id, group_id, cell_id, aod
  </sql>
</mapper>

 src/main/resources/mapper/GridAodMapper.xml

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,18 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.flightfeather.uav.domain.mapper.GridAodMapper">
  <resultMap id="BaseResultMap" type="com.flightfeather.uav.domain.entity.GridAod">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="group_id" jdbcType="INTEGER" property="groupId" />
    <result column="data_time" jdbcType="TIMESTAMP" property="dataTime" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
    -->
    id, group_id, data_time
  </sql>
</mapper>

 src/main/resources/mapper/GridDataDetailMapper.xml

@@ -18,4 +18,15 @@
    -->
    id, data_id, group_id, cell_id, PM25, rank
  </sql>

  <!-- æ·»åŠ æ‰¹é‡æ›´æ–°PM25的函数 -->
  <update id="updatePM25Batch" parameterType="java.util.List">
    <foreach collection="list" item="item" separator=";">
      UPDATE grid_data_detail
      SET PM25 = #{item.pm25}
      WHERE data_id = #{item.dataId}
      AND group_id = #{item.groupId}
      AND cell_id = #{item.cellId}
    </foreach>
  </update>
</mapper>

 src/main/resources/templates/GridData-AOD-Template.xlsx

Binary files differ

 src/main/resources/templates/GridData-PM2.5-Template.xlsx

Binary files differ