From 01ac08186355e85cab4a7c6f4403180184894f23 Mon Sep 17 00:00:00 2001
From: riku <risaku@163.com>
Date: 星期六, 09 十月 2021 14:38:44 +0800
Subject: [PATCH] 1. 新增网格化数据统计功能
---
src/main/kotlin/com/flightfeather/uav/dataprocess/PreData.kt | 143 +++++++++++
src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt | 16
src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt | 5
src/test/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImplTest.kt | 99 +++++++
src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt | 3
src/test/kotlin/com/flightfeather/uav/model/epw/EPWModelTest.kt | 3
pom.xml | 8
src/main/kotlin/com/flightfeather/uav/common/utils/FileUtil.kt | 14
src/test/kotlin/com/flightfeather/uav/model/epw/EPWDataPrepTest.kt | 75 +++++
src/test/kotlin/com/flightfeather/uav/dataprocess/DataProcessTest.kt | 67 +++++
src/test/kotlin/com/flightfeather/uav/Test.kt | 27 ++
src/main/kotlin/com/flightfeather/uav/model/BaseModel.kt | 7
src/main/kotlin/com/flightfeather/uav/domain/entity/ExpandFun.kt | 15
src/main/kotlin/com/flightfeather/uav/dataprocess/DataProcess.kt | 176 +++++++++++++
src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImpl.kt | 53 ++-
src/main/resources/application.yml | 12
16 files changed, 663 insertions(+), 60 deletions(-)
diff --git a/pom.xml b/pom.xml
index 0e3974e..11f104f 100644
--- a/pom.xml
+++ b/pom.xml
@@ -119,6 +119,14 @@
<version>4.1.2</version>
</dependency>
+ <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi-ooxml</artifactId>
+ <version>4.1.2</version>
+ </dependency>
+
+
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
diff --git a/src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt b/src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt
index c9c6086..0eb806e 100644
--- a/src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt
+++ b/src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt
@@ -2,6 +2,7 @@
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.util.CellRangeAddress
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.time.LocalDate
import java.util.*
import kotlin.math.max
@@ -24,7 +25,7 @@
/**
* 鑷姩澶勭悊琛屽悎骞舵暟鎹�
*/
- fun write2(heads: List<String>, contents: List<Array<Any>>, workbook: HSSFWorkbook, sheetName:String) {
+ fun write2(heads: List<String>, contents: List<Array<Any>>, workbook: SXSSFWorkbook, sheetName:String) {
val sheet = workbook.createSheet(sheetName)
@@ -152,12 +153,12 @@
/**
* 鑷姩澶勭悊琛屽悎骞舵暟鎹�
*/
- fun write(heads: List<Array<String>>, contents: List<Array<Any>>, workbook: HSSFWorkbook, sheetName: String = "sheet1") {
+ fun write(heads: List<Array<String>>, contents: List<Array<Any>>, workbook: SXSSFWorkbook, sheetName: String = "sheet1", row: Int = 0): Int {
- val sheet = workbook.createSheet(sheetName)
- println("sheet: $sheetName")
+ val sheet = workbook.getSheet(sheetName)?: workbook.createSheet(sheetName)
+// println("sheet: $sheetName")
- var rowIndex = 0
+ var rowIndex = row
heads.forEach {
val rows = sheet.createRow(rowIndex)
@@ -202,7 +203,7 @@
cell[0]
}
} else {
- //褰撴暟鎹笉鏄暟缁勬椂锛岄渶瑕佹寜鏈�澶ц鏁板悎骞跺崟鍏冩牸
+ //褰撴暟鎹笉鏄暟缁勬椂锛屾牴鎹缃悎骞�
if (cell is MyCell) {
rowspan = cell.rowSpan
colSpan = cell.colSpan
@@ -216,7 +217,7 @@
when (c) {
is MyCell -> {
rows.createCell(col).apply {
- c.fontColor?.let {fC ->
+ c.fontColor?.let { fC ->
val font = workbook.createFont()
val cellStyle = workbook.createCellStyle()
@@ -299,6 +300,7 @@
// workbook.close()
// out.flush()
// out.close()
+ return rowIndex
}
private fun getMaxRows(rowArray: Array<Any>): Int {
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 678ec0a..c6dd971 100644
--- a/src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt
+++ b/src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt
@@ -4,6 +4,7 @@
import com.flightfeather.uav.domain.entity.RealTimeData
import com.flightfeather.uav.socket.bean.AirData
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.io.File
import java.io.FileInputStream
import java.io.FileOutputStream
@@ -153,7 +154,7 @@
}
- val newWorkBook = HSSFWorkbook()
+ val newWorkBook = SXSSFWorkbook(10000)
ExcelUtil.write2(heads, contents, newWorkBook, "data")
newWorkBook.write(out)
@@ -242,7 +243,7 @@
contents.add(cList.toTypedArray())
}
- val newWorkBook = HSSFWorkbook()
+ val newWorkBook = SXSSFWorkbook(10000)
ExcelUtil.write2(heads, contents, newWorkBook, "data")
newWorkBook.write(out)
diff --git a/src/main/kotlin/com/flightfeather/uav/common/utils/FileUtil.kt b/src/main/kotlin/com/flightfeather/uav/common/utils/FileUtil.kt
index f825d2d..851cedf 100644
--- a/src/main/kotlin/com/flightfeather/uav/common/utils/FileUtil.kt
+++ b/src/main/kotlin/com/flightfeather/uav/common/utils/FileUtil.kt
@@ -36,7 +36,7 @@
}
}
- fun saveObdData(str: String) {
+ fun saveObdData(str: String, newFile: Boolean = false) {
//妫�鏌ユ枃妗ｆ槸鍚﹀瓨鍦�
if (!file.exists()) {
file.parentFile.mkdirs()
@@ -45,7 +45,7 @@
println("----鍒涘缓鏂囦欢锛�${file.absolutePath}")
}
//鏂囦欢鏈�澶�512Kb,瓒呰繃鍚庢柊寤烘枃妗�
- if (file.length() + str.toByteArray().size > 512 * 1024 || TimeUtil.isNextDay(oldTime, Date())) {
+ if (newFile || file.length() + str.toByteArray().size > 512 * 1024 || TimeUtil.isNextDay(oldTime, Date())) {
//瓒呰繃涓�澶╁悗锛屾洿鏂板綋鍓嶆椂闂�
oldTime = Date()
@@ -59,10 +59,10 @@
flush()
close()
}
- fw?.run {
- flush()
- close()
- }
+// fw?.run {
+// flush()
+// close()
+// }
//鏂板缓杈撳嚭娴�
fw = FileWriter(file, true)
bw = BufferedWriter(fw)
@@ -79,7 +79,7 @@
flush()
}
- readyToShutDownStream(bw, fw)
+// readyToShutDownStream(bw, fw)
println("----鍐欏叆瀹屾垚")
}
diff --git a/src/main/kotlin/com/flightfeather/uav/dataprocess/DataProcess.kt b/src/main/kotlin/com/flightfeather/uav/dataprocess/DataProcess.kt
new file mode 100644
index 0000000..3f39b6a
--- /dev/null
+++ b/src/main/kotlin/com/flightfeather/uav/dataprocess/DataProcess.kt
@@ -0,0 +1,176 @@
+package com.flightfeather.uav.dataprocess
+
+import com.flightfeather.uav.common.utils.DateUtil
+import com.flightfeather.uav.common.utils.ExcelUtil
+import com.flightfeather.uav.lightshare.bean.DataVo
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
+import org.apache.poi.xssf.usermodel.XSSFWorkbook
+import java.io.File
+import java.io.FileInputStream
+import java.io.FileOutputStream
+import java.util.*
+
+/**
+ * 鏁版嵁澶勭悊
+ * 鐩爣锛氬皬鏃舵暟鎹�佹棩鏁版嵁鍜屾棩鍙樺寲3绫�
+ * 鎸囨爣锛氬姣忕被鏁版嵁锛岃绠楄褰曟暟銆佸潎鍊笺�佹爣鍑嗗樊銆佹渶灏忓�笺�佹渶澶у�硷紱濡傛灉璁＄畻閲忓彲鎺ュ彈锛岃绠�10/25/50/75/90 %鍒嗕綅鍊笺��
+ * 杈撳嚭鏍峰紡锛氾紙鍒楁暟鎹級鏃堕棿銆佽褰曟暟銆佸潎鍊笺�佹爣鍑嗗樊銆佹渶灏忓�笺�佹渶澶у�硷紝鍒嗕綅鍊硷紱鏃堕棿鍙互鏄痽yyymmddhh鈥︾畝鍖栨牸寮忔垨鏍囧噯鏃堕棿鏍煎紡锛涘皬鏃舵暟鎹爣璁板悗璁帮紙鏃堕棿璁板湪鍚庯紝濡�17鏃惰〃绀�16~17鏃朵箣闂寸殑缁撴灉锛夋垨鍓嶈鏂瑰紡锛屾棩鍙樺寲鐨勫皬鏃朵害鍚屻��
+ */
+class DataProcess(
+ private val outPutPath: String
+) {
+ companion object {
+ const val SHEET1 = "灏忔椂鏁版嵁"
+ const val SHEET2 = "鏃ユ暟鎹�"
+ const val SHEET3 = "鏃ュ彉鍖�"
+ }
+ //姣忔棩灏忔椂鏁版嵁
+ private val dateData = mutableMapOf<String?, PreData>()
+ //姣忔棩鏁版嵁
+ private val dayData = mutableMapOf<String?, PreData>()
+ //鏃ュ皬鏃跺彉鍖栨暟鎹�
+ private val dailyVariationData = mutableMapOf<String?, PreData>()
+
+ //鍐欏叆excel鏂囦欢
+ private var workBook = SXSSFWorkbook(1000)
+ //杈撳嚭娴�
+ private lateinit var out: FileOutputStream
+ //杈撳叆娴�
+ private lateinit var input: FileInputStream
+ //excel 姣忓紶sheet褰撳墠鍐欏叆鐨勮鏁�
+ private val sheetRow = mutableMapOf<String, Int>().apply {
+ put(SHEET1, 0)
+ put(SHEET2, 0)
+ put(SHEET3, 0)
+ }
+
+ init {
+ outPutHead()
+ }
+
+ /**
+ * 鎵ц鏁版嵁棰勫鐞�
+ * 1. 鍗曟杈撳叆鐨勬暟鎹姹備负涓�澶╃殑鏁版嵁锛岃緭鍏ュ悗浼氱洿鎺ヨ绠椻�滄瘡鏃ュ皬鏃舵暟鎹�濆拰鈥滄瘡鏃ユ暟鎹�濈殑缁撴灉骞跺啓鍏xcel
+ * 2. 鏃ュ皬鏃跺彉鍖栨暟鎹敱浜庤法鏃ョ粺璁★紝闇�瑕佹墜鍔ㄨ皟鐢�
+ */
+ fun process(dataList: List<DataVo>) {
+ dateData.clear()
+ dayData.clear()
+// dailyVariationData.clear()
+ dataList.forEach {
+ val date = it.time?.substring(0, 13)
+ val day = it.time?.substring(0, 10)
+ val hour = it.time?.substring(11, 13)
+
+ if (!dateData.containsKey(date)) {
+ dateData[date] = PreData(date)
+ }
+ dateData[date]?.add(it)
+
+ if (!dayData.containsKey(day)) {
+ dayData[day] = PreData(day)
+ }
+ dayData[day]?.add(it)
+
+ if (!dailyVariationData.containsKey(hour)) {
+ dailyVariationData[hour] = PreData(hour, false)
+ }
+ dailyVariationData[hour]?.add(it)
+ }
+
+ outPut()
+ }
+
+ /**
+ * 杈撳嚭鏂囨。鏍囬
+ */
+ private fun outPutHead() {
+ val file = File(outPutPath)
+ out = FileOutputStream(file)
+
+ val contents = mutableListOf<Array<Any>>()
+ //绗竴琛屼负鏃堕棿浠ュ強鐩戞祴鍥犲瓙鍚嶇О锛屽苟涓旈渶瑕佹牴鎹笅涓�琛岀殑缁熻椤瑰悎骞跺垪
+ val firstRow = mutableListOf<ExcelUtil.MyCell>()
+ firstRow.add(ExcelUtil.MyCell("鏃堕棿", 2))
+ PreData.needFactor.forEach { firstRow.add(ExcelUtil.MyCell(it.des, colSpan = PreData.items.size)) }
+ contents.add(firstRow.toTypedArray())
+ //绗簩琛屼负姣忕鐩戞祴鍥犲瓙瀵瑰簲鐨勭粺璁￠」锛屽叿浣撲负璁板綍鏁般�佸潎鍊笺�佹爣鍑嗗樊銆佹渶灏忓�笺�佹渶澶у�笺��10/25/50/75/90%鍒嗕綅鍊硷紝姣忕鐩戞祴鍥犲瓙鏈�10鍒�
+ val secondRow = mutableListOf<String>()
+ secondRow.add("")
+ repeat(PreData.needFactor.size) { secondRow.addAll(PreData.items) }
+ contents.add(secondRow.toTypedArray())
+ //鍒唖heet寤虹珛鏍囬
+ sheetRow.forEach { (s, i) ->
+ val row = ExcelUtil.write(emptyList(), contents, workBook, s, i)
+ sheetRow[s] = row
+ }
+ workBook.write(out)
+ workBook.close()
+ out.flush()
+ out.close()
+ }
+
+ /**
+ * 鍐欏叆姣忔棩灏忔椂鏁版嵁鍜屾瘡鏃ユ暟鎹粨鏋�
+ * 鑷姩璋冪敤锛� 瑕佹眰杈撳叆鐨勬暟鎹互姣忔棩涓轰竴缁�
+ */
+ private fun outPut() {
+ input = FileInputStream(outPutPath)
+ workBook = SXSSFWorkbook(XSSFWorkbook(input), 1000)
+ out = FileOutputStream(outPutPath)
+ //绗笁琛屽紑濮嬩负璁＄畻缁撴灉锛屾瘡绉嶇洃娴嬪洜瀛愰兘鏈夊搴旂殑涓�绯诲垪缁熻椤�
+
+ //姣忔棩灏忔椂鏁版嵁
+ val contents = mutableListOf<Array<Any>>()
+ dateData.forEach { (_, pData) ->
+ contents.add(pData.getOutPutContent())
+ }
+ sheetRow[SHEET1] = ExcelUtil.write(emptyList(), contents, workBook, SHEET1, sheetRow[SHEET1] ?: 0)
+
+ //姣忔棩鏁版嵁
+ contents.clear()
+ dayData.forEach { (_, pData) ->
+ contents.add(pData.getOutPutContent())
+ }
+ sheetRow[SHEET2] = ExcelUtil.write(emptyList(), contents, workBook, SHEET2, sheetRow[SHEET2] ?: 0)
+
+ workBook.write(out)
+ workBook.close()
+ input.close()
+ out.flush()
+ out.close()
+ }
+
+ /**
+ * 鍐欏叆鏃ュ皬鏃跺彉鍖栨暟鎹�
+ * 鎵嬪姩璋冪敤
+ */
+ fun outPutDailyVariation() {
+ input = FileInputStream(outPutPath)
+ workBook = SXSSFWorkbook(XSSFWorkbook(input), 1000)
+ out = FileOutputStream(outPutPath)
+ //鏃ュ皬鏃跺彉鍖栨暟鎹�
+ val contents = mutableListOf<Array<Any>>()
+ dailyVariationData.forEach { (_, pData) ->
+ contents.add(pData.getOutPutContent())
+ }
+ sheetRow[SHEET3] = ExcelUtil.write(emptyList(), contents, workBook, SHEET3, sheetRow[SHEET3] ?: 0)
+
+ workBook.write(out)
+ workBook.close()
+ input.close()
+ out.flush()
+ out.close()
+ }
+
+ /**
+ * 鏁版嵁澶勭悊缁撴潫
+ * 璋冪敤浠ュ叧闂緭鍑烘祦锛岄噴鏀惧唴瀛�
+ */
+ fun done() {
+ workBook.close()
+ input.close()
+ out.flush()
+ out.close()
+ }
+}
\ No newline at end of file
diff --git a/src/main/kotlin/com/flightfeather/uav/dataprocess/PreData.kt b/src/main/kotlin/com/flightfeather/uav/dataprocess/PreData.kt
new file mode 100644
index 0000000..8a8d0cb
--- /dev/null
+++ b/src/main/kotlin/com/flightfeather/uav/dataprocess/PreData.kt
@@ -0,0 +1,143 @@
+package com.flightfeather.uav.dataprocess
+
+import com.flightfeather.uav.lightshare.bean.DataVo
+import com.flightfeather.uav.socket.eunm.FactorType
+import java.math.BigDecimal
+import kotlin.math.max
+import kotlin.math.min
+import kotlin.math.round
+import kotlin.math.sqrt
+
+/**
+ * 棰勫鐞嗘暟鎹�
+ */
+class PreData(
+ //缁熻鏃堕棿
+ private val time: String?,
+ //鏄惁闇�瑕佽绠楀垎浣嶅��
+ private val needQuartile: Boolean = true
+) {
+
+ inner class TempData {
+ //鏁版嵁骞虫柟鍜岋紙鐢ㄤ簬璁＄畻鏍囧噯宸級
+ var sumOfSquares: BigDecimal = BigDecimal.ZERO
+ //鏁版嵁鍜�
+ var total: BigDecimal = BigDecimal.ZERO
+
+ //璁℃暟
+ var count: Int = 0
+ var minV: Double = Double.MAX_VALUE
+ var maxV: Double = Double.MIN_VALUE
+
+ //鏁版嵁鍊艰褰曪紝鎸夌収鍗囧簭鎺掑垪锛岀敤浜庤幏鍙栧垎浣嶅��
+ // "10%鍒嗕綅鍊�",
+ // "25%鍒嗕綅鍊�",
+ // "50%鍒嗕綅鍊�",
+ // "75%鍒嗕綅鍊�",
+ // "90%鍒嗕綅鍊�"
+ val dataList = mutableListOf<Double>()
+ }
+
+ companion object {
+ //闇�瑕佺粺璁＄殑鐩戞祴鍥犲瓙
+ val needFactor = listOf(
+// FactorType.NO2, FactorType.CO, FactorType.H2S, FactorType.SO2, FactorType.O3, FactorType.PM25, FactorType.PM10,
+ FactorType.VOC
+ )
+ //闇�瑕佽绠楃殑椤圭洰
+ val items = listOf(
+ "璁板綍鏁�", "鍧囧��", "鏍囧噯宸�", "鏈�灏忓��", "鏈�澶у��", "10%鍒嗕綅鍊�", "25%鍒嗕綅鍊�", "50%鍒嗕綅鍊�", "75%鍒嗕綅鍊�", "90%鍒嗕綅鍊�"
+ )
+ }
+
+ //鎸夌収姣忕鐩戞祴鍥犲瓙璁板綍褰撳墠鏃堕棿涓嬬殑缁熻涓棿鏁版嵁
+ private val dataMap = mutableMapOf<String?, TempData>()
+
+ fun add(data: DataVo) {
+ data.values?.forEach {
+ if (!dataMap.containsKey(it.factorName)) {
+ dataMap[it.factorName] = TempData()
+ }
+ dataMap[it.factorName]?.apply {
+ sumOfSquares = sumOfSquares.plus(BigDecimal.valueOf(
+ (it.factorData ?: .0) * (it.factorData ?: .0)
+ ))
+ total = total.plus(BigDecimal.valueOf(it.factorData ?: .0))
+ count++
+ minV = min(minV, it.factorData?: .0)
+ maxV = max(maxV, it.factorData?: .0)
+
+ if (needQuartile) {
+ dataList.add(it.factorData ?: .0)
+ }
+ }
+ }
+ }
+
+ /**
+ * 杈撳嚭褰撳墠缁熻鏃堕棿涓嬬殑涓�琛岀粺璁℃暟鎹�
+ */
+ fun getOutPutContent(): Array<Any> {
+ //姣忕鐩戞祴鍥犲瓙閮芥湁瀵瑰簲鐨勪竴绯诲垪缁熻椤�
+ val content = mutableListOf<Any>()
+ //绗竴鍒楁槸缁熻鏃堕棿
+ content.add(time ?: "閿欒鏃堕棿")
+ needFactor.forEach {
+ dataMap[it.name]?.let {t ->
+ content.apply {
+ //"璁板綍鏁�",
+ val c = BigDecimal("${t.count}")
+ add(t.count)
+ // "鍧囧��",
+ val avg = t.total.div(c)
+ add(avg.toString())
+ // "鏍囧噯宸�",
+ add(
+ sqrt(
+ t.sumOfSquares.minus(
+ avg.times(BigDecimal(2)).times(t.total)
+ ).plus(
+ avg.times(avg).times(c)
+ ).div(c).toDouble()
+ ).toString()
+ )
+ // "鏈�灏忓��",
+ add(t.minV)
+ // "鏈�澶у��",
+ add(t.maxV)
+ //鍒嗕綅鍊�
+ if (needQuartile) {
+ t.dataList.sortBy { d -> d }
+ val size = t.dataList.size
+ // "10%鍒嗕綅鍊�",
+ add(t.dataList[round(size * .1).toInt()])
+ // "25%鍒嗕綅鍊�",
+ add(t.dataList[round(size * .25).toInt()])
+ // "50%鍒嗕綅鍊�",
+ add(t.dataList[round(size * .5).toInt()])
+ // "75%鍒嗕綅鍊�",
+ add(t.dataList[round(size * .75).toInt()])
+ // "90%鍒嗕綅鍊�"
+ add(t.dataList[round(size * .9).toInt()])
+ } else {
+ add("")
+ add("")
+ add("")
+ add("")
+ add("")
+ }
+ }
+ }
+ }
+
+ clear()
+ return content.toTypedArray()
+ }
+
+ /**
+ * 閲婃斁鍐呭瓨
+ */
+ private fun clear() {
+ dataMap.clear()
+ }
+}
\ No newline at end of file
diff --git a/src/main/kotlin/com/flightfeather/uav/domain/entity/ExpandFun.kt b/src/main/kotlin/com/flightfeather/uav/domain/entity/ExpandFun.kt
index af678c0..46d5633 100644
--- a/src/main/kotlin/com/flightfeather/uav/domain/entity/ExpandFun.kt
+++ b/src/main/kotlin/com/flightfeather/uav/domain/entity/ExpandFun.kt
@@ -3,6 +3,7 @@
import com.flightfeather.uav.common.utils.DateUtil
import com.flightfeather.uav.common.utils.GsonUtils
import com.flightfeather.uav.socket.bean.AirData
+import com.flightfeather.uav.socket.eunm.FactorType
/**
* 鏁版嵁搴撹〃瀹炰綋鎵╁睍鏂规硶
@@ -15,9 +16,11 @@
list.add("绾害")
val values = GsonUtils.parserJsonToArrayBeans(factors, AirData::class.java)
values.forEach {
- val name = it.factorName ?: ""
- list.add(name)
- list.add("$name(鐗╃悊閲�)")
+ if (FactorType.outputFactor(it.factorName)) {
+ val name = it.factorName ?: ""
+ list.add(name)
+// list.add("$name(鐗╃悊閲�)")
+ }
}
return list.toTypedArray()
}
@@ -38,8 +41,10 @@
}
val values = GsonUtils.parserJsonToArrayBeans(factors, AirData::class.java)
values.forEach {
- row.add(it.factorData ?: -1.0)
- row.add(it.physicalQuantity ?: -1.0)
+ if (FactorType.outputFactor(it.factorName)) {
+ row.add(it.factorData ?: -1.0)
+// row.add(it.physicalQuantity ?: -1.0)
+ }
}
return row.toTypedArray()
}
diff --git a/src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt b/src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt
index d94a2f2..bb9edb1 100644
--- a/src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt
+++ b/src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt
@@ -4,6 +4,7 @@
import com.flightfeather.uav.lightshare.bean.DataImportResult
import com.flightfeather.uav.lightshare.bean.DataVo
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.springframework.web.multipart.MultipartFile
import javax.servlet.http.HttpServletResponse
@@ -15,7 +16,7 @@
fun importData(file: MultipartFile): BaseResponse<DataImportResult>
- fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): HSSFWorkbook
+ fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): SXSSFWorkbook
fun outToExcel(deviceCode: String, startTime: String, endTime: String, response: HttpServletResponse): HttpServletResponse
}
\ No newline at end of file
diff --git a/src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImpl.kt b/src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImpl.kt
index c7843aa..75a581e 100644
--- a/src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImpl.kt
+++ b/src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImpl.kt
@@ -1,5 +1,6 @@
package com.flightfeather.uav.lightshare.service.impl
+import com.flightfeather.uav.common.utils.DateUtil
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.common.utils.FileExchange
import com.flightfeather.uav.common.utils.GsonUtils
@@ -11,15 +12,13 @@
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import com.flightfeather.uav.socket.bean.AirData
import com.github.pagehelper.PageHelper
-import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.springframework.stereotype.Service
import org.springframework.web.multipart.MultipartFile
import tk.mybatis.mapper.entity.Example
import java.io.ByteArrayInputStream
-import java.io.FileInputStream
-import java.io.InputStream
-import java.text.DateFormat
import java.text.SimpleDateFormat
+import java.util.*
import javax.servlet.http.HttpServletResponse
@Service
@@ -48,11 +47,14 @@
}
}
}).forEach {
+ if (it.longitude == null || it.latitude == null) {
+ return@forEach
+ }
result.add(DataVo(
dateFormatter.format(it.dataTime),
it.deviceCode,
GsonUtils.parserJsonToArrayBeans(it.factors, AirData::class.java),
- it.longitude.toDouble(), it.latitude.toDouble()
+ it.longitude?.toDouble(), it.latitude?.toDouble()
))
}
if (startTime == null && endTime == null) {
@@ -89,22 +91,37 @@
return BaseResponse(true, data = DataImportResult(""))
}
- override fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): HSSFWorkbook {
+ override fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): SXSSFWorkbook {
val sTime = dateFormatter.parse(startTime)
val eTime = dateFormatter.parse(endTime)
- val r = realTimeDataMapper.selectByExample(Example(RealTimeData::class.java).apply {
- createCriteria().andEqualTo("deviceCode", deviceCode)
- .apply {
- sTime?.let { andGreaterThanOrEqualTo("dataTime", it) }
- eTime?.let { andLessThanOrEqualTo("dataTime", it) }
+ var page = 1
+ var totalPage = 1
+ val pageSize = 10000
+ val workbook = SXSSFWorkbook()
+ var rowIndex = 0
+ while (page <= totalPage) {
+ val pageInfo = PageHelper.startPage<RealTimeData>(page, pageSize)
+ val r = realTimeDataMapper.selectByExample(Example(RealTimeData::class.java).apply {
+ createCriteria().andEqualTo("deviceCode", deviceCode)
+ .apply {
+ sTime?.let { andGreaterThanOrEqualTo("dataTime", it) }
+ eTime?.let { andLessThanOrEqualTo("dataTime", it) }
+ }
+ })
+ if (r.isNotEmpty()) {
+ val heads = if (page == 1) {
+ println("[${DateUtil.instance.dateToString(Date(), DateUtil.DateStyle.YYYY_MM_DD_HH_MM_SS)}] totalPage: ${pageInfo.pages}")
+ getTableTitle(r[0])
+ } else {
+ emptyList()
}
- })
- val workbook = HSSFWorkbook()
- if (r.isNotEmpty()) {
- val heads = getTableTitle(r[0])
- val contents = getTableContents(r)
-
- ExcelUtil.write(heads, contents, workbook)
+ val contents = getTableContents(r)
+ print("[${DateUtil.instance.dateToString(Date(), DateUtil.DateStyle.YYYY_MM_DD_HH_MM_SS)}] currentPage: ${pageInfo.pageNum}......")
+ rowIndex = ExcelUtil.write(heads, contents, workbook, row = rowIndex)
+ println("output done")
+ }
+ totalPage = pageInfo.pages
+ page++
}
return workbook
}
diff --git a/src/main/kotlin/com/flightfeather/uav/model/BaseModel.kt b/src/main/kotlin/com/flightfeather/uav/model/BaseModel.kt
index 7d472a4..42a5b09 100644
--- a/src/main/kotlin/com/flightfeather/uav/model/BaseModel.kt
+++ b/src/main/kotlin/com/flightfeather/uav/model/BaseModel.kt
@@ -4,6 +4,7 @@
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.socket.eunm.FactorType
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.io.File
import java.io.FileOutputStream
import java.util.*
@@ -88,14 +89,14 @@
fun outputToExcel(
fName: String? = null,
- _workbook: HSSFWorkbook? = null,
+ _workbook: SXSSFWorkbook? = null,
_out: FileOutputStream? = null,
sheetName: String = "sheet1",
done: Boolean = true
- ): Pair<HSSFWorkbook, FileOutputStream>? {
+ ): Pair<SXSSFWorkbook, FileOutputStream>? {
// val rMap = formatConversion()
- val workbook = _workbook ?: HSSFWorkbook()
+ val workbook = _workbook ?: SXSSFWorkbook()
val fileName = fName ?: "姹℃煋婧簮鏉冮噸妯″瀷${DateUtil.instance.dateToString(Date(), "yyyy-MM-ddHHmmss")}.xls"
// val filePath = "E:\\work\\export\\$fileName"
// val filePath = "E:\\宸ヤ綔\\寮�鍙慭\璧拌埅鐩戞祴\\绠楁硶鐩稿叧\\鑷姩杈撳嚭\\$fileName"
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index e03ad45..1087cd6 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -7,9 +7,9 @@
# password: cn.FLIGHTFEATHER
# 绾夸笂鏈嶅姟鍣�
- url: jdbc:mysql://localhost:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
- username: dronemonitor
- password: dronemonitor_hackxrnomxm
+# url: jdbc:mysql://localhost:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
+# username: dronemonitor
+# password: dronemonitor_hackxrnomxm
# 寮�鍙戞湰鍦版湇鍔″櫒
# url: jdbc:mysql://localhost:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
@@ -17,9 +17,9 @@
# password: 123456
# 寮�鍙戣繙绋嬫湇鍔″櫒
-# url: jdbc:mysql://47.100.191.150:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
-# username: remoteU1
-# password: eSoF8DnzfGTlhAjE
+ url: jdbc:mysql://47.100.191.150:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
+ username: remoteU1
+ password: eSoF8DnzfGTlhAjE
hikari:
maximum-pool-size: 500
minimum-idle: 20
diff --git a/src/test/kotlin/com/flightfeather/uav/Test.kt b/src/test/kotlin/com/flightfeather/uav/Test.kt
index 52fc02f..85e68a9 100644
--- a/src/test/kotlin/com/flightfeather/uav/Test.kt
+++ b/src/test/kotlin/com/flightfeather/uav/Test.kt
@@ -1,6 +1,7 @@
package com.flightfeather.uav
import com.flightfeather.uav.common.utils.FileExchange
+import com.flightfeather.uav.common.utils.FileUtil
import com.flightfeather.uav.domain.entity.Company
import com.flightfeather.uav.socket.bean.DataUnit
import com.flightfeather.uav.socket.decoder.AirDataDecoder
@@ -10,6 +11,7 @@
import java.io.File
import java.io.FileOutputStream
import java.io.OutputStreamWriter
+import java.math.BigDecimal
import java.text.SimpleDateFormat
import java.util.*
@@ -69,7 +71,7 @@
@Test
fun get_crc16(){
- val dataSegment = "QN=20210713115502858;ST=22;CN=2011;PW=555555;MN=FYHB0MH0300045;Flag=1;CP=&&DataTime=20210713115000;a34001-Avg=0.025,a34001-CPM=5.9,a34001-Flag=N;a50001-Avg=0.0,a50001-Flag=D;a01001-Avg=33.0,a01001-Flag=N;a01002-Avg=68.2,a01002-Flag=N;a01007-Avg=0.0,a01007-Flag=N;a01008-Avg=0.0,a01008-Flag=N;Period=5;Scale=0.0;SelfTemp=0.0;SelfHum=0.0;IsReplacement=N&&"
+ val dataSegment = "QN=20210713133901044;ST=22;CN=2011;PW=555555;MN=FYHB0DT0100001;Flag=1;CP=&&DataTime=20210713133800;a34001-Avg=0.017,a34001-CPM=3.9,a34001-Flag=N;a50001-Avg=71.1,a50001-Flag=N;a01001-Avg=34.0,a01001-Flag=N;a01002-Avg=59.3,a01002-Flag=N;a01007-Avg=0.6,a01007-Flag=N;a01008-Avg=256.3,a01008-Flag=N;Period=1;Scale=1.0;SelfTemp=0.0;SelfHum=0.0;IsReplacement=N&&"
var CRC = 0x0000ffff
val POLYNOMIAL = 0x0000a001
var i: Int
@@ -100,4 +102,27 @@
}
println(strCRC)
}
+
+ @Test
+ fun foo15() {
+ var i = 0
+ do {
+ if (i == 3) {
+ FileUtil.instance?.saveObdData("msg", true)
+ } else {
+ FileUtil.instance?.saveObdData("msg")
+ }
+ i++
+ } while (i < 10)
+ }
+
+ @Test
+ fun foo16() {
+ val l = mutableListOf<BigDecimal>().apply {
+ add(BigDecimal.valueOf(6.23))
+ add(BigDecimal("6.23"))
+ add(BigDecimal(6.23))
+ }
+ l.forEach { println(it) }
+ }
}
\ No newline at end of file
diff --git a/src/test/kotlin/com/flightfeather/uav/dataprocess/DataProcessTest.kt b/src/test/kotlin/com/flightfeather/uav/dataprocess/DataProcessTest.kt
new file mode 100644
index 0000000..bea0d39
--- /dev/null
+++ b/src/test/kotlin/com/flightfeather/uav/dataprocess/DataProcessTest.kt
@@ -0,0 +1,67 @@
+package com.flightfeather.uav.dataprocess
+
+import com.flightfeather.uav.common.utils.DateUtil
+import com.flightfeather.uav.common.utils.ExcelUtil
+import com.flightfeather.uav.domain.entity.RealTimeData
+import com.flightfeather.uav.domain.mapper.RealTimeDataMapper
+import com.flightfeather.uav.lightshare.service.RealTimeDataService
+import com.github.pagehelper.PageHelper
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
+import org.junit.Assert.*
+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.test.context.junit4.SpringRunner
+import tk.mybatis.mapper.entity.Example
+import java.text.SimpleDateFormat
+import java.time.LocalDateTime
+import java.time.format.DateTimeFormatter
+import java.util.*
+
+@RunWith(SpringRunner::class)
+@SpringBootTest
+class DataProcessTest {
+
+ @Autowired
+ lateinit var realTimeDataMapper: RealTimeDataMapper
+
+ @Autowired
+ lateinit var realTimeDataService: RealTimeDataService
+
+ @Test
+ fun dataProcess() {
+ val fileName = "缃戞牸鍖栫洃娴嬫暟鎹澶勭悊${Date().time}.xls"
+ val filePath = "C:\\work\\宸ヤ綔\\璧拌埅鐩戞祴\\璧拌埅鐩戞祴\\$fileName"
+// val filePath = "E:\\宸ヤ綔\\寮�鍙慭\璧拌埅鐩戞祴\\鏁版嵁棰勫鐞哱\$fileName"
+ val process = DataProcess(filePath)
+
+ /**
+ * 鎸夋瘡鏃ラ棿闅斿惊鐜幏鍙栨暟鎹紝杩涜鏁板鐞�
+ * 鏁版嵁涓�4绉掗挓1涓�硷紝鍥犳涓�澶╃殑鏁版嵁閲忔渶澶氫负21600涓�
+ */
+ //鎴鏃堕棿
+ val endDateTime = LocalDateTime.of(2021, 10, 5, 10, 0, 0)
+ var sTime = LocalDateTime.of(2021, 6, 19, 0, 0, 0)
+ var eTime = LocalDateTime.of(2021, 6, 19, 23, 59, 59)
+
+ while (sTime.isBefore(endDateTime)) {
+ if (eTime.isAfter(endDateTime)) {
+ eTime = endDateTime
+ }
+ val deviceCode = "0d0000000001"
+ val dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
+ val s = sTime.format(dateFormatter)
+ val e = eTime.format(dateFormatter)
+ val data = realTimeDataService.getSecondData(deviceCode, s, e, 1, 30000).data
+ data?.let {
+ process.process(it)
+ }
+ sTime = sTime.plusDays(1)
+ eTime = eTime.plusDays(1)
+ }
+
+ process.outPutDailyVariation()
+ process.done()
+ }
+}
\ No newline at end of file
diff --git a/src/test/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImplTest.kt b/src/test/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImplTest.kt
index 4845484..f70d06c 100644
--- a/src/test/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImplTest.kt
+++ b/src/test/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImplTest.kt
@@ -1,7 +1,12 @@
package com.flightfeather.uav.lightshare.service.impl
import com.flightfeather.uav.common.utils.DateUtil
+import com.flightfeather.uav.common.utils.ExcelUtil
+import com.flightfeather.uav.dataprocess.DataProcess
+import com.flightfeather.uav.domain.entity.RealTimeData
import com.flightfeather.uav.lightshare.service.RealTimeDataService
+import com.github.pagehelper.PageHelper
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.junit.Test
import org.junit.Assert.*
@@ -9,6 +14,7 @@
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
+import tk.mybatis.mapper.entity.Example
import java.io.File
import java.io.FileOutputStream
import java.util.*
@@ -23,14 +29,91 @@
@Test
fun outToExcel() {
if (realTimeDataService is RealTimeDataServiceImpl) {
- val workbook = realTimeDataService.outToWorkbook("0a0000000001", "2021-07-05 00:00:00", "2021-07-06 23:59:59")
- val fileName = "姹℃煋婧簮鏉冮噸妯″瀷${DateUtil.instance.dateToString(Date(), "yyyy-MM-ddHHmmss")}.xls"
- val filePath = "E:\\宸ヤ綔\\寮�鍙慭\璧拌埅鐩戞祴\\鏍煎紡鍖栨暟鎹甛\$fileName"
- val out = FileOutputStream(File(filePath))
- workbook.write(out)
- workbook.close()
- out.flush()
- out.close()
+// val deviceCode = "0a0000000001"
+// val deviceCode = "0a0000000002"
+ val deviceCode = "0d0000000001"
+ val timeSet = listOf(
+// Pair("2021-06-10 00:00:00", "2021-06-30 23:59:59"),
+ Pair("2021-07-01 00:00:00", "2021-07-31 23:59:59"),
+ Pair("2021-08-01 00:00:00", "2021-08-31 23:59:59"),
+ Pair("2021-09-01 00:00:00", "2021-09-30 23:59:59"),
+// 缃戞牸鍖栫洃娴嬫瘡鏃�
+// Pair("2021-06-02 00:00:00", "2021-06-03 23:59:59"),
+// Pair("2021-06-04 00:00:00", "2021-06-05 23:59:59"),
+// Pair("2021-06-06 00:00:00", "2021-06-07 23:59:59"),
+// Pair("2021-06-08 00:00:00", "2021-06-09 23:59:59"),
+// Pair("2021-06-10 00:00:00", "2021-06-11 23:59:59"),
+// Pair("2021-06-12 00:00:00", "2021-06-13 23:59:59"),
+// Pair("2021-06-14 00:00:00", "2021-06-15 23:59:59"),
+// Pair("2021-06-16 00:00:00", "2021-06-17 23:59:59"),
+// Pair("2021-06-18 00:00:00", "2021-06-19 23:59:59"),
+// Pair("2021-06-20 17:00:00", "2021-06-21 23:59:59"),
+// Pair("2021-06-22 00:00:00", "2021-06-23 23:59:59"),
+// Pair("2021-06-24 00:00:00", "2021-06-25 23:59:59"),
+// Pair("2021-06-26 00:00:00", "2021-06-27 23:59:59"),
+// Pair("2021-06-28 00:00:00", "2021-06-29 23:59:59"),
+// Pair("2021-06-30 00:00:00", "2021-06-30 23:59:59"),
+
+// Pair("2021-07-01 00:00:00", "2021-07-02 23:59:59"),
+// Pair("2021-07-03 00:00:00", "2021-07-04 23:59:59"),
+// Pair("2021-07-05 00:00:00", "2021-07-06 23:59:59"),
+// Pair("2021-07-07 00:00:00", "2021-07-08 23:59:59"),
+// Pair("2021-07-09 00:00:00", "2021-07-10 23:59:59"),
+// Pair("2021-07-11 00:00:00", "2021-07-12 23:59:59"),
+// Pair("2021-07-13 00:00:00", "2021-07-14 23:59:59"),
+// Pair("2021-07-15 00:00:00", "2021-07-16 23:59:59"),
+// Pair("2021-07-17 00:00:00", "2021-07-18 23:59:59"),
+// Pair("2021-07-19 17:00:00", "2021-07-20 23:59:59"),
+// Pair("2021-07-21 00:00:00", "2021-07-22 23:59:59"),
+// Pair("2021-07-23 00:00:00", "2021-07-24 23:59:59"),
+// Pair("2021-07-25 00:00:00", "2021-07-26 23:59:59"),
+// Pair("2021-07-27 00:00:00", "2021-07-28 23:59:59"),
+// Pair("2021-07-29 00:00:00", "2021-07-30 23:59:59"),
+// Pair("2021-07-31 00:00:00", "2021-07-31 23:59:59"),
+
+// Pair("2021-08-01 00:00:00", "2021-08-02 23:59:59"),
+// Pair("2021-08-03 00:00:00", "2021-08-04 23:59:59"),
+// Pair("2021-08-05 00:00:00", "2021-08-06 23:59:59"),
+// Pair("2021-08-07 00:00:00", "2021-08-08 23:59:59"),
+// Pair("2021-08-09 00:00:00", "2021-08-10 23:59:59"),
+// Pair("2021-08-11 00:00:00", "2021-08-12 23:59:59"),
+// Pair("2021-08-13 00:00:00", "2021-08-14 23:59:59"),
+// Pair("2021-08-15 00:00:00", "2021-08-16 23:59:59"),
+// Pair("2021-08-17 00:00:00", "2021-08-18 23:59:59"),
+// Pair("2021-08-19 17:00:00", "2021-08-20 23:59:59"),
+// Pair("2021-08-21 00:00:00", "2021-08-22 23:59:59"),
+// Pair("2021-08-23 00:00:00", "2021-08-24 23:59:59"),
+// Pair("2021-08-25 00:00:00", "2021-08-26 23:59:59"),
+// Pair("2021-08-27 00:00:00", "2021-08-28 23:59:59"),
+// Pair("2021-08-29 00:00:00", "2021-08-30 23:59:59"),
+// Pair("2021-08-31 00:00:00", "2021-08-31 23:59:59"),
+
+// Pair("2021-09-01 00:00:00", "2021-09-02 23:59:59"),
+// Pair("2021-09-03 00:00:00", "2021-09-04 23:59:59"),
+// Pair("2021-09-05 00:00:00", "2021-09-06 23:59:59"),
+// Pair("2021-09-07 00:00:00", "2021-09-08 23:59:59"),
+// Pair("2021-09-09 00:00:00", "2021-09-10 23:59:59"),
+// Pair("2021-09-11 00:00:00", "2021-09-12 23:59:59"),
+// Pair("2021-09-13 00:00:00", "2021-09-14 23:59:59"),
+// Pair("2021-09-15 00:00:00", "2021-09-16 23:59:59"),
+// Pair("2021-09-17 00:00:00", "2021-09-18 23:59:59"),
+// Pair("2021-09-19 17:00:00", "2021-09-20 23:59:59"),
+// Pair("2021-09-21 00:00:00", "2021-09-22 23:59:59"),
+// Pair("2021-09-23 00:00:00", "2021-09-24 23:59:59"),
+// Pair("2021-09-25 00:00:00", "2021-09-26 23:59:59"),
+// Pair("2021-09-27 00:00:00", "2021-09-28 23:59:59"),
+// Pair("2021-09-29 00:00:00", "2021-09-30 23:59:59"),
+ )
+ timeSet.forEach {(s, e) ->
+ val workbook = realTimeDataService.outToWorkbook(deviceCode, s, e)
+ val fileName = "缃戞牸鍖栫洃娴媉${deviceCode}_${s.substring(0, 10).replace("-", ".")}-${e.substring(8, 10)}.xls"
+ val filePath = "E:\\宸ヤ綔\\寮�鍙慭\璧拌埅鐩戞祴\\鏍煎紡鍖栨暟鎹甛\$fileName"
+ val out = FileOutputStream(File(filePath))
+ workbook.write(out)
+ workbook.close()
+ out.flush()
+ out.close()
+ }
}
}
}
\ No newline at end of file
diff --git a/src/test/kotlin/com/flightfeather/uav/model/epw/EPWDataPrepTest.kt b/src/test/kotlin/com/flightfeather/uav/model/epw/EPWDataPrepTest.kt
index c060c14..5b8301e 100644
--- a/src/test/kotlin/com/flightfeather/uav/model/epw/EPWDataPrepTest.kt
+++ b/src/test/kotlin/com/flightfeather/uav/model/epw/EPWDataPrepTest.kt
@@ -5,6 +5,8 @@
import com.flightfeather.uav.lightshare.bean.DataVo
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
+import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.junit.Assert.*
import org.junit.Test
import org.junit.runner.RunWith
@@ -12,7 +14,10 @@
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
import java.io.File
+import java.io.FileInputStream
import java.io.FileOutputStream
+import java.time.LocalDateTime
+import java.time.format.DateTimeFormatter
import java.util.*
@RunWith(SpringRunner::class)
@@ -81,7 +86,7 @@
}
if (dataList.isNotEmpty()) {
- val workbook = HSSFWorkbook()
+ val workbook = SXSSFWorkbook()
val heads = mutableListOf<Array<String>>()
heads.add(dataList[0].toRowTitle())
val contents = mutableListOf<Array<Any>>()
@@ -100,4 +105,72 @@
}
}
}
+
+ @Test
+ fun foo2() {
+ var fileName = "缃戞牸鍖栫洃娴嬮澶勭悊杩炵画鏁版嵁${Date().time}.xls"
+ var filePath = "C:\\work\\宸ヤ綔\\璧拌埅鐩戞祴\\棰勫鐞嗚繛缁暟鎹甛\$fileName"
+ var workBook = SXSSFWorkbook(1000)
+ var out: FileOutputStream
+ var input: FileInputStream
+ var row = 0
+ val maxRow = 100000
+ var hasHead = false
+
+ val ePWDataPrep = EPWDataPrep()
+
+ val endDateTime = LocalDateTime.now()
+ var sTime = LocalDateTime.of(2021, 6, 19, 0, 0, 0)
+ var eTime = LocalDateTime.of(2021, 6, 19, 23, 59, 59)
+
+ while (sTime.isBefore(endDateTime)) {
+ val deviceCode = "0d0000000001"
+ val dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
+ val s = sTime.format(dateFormatter)
+ val e = eTime.format(dateFormatter)
+ val data = realTimeDataService.getSecondData(deviceCode, s, e, 1, 30000).data
+
+ data?.let {
+ val dataList = ePWDataPrep.mDataPrep(it)
+
+ if (row + dataList.size > maxRow) {
+ fileName = "缃戞牸鍖栫洃娴嬮澶勭悊杩炵画鏁版嵁${Date().time}.xls"
+ filePath = "C:\\work\\宸ヤ綔\\璧拌埅鐩戞祴\\棰勫鐞嗚繛缁暟鎹甛\$fileName"
+ workBook = SXSSFWorkbook(1000)
+ row = 0
+ hasHead = false
+ }
+
+ if (!hasHead) {
+ out = FileOutputStream(filePath)
+ val heads = mutableListOf<Array<String>>()
+ heads.add(dataList[0].toRowTitle())
+ row = ExcelUtil.write(heads, emptyList(), workBook, row = row)
+ workBook.write(out)
+ workBook.close()
+ out.flush()
+ out.close()
+ hasHead = true
+ }
+
+ input = FileInputStream(filePath)
+ workBook = SXSSFWorkbook(XSSFWorkbook(input), 1000)
+ out = FileOutputStream(filePath)
+
+ val contents = mutableListOf<Array<Any>>()
+ dataList.forEach {d ->
+ contents.add(d.toRowContent())
+ }
+ row = ExcelUtil.write(emptyList(), contents, workBook, row = row)
+
+ workBook.write(out)
+ workBook.close()
+ input.close()
+ out.flush()
+ out.close()
+ }
+ sTime = sTime.plusDays(1)
+ eTime = eTime.plusDays(1)
+ }
+ }
}
\ No newline at end of file
diff --git a/src/test/kotlin/com/flightfeather/uav/model/epw/EPWModelTest.kt b/src/test/kotlin/com/flightfeather/uav/model/epw/EPWModelTest.kt
index 9dccf11..261b525 100644
--- a/src/test/kotlin/com/flightfeather/uav/model/epw/EPWModelTest.kt
+++ b/src/test/kotlin/com/flightfeather/uav/model/epw/EPWModelTest.kt
@@ -6,6 +6,7 @@
import com.flightfeather.uav.lightshare.bean.DataVo
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.BeanUtils
@@ -86,7 +87,7 @@
val deviceCode = "0d0000000001"
val epwModel = EPWModel()
- var workbook: HSSFWorkbook? = null
+ var workbook: SXSSFWorkbook? = null
var out: FileOutputStream? = null
for (i in timeSet.indices) {
--
Gitblit v1.9.3