underway-spring.git

			@@ -1,9 +1,16 @@
			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.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
			@@ -11,6 +18,9 @@
			import java.text.SimpleDateFormat
			import java.util.*

			/**
			* 采集数据格式转换
			*/
			class FileExchange {

			companion object {
			@@ -23,11 +33,337 @@
			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: Exception) {
			e.printStackTrace()
			throw BizException("excel文件内容错误，数据转换失败！", e)
			}
			// 判断网格单元格编号是否在正确的范围内
			val resultCellIdSet = result.map { it.cellId ?: -1 }.toSet()
			// 用户导入中缺少的的单元格
			val lackCells = correctRangeGridCells - resultCellIdSet
			// excel 中多出的的单元格
			val outCells = resultCellIdSet - correctRangeGridCells
			if (lackCells.isNotEmpty()) {
			throw BizException("导入数据中缺少以下网格单元格：${lackCells.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: Exception) {
			e.printStackTrace()
			throw BizException("excel文件内容错误，数据转换失败！", e)
			}
			// 判断网格单元格编号是否在正确的范围内
			val resultCellIdSet = result.map { it.cellId ?: -1 }.toSet()
			// 用户导入中缺少的的单元格
			val lackCells = correctRangeGridCells - resultCellIdSet
			// excel 中多出的的单元格
			val outCells = resultCellIdSet - correctRangeGridCells
			if (lackCells.isNotEmpty()) {
			throw BizException("导入数据中缺少以下网格单元格：${lackCells.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)
			@@ -40,11 +376,27 @@
			val datetime = Date(time)
			//监测因子
			val jO = JSONObject.parseObject(value)
			val tmp = jO.getDoubleValue(TMP)
			val spC = jO.getDoubleValue(SPCOND)
			val tur = jO.getDoubleValue(TUR)
			val dO = jO.getDoubleValue(DO)
			val ph = jO.getDoubleValue(PH)
			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>()

			@@ -77,6 +429,18 @@
			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
			})
			}
			@@ -150,7 +514,7 @@
			}


			val newWorkBook = HSSFWorkbook()
			val newWorkBook = SXSSFWorkbook(10000)
			ExcelUtil.write2(heads, contents, newWorkBook, "data")

			newWorkBook.write(out)
			@@ -171,7 +535,15 @@
			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 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) {
			@@ -239,7 +611,7 @@
			contents.add(cList.toTypedArray())
			}

			val newWorkBook = HSSFWorkbook()
			val newWorkBook = SXSSFWorkbook(10000)
			ExcelUtil.write2(heads, contents, newWorkBook, "data")

			newWorkBook.write(out)
			@@ -248,4 +620,4 @@
			out.flush()
			out.close()
			}
			}
			}