From eb3dd00b0b7fcda477229d518d250f9c842b790b Mon Sep 17 00:00:00 2001
From: feiyu02 <risaku@163.com>
Date: 星期二, 21 十月 2025 17:45:44 +0800
Subject: [PATCH] 2025.10.21 1. 走航季度报告相关数据计算逻辑调整
---
src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt | 297 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 288 insertions(+), 9 deletions(-)
diff --git a/src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt b/src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt
index efc4c36..423644f 100644
--- a/src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt
+++ b/src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt
@@ -1,10 +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
@@ -28,11 +34,263 @@
private val format = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
}
- fun exchangeJinanData(file: File) {
+ /**
+ * 杞崲闈欏畨鍖鸿溅杞借蛋鑸暟鎹�
+ */
+ 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
+ // 寰楀埌褰撳墠鍗曞厓鏍肩殑瀹氫綅淇℃伅 锛坋g: B3,BA3...锛�
+ val cellIdCellPosStr = CellAddress(it.rowNum, 0).formatAsString()
+ // 寰楀埌褰撳墠鍗曞厓鏍兼暟鎹被鍨�
+ val cellIdCellType = it.getCell(0).cellType
+ when (cellIdCellType) {
+ CellType.NUMERIC -> {
+ // 褰撳�间负鏁板瓧绫诲瀷鏃惰浆鍖栦负缃戞牸鍗曞厓鏍糹d
+ this.cellId = it.getCell(0)?.numericCellValue?.toInt()
+ }
+ else -> {
+ throw BizException("鍗曞厓鏍糩${cellIdCellPosStr}]涓嶆槸鏁板瓧绫诲瀷")
+ }
+ }
+
+ // 杞崲PM2.5
+ // 寰楀埌褰撳墠鍗曞厓鏍肩殑瀹氫綅淇℃伅 锛坋g: 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
+ // 寰楀埌褰撳墠鍗曞厓鏍肩殑瀹氫綅淇℃伅 锛坋g: B3,BA3...锛�
+ val cellIdCellPosStr = CellAddress(it.rowNum, 0).formatAsString()
+ // 寰楀埌褰撳墠鍗曞厓鏍兼暟鎹被鍨�
+ val cellIdCellType = it.getCell(0).cellType
+ when (cellIdCellType) {
+ CellType.NUMERIC -> {
+ // 褰撳�间负鏁板瓧绫诲瀷鏃惰浆鍖栦负缃戞牸鍗曞厓鏍糹d
+ this.cellId = it.getCell(0)?.numericCellValue?.toInt()
+ }
+ else -> {
+ throw BizException("鍗曞厓鏍糩${cellIdCellPosStr}]涓嶆槸鏁板瓧绫诲瀷")
+ }
+ }
+
+ // 杞崲AOD
+ // 寰楀埌褰撳墠鍗曞厓鏍肩殑瀹氫綅淇℃伅 锛坋g: 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",
"latitude",
- "data_time",
"NO2",
"CO",
"H2S",
@@ -48,8 +306,9 @@
"wind_speed",
"wind_direction"
)
+ val result = mutableListOf<RealTimeDataVehicle>()
try {
- ExcelUtil.read(file, headerCheck = {
+ ExcelUtil.readXLXS(file, headerCheck = {
val cellIterator = it.cellIterator()
val headIterator = headers.iterator()
while (headIterator.hasNext()) {
@@ -57,21 +316,41 @@
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
}, onRow = {
- it.cellIterator().forEach {
- it.numericCellValue
+ val data = RealTimeDataVehicle().apply {
+ this.deviceCode = deviceCode
+ dataTime = it.getCell(0).dateCellValue
+ longitude = it.getCell(1).numericCellValue.toBigDecimal()
+ latitude = it.getCell(2).numericCellValue.toBigDecimal()
+ no2 = it.getCell(3).numericCellValue.toFloat()
+ co = it.getCell(4).numericCellValue.toFloat()
+ h2s = it.getCell(5).numericCellValue.toFloat()
+ so2 = it.getCell(6).numericCellValue.toFloat()
+ o3 = it.getCell(7).numericCellValue.toFloat()
+ pm25 = it.getCell(8).numericCellValue.toFloat()
+ pm10 = it.getCell(9).numericCellValue.toFloat()
+ temperature = it.getCell(10).numericCellValue.toFloat()
+ humidity = it.getCell(11).numericCellValue.toFloat()
+ voc = it.getCell(12).numericCellValue.toFloat()
+ noi = it.getCell(13).numericCellValue.toFloat()
+ velocity = it.getCell(14).numericCellValue.toFloat()
+ windSpeed = it.getCell(15).numericCellValue.toFloat()
+ windDirection = it.getCell(16).numericCellValue.toFloat()
}
+ result.add(data)
})
} catch (e: Exception) {
- throw ResponseErrorException("excel鏂囦欢鍐呭閿欒锛屾暟鎹浆鎹㈠け璐ワ紒", e)
+ e.printStackTrace()
+ throw BizException("excel鏂囦欢鍐呭閿欒锛屾暟鎹浆鎹㈠け璐ワ紒", e)
}
+ return result
}
/**
--
Gitblit v1.9.3