Merge remote-tracking branch 'supervision/hc-socket-1129'

feiyu02

2024-12-31 0c59552dc14c9023d4c0a9d57509cce1d5a6d6da

 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<*>) {