underway-spring.git

			@@ -1,7 +1,17 @@
			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
			@@ -12,15 +22,47 @@
			*/
			object ExcelUtil {

			private const val isLog = false

			class MyCell(
			var text: String,
			var rowSpan: Int = 1
			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: HSSFWorkbook, sheetName:String) {
			fun write2(heads: List<String>, contents: List<Array<Any>>, workbook: SXSSFWorkbook, sheetName: String) {

			val sheet = workbook.createSheet(sheetName)

			@@ -47,39 +89,39 @@
			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) {
			println("合并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) {
			println("合并1-2：$rowIndex;${rowIndex + rowspan - 1};$i")
			sheet.addMergedRegion(CellRangeAddress(rowIndex, rowIndex + rowspan - 1, i, i))
			}
			cell
			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)
			println("write1-1: ${c.text};($rowIndex, ${i})")
			log("write1-1: ${c.text};($rowIndex, ${i})")
			}
			is String -> {
			rows.createCell(i).setCellValue(c)
			println("write1-2: ${c};($rowIndex, ${i})")
			log("write1-2: ${c};($rowIndex, ${i})")
			}
			is Double -> rows.createCell(i).setCellValue(c)
			is Boolean -> rows.createCell(i).setCellValue(c)
			@@ -102,7 +144,7 @@
			if (c is MyCell) {
			rowspan = c.rowSpan
			}
			val _c = array[i-1]
			val _c = array[i - 1]
			if (_c is MyCell) {
			lastRowSpan = _c.rowSpan
			}
			@@ -114,18 +156,18 @@
			rows = sheet.getRow(_rowIndex) ?: sheet.createRow(_rowIndex)
			}
			if (rowspan > 1) {
			println("合并2：${index};${index + rowspan - 1};$i")
			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)
			println("write2-1: ${c.text};($_rowIndex, ${map.key})")
			log("write2-1: ${c.text};($_rowIndex, ${map.key})")
			}
			is String -> {
			rows.createCell(map.key).setCellValue(c)
			println("write2-2: ${c};($_rowIndex, ${map.key})")
			log("write2-2: ${c};($_rowIndex, ${map.key})")
			}
			is Double -> rows.createCell(map.key).setCellValue(c)
			is Boolean -> rows.createCell(map.key).setCellValue(c)
			@@ -145,6 +187,174 @@
			// 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 {
			@@ -154,4 +364,10 @@
			}
			return maxRows
			}

			private fun log(log: String) {
			if (isLog) {
			println(log)
			}
			}
			}