List/Map 导出到表格(使用注解和反射)
2019-08-16 11:51:05来源:博客园 阅读 ()
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"))
}
}
导出结果:
效率似乎不怎么高 ^_^
原文链接:https://www.cnblogs.com/cloudfloating/p/11299057.html
如有疑问请与原作者联系
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- 深入解析ThreadLocal和ThreadLocalMap 2020-06-08
- 「starter推荐」简单高效Excel 导出工具 2020-06-08
- 头条面试居然跟我扯了半小时的Semaphore 2020-06-08
- Invalid [xxx] in servlet mapping 、 <url-pattern& 2020-06-07
- HashMap:源代码(构造方法、put、resize、get、remove、rep 2020-06-04
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash