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

分手后的思念是犯贱 2023-08-17 16:31 45阅读 0赞

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

Github：[https://github.com/imcloudfloating/ListToExcell][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

[https_github.com_imcloudfloating_ListToExcell]: https://github.com/imcloudfloating/ListToExcel
[films]: /images/20230810/075dee9954174522b03f3e070474e17a.png
[actors]: /images/20230810/2c4671ef4af14a4199aa5e5a1f1bc38b.png