package com.flightfeather.uav.common.utils
|
|
import com.flightfeather.uav.common.exception.ResponseErrorException
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook
|
import org.apache.poi.ss.usermodel.Row
|
import org.apache.poi.ss.util.CellRangeAddress
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook
|
import org.apache.poi.xssf.usermodel.XSSFRow
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook
|
import org.jetbrains.kotlin.incremental.isJavaFile
|
import java.io.ByteArrayInputStream
|
import java.io.File
|
import java.io.FileInputStream
|
import java.io.InputStream
|
import java.time.LocalDate
|
import java.util.*
|
import kotlin.math.max
|
|
/**
|
* @author riku
|
* Date: 2020/6/12
|
*/
|
object ExcelUtil {
|
|
private const val isLog = false
|
|
class MyCell(
|
var text: String,
|
var rowSpan: Int = 1,
|
var colSpan: Int = 1,
|
var fontColor: Short? = null,
|
)
|
|
/**
|
* 读取文件
|
* @param file 要读取的文件
|
* @param headerCheck 文件首行检查回调
|
* @param onRow 单行内容回调，从第二行开始
|
*/
|
fun read(file: File, headerCheck: (header: XSSFRow) -> Boolean, onRow: (row: Row) -> Unit): Boolean {
|
if (!file.exists() || !file.isFile) throw ResponseErrorException("it's not a normal file!")
|
if (!file.extension.equals("xls", ignoreCase = true) || !file.extension.equals("xlsx", ignoreCase = true)) {
|
throw ResponseErrorException("file's extension name should be xls or xlsx!")
|
}
|
return read(FileInputStream(file), headerCheck, onRow)
|
}
|
|
fun read(input: InputStream, headerCheck: (header: XSSFRow) -> Boolean, onRow: (row: Row) -> Unit): Boolean {
|
val workbook = XSSFWorkbook(input)
|
val sheet1 = workbook.getSheetAt(0)
|
val header = sheet1.getRow(sheet1.topRow.toInt())
|
return if (headerCheck(header)) {
|
// 获取迭代器并去除第一行标题
|
val iterator = sheet1.rowIterator().also { it.next() }
|
iterator.forEach { onRow(it) }
|
true
|
} else {
|
false
|
}
|
}
|
|
/**
|
* 自动处理行合并数据
|
*/
|
fun write2(heads: List<String>, contents: List<Array<Any>>, workbook: SXSSFWorkbook, sheetName: String) {
|
|
val sheet = workbook.createSheet(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.getRow(rowIndex) ?: 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
|
val c = cell[0]
|
if (c is MyCell) {
|
rowspan = c.rowSpan
|
}
|
if (rowspan > 1) {
|
log("合并1-1：$rowIndex;${rowIndex + rowspan - 1};$i")
|
sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
|
}
|
if (cell.isEmpty()) {
|
""
|
} else {
|
cell[0]
|
}
|
} else {
|
//当数据不是数组时，需要按最大行数合并单元格
|
if (rowspan > 1) {
|
log("合并1-2：$rowIndex;${rowIndex + rowspan - 1};$i")
|
sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
|
}
|
cell
|
}
|
when (c) {
|
is MyCell -> {
|
rows.createCell(i).setCellValue(c.text)
|
log("write1-1: ${c.text};($rowIndex, ${i})")
|
}
|
is String -> {
|
rows.createCell(i).setCellValue(c)
|
log("write1-2: ${c};($rowIndex, ${i})")
|
}
|
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++
|
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
|
val c = array[i]
|
if (c is MyCell) {
|
rowspan = c.rowSpan
|
}
|
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) {
|
log("合并2：${index};${index + rowspan - 1};$i")
|
sheet.addMergedRegion(CellRangeAddress(index, index + rowspan - 1, i, i))
|
}
|
|
when (c) {
|
is MyCell -> {
|
rows.createCell(map.key).setCellValue(c.text)
|
log("write2-1: ${c.text};($_rowIndex, ${map.key})")
|
}
|
is String -> {
|
rows.createCell(map.key).setCellValue(c)
|
log("write2-2: ${c};($_rowIndex, ${map.key})")
|
}
|
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 write(
|
heads: List<Array<String>>,
|
contents: List<Array<Any>>,
|
workbook: SXSSFWorkbook,
|
sheetName: String = "sheet1",
|
row: Int = 0,
|
): Int {
|
|
val sheet = workbook.getSheet(sheetName) ?: workbook.createSheet(sheetName)
|
// println("sheet: $sheetName")
|
|
var rowIndex = row
|
|
heads.forEach {
|
val rows = sheet.createRow(rowIndex)
|
for (i in it.indices) {
|
rows.createCell(i).setCellValue(it[i])
|
}
|
rowIndex++
|
}
|
|
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) {
|
log("合并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) {
|
log("合并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 {
|
c.fontColor?.let { fC ->
|
val font = workbook.createFont()
|
val cellStyle = workbook.createCellStyle()
|
|
font.color = fC
|
cellStyle.setFont(font)
|
setCellStyle(cellStyle)
|
}
|
setCellValue(c.text)
|
}
|
log("write1-1: ${c.text};($rowIndex, ${col})")
|
}
|
is String -> {
|
rows.createCell(col).setCellValue(c)
|
log("write1-2: ${c};($rowIndex, ${col})")
|
}
|
is Double -> rows.createCell(col).setCellValue(c)
|
is Int -> rows.createCell(col).setCellValue(c.toDouble())
|
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) {
|
log("合并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(map.key).setCellValue(c.text)
|
log("write2-1: ${c.text};($_rowIndex, ${map.key})")
|
}
|
is String -> {
|
rows.createCell(map.key).setCellValue(c)
|
log("write2-2: ${c};($_rowIndex, ${map.key})")
|
}
|
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()
|
return rowIndex
|
}
|
|
private fun getMaxRows(rowArray: Array<Any>): Int {
|
var maxRows = 1
|
rowArray.forEach {
|
if (it is Array<*>) {
|
maxRows = max(it.size, maxRows)
|
}
|
}
|
return maxRows
|
}
|
|
private fun log(log: String) {
|
if (isLog) {
|
println(log)
|
}
|
}
|
}
|
