From a700aeb0a07d11da1e6b2ae999983ba17a415c70 Mon Sep 17 00:00:00 2001
From: feiyu02 <risaku@163.com>
Date: 星期三, 30 七月 2025 16:02:04 +0800
Subject: [PATCH] 2025.7.30 1. 新增基础数据产品相关接口（待完成）
---
src/main/kotlin/cn/flightfeather/supervision/common/utils/ExcelUtil.kt | 240 ++++++++++++++++++++++++++++++++----------------------------
1 files changed, 128 insertions(+), 112 deletions(-)
diff --git a/src/main/kotlin/cn/flightfeather/supervision/common/utils/ExcelUtil.kt b/src/main/kotlin/cn/flightfeather/supervision/common/utils/ExcelUtil.kt
index e0cb1f2..fc653ac 100644
--- a/src/main/kotlin/cn/flightfeather/supervision/common/utils/ExcelUtil.kt
+++ b/src/main/kotlin/cn/flightfeather/supervision/common/utils/ExcelUtil.kt
@@ -1,12 +1,17 @@
package cn.flightfeather.supervision.common.utils
+import cn.flightfeather.supervision.common.exception.BizException
import org.apache.poi.hssf.usermodel.HSSFWorkbook
+import org.apache.poi.ss.usermodel.CellType
+import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.util.CellRangeAddress
-import java.io.File
+import org.apache.poi.xssf.usermodel.XSSFWorkbook
+import java.io.InputStream
import java.io.OutputStream
import java.time.LocalDate
import java.util.*
import kotlin.math.max
+
/**
* @author riku
@@ -14,29 +19,88 @@
*/
object ExcelUtil {
+ /**
+ * 鍗曞厓鏍�
+ */
class MyCell(
var text: String,
var rowSpan: Int = 1,
var colSpan: Int = 1,
- var fontColor: Short? = null
+ var fontColor: Short? = null,
+ var isPercent:Boolean = false
)
/**
- * 鑷姩澶勭悊琛屽悎骞舵暟鎹�
+ * 琛ㄥご瀹氫箟
*/
- fun write(heads: List<Array<String>>, contents: List<Array<Any>>, workbook: HSSFWorkbook, sheetName: String = "sheet1") {
+ class MyHeader(
+ var name: String,
+ val type: CellType,
+ )
+
+ private fun cellTypeName(type: CellType): String = when (type) {
+ CellType.NUMERIC -> "鏁板瓧"
+ CellType.STRING -> "鏂囨湰"
+ CellType.FORMULA -> "FORMULA"
+ CellType.BLANK -> "绌虹櫧"
+ CellType.BOOLEAN -> "甯冨皵鍊�"
+ else -> "鏈畾涔�"
+ }
+
+
+ /**
+ * 鍖呮嫭 .xlsx 鏂囦欢
+ */
+ fun readXLXS(input: InputStream, headers: List<MyHeader>, onRow: (row: Row) -> Unit) {
+ val workbook = XSSFWorkbook(input)
+ val sheet1 = workbook.getSheetAt(0)
+ val header = sheet1.getRow(sheet1.topRow.toInt())
+ // 妫�鏌ヤ紶鍏ョ殑鏂囦欢琛ㄥご鏄惁绗﹀悎瑕佹眰
+ val cellIterator = header.cellIterator()
+ val headIterator = headers.iterator()
+ while (headIterator.hasNext()) {
+ val head = headIterator.next()
+ if (cellIterator.hasNext()) {
+ val cellText = cellIterator.next().stringCellValue
+ if (!cellText.equals(head.name)) {
+ throw BizException("鏂囦欢鏍煎紡閿欒, 琛ㄥご[${head}]搴旇涓篬${cellText}]")
+ }
+ } else {
+ throw BizException("鏂囦欢鏍煎紡閿欒, 琛ㄥご[${head}]缂哄け")
+ }
+ }
+ // 鑾峰彇杩唬鍣ㄥ苟鍘婚櫎绗竴琛屾爣棰�
+ val iterator = sheet1.rowIterator().also { it.next() }
+ iterator.forEach {
+ // 妫�鏌ュ綋鍓嶈姣忎釜鍗曞厓鏍肩被鍨嬫槸鍚︿笌琛ㄥご瀹氫箟鍖归厤
+ headers.forEachIndexed { i, h ->
+ val t = it.getCell(i + 1).cellType
+ if (t != CellType.BLANK || t != h.type) {
+ throw BizException("绗�${it.rowNum + 1}琛岋紝绗�${i + 1}鍒楀崟鍏冩牸鏍煎紡閿欒锛屽簲璇ヤ负${cellTypeName(h.type)}")
+ }
+ }
+ onRow(it)
+ }
+ }
+
+ /**
+ * 鍐欏叆excel
+ */
+ fun write(heads: List<Array<Any>>, contents: MutableList<Array<Any>>, workbook: HSSFWorkbook, sheetName: String
+ = "sheet1") {
val sheet = workbook.createSheet(sheetName)
var rowIndex = 0
- heads.forEach {
- val rows = sheet.createRow(rowIndex)
- for (i in it.indices) {
- rows.createCell(i).setCellValue(it[i])
- }
- rowIndex++
- }
+// heads.forEach {
+// val rows = sheet.createRow(rowIndex)
+// for (i in it.indices) {
+// rows.createCell(i).setCellValue(it[i])
+// }
+// rowIndex++
+// }
+ contents.addAll(0, heads)
contents.forEach {
val maxRow = getMaxRows(it)
@@ -87,22 +151,36 @@
when (c) {
is MyCell -> {
rows.createCell(col).apply {
- c.fontColor?.let {fC ->
- val font = workbook.createFont()
+ if (c.isPercent) {
val cellStyle = workbook.createCellStyle()
-
- font.color = fC
- cellStyle.setFont(font)
+ val format = workbook.createDataFormat()
+ cellStyle.dataFormat = format.getFormat("0.0%")
setCellStyle(cellStyle)
+ val v = c.text.toDoubleOrNull()
+ if (v != null) {
+ setCellValue(v)
+ }
+ } else {
+ c.fontColor?.let {fC ->
+ val font = workbook.createFont()
+ val cellStyle = workbook.createCellStyle()
+
+ font.color = fC
+ cellStyle.setFont(font)
+ setCellStyle(cellStyle)
+ }
+ setCellValue(c.text)
}
- setCellValue(c.text)
}
println("write1-1: ${c.text};($rowIndex, ${col})")
}
is String -> {
- rows.createCell(col).setCellValue(c)
- println("write1-2: ${c};($rowIndex, ${col})")
+ if (c.isNotBlank()) {
+ rows.createCell(col).setCellValue(c)
+ println("write1-2: ${c};($rowIndex, ${col})")
+ }
}
+ is Int -> rows.createCell(col).setCellValue(c.toDouble())
is Double -> rows.createCell(col).setCellValue(c)
is Boolean -> rows.createCell(col).setCellValue(c)
is Date -> rows.createCell(col).setCellValue(c)
@@ -146,13 +224,37 @@
when (c) {
is MyCell -> {
- rows.createCell(map.key).setCellValue(c.text)
+ rows.createCell(col).apply {
+ if (c.isPercent) {
+ val cellStyle = workbook.createCellStyle()
+ val format = workbook.createDataFormat()
+ cellStyle.dataFormat = format.getFormat("0.0%")
+ setCellStyle(cellStyle)
+ val v = c.text.toDoubleOrNull()
+ if (v != null) {
+ setCellValue(v)
+ }
+ } else {
+ c.fontColor?.let {fC ->
+ val font = workbook.createFont()
+ val cellStyle = workbook.createCellStyle()
+
+ font.color = fC
+ cellStyle.setFont(font)
+ setCellStyle(cellStyle)
+ }
+ setCellValue(c.text)
+ }
+ }
println("write2-1: ${c.text};($_rowIndex, ${map.key})")
}
is String -> {
- rows.createCell(map.key).setCellValue(c)
- println("write2-2: ${c};($_rowIndex, ${map.key})")
+ if (c.isNotBlank()) {
+ rows.createCell(map.key).setCellValue(c)
+ println("write2-2: ${c};($_rowIndex, ${map.key})")
+ }
}
+ is Int -> rows.createCell(map.key).setCellValue(c.toDouble())
is Double -> rows.createCell(map.key).setCellValue(c)
is Boolean -> rows.createCell(map.key).setCellValue(c)
is Date -> rows.createCell(map.key).setCellValue(c)
@@ -172,107 +274,21 @@
}
/**
- * 鑷姩澶勭悊琛屽悎骞舵暟鎹�
+ * 鍐欏叆excel
*/
- fun write2(out: OutputStream, heads: List<String>, contents: List<Array<Any>>): String {
+ fun write2(out: OutputStream, heads: List<Array<Any>>, contents: MutableList<Array<Any>>, sheetName: String = "sheet1") {
val workbook = HSSFWorkbook()
- val sheet = workbook.createSheet("sheet1")
+ write(heads, contents, workbook, sheetName)
- var rowIndex = 0
-
- if (heads.isNotEmpty()) {
- val rows = sheet.createRow(rowIndex)
- for (i in 0 until heads.size) {
- rows.createCell(i).setCellValue(heads[i])
- }
- rowIndex++
- }
-
- contents.forEach {
- val maxRow = getMaxRows(it)
-
- var rows = sheet.createRow(rowIndex)
-
- val arrayMap = mutableMapOf<Int, Array<*>>()
-
- for (i in it.indices) {
- val cell = it[i]
-
- var rowspan = maxRow//鍚堝苟鐨勮鐨勮法搴�
-
- val c =
- if (cell is Array<*>) {
- //褰撴暟鎹负鏁扮粍鏃讹紝闇�瑕佹牴鎹渶澶ц鏁伴噸鏂拌绠楄鍗曞厓鏍肩殑琛岃法搴�
- arrayMap[i] = cell
- rowspan = maxRow / if(cell.size==0) 1 else cell.size
- if (rowspan > 1) {
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
- }
- if (cell.isEmpty()) {
- ""
- } else {
- cell[0]
- }
- } else {
- //褰撴暟鎹笉鏄暟缁勬椂锛岄渶瑕佹寜鏈�澶ц鏁板悎骞跺崟鍏冩牸
- if (rowspan > 1) {
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
- }
- cell
- }
- when (c) {
- is String -> rows.createCell(i).setCellValue(c)
- is Double -> rows.createCell(i).setCellValue(c)
- is Boolean -> rows.createCell(i).setCellValue(c)
- is Date -> rows.createCell(i).setCellValue(c)
- is Calendar -> rows.createCell(i).setCellValue(c)
- is LocalDate -> rows.createCell(i).setCellValue(c)
- }
- }
-
- for (i in 1 until maxRow) {
- rowIndex++
- rows = sheet.createRow(rowIndex)
- arrayMap.forEach {map ->
- val array = map.value
- if (i < array.size) {
- val rowspan = maxRow / array.size
- if (rowspan > 1) {
- sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
- }
- val c = array[i]
- when (c) {
- is String -> rows.createCell(map.key).setCellValue(c)
- 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++
- }
-
- val fileName = "${DateUtil().DateToString(Date(), "yyyy-MM-dd hh:mm:ss")}.xls"
- val filePath = "${Constant.DEFAULT_FILE_PATH}/files/$fileName"
- val xlsFile = File(filePath)
- if (!xlsFile.parentFile.exists()) {
- xlsFile.parentFile.mkdirs()
- }
-// val xlsStream = FileOutputStream(xlsFile)
workbook.write(out)
workbook.close()
out.flush()
out.close()
- return fileName
}
- private fun getMaxRows(rowArray: Array<Any>): Int {
+ private fun getMaxRows(rowArray: Array<out Any>): Int {
var maxRows = 1
rowArray.forEach {
if (it is Array<*>) {
--
Gitblit v1.9.3