基于自定义实现导出Excel
一般导入(解析Excel)导出不用Java做,这门语言不太适合做文件处理可以使用前端的node.js或goland做文件处理,小编写了一套基于自定义注解的导入和导出方便大家使用。
网上找的导入导出代码很多代码都是基本生成Excel文档,表头、文档名称都得手动传入参数实际实用性不强,小编基于自定义注解动态获取这些名称可用性非常高。
引用POM文件
<!-- 自定义注解 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<!-- Excle导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
新建自定义注解
- 模型上使用该注解可以动态获得Excel名称
package com.monkey.excel;
import java.lang.annotation.*;
/**
* @Author: LailaiMonkey
* @Description:描述表格名
* @Date:Created in 2020-08-29 16:48
* @Modified By:
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MonkeyExcelName {
String value();
}
- 模型字段上使用该注解可以动态获得列名
package com.monkey.excel;
import java.lang.annotation.*;
/**
* @Author: LailaiMonkey
* @Description:描述列名
* @Date:Created in 2020-08-29 16:48
* @Modified By:
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MonkeyExcelColumnName {
String value();
int sort() default Integer.MAX_VALUE;
}
新建两个描述表格元素类
这两类主要作用是获得Excel和列的名称,创建Excel时候直接使用不需要再次获取。
- 描述表格名称及所有列信息
package com.monkey.excel;
import org.apache.commons.lang3.reflect.FieldUtils;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
/**
* @Author: LailaiMonkey
* @Description:描述表格名称及所有列信息
* @Date:Created in 2020-08-29 16:03
* @Modified By:
*/
public class MonkeyExcel {
private List<MonkeyExcelColumn> columns;
private String name;
MonkeyExcel(Class<?> clazz) {
this.columns = getCellName(clazz);
this.name = getExcelName(clazz);
}
private List<MonkeyExcelColumn> getCellName(Class<?> clazz) {
List<MonkeyExcelColumn> cellNames = new ArrayList<>();
Field[] fields = FieldUtils.getAllFields(clazz);
for (Field field : fields) {
MonkeyExcelColumnName annotation = field.getAnnotation(MonkeyExcelColumnName.class);
if (annotation != null) {
cellNames.add(new MonkeyExcelColumn(annotation.value(), field.getName(), annotation.sort()));
} else {
cellNames.add(new MonkeyExcelColumn(field.getName(), field.getName(), Integer.MAX_VALUE));
}
}
cellNames.sort(Comparator.comparingInt(MonkeyExcelColumn::getSort));
return cellNames;
}
private String getExcelName(Class clazz) {
MonkeyExcelName annotation = (MonkeyExcelName) clazz.getAnnotation(MonkeyExcelName.class);
if (annotation != null) {
return annotation.value();
}
return clazz.getSimpleName();
}
public List<MonkeyExcelColumn> getColumns() {
return columns;
}
public String getName() {
return name;
}
}
- 描述所有列信息
package com.monkey.excel;
/**
* @Author: LailaiMonkey
* @Description:描述所有列信息
* @Date:Created in 2020-08-29 18:39
* @Modified By:
*/
public class MonkeyExcelColumn {
private String name;
private String fieldName;
private Integer sort;
MonkeyExcelColumn(String name, String fieldName, Integer sort) {
this.name = name;
this.fieldName = fieldName;
this.sort = sort;
}
public String getName() {
return name;
}
public String getFieldName() {
return fieldName;
}
public Integer getSort() {
return sort;
}
}
生成Excel工具类
package com.monkey.excel;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* @Author: LailaiMonkey
* @Description:导出工具类
* @Date:Created in 2020-08-29 16:02
* @Modified By:
*/
public class MonkeyExcelExportUtils {
public static void export(HttpServletResponse response, List<?> list) {
if (CollectionUtils.isEmpty(list)) {
return;
}
//声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook();
//生成一个表格
SXSSFSheet sheet = workbook.createSheet();
//获取要生成表格信息
MonkeyExcel excelModel = new MonkeyExcel(list.get(0).getClass());
//获取所有列名
List<MonkeyExcelColumn> models = excelModel.getColumns();
//循环字段名数组,创建标题行
Row row = sheet.createRow(0);
for (int i = 0; i < models.size(); i++) {
//创建列
Cell cell = row.createCell(i);
//设置单元类型为String
cell.setCellType(CellType.STRING);
cell.setCellValue(models.get(i).getName());
}
//创建普通行
for (int i = 0; i < list.size(); i++) {
//因为第一行已经用于创建标题行,故从第二行开始创建
row = sheet.createRow(i + 1);
for (int j = 0; j < models.size(); j++) {
//创建列
Cell cell = row.createCell(j);
//集合中某行为空
Object value = null;
if (list.get(i) != null) {
value = getFieldValue(list.get(i), models.get(j).getFieldName());
}
cell.setCellValue(transCellType(value));
}
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.setHeader("Content-Disposition", "attachment;fileName=" +
URLEncoder.encode(excelModel.getName(), "utf-8") +
".xlsx");
response.flushBuffer();
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
private static Object getFieldValue(Object object, String fieldName) {
Class<?> clazz = object.getClass();
try {
Field name = FieldUtils.getField(clazz, fieldName, true);
return FieldUtils.readField(name, object, true);
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
private static String transCellType(Object value) {
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(date);
}
return "null".equals(String.valueOf(value)) ? "" : String.valueOf(value);
}
}
使用方法
controller调用
MonkeyExcelExportUtils.export(response, 需要生成数据集合);
传入HttpServletResponse请求和数据集合。
模型定义: