Android 生成Excel并导出全流程

前言

最近接到需求，要在安卓上离线完成根据数据生成Excel文件，但搜到了都不是能立马使用

例如 

//    implementation 'org.apache.poi:poi:3.17'
//    implementation 'com.alibaba:easyexcel:4.0.1'

这两最大的问题是专用于java的，如果Android直接集成的话，会有一些“java.awt.Color”等类缺失的问题，应该是属于javase的一些内容。虽然看例子能直接使用 javabean

那我们只能退而求其次选择Android兼容性更高的

 implementation 'net.sourceforge.jexcelapi:jxl:2.6.12'

1，权限申请

    public static boolean verifyAllPermissions(Activity activity) {
        boolean write = verifyWrite(activity);
       
        if (write) {
            Log.i(TAG, "权限完整可以正常运行");
            return true;
        }
        Log.i(TAG, "开始请求权限");
        ActivityCompat.requestPermissions(activity,
                new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE},
                REQUEST_EXTERNAL_STORAGE);
        return false;

    }

 if (verifyAllPermissions) {
                testExcel()
 }

2，生成文件

  private fun testExcel() {


        val path = Environment.getExternalStorageDirectory().absolutePath
        Log.d("ExcelActivity", "path:$path")
        val directory =
            "$path/excel"
        val fileName = "/0726配餐simple.xlsx"
        val abFilePath = "$directory$fileName"
        Log.d("ExcelActivity", "directory:$directory")
        Log.d("ExcelActivity", "abFilePath:$abFilePath")


        val fileDirectory = File(directory)
        val file = File(abFilePath)


        try {
            if (!fileDirectory.exists()) {
                fileDirectory.mkdirs()
                Log.e("ExcelActivity", "路径不存在创建路径")
            }
            file.createNewFile();
        } catch (e: IOException) {
            Log.e("ExcelActivity", "createNewFile e:$e")
            e.printStackTrace()
            "创建文件失败$e".toast()
            return
        }
        Log.d("ExcelActivity", "文件创建成功:${file.absolutePath} ${file.exists()}")
        if (!file.exists()) {
            "文件不存在".toast()
            return
        }

        exportExcel(abFilePath)
    }

3，准备数据

class ExcelBean(var id: String, var name: String, var time: String, var date: String)

......


val dataList = arrayListOf<ExcelBean>()
            val titleList =
                arrayListOf("ID", "名称", "日期", "时间戳", "标题5", "标题6", "标题7", "标题8")
for (i in 0..60) {
                dataList.add(
                    ExcelBean(
                        "$i",
                        "名字$i",
                        System.currentTimeMillis().toString(),
                        "${Date()}"
                    )
                )
}

4，写入文件

API支持的并不多，下面列举的常见的效果，其他API 网上有很多例子，本文旨在整个调通

fun exportExcel(filePath: String) {
        try {

            val dataList = arrayListOf<ExcelBean>()
            val titleList =
                arrayListOf("ID", "名称", "日期", "时间戳", "标题5", "标题6", "标题7", "标题8")
            for (i in 0..60) {
                dataList.add(
                    ExcelBean(
                        "$i",
                        "名字$i",
                        System.currentTimeMillis().toString(),
                        "${Date()}"
                    )
                )
            }
            val workbook = Workbook.createWorkbook(File(filePath))
            // 设置sheet名称
            val sheet = workbook.createSheet("0726配餐", 0)


            // 合并单元格
            sheet.mergeCells(0, 0, titleList.size - 1, 0)
            sheet.mergeCells(0, 1, titleList.size - 1, 1)

            // 调整列宽
            sheet.setRowView(0, 800)
            sheet.setRowView(1, 600)
            sheet.setRowView(2, 500)

            //创建字体，参数1：字体样式，参数2：字号，参数3：粗体
            val font = WritableFont(WritableFont.createFont("宋体"), 18, WritableFont.BOLD);
            val wcTop = WritableCellFormat(font)
            wcTop.run {
                // 垂直居中
                alignment = Alignment.CENTRE
                verticalAlignment = VerticalAlignment.CENTRE
                wrap = true
            }
            // 顶部标题
            sheet.addCell(Label(0, 0, "陪餐记录导出表", wcTop))

            // 次级标题
            val wcTop2 = WritableCellFormat(
                WritableFont(
                    WritableFont.createFont("宋体"),
                    12
                )
            )
            wcTop2.run {
                alignment = Alignment.CENTRE
                verticalAlignment = VerticalAlignment.CENTRE
                wrap = true
            }
            //X月X日-Y月Y日
            sheet.addCell(Label(0, 1, "(0801 - 0815)", wcTop2))


            // 内容标题栏
            val startRow = 3

            titleList.forEachIndexed { index, title ->
                run {

                    val wc = WritableCellFormat(
                        WritableFont(
                            WritableFont.createFont("宋体"),
                            12,
                            WritableFont.BOLD
                        )
                    )
                    wc.run {
                        verticalAlignment = VerticalAlignment.CENTRE
                        wrap = true
                    }
                    sheet.addCell(Label(index, startRow - 1, title, wc))
                }
            }

            // 内容
            dataList.forEachIndexed { index, excelBean ->
                run {
                    val wc = WritableCellFormat()
                    wc.wrap = true

                    val valueList = arrayListOf(
                        excelBean.id,
                        excelBean.name,
                        excelBean.time,
                        excelBean.date,
                        "A",
                        "B",
                        "C",
                        "D"
                    )
                    valueList.forEachIndexed { indexC, value ->
                        sheet.addCell(Label(indexC, index + startRow, value, wc))
                    }

                }
            }
            workbook.write()
            workbook.close()
            "导出成功:$filePath".toast()
        } catch (e: IOException) {
            e.printStackTrace()
        } catch (e: WriteException) {
            e.printStackTrace()
        }
    }