From 01eae19a4870033e879a3faa6749eece92926cab Mon Sep 17 00:00:00 2001
From: feiyu02 <risaku@163.com>
Date: 星期五, 26 一月 2024 17:10:55 +0800
Subject: [PATCH] 1. 新增多项yml配置文件 2. 新增导入静安监测数据功能模块（未完成）
---
src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt | 133 +++++++++++++++++++++++++++++++-------------
1 files changed, 93 insertions(+), 40 deletions(-)
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..5d2a37b 100644
--- a/src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt
+++ b/src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt
@@ -1,7 +1,16 @@
package com.flightfeather.uav.common.utils
-import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import com.flightfeather.uav.common.exception.ResponseErrorException
+import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.util.CellRangeAddress
+import org.apache.poi.xssf.streaming.SXSSFWorkbook
+import org.apache.poi.xssf.usermodel.XSSFRow
+import org.apache.poi.xssf.usermodel.XSSFWorkbook
+import org.jetbrains.kotlin.incremental.isJavaFile
+import java.io.ByteArrayInputStream
+import java.io.File
+import java.io.FileInputStream
+import java.io.InputStream
import java.time.LocalDate
import java.util.*
import kotlin.math.max
@@ -15,16 +24,44 @@
private const val isLog = false
class MyCell(
- var text: String,
- var rowSpan: Int = 1,
- var colSpan: Int = 1,
- var fontColor: Short? = null
+ var text: String,
+ var rowSpan: Int = 1,
+ var colSpan: Int = 1,
+ var fontColor: Short? = null,
)
+
+ /**
+ * 璇诲彇鏂囦欢
+ * @param file 瑕佽鍙栫殑鏂囦欢
+ * @param headerCheck 鏂囦欢棣栬妫�鏌ュ洖璋�
+ * @param onRow 鍗曡鍐呭鍥炶皟锛屼粠绗簩琛屽紑濮�
+ */
+ fun read(file: File, headerCheck: (header: XSSFRow) -> Boolean, onRow: (row: Row) -> Unit): Boolean {
+ if (!file.exists() || !file.isFile) throw ResponseErrorException("it's not a normal file!")
+ if (!file.extension.equals("xls", ignoreCase = true) || !file.extension.equals("xlsx", ignoreCase = true)) {
+ throw ResponseErrorException("file's extension name should be xls or xlsx!")
+ }
+ return read(FileInputStream(file), headerCheck, onRow)
+ }
+
+ fun read(input: InputStream, headerCheck: (header: XSSFRow) -> Boolean, onRow: (row: Row) -> Unit): Boolean {
+ val workbook = XSSFWorkbook(input)
+ val sheet1 = workbook.getSheetAt(0)
+ val header = sheet1.getRow(sheet1.topRow.toInt())
+ return if (headerCheck(header)) {
+ // 鑾峰彇杩唬鍣ㄥ苟鍘婚櫎绗竴琛屾爣棰�
+ val iterator = sheet1.rowIterator().also { it.next() }
+ iterator.forEach { onRow(it) }
+ true
+ } else {
+ false
+ }
+ }
/**
* 鑷姩澶勭悊琛屽悎骞舵暟鎹�
*/
- 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)
@@ -51,31 +88,31 @@
var rowspan = maxRow//鍚堝苟鐨勮鐨勮法搴�
val c =
- if (cell is Array<*>) {
- //褰撴暟鎹负鏁扮粍鏃讹紝闇�瑕佹牴鎹渶澶ц鏁伴噸鏂拌绠楄鍗曞厓鏍肩殑琛岃法搴�
- arrayMap[i] = cell
- rowspan = maxRow / if (cell.size == 0) 1 else cell.size
- val c = cell[0]
- if (c is MyCell) {
- rowspan = c.rowSpan
- }
- if (rowspan > 1) {
- log("鍚堝苟1-1锛�$rowIndex;${rowIndex + rowspan - 1};$i")
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
- }
- if (cell.isEmpty()) {
- ""
- } else {
- cell[0]
- }
- } else {
- //褰撴暟鎹笉鏄暟缁勬椂锛岄渶瑕佹寜鏈�澶ц鏁板悎骞跺崟鍏冩牸
- if (rowspan > 1) {
- log("鍚堝苟1-2锛�$rowIndex;${rowIndex + rowspan - 1};$i")
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
- }
- cell
+ if (cell is Array<*>) {
+ //褰撴暟鎹负鏁扮粍鏃讹紝闇�瑕佹牴鎹渶澶ц鏁伴噸鏂拌绠楄鍗曞厓鏍肩殑琛岃法搴�
+ arrayMap[i] = cell
+ rowspan = maxRow / if (cell.size == 0) 1 else cell.size
+ val c = cell[0]
+ if (c is MyCell) {
+ rowspan = c.rowSpan
}
+ if (rowspan > 1) {
+ log("鍚堝苟1-1锛�$rowIndex;${rowIndex + rowspan - 1};$i")
+ sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
+ }
+ if (cell.isEmpty()) {
+ ""
+ } else {
+ cell[0]
+ }
+ } else {
+ //褰撴暟鎹笉鏄暟缁勬椂锛岄渶瑕佹寜鏈�澶ц鏁板悎骞跺崟鍏冩牸
+ if (rowspan > 1) {
+ log("鍚堝苟1-2锛�$rowIndex;${rowIndex + rowspan - 1};$i")
+ sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
+ }
+ cell
+ }
when (c) {
is MyCell -> {
rows.createCell(i).setCellValue(c.text)
@@ -106,7 +143,7 @@
if (c is MyCell) {
rowspan = c.rowSpan
}
- val _c = array[i-1]
+ val _c = array[i - 1]
if (_c is MyCell) {
lastRowSpan = _c.rowSpan
}
@@ -152,12 +189,18 @@
/**
* 鑷姩澶勭悊琛屽悎骞舵暟鎹�
*/
- 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)
@@ -194,7 +237,10 @@
}
if (rowspan > 1 || colSpan > 1) {
log("鍚堝苟1-1锛�$rowIndex;${rowIndex + rowspan - 1};$col")
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, col, col + colSpan - 1))
+ sheet.addMergedRegion(CellRangeAddress(rowIndex,
+ rowIndex + rowspan - 1,
+ col,
+ col + colSpan - 1))
}
if (cell.isEmpty()) {
""
@@ -202,21 +248,24 @@
cell[0]
}
} else {
- //褰撴暟鎹笉鏄暟缁勬椂锛岄渶瑕佹寜鏈�澶ц鏁板悎骞跺崟鍏冩牸
+ //褰撴暟鎹笉鏄暟缁勬椂锛屾牴鎹缃悎骞�
if (cell is MyCell) {
rowspan = cell.rowSpan
colSpan = cell.colSpan
}
if (rowspan > 1 || colSpan > 1) {
log("鍚堝苟1-2锛�$rowIndex;${rowIndex + rowspan - 1};$col")
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, col, col + colSpan - 1))
+ sheet.addMergedRegion(CellRangeAddress(rowIndex,
+ rowIndex + rowspan - 1,
+ col,
+ col + colSpan - 1))
}
cell
}
when (c) {
is MyCell -> {
rows.createCell(col).apply {
- c.fontColor?.let {fC ->
+ c.fontColor?.let { fC ->
val font = workbook.createFont()
val cellStyle = workbook.createCellStyle()
@@ -271,7 +320,10 @@
}
if (rowspan > 1 || colSpan > 1) {
log("鍚堝苟2锛�${index};${index + rowspan - 1};${map.key}")
- sheet.addMergedRegion(CellRangeAddress(index, index + rowspan - 1, map.key, map.key + colSpan - 1))
+ sheet.addMergedRegion(CellRangeAddress(index,
+ index + rowspan - 1,
+ map.key,
+ map.key + colSpan - 1))
}
when (c) {
@@ -299,6 +351,7 @@
// workbook.close()
// out.flush()
// out.close()
+ return rowIndex
}
private fun getMaxRows(rowArray: Array<Any>): Int {
--
Gitblit v1.9.3