List/Map 导出到表格(使用注解和反射)

java 的 poi 库可以用来创建和操作 excel 表格，有时候我们只需要简单地将 list 或 map 导出到表格，样板代码比较多，不够优雅。如果能像 gson 那样，使用注解标记要导出的属性，就方便的多。

github：https://github.com/imcloudfloating/listtoexcell

poi 的依赖：

<dependency>
     <groupid>org.apache.poi</groupid>
     <artifactid>poi</artifactid>
     <version>4.1.0</version>
</dependency>

1. 创建注解

package cloud.list2excel.annotation;

import org.apache.poi.hssf.util.hssfcolor;

import java.lang.annotation.elementtype;
import java.lang.annotation.retention;
import java.lang.annotation.retentionpolicy;
import java.lang.annotation.target;

@retention(retentionpolicy.runtime)
@target(elementtype.field)
public @interface column{
    string title() default "";
    short fontsize() default 14;
    hssfcolor.hssfcolorpredefined fontcolor() default hssfcolor.hssfcolorpredefined.black;
    hssfcolor.hssfcolorpredefined bordercolor() default hssfcolor.hssfcolorpredefined.black;
}

创建一个 @column 注解，用于标记字段

• title：该字段在表格头部的名称，默认值为属性名。
• fontsize：字体大小，默认 14px。
• fontcolor：字体颜色，默认黑色。
• bordercolor：边框颜色，默认黑色。

2. 使用反射获取注解，用 poi 将数据写入到表格

这部分用的 kotlin，但是反射和注解还是用的 java，因为 kotlin 反射获取的字段是排过序的，不是声明的顺序。

package cloud.list2excel.util

import cloud.list2excel.annotation.column
import org.apache.poi.hssf.usermodel.hssfcellstyle
import org.apache.poi.hssf.usermodel.hssfsheet
import org.apache.poi.hssf.usermodel.hssfworkbook
import org.apache.poi.ss.usermodel.borderstyle

/**
 * list/map 导出 excel 表格
 * @author cloud
 */
object listtoexcel {

    private var workbook: hssfworkbook = hssfworkbook()
    private val cellstyles: mutablelist<hssfcellstyle> = arraylist()

    /**
     * 处理单个 sheet
     */
    fun from(data: list<any>): hssfworkbook {
        toworkbook("sheet0", data)
        return workbook
    }

    /**
     * 处理多个 sheet
     */
    fun from(data: map<string, list<any>>): hssfworkbook {
        if (data.isempty())
            return workbook

        for (sheet in data) {
            toworkbook(sheet.key, sheet.value)
        }

        return workbook
    }

    private fun toworkbook(sheetname: string, list: list<any>) {
        val sheet = workbook.createsheet(sheetname)

        if (list.isempty())
            return

        val headers: mutablelist<string> = arraylist()
        val data: mutablelist<mutablelist<any>> = arraylist()

        // 获取注解并设置表头
        for (field in list[0].javaclass.declaredfields) {
            field.isaccessible = true

            val annotation = field.getannotation(column::class.java)

            if (annotation != null) {
                headers.add(if (annotation.title == "") field.name else annotation.title)

                val cellstyle = workbook.createcellstyle().also { style ->
                    style.setfont(workbook.createfont().also {
                        it.fontheightinpoints = annotation.fontsize
                        it.color = annotation.fontcolor.index
                    })

                }

                cellstyle.run {
                    leftbordercolor = annotation.bordercolor.index
                    topbordercolor = annotation.bordercolor.index
                    rightbordercolor = annotation.bordercolor.index
                    bottombordercolor = annotation.bordercolor.index

                    borderleft = borderstyle.thin
                    bordertop = borderstyle.thin
                    borderright = borderstyle.thin
                    borderbottom = borderstyle.thin
                }

                cellstyles.add(cellstyle)
            }
        }

        // 获取数据
        for (obj in list) {
            val rowdata: mutablelist<any> = arraylist()
            for (field in obj.javaclass.declaredfields) {
                field.isaccessible = true

                val annotation = field.getannotation(column::class.java)

                if (annotation != null) {
                    val t = field.get(obj)
                    if (t == null) {
                        rowdata.add("")
                    } else {
                        rowdata.add(t)
                    }
                }
            }
            data.add(rowdata)
        }

        setheader(sheet, headers)
        setdata(sheet, data)
    }

    /**
     * 设置表格头
     */
    private fun setheader(sheet: hssfsheet, headers: list<string>) {
        val row = sheet.createrow(0)

        for (i in headers.indices) {
            val cell = row.createcell(i)
            cell.setcellvalue(headers[i])
            cell.setcellstyle(cellstyles[i])
        }
    }

    /**
     * 写入数据
     */
    private fun setdata(sheet: hssfsheet, data: list<list<any>>) {
        for (i in data.indices) {
            val row = sheet.createrow(i + 1)

            for (j in data[i].indices) {
                val cell = row.createcell(j)
                cell.setcellvalue(data[i][j].tostring())
                cell.setcellstyle(cellstyles[j])
                sheet.autosizecolumn(j)
            }
        }
    }
}

from() 的参数为 list 时，直接写入 workbook 然后返回，为 map 时，将 map 中的 list 逐个写入到 workbook，map 的 key 作为 sheet 的名称。

3. 使用

创建两个实体类测试：

package cloud.list2excel.util

import cloud.list2excel.annotation.column
import org.apache.poi.hssf.util.hssfcolor
import java.sql.date

data class film(
    @column(title = "id", fontcolor = hssfcolor.hssfcolorpredefined.red)
    var id: int? = null,

    @column(title = "title")
    var title: string? = null,

    @column(title = "release date")
    var release_date: date? = null,

    @column(title = "duration")
    var duration: string? = null
)

package cloud.list2excel.util

import cloud.list2excel.annotation.column
import java.sql.date

data class actor(
    @column(title = "id")
    var id: int? = null,

    @column(title = "full name")
    var name: string? = null,

    @column(title = "birth")
    var birth: date?=null
)

测试类：

此处写入了两个 sheet。

package cloud.list2excel.util

import java.io.file
import java.sql.date

class listtoexceltest {

    private val films = listof(
        film(1, "iron man", date.valueof("2008-4-30"), "126 min"),
        film(2, "star wars: episode iv - a new hope", date.valueof("1977-5-25"), "121 min"),
        film(3, "zootropolis", date.valueof("2016-3-4"), "109 min")
    )

    private val actors = listof(
        actor(1, "robert john downey jr.", date.valueof("1965-4-4")),
        actor(2, "mark hamill", date.valueof("1951-9-25")),
        actor(3, "ginnifer goodwin", date.valueof("1978-5-22"))
    )

    private val data = mapof(
        pair("films", films),
        pair("actors", actors)
    )

    @org.junit.test
    fun toexcel() {
        val before = system.currenttimemillis()
        val workbook = listtoexcel.from(data)
        val after = system.currenttimemillis()
        println("time usage: ${after - before}ms")
        workbook.write(file("/home/data.xls"))
    }
}

导出结果：

效率似乎不怎么高 ^_^