欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-10-16 23:17:24
Java 的 POI 库可以用来创建和操作 Excel 表格,有时候我们只需要简单地将 List 或 Map 导出到表格,样板代码比较多,不够优雅。如果能像 Gson 那样,使用注解标记要导出的属性,就方便的多。 Github: "https://github.com/imcloudfloating ......

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"))
    }
}

导出结果:

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

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

效率似乎不怎么高 ^_^