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 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
|
* Date: 2020/6/12
|
*/
|
object ExcelUtil {
|
|
/**
|
* 单元格
|
*/
|
class MyCell(
|
var text: String,
|
var rowSpan: Int = 1,
|
var colSpan: Int = 1,
|
var fontColor: Short? = null,
|
var isPercent:Boolean = false
|
)
|
|
/**
|
* 表头定义
|
*/
|
class MyHeader(
|
var name: String,
|
val type: CellType,
|
val ignore: Boolean = false
|
)
|
|
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)?.cellType ?: CellType.BLANK
|
if (t != CellType.BLANK && !h.ignore && 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++
|
// }
|
contents.addAll(0, heads)
|
|
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) {
|
println("合并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) {
|
println("合并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 {
|
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("write1-1: ${c.text};($rowIndex, ${col})")
|
}
|
is String -> {
|
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)
|
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) {
|
println("合并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(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 -> {
|
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)
|
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()
|
}
|
|
/**
|
* 写入excel
|
*/
|
fun write2(out: OutputStream, heads: List<Array<Any>>, contents: MutableList<Array<Any>>, sheetName: String = "sheet1") {
|
|
val workbook = HSSFWorkbook()
|
|
write(heads, contents, workbook, sheetName)
|
|
workbook.write(out)
|
workbook.close()
|
out.flush()
|
out.close()
|
}
|
|
private fun getMaxRows(rowArray: Array<out Any>): Int {
|
var maxRows = 1
|
rowArray.forEach {
|
if (it is Array<*>) {
|
maxRows = max(it.size, maxRows)
|
}
|
}
|
return maxRows
|
}
|
}
|
