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")) } }
导出结果:
效率似乎不怎么高 ^_^