Android使用jxl快速导出Excel表格

公司有个项目是需要把数据导出Excel表格的，最初是使用的poi来导出的，把poi的jar包放进去后出现各种各样的问题，实在麻烦，然后在网上继续找有没有简单的，就找到了jxl，个人感觉操作确实和网上说的是比poi简单，当然，功能也简单，不过能满足我的业务需求就行，效果图如下：

首先添加dependencies：

implementation group: 'net.sourceforge.jexcelapi', name: 'jxl', version: '2.6.12'

生成excel表格的工具类：

package com.zxl.temp.utils

import android.content.Context
import android.os.Environment
import android.text.TextUtils
import android.util.Log
import com.zxl.temp.R
import jxl.Workbook
import jxl.WorkbookSettings
import jxl.format.Alignment
import jxl.format.Border
import jxl.format.BorderLineStyle
import jxl.format.Colour
import jxl.write.*
import java.io.File
import java.io.FileInputStream
import java.io.IOException
import java.io.InputStream
import kotlin.collections.ArrayList

object ExcelUtil {
    var arial14font: WritableFont? = null

    var arial14format: WritableCellFormat? = null
    var arial10font: WritableFont? = null
    var arial10format: WritableCellFormat? = null
    var arial12font: WritableFont? = null
    var arial12format: WritableCellFormat? = null

    private const val UTF8_ENCODING = "UTF-8"

    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private fun format() {
        try {
            arial14font = WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD)
            arial14font!!.colour = Colour.LIGHT_BLUE
            arial14format = WritableCellFormat(arial14font)
            arial14format!!.alignment = Alignment.CENTRE
            arial14format!!.setBorder(Border.ALL, BorderLineStyle.THIN)
            arial14format!!.setBackground(Colour.VERY_LIGHT_YELLOW)
            arial10font = WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD)
            arial10format = WritableCellFormat(arial10font)
            arial10format!!.alignment = Alignment.CENTRE
            arial10format!!.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? = null
        try {
            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? = null
            var `in`: InputStream? = null
            try {
                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表