From 0c59552dc14c9023d4c0a9d57509cce1d5a6d6da Mon Sep 17 00:00:00 2001
From: feiyu02 <risaku@163.com>
Date: 星期二, 31 十二月 2024 10:07:45 +0800
Subject: [PATCH] Merge remote-tracking branch 'supervision/hc-socket-1129'
---
src/main/kotlin/cn/flightfeather/supervision/common/utils/ExcelUtil.kt | 125 ++++++++++++++++++++++++++++++++++++-----
1 files changed, 108 insertions(+), 17 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 3bd21a9..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,17 +19,75 @@
*/
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<Any>>, contents: MutableList<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)
@@ -88,15 +151,26 @@
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})")
}
@@ -150,7 +224,28 @@
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 -> {
@@ -179,7 +274,7 @@
}
/**
- * 鑷姩澶勭悊琛屽悎骞舵暟鎹�
+ * 鍐欏叆excel
*/
fun write2(out: OutputStream, heads: List<Array<Any>>, contents: MutableList<Array<Any>>, sheetName: String = "sheet1") {
@@ -193,11 +288,7 @@
out.close()
}
- fun write3() {
-
- }
-
- 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