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

2019-08-16 11:51:05来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

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

导出结果:

films

actors

效率似乎不怎么高 ^_^


原文链接:https://www.cnblogs.com/cloudfloating/p/11299057.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:方法的形式参数是类名的时候如何调用

下一篇:几种自定义Spring生命周期的初始化和销毁方法