package com.flightfeather.uav.common.utils
|
|
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
|
import java.io.FileOutputStream
|
import java.io.InputStream
|
import java.text.SimpleDateFormat
|
import java.util.*
|
|
/**
|
* 采集数据格式转换
|
*/
|
class FileExchange {
|
|
companion object {
|
private const val TMP = "65536"
|
private const val SPCOND = "65548"
|
private const val TUR = "65702"
|
private const val DO = "65542"
|
private const val PH = "65558"
|
|
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",
|
"latitude",
|
"NO2",
|
"CO",
|
"H2S",
|
"SO2",
|
"O3",
|
"PM25",
|
"PM10",
|
"temperature",
|
"humidity",
|
"VOC",
|
"NOI",
|
"velocity",
|
"wind_speed",
|
"wind_direction"
|
)
|
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("文件格式错误, 表头[${head}]应该为[${cellText}]")
|
}
|
} else {
|
throw BizException("文件格式错误, 表头[${head}]缺失")
|
}
|
}
|
true
|
}, onRow = {
|
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) {
|
e.printStackTrace()
|
throw BizException("excel文件内容错误，数据转换失败！", e)
|
}
|
return result
|
}
|
|
/**
|
* 转换无人船的水质监测数据
|
*/
|
fun exchangeBoatData(deviceCode: String, file: InputStream): List<RealTimeData> {
|
val workbook = HSSFWorkbook(file)
|
val sheet = workbook.getSheetAt(0)
|
|
val dataList = mutableListOf<RealTimeData>()
|
|
val lastData = mutableListOf<Double>()
|
|
for (i in 1 until sheet.lastRowNum) {
|
val row = sheet.getRow(i)
|
val time = row.getCell(2).numericCellValue.toLong() * 1000
|
val lat = row.getCell(4).numericCellValue
|
val lng = row.getCell(5).numericCellValue
|
val value = row.getCell(6).stringCellValue
|
|
//时间
|
val datetime = Date(time)
|
//监测因子
|
val jO = JSONObject.parseObject(value)
|
var tmp = jO.getDoubleValue(TMP)
|
var spC = jO.getDoubleValue(SPCOND)
|
var tur = jO.getDoubleValue(TUR)
|
var dO = jO.getDoubleValue(DO)
|
var ph = jO.getDoubleValue(PH)
|
|
if (lastData.isEmpty()) {
|
lastData.addAll(listOf(tmp, spC, tur, dO, ph))
|
} else {
|
if (tmp == .0) tmp = lastData[0]
|
if (spC == .0) spC = lastData[1]
|
if (tur == .0) tur = lastData[2]
|
if (dO == .0) dO = lastData[3]
|
if (ph == .0) ph = lastData[4]
|
}
|
|
lastData[0] = tmp
|
lastData[1] = spC
|
lastData[2] = tur
|
lastData[3] = dO
|
lastData[4] = ph
|
|
val factorsList = mutableListOf<AirData>()
|
|
factorsList.apply {
|
add(AirData().apply {
|
factorId = "1"
|
factorName = "TMP"
|
factorData = tmp
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "2"
|
factorName = "spC"
|
factorData = spC
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "3"
|
factorName = "tur"
|
factorData = tur
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "4"
|
factorName = "DO"
|
factorData = dO
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "5"
|
factorName = "PH"
|
factorData = ph
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "12"
|
factorName = "LNG"
|
factorData = lng
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "13"
|
factorName = "LAT"
|
factorData = lat
|
physicalQuantity = 0.0
|
})
|
}
|
|
val factors = JSONObject.toJSON(factorsList).toString()
|
|
dataList.add(RealTimeData().apply {
|
this.deviceCode = deviceCode
|
latitude = lat.toBigDecimal()
|
longitude = lng.toBigDecimal()
|
dataTime = datetime
|
createTime = Date()
|
this.factors = factors
|
})
|
}
|
|
return dataList
|
}
|
|
fun doTask() {
|
//source
|
val path = "E:\\工作\\金山走航\\MissionData.xls"
|
val workbook = HSSFWorkbook(FileInputStream(path))
|
val sheet = workbook.getSheetAt(0)
|
|
//target
|
val fileName = "无人船数据.xls"
|
val filePath = "e:/$fileName"
|
val out = FileOutputStream(File(filePath))
|
|
val heads = listOf("时间", "经度", "纬度", "Temperature(°C)", "spCond(uS/cm)", "Turbidity(NTU)", "DO(mg/L)", "PH")
|
val contents = mutableListOf<Array<Any>>()
|
|
val lastRow = mutableListOf<Any>()
|
for (i in 1 until sheet.lastRowNum) {
|
val row = sheet.getRow(i)
|
val time = row.getCell(2).numericCellValue.toLong() * 1000
|
val lat = row.getCell(4).numericCellValue
|
val lng = row.getCell(5).numericCellValue
|
val value = row.getCell(6).stringCellValue
|
|
val cList = mutableListOf<Any>()
|
//时间
|
val date = Date(time)
|
val d = format.format(date)
|
cList.add(d)
|
//经度
|
cList.add(lng)
|
cList.add(lat)
|
//
|
val jO = JSONObject.parseObject(value)
|
cList.add((jO[TMP] ?: "0").toString())
|
cList.add((jO[SPCOND] ?: "0").toString())
|
cList.add((jO[TUR] ?: "0").toString())
|
cList.add((jO[DO] ?: "0").toString())
|
cList.add((jO[PH] ?: "0").toString())
|
|
// if (lastRow.isEmpty()) {
|
// lastRow.addAll(cList)
|
// } else {
|
// for (i in cList.indices) {
|
// if (cList[i] == "") {
|
// cList[i] = lastRow[i]
|
// }
|
// }
|
// lastRow.clear()
|
// lastRow.addAll(cList)
|
// }
|
|
contents.add(cList.toTypedArray())
|
}
|
|
|
val newWorkBook = SXSSFWorkbook(10000)
|
ExcelUtil.write2(heads, contents, newWorkBook, "data")
|
|
newWorkBook.write(out)
|
newWorkBook.close()
|
workbook.close()
|
out.flush()
|
out.close()
|
}
|
|
fun doTask2() {
|
//source
|
val path = "E:\\工作\\金山走航\\无人船数据-补充空白值.xls"
|
val workbook = HSSFWorkbook(FileInputStream(path))
|
val sheet = workbook.getSheetAt(0)
|
|
//target
|
val fileName = "无人船走航数据.xls"
|
val filePath = "e:/$fileName"
|
val out = FileOutputStream(File(filePath))
|
|
val heads = listOf("id",
|
"device_code",
|
"latitude",
|
"longitude",
|
"altitude",
|
"height",
|
"factors",
|
"data_time",
|
"create_time")
|
val contents = mutableListOf<Array<Any>>()
|
|
for (i in 1..sheet.lastRowNum) {
|
val row = sheet.getRow(i)
|
val deviceCode = "0c"
|
val latitude = row.getCell(2).numericCellValue
|
val longitude = row.getCell(1).numericCellValue
|
val altitude = ""
|
val height = ""
|
val dataTime = row.getCell(0).stringCellValue
|
val createTime = dataTime
|
|
val factorsList = mutableListOf<AirData>()
|
val tmp = row.getCell(3).numericCellValue
|
val spC = row.getCell(4).numericCellValue
|
val tur = row.getCell(5).numericCellValue
|
val dO = row.getCell(6).numericCellValue
|
val ph = row.getCell(7).numericCellValue
|
|
factorsList.apply {
|
add(AirData().apply {
|
factorId = "1"
|
factorName = "TMP"
|
factorData = tmp
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "2"
|
factorName = "spC"
|
factorData = spC
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "3"
|
factorName = "tur"
|
factorData = tur
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "4"
|
factorName = "DO"
|
factorData = dO
|
physicalQuantity = 0.0
|
})
|
add(AirData().apply {
|
factorId = "5"
|
factorName = "PH"
|
factorData = ph
|
physicalQuantity = 0.0
|
})
|
}
|
|
val factors = JSONObject.toJSON(factorsList).toString()
|
val cList = mutableListOf<Any>()
|
cList.add("")
|
cList.add(deviceCode)
|
cList.add(latitude)
|
cList.add(longitude)
|
cList.add(altitude)
|
cList.add(height)
|
cList.add(factors)
|
cList.add(dataTime)
|
cList.add(createTime)
|
|
contents.add(cList.toTypedArray())
|
}
|
|
val newWorkBook = SXSSFWorkbook(10000)
|
ExcelUtil.write2(heads, contents, newWorkBook, "data")
|
|
newWorkBook.write(out)
|
newWorkBook.close()
|
workbook.close()
|
out.flush()
|
out.close()
|
}
|
}
|
