公司有个项目是需要把数据导出Excel表格的,最初是使用的poi来导出的,把poi的jar包放进去后出现各种各样的问题,实在麻烦,然后在网上继续找有没有简单的,就找到了jxl,个人感觉操作确实和网上说的是比poi简单,当然,功能也简单,不过能满足我的业务需求就行,效果图如下:
首先添加dependencies:
implementation group: 'net.sourceforge.jexcelapi', name: 'jxl', version: '2.6.12'
生成excel表格的工具类:
package com.zxl.temp.utilsimport android.content.Contextimport android.os.Environmentimport android.text.TextUtilsimport android.util.Logimport com.zxl.temp.Rimport jxl.Workbookimport jxl.WorkbookSettingsimport jxl.format.Alignmentimport jxl.format.Borderimport jxl.format.BorderLineStyleimport jxl.format.Colourimport jxl.write.*import java.io.Fileimport java.io.FileInputStreamimport java.io.IOExceptionimport java.io.InputStreamimport kotlin.collections.ArrayListobject ExcelUtil {var arial14font: WritableFont? = nullvar arial14format: WritableCellFormat? = nullvar arial10font: WritableFont? = nullvar arial10format: WritableCellFormat? = nullvar arial12font: WritableFont? = nullvar arial12format: WritableCellFormat? = nullprivate const val UTF8_ENCODING = "UTF-8"/*** 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...*/private fun format() {try {arial14font = WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD)arial14font!!.colour = Colour.LIGHT_BLUEarial14format = WritableCellFormat(arial14font)arial14format!!.alignment = Alignment.CENTREarial14format!!.setBorder(Border.ALL, BorderLineStyle.THIN)arial14format!!.setBackground(Colour.VERY_LIGHT_YELLOW)arial10font = WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD)arial10format = WritableCellFormat(arial10font)arial10format!!.alignment = Alignment.CENTREarial10format!!.setBorder(Border.ALL, BorderLineStyle.THIN)arial10format!!.setBackground(Colour.GRAY_25)arial12font = WritableFont(WritableFont.ARIAL, 10)arial12format = WritableCellFormat(arial12font)arial10format!!.alignment = Alignment.CENTRE //对齐格式arial12format!!.setBorder(Border.ALL, BorderLineStyle.THIN) //设置边框} catch (e: WriteException) {e.printStackTrace()}}private fun initExcel(fileName: String, colName: Array<String>) {format()var workbook: WritableWorkbook? = nulltry {val file = File(fileName)if (!file.exists()) {file.createNewFile()}workbook = Workbook.createWorkbook(file)val sheet = workbook!!.createSheet("error", 0)//创建标题栏sheet.addCell(Label(0, 0, fileName, arial14format) as WritableCell?)for (col in colName.indices) {sheet.addCell(Label(col, 0, colName[col], arial10format))}sheet.setRowView(0, 340) //设置行高workbook!!.write()} catch (e: Exception) {e.printStackTrace()} finally {if (workbook != null) {try {workbook.close()} catch (e: Exception) {e.printStackTrace()}}}}private fun <T> writeObjListToExcel(objList: List<T>?, fileName: String?) {if (objList != null && objList.isNotEmpty()) {var writebook: WritableWorkbook? = nullvar `in`: InputStream? = nulltry {val setEncode = WorkbookSettings()setEncode.encoding = UTF8_ENCODING`in` = FileInputStream(File(fileName))val workbook: Workbook = Workbook.getWorkbook(`in`)writebook = Workbook.createWorkbook(File(fileName), workbook)val sheet = writebook.getSheet(0)for (j in objList.indices) {val list = objList[j] as ArrayList<String>for (i in 0 until list.size) {sheet.addCell(Label(i, j + 1, list[i], arial12format))if (list[i].length <= 5) {sheet.setColumnView(i, list[i].length + 8) //设置列宽} else {sheet.setColumnView(i, list[i].length + 5) //设置列宽}}sheet.setRowView(j + 1, 350) //设置行高}writebook.write()} catch (e: java.lang.Exception) {e.printStackTrace()} finally {if (writebook != null) {try {writebook.close()} catch (e: java.lang.Exception) {e.printStackTrace()}}if (`in` != null) {try {`in`.close()} catch (e: IOException) {e.printStackTrace()}}}}}private fun makeDir(dir: File?) {if (!dir!!.exists()) {dir.mkdirs()}}fun <T> exportExcel(context: Context?,title: Array<String>,objList: List<T>?,fileName: String) {var folderPath = CommonlyUsedUtil.getStoragePath(context, true)if (TextUtils.isEmpty(folderPath)) {folderPath = CommonlyUsedUtil.getStoragePath(context, false)if (TextUtils.isEmpty(folderPath)) {folderPath = Environment.getExternalStorageDirectory().path}}val file = File("$folderPath/ErrorInfo")makeDir(file)val filePath = "$folderPath/ErrorInfo/${fileName}"Log.d("ExcelUtil", "文件路径:${file}/$fileName")initExcel(file.toString() + File.separator + fileName, title)writeObjListToExcel(objList, filePath)ToastUtil.showToast(context!!.resources.getString(R.string.label_export_success, filePath))}}
调用工具类生成表格
private val title = arrayOf(APP.getContext()!!.resources.getString(R.string.label_number),APP.getContext()!!.resources.getString(R.string.label_error_code),APP.getContext()!!.resources.getString(R.string.label_error_title),APP.getContext()!!.resources.getString(R.string.label_fault_time))viewBinding!!.btnExport.setOnClickListener {if (!ClickUtil.isFastClick()) {return@setOnClickListener}if (APP.faultInfoList.size == 0) {ToastUtil.showToast(resources.getString(R.string.label_export_empty))return@setOnClickListener}val date = SimpleDateFormat("yyyyMMddHHmmss").format(System.currentTimeMillis())val fileName = "$date.xls"val dataList = mutableListOf<MutableList<String>>()//此处将数据集合转化为mutableListOf<MutableList<String>>APP.faultInfoList.forEach {val colList = mutableListOf<String>()colList.add("${it.num}")colList.add("${it.code}")colList.add("${it.msg}")colList.add("${it.time}")dataList.add(colList)}ExcelUtil.exportExcel(this, title, dataList, fileName)}
这样就能在指定的路径下生成xls文件了,失败的话请检查权限问题。
参考文档:
Android使用jxl快速生成Excel表