基于自定義實作導出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請求和資料集合。
模型定義: