From 83455446544f89b0663a3f520744331ad8259289 Mon Sep 17 00:00:00 2001
From: feiyu02 <risaku@163.com>
Date: 星期五, 02 二月 2024 17:49:43 +0800
Subject: [PATCH] 1. 新增导入静安监测数据功能模块
---
src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt | 282 +++++++++++++++++++++++++++++++++++++++++++++++++------
1 files changed, 249 insertions(+), 33 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 0d41d40..6d598ef 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,17 @@
package com.flightfeather.uav.common.utils
+import com.flightfeather.uav.common.exception.ResponseErrorException
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+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
@@ -12,15 +22,47 @@
*/
object ExcelUtil {
+ private const val isLog = false
+
class MyCell(
- var text: String,
- var rowSpan: Int = 1
+ 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)
@@ -47,39 +89,39 @@
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) {
- println("鍚堝苟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) {
- println("鍚堝苟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)
- println("write1-1: ${c.text};($rowIndex, ${i})")
+ log("write1-1: ${c.text};($rowIndex, ${i})")
}
is String -> {
rows.createCell(i).setCellValue(c)
- println("write1-2: ${c};($rowIndex, ${i})")
+ log("write1-2: ${c};($rowIndex, ${i})")
}
is Double -> rows.createCell(i).setCellValue(c)
is Boolean -> rows.createCell(i).setCellValue(c)
@@ -102,7 +144,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
}
@@ -114,18 +156,18 @@
rows = sheet.getRow(_rowIndex) ?: sheet.createRow(_rowIndex)
}
if (rowspan > 1) {
- println("鍚堝苟2锛�${index};${index + rowspan - 1};$i")
+ log("鍚堝苟2锛�${index};${index + rowspan - 1};$i")
sheet.addMergedRegion(CellRangeAddress(index, index + rowspan - 1, i, i))
}
when (c) {
is MyCell -> {
rows.createCell(map.key).setCellValue(c.text)
- println("write2-1: ${c.text};($_rowIndex, ${map.key})")
+ log("write2-1: ${c.text};($_rowIndex, ${map.key})")
}
is String -> {
rows.createCell(map.key).setCellValue(c)
- println("write2-2: ${c};($_rowIndex, ${map.key})")
+ log("write2-2: ${c};($_rowIndex, ${map.key})")
}
is Double -> rows.createCell(map.key).setCellValue(c)
is Boolean -> rows.createCell(map.key).setCellValue(c)
@@ -145,6 +187,174 @@
// out.close()
}
+ /**
+ * 鑷姩澶勭悊琛屽悎骞舵暟鎹�
+ */
+ fun write(
+ heads: List<Array<String>>,
+ contents: List<Array<Any>>,
+ workbook: SXSSFWorkbook,
+ sheetName: String = "sheet1",
+ row: Int = 0,
+ ): Int {
+
+ val sheet = workbook.getSheet(sheetName) ?: workbook.createSheet(sheetName)
+// println("sheet: $sheetName")
+
+ var rowIndex = row
+
+ heads.forEach {
+ val rows = sheet.createRow(rowIndex)
+ for (i in it.indices) {
+ rows.createCell(i).setCellValue(it[i])
+ }
+ rowIndex++
+ }
+
+ contents.forEach {
+ val maxRow = getMaxRows(it)
+
+ var rows = sheet.getRow(rowIndex) ?: sheet.createRow(rowIndex)
+
+ val arrayMap = mutableMapOf<Int, Array<*>>()
+
+ //鍒楀簭鍙�
+ var col = 0
+ for (i in it.indices) {
+ val cell = it[i]
+
+ var rowspan = maxRow//鍚堝苟鐨勮鐨勮法搴�
+ var colSpan = 1
+
+ val c =
+ if (cell is Array<*>) {
+ //褰撴暟鎹负鏁扮粍鏃讹紝闇�瑕佹牴鎹渶澶ц鏁伴噸鏂拌绠楄鍗曞厓鏍肩殑琛岃法搴�
+ arrayMap[col] = cell
+ rowspan = maxRow / if (cell.size == 0) 1 else cell.size
+ val c = cell[0]
+ if (c is MyCell) {
+ rowspan = c.rowSpan
+ colSpan = c.colSpan
+ }
+ if (rowspan > 1 || colSpan > 1) {
+ log("鍚堝苟1-1锛�$rowIndex;${rowIndex + rowspan - 1};$col")
+ sheet.addMergedRegion(CellRangeAddress(rowIndex,
+ rowIndex + rowspan - 1,
+ col,
+ col + colSpan - 1))
+ }
+ if (cell.isEmpty()) {
+ ""
+ } else {
+ 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))
+ }
+ cell
+ }
+ when (c) {
+ is MyCell -> {
+ rows.createCell(col).apply {
+ c.fontColor?.let { fC ->
+ val font = workbook.createFont()
+ val cellStyle = workbook.createCellStyle()
+
+ font.color = fC
+ cellStyle.setFont(font)
+ setCellStyle(cellStyle)
+ }
+ setCellValue(c.text)
+ }
+ log("write1-1: ${c.text};($rowIndex, ${col})")
+ }
+ is String -> {
+ rows.createCell(col).setCellValue(c)
+ log("write1-2: ${c};($rowIndex, ${col})")
+ }
+ is Double -> rows.createCell(col).setCellValue(c)
+ is Int -> rows.createCell(col).setCellValue(c.toDouble())
+ is Boolean -> rows.createCell(col).setCellValue(c)
+ is Date -> rows.createCell(col).setCellValue(c)
+ is Calendar -> rows.createCell(col).setCellValue(c)
+ is LocalDate -> rows.createCell(col).setCellValue(c)
+ }
+
+ col += colSpan
+ }
+
+ for (i in 1 until maxRow) {
+ rowIndex++
+ arrayMap.forEach { map ->
+ rows = sheet.getRow(rowIndex) ?: sheet.createRow(rowIndex)
+ val array = map.value
+ if (i < array.size) {
+// var rowspan = maxRow / array.size
+ var lastRowSpan = 1
+ var rowspan = 1
+ var colSpan = 1
+ val c = array[i]
+ if (c is MyCell) {
+ rowspan = c.rowSpan
+ colSpan = c.colSpan
+ }
+ val _c = array[i - 1]
+ if (_c is MyCell) {
+ lastRowSpan = _c.rowSpan
+ }
+ var _rowIndex = rowIndex
+ var index = rowIndex
+ if (lastRowSpan > 1) {
+ index = rowIndex + (i * lastRowSpan) - 1
+ _rowIndex = index
+ rows = sheet.getRow(_rowIndex) ?: sheet.createRow(_rowIndex)
+ }
+ 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))
+ }
+
+ when (c) {
+ is MyCell -> {
+ rows.createCell(map.key).setCellValue(c.text)
+ log("write2-1: ${c.text};($_rowIndex, ${map.key})")
+ }
+ is String -> {
+ rows.createCell(map.key).setCellValue(c)
+ log("write2-2: ${c};($_rowIndex, ${map.key})")
+ }
+ is Double -> rows.createCell(map.key).setCellValue(c)
+ is Boolean -> rows.createCell(map.key).setCellValue(c)
+ is Date -> rows.createCell(map.key).setCellValue(c)
+ is Calendar -> rows.createCell(map.key).setCellValue(c)
+ is LocalDate -> rows.createCell(map.key).setCellValue(c)
+ }
+ }
+ }
+ }
+
+ rowIndex++
+ }
+// workbook.write(out)
+// workbook.close()
+// out.flush()
+// out.close()
+ return rowIndex
+ }
+
private fun getMaxRows(rowArray: Array<Any>): Int {
var maxRows = 1
rowArray.forEach {
@@ -154,4 +364,10 @@
}
return maxRows
}
+
+ private fun log(log: String) {
+ if (isLog) {
+ println(log)
+ }
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3