Android (Kotlin)将数据导出并自动生成Excel表
2021-04-14 本文已影响0人
想看烟花么
代码的世界虽逻辑繁华，却又大道至简。
背景
大家或许有过跟我类似的一些开发经历，例如涉及到相关报表业务，有时候我们需要将某段时间内的数据以Excel文件的格式导出或存储，从而以便更加直观的统计或分析数据所表现出的深沉涵义。
支持库
我们知道Apache官网开发组件下有个Java版本的POI库可以用来生成Excel文件，但是经过实践，在Android端的gradle配置文件中直接引用，开发阶段编译时不会有任何问题，但是程序Run后，App直接奔溃或出现一系列类似于javax.xml.xxxx.ClassNotFoundException的异常，导致App无法正常工作。
那么Android端如何或者说还有其他库能支持生成Excel么？很幸运是有的，github已有大神将Apache的POI裁剪了一个版本用以支持Android, link: android5xlsx
环境与配置
1.去android5xlsx将poi-3.12-android-a.jar 和 poi-ooxml-schemas-3.12-20150511-a.jar下载下来并放到libs文件夹下
image.png
2.在项目的app根目录gradle文件里配置
dependencies {
implementation files('libs/poi-3.12-android-a.jar')
implementation files('libs/poi-ooxml-schemas-3.12-20150511-a.jar')
}
动态生成单元格数据核心算法
private fun createCell(currentSheet: Sheet, cellIndex: Int, saveValue: String) {
//create fist row.
val lastRowNum = currentSheet.lastRowNum
if (lastRowNum == 0) {
val firstRow = currentSheet.createRow(1)
val firstCell = firstRow.createCell(cellIndex)
firstCell.setCellValue(saveValue)
return
}
//check is valid.
val titleRowNum = 1
val physicalRowNum = currentSheet.physicalNumberOfRows - titleRowNum
if (physicalRowNum < 1) {
return
}
//check and create new max row.
val maxRow = currentSheet.getRow(physicalRowNum)
val maxCell = maxRow.getCell(cellIndex)
if (maxCell != null) {
val newRow = currentSheet.createRow(physicalRowNum + 1)
val newCell = newRow.createCell(cellIndex)
newCell.setCellValue(saveValue)
return
}
//populate first 'null' and next 'null' value of cell between the first and last rows.
for (i in physicalRowNum downTo 1) {
val popRow = currentSheet.getRow(i)
val popCell = popRow.getCell(cellIndex)
if (popCell != null) {
val nextNullOfRow = currentSheet.getRow(i + 1)
val nextNullOfCell = nextNullOfRow.createCell(cellIndex)
nextNullOfCell.setCellValue(saveValue)
break
}
if (i == 1) {
val firstRow = currentSheet.getRow(1)
val firstCell = firstRow.createCell(cellIndex)
firstCell.setCellValue(saveValue)
break
}
}
}
完整可执行代码片段
package com.patrick.utils
import android.content.Context
import android.os.*
import android.util.Log
import kotlinx.coroutines.*
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.io.File
import java.io.FileOutputStream
import java.lang.NullPointerException
import java.lang.ref.WeakReference
import java.util.concurrent.LinkedBlockingQueue
import java.util.concurrent.atomic.AtomicInteger
/**
* Represents excel create tool.
* @dependency:
* https://github.com/andruhon/android5xlsx
*/
object ExcelUtil {
private const val TAG_LOG = "log_cell_data"
private const val mSplitTag = "|"
private const val mDelayShort = 500L
private var mInterruptDuration = 0L
private var mContextWeakReference: WeakReference<Context>? = null
private var mLinkedBlockingQueue = LinkedBlockingQueue<String>(16)
private var mSXSSFWorkbook: SXSSFWorkbook? = null
private var mExcelPathPrefix = ""
private var mWriteCounter = AtomicInteger(0)
//condition
private const val XNumber = 15
@Volatile
private var mStop: Boolean = false
private set
enum class CellTitleName(val cellIndex: Int) {
A(1),
B(2),
C(3),
}
private fun initExcelTool() {
mSXSSFWorkbook = SXSSFWorkbook(128)
}
fun putCellData(cellTile: CellTitleName, cellValue: String) {
if (mContextWeakReference == null || mContextWeakReference?.get() == null) {
throw NullPointerException("please call ExcelUtil.init(...) first")
}
mLinkedBlockingQueue.put("${cellTile.name}|$cellValue")
}
fun init(context: Context?) {
initExcelTool()
mContextWeakReference = WeakReference(context)
mExcelPathPrefix = "${
mContextWeakReference?.get()?.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS)
}${File.separator}measureResult"
startLoop()
}
private fun fileHandle() {
val file = File("$mExcelPathPrefix.xlsx")
if (file.exists()) {
file.copyTo(File("${mExcelPathPrefix}_last.xlsx"), true)
file.delete()
}
}
private fun startLoop() {
mStop = false
GlobalScope.launch {
fileHandle()
while (true) {
try {
if (mStop) {
break
}
//interrupt but not fatal error that means app is still alive, then write data which has created within 6 min.
if (!mStop && mInterruptDuration > 10000 * 60 * 5) {
writeIntoExcelFile(mSXSSFWorkbook)
break
}
consumer()
delay(mDelayShort)
yield()
} catch (e: CancellationException) {
break
}
}
}
}
private suspend fun consumer() {
mInterruptDuration = 0L
val durationUnit: String = mLinkedBlockingQueue.take()
val cellInfos = durationUnit.split(mSplitTag)
Log.d(TAG_LOG, "${cellInfos[0]}:${cellInfos[1]}")
prepareForXLSXWrite(cellInfos)
}
/**
* create cell data before execute write excel file.
* @param cellInfos | String[2]: format "cellTileA|cellValue"
*/
private fun prepareForXLSXWrite(cellInfos: List<String>, count: Int = 0) {
val sheetName = "Measurement"
/**
*
* do not coding as below ,otherwise it work only once.next time will Exception:streamed closed.
* <code>
* object?.use{
* ``````
* `````
* }
* </code>
*/
mSXSSFWorkbook?.let { workbook ->
var currentSheet: Sheet? = workbook.getSheet(sheetName)
if (currentSheet == null) {
currentSheet = workbook.createSheet(sheetName)
currentSheet.defaultRowHeight = (currentSheet.defaultRowHeight * 2).toShort()
currentSheet.defaultColumnWidth = currentSheet.defaultColumnWidth * 2
//create title row.
val titleRow = currentSheet.createRow(0)
//create title of 'A'.
val loginCell = titleRow.createCell(CellTitleName.A.cellIndex)
loginCell.setCellValue(CellTitleName.A.name)
//create title of 'B'.
val tabSwitchCell = titleRow.createCell(CellTitleName.B.cellIndex)
tabSwitchCell.setCellValue(CellTitleName.B.name)
//create title of 'C'.
val tabPopupCell = titleRow.createCell(CellTitleName.C.cellIndex)
tabPopupCell.setCellValue(CellTitleName.C.name)
}
currentSheet?.let {
when (cellInfos[0]) {
CellTitleName.A.name -> {
createCell(it, CellTitleName.A.cellIndex, cellInfos[1])
}
CellTitleName.B.name -> {
createCell(it, CellTitleName.B.cellIndex, cellInfos[1])
}
CellTitleName.C.name -> {
createCell(it, CellTitleName.C.cellIndex, cellInfos[1])
}
else -> {
}
}
mWriteCounter.incrementAndGet()
}
if (mWriteCounter.get() == XNumber) {
writeIntoExcelFile(workbook)
}
} ?: kotlin.run {
if (count < 1) {
mSXSSFWorkbook = SXSSFWorkbook()
prepareForXLSXWrite(cellInfos, 999)
}
}
}
/**
* 1.check
* 2.create a new row and a new cell if need
* 3.populate data into cell
* @param currentSheet | Sheet
* @param saveValue | String
*/
private fun createCell(currentSheet: Sheet, cellIndex: Int, saveValue: String) {
//create fist row.
val lastRowNum = currentSheet.lastRowNum
if (lastRowNum == 0) {
val firstRow = currentSheet.createRow(1)
val firstCell = firstRow.createCell(cellIndex)
firstCell.setCellValue(saveValue)
return
}
val titleRowNum = 1
val physicalRowNum = currentSheet.physicalNumberOfRows - titleRowNum
if (physicalRowNum < 1) {
return
}
//check and create new max row.
val maxRow = currentSheet.getRow(physicalRowNum)
val maxCell = maxRow.getCell(cellIndex)
if (maxCell != null) {
val newRow = currentSheet.createRow(physicalRowNum + 1)
val newCell = newRow.createCell(cellIndex)
newCell.setCellValue(saveValue)
return
}
//populate first 'null' and next 'null' value of cell between the first and last rows.
for (i in physicalRowNum downTo 1) {
val popRow = currentSheet.getRow(i)
val popCell = popRow.getCell(cellIndex)
if (popCell != null) {
val nextNullOfRow = currentSheet.getRow(i + 1)
val nextNullOfCell = nextNullOfRow.createCell(cellIndex)
nextNullOfCell.setCellValue(saveValue)
break
}
if (i == 1) {
val firstRow = currentSheet.getRow(1)
val firstCell = firstRow.createCell(cellIndex)
firstCell.setCellValue(saveValue)
break
}
}
}
/**
* execute write
* @param aSXSSFWorkbook | SXSSFWorkbook?
*/
private fun writeIntoExcelFile(aSXSSFWorkbook: SXSSFWorkbook?) {
aSXSSFWorkbook?.let { workbook ->
val filePath = "$mExcelPathPrefix.xlsx"
val outputStream = FileOutputStream(File(filePath))
try {
workbook.write(outputStream)
outputStream.flush()
} catch (e: Exception) {
Log.d(TAG_LOG, "${e.printStackTrace()}")
} finally {
outputStream.close()
workbook.close()
mStop = true
mWriteCounter.set(0)
mSXSSFWorkbook = null
}
}
}
}
