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