package cn.flightfeather.supervision.common.utils
|
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook
|
import org.apache.poi.ss.util.CellRangeAddress
|
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
|
)
|
|
/**
|
* 自动处理行合并数据
|
*/
|
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()
|
}
|
|
/**
|
* 自动处理行合并数据
|
*/
|
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<Any>): Int {
|
var maxRows = 1
|
rowArray.forEach {
|
if (it is Array<*>) {
|
maxRows = max(it.size, maxRows)
|
}
|
}
|
return maxRows
|
}
|
}
|
