1. 新增接口网格数据excel模板、网格数据excel导入 2. 接口解析网格数据抛出相关excel报错信息 3. FileExchange.kt新...

hcong

2024-12-23 fcefe41e05439f548a58c7e5b6aa6e58f7b80ada

1. 新增接口网格数据excel模板、网格数据excel导入 2. 接口解析网格数据抛出相关excel报错信息 3. FileExchange.kt新增excel转换到GridDataDetail列表方法 4. 新增导入excel的单元测试代码

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

@@ -2,6 +2,7 @@

import com.alibaba.fastjson.JSONObject
import com.flightfeather.uav.common.exception.BizException
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
@@ -93,6 +94,52 @@
    }

    /**
     * è½¬æ¢ä¸ºPM2.5表格数据
     */
    fun exchangeGridData(file: InputStream): 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("文件格式错误, è¡¨å¤´[${head}]应该为[${cellText}]")
                        }
                    } else {
                        throw BizException("文件格式错误, è¡¨å¤´[${head}]缺失")
                    }
                }
                true
            }, onRow = {
                val data = GridDataDetail().apply {
                    try {
                        this.cellId = it.getCell(0)?.numericCellValue?.toInt()
                    } catch (e: Exception) {
                        throw BizException("单元格[${it.rowNum}${0}]不是数字类型")
                    }
                    try {
                        this.pm25 = it.getCell(1)?.numericCellValue?.toFloat()
                    } catch (e: Exception) {
                        throw BizException("单元格[${it.rowNum}${1}]不是数字类型")
                    }
                }
                result.add(data)
            })
        } catch (e: BizException) {
            throw e
        }
        return result
    }

    /**
     * è½¬æ¢è½¦è½½èµ°èˆªæ•°æ®
     */
    fun exchangeVehicleData(deviceCode: String, file: InputStream): List<RealTimeDataVehicle> {

 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/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

@@ -59,4 +59,17 @@
            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)
    }
}

 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,16 @@
package com.flightfeather.uav.lightshare.service

import com.flightfeather.uav.common.exception.BizException
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 +26,9 @@
    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, type: Int?, dataTime: LocalDateTime?, update: Int?, file: MultipartFile): GridDataImportResult?

    fun downloadTemplate(response: HttpServletResponse): Boolean
}

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

@@ -1,5 +1,7 @@
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.GridCell
import com.flightfeather.uav.domain.entity.GridData
import com.flightfeather.uav.domain.entity.GridDataDetail
@@ -7,10 +9,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 +29,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)
@@ -37,4 +47,65 @@
    override fun fetchGridDataDetail(dataId: Int, groupId: Int?, cellId: Int?): List<GridDataDetail?> {
        return satelliteGridRep.fetchGridDataDetail(dataId, groupId, cellId)
    }

    override fun importGridData(groupId: Int, type: Int?, dataTime: LocalDateTime?, update: Int?, file: MultipartFile): GridDataImportResult? {
        val gridData = satelliteGridRep.fetchGridData(groupId, dataTime, type)
        if (!file.contentType!!.startsWith("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
            throw BizException("文件类型错误，请上传xlsx类型文件")
        }
        if (gridData.isNotEmpty() && update == 0) {
            return GridDataImportResult(false, "数据库对应网格组和日期下已存在遥测数据")
        }
        val exchangeGridDataDetails
                = fileExchange.exchangeGridData(ByteArrayInputStream(file.bytes))
        val gridCellsInDB = satelliteGridRep.fetchGridCell(groupId)
        val setA = gridCellsInDB.asSequence().map { it?.id ?: -1 }.toSet()
        val setB = exchangeGridDataDetails.asSequence().map { it.cellId ?: -1 }.toSet()
        // excel ä¸­ç¼ºå°‘的的单元格
        val onlyInA = setA - setB
        // excel ä¸­å¤šå‡ºçš„的单元格
        val onlyInB = setB - setA
        if (onlyInA.isNotEmpty()) {
            throw BizException("导入数据中缺少以下网格单元格：${onlyInA.joinToString(",")}")
        }
        if (onlyInB.isNotEmpty()) {
            throw BizException("导入数据中有多余网格单元格：${onlyInB.joinToString(",")}")
        }
        if (update == 1) {
            exchangeGridDataDetails.forEach {
                it.dataId = gridData[0]?.id
                it.groupId = gridData[0]?.groupId
            }
            satelliteGridRep.updatePM25Batch(exchangeGridDataDetails)
            return GridDataImportResult(true, "覆盖成功")
        }
        val gridDataEntity = GridData()
        gridDataEntity.groupId = groupId
        gridDataEntity.dataTime = dataTime?.atZone(ZoneId.systemDefault())?.toInstant()?.toEpochMilli()
            ?.let { Date(it) }
        gridDataEntity.type = type?.toByte()
        satelliteGridRep.insertGridDataAndDetail(gridDataEntity, exchangeGridDataDetails)
        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
    }
}

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

@@ -7,7 +7,11 @@
import io.swagger.annotations.ApiOperation
import io.swagger.annotations.ApiParam
import org.springframework.web.bind.annotation.*
import org.springframework.web.multipart.MultipartFile
import springfox.documentation.annotations.ApiIgnore
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import javax.servlet.http.HttpServletResponse

/**
 * å«æ˜Ÿé¥æµ‹
@@ -49,4 +53,22 @@
        @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("遥测数据类型", allowableValues = "0：原始卫星遥测数据；1：融合数据") @RequestParam(required = false) type: Int?,
        @ApiParam("遥测数据时间")
        @RequestParam @JsonFormat(pattern = "YYYY-MM-DD HH:mm:ss") dateTime: String?,
        @ApiParam("覆盖旧数据 0: ä¸è¦†ç›– 1: è¦†ç›–") @RequestParam update: Int?,
        @RequestParam("excel") file: MultipartFile,
    ) = resPack {
        satelliteTelemetryService.importGridData(groupId, type, LocalDateTime.parse(dateTime, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")), update, file)
    }

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

 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-PM2.5-Template.xlsx

Binary files differ

 src/test/kotlin/com/flightfeather/uav/common/utils/FileExchangeTest.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,198 @@
package com.flightfeather.uav.common.utils;

import com.flightfeather.uav.common.exception.BizException;
import com.flightfeather.uav.domain.entity.GridDataDetail;
import com.flightfeather.uav.lightshare.service.SatelliteTelemetryService;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URL;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;

import static org.junit.Assert.assertEquals;

@RunWith(SpringRunner.class)
@SpringBootTest
public class FileExchangeTest {

    @Autowired
    private SatelliteTelemetryService satelliteTelemetryService;

    public void start() throws BizException, IOException {
        test1();
        test2();
        test3();
        test4();
        test5();
        test6();
        test7();
    }

//    ç¬¬ä¸€åˆ—数字类型错误
    @Test
    public void test1() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-1_type_error.xlsx");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1, LocalDateTime.of(2029, 7, 29, 0, 0), 0, multipartFile);
            }catch (BizException e) {
                // éªŒè¯å¼‚常消息是否与预期相符
                if (!e.getMessage().contains("不是数字类型")) {
                    System.out.println(e.getMessage());
                    throw e;
                }
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
    //    ç¬¬äºŒåˆ—数字类型错误
    @Test
    public void test2() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-2_type_error.xlsx");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1, LocalDateTime.of(2029, 7, 29, 0, 0), 0, multipartFile);
            }catch (BizException e) {
                // éªŒè¯å¼‚常消息是否与预期相符
                if (!e.getMessage().contains("不是数字类型")) {
                    System.out.println(e.getMessage());
                    throw e;
                }
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
    // ç½‘格编号越界错误
    @Test
    public void test3() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-index_out.xlsx");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1, LocalDateTime.of(2029, 7, 29, 0, 0), 0, multipartFile);
            }catch (BizException e) {
                // éªŒè¯å¼‚常消息是否与预期相符
                if (!e.getMessage().contains("导入数据中有多余网格单元格")) {
                    System.out.println(e.getMessage());
                    throw e;
                }
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
    // ç½‘格编号缺失错误
    @Test
    public void test4() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-index_short.xlsx");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1, LocalDateTime.of(2029, 7, 29, 0, 0), 0, multipartFile);
            }catch (BizException e) {
                // éªŒè¯å¼‚常消息是否与预期相符
                if (!e.getMessage().contains("导入数据中缺少以下网格单元格")) {
                    System.out.println(e.getMessage());
                    throw e;
                }
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
    // æ–‡ä»¶ç±»åž‹é”™è¯¯
    @Test
    public void test5() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-fileType-error.txt");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1, LocalDateTime.of(2029, 7, 29, 0, 0), 0, multipartFile);
            }catch (BizException e) {
                // éªŒè¯å¼‚常消息是否与预期相符
                if (!e.getMessage().contains("导入数据中缺少以下网格单元格")) {
                    System.out.println(e.getMessage());
                    throw e;
                }
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
    //    æ–°å¢žæ•°æ®å¯¼å…¥æˆåŠŸ
    @Test
    public void test6() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-success-insert.xlsx");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1,
                        LocalDateTime.of(2029, 7, 29, 0, 0), 0, multipartFile);
            }catch (BizException e) {
                throw e;
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
    //    æ›´æ–°æ•°æ®å¯¼å…¥æˆåŠŸ
    @Test
    public void test7() throws IOException, BizException {
        URL resource = getClass().getClassLoader().getResource("templates/GridData-success-update.xlsx");
        if (resource != null) {
            System.out.println(resource.getPath());
            // åˆ›å»ºæ–‡ä»¶è¾“入流
            FileInputStream inputStream = new FileInputStream(new File(resource.getPath()));
            // åˆ›å»ºMockMultipartFile对象
            MockMultipartFile multipartFile = new MockMultipartFile("file", "111.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inputStream);
            try {
                satelliteTelemetryService.importGridData(1, 1,
                        LocalDateTime.of(2029, 7, 29, 0, 0), 1, multipartFile);
            }catch (BizException e) {
                throw e;
            }
        } else {
            System.out.println("资源文件未找到");
        }
    }
}

 src/test/resources/templates/GridData-1_type_error.xlsx

Binary files differ

 src/test/resources/templates/GridData-2_type_error.xlsx

Binary files differ

 src/test/resources/templates/GridData-fileType-error.txt

Binary files differ

 src/test/resources/templates/GridData-index_out.xlsx

Binary files differ

 src/test/resources/templates/GridData-index_short.xlsx

Binary files differ

 src/test/resources/templates/GridData-success-insert.xlsx

Binary files differ

 src/test/resources/templates/GridData-success-update.xlsx

Binary files differ