java导出列表信息功能接口实现
可直接拷貝代码实现该功能
一:放置項目目录位置
1:导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2:自定义导出Excel数据注解
package com.venus.lang.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义导出Excel数据注解
*
* @author admin
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelProperty {
/**
* 导出到Excel中的名字.
*/
public String name() default "";
/**
* 导出到Excel中的名字.
*/
public int index() default 0;
/**
* 日期格式, 如: yyyy-MM-dd
*/
public String dateFormat() default "";
/**
* 读取内容转表达式 (如: 0=男,1=女,2=未知)
*/
public String readConverterExp() default "";
/**
* 导出类型(0数字 1字符串)
*/
public ColumnType cellType() default ColumnType.STRING;
/**
* 导出时在excel中每个列的高度 单位为字符
*/
public double height() default 14;
/**
* 导出时在excel中每个列的宽 单位为字符
*/
public double width() default 16;
/**
* 文字后缀,如% 90 变成90%
*/
public String suffix() default "";
/**
* 当值为空时,字段的默认值
*/
public String defaultValue() default "";
/**
* 提示信息
*/
public String prompt() default "";
/**
* 设置只能选择不能输入的列内容.
*/
public String[] combo() default {};
/**
* 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
*/
public boolean isExport() default true;
/**
* 另一个类中的属性名称,支持多级获取,以小数点隔开
*/
public String targetAttr() default "";
/**
* 字段类型(0:导出导入;1:仅导出;2:仅导入)
*/
Type type() default Type.ALL;
public enum Type {
ALL(0), EXPORT(1), IMPORT(2);
private final int value;
Type(int value) {
this.value = value;
}
public int value() {
return this.value;
}
}
public enum ColumnType {
NUMERIC(0), STRING(1);
private final int value;
ColumnType(int value) {
this.value = value;
}
public int value() {
return this.value;
}
}
}
3:ExcelPropertys注解集
package com.venus.lang.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* ExcelPropertys注解集
*
* @author admin
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelPropertys {
ExcelProperty[] value();
}
4:监听类
package com.venus.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* 监听类
*
* @author JIANGMING
* @Date 2021/03/01
*/
public class ExcelListener extends AnalysisEventListener {
/**
* 可以通过实例获取该值
*/
private List<Object> dataList = new ArrayList<>();
@Override
public void invoke(Object object, AnalysisContext context) {
//数据存储到list,供批量处理,或后续自己业务逻辑处理。
dataList.add(object);
handleBusinessLogic();
/**
//如数据过大,可以进行定量分批处理
if (dataList.size() >= 200) {
handleBusinessLogic();
dataList.clear();
}*/
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//非必要语句,查看导入的数据
System.out.println("导入的数据条数为: " + dataList.size());
System.out.println("---: " + dataList);
}
/**
* 根据业务自行实现该方法,例如将解析好的dataList存储到数据库中
*/
private void handleBusinessLogic() {
}
public List<Object> getDataList() {
return dataList;
}
public void setDataList(List<Object> dataList) {
this.dataList = dataList;
}
}
4:Excel导出合并/导入工具类
package com.venus.util;
import cn.hutool.core.convert.Convert;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.venus.lang.annotation.ExcelProperty;
import com.venus.lang.annotation.ExcelPropertys;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
/**
* Excel导出合并/导入工具类
*
* @author JIANGMING
* @Date 2021/03/01
*/
public class ExcelMergeUtil<T> extends AbstractMergeStrategy {
private static final Logger log = LoggerFactory.getLogger(ExcelMergeUtil.class);
/**
* Excel sheet最大行数,默认65536
*/
public static final int sheetSize = 65536;
/**
* 导入导出数据列表
*/
private List<T> list;
/**
* 工作表名称
*/
private String sheetName;
/**
* 导出类型(EXPORT:导出数据;IMPORT:导入模板)
*/
private ExcelProperty.Type type;
/**
* 注解列表
*/
private List<Object[]> fields;
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
private Map<String, List<RowRangeDtoUtil>> strategyMap;
private Sheet sheet;
/**
* 实体对象
*/
public Class<T> clazz;
public ExcelMergeUtil(Class<T> clazz) {
this.clazz = clazz;
}
public void init(List<T> list, String sheetName, ExcelProperty.Type type) {
if (list == null) {
list = new ArrayList<T>();
}
this.list = list;
this.sheetName = sheetName;
this.type = type;
createExcelField();
createWorkbook();
}
/**
* 创建一个工作簿
*/
public void createWorkbook() {
this.wb = new SXSSFWorkbook(500);
}
/**
* 得到所有定义字段
*/
private void createExcelField() {
this.fields = new ArrayList<Object[]>();
List<Field> tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
for (Field field : tempFields) {
// 单注解
if (field.isAnnotationPresent(ExcelProperty.class)) {
putToField(field, field.getAnnotation(ExcelProperty.class));
}
// 多注解
if (field.isAnnotationPresent(ExcelPropertys.class)) {
ExcelPropertys attrs = field.getAnnotation(ExcelPropertys.class);
ExcelProperty[] excelPropertys = attrs.value();
for (ExcelProperty excelProperty : excelPropertys) {
putToField(field, excelProperty);
}
}
}
}
/**
* 放到字段集合中
*/
private void putToField(Field field, ExcelProperty attr) {
if (attr != null && (attr.type() == ExcelProperty.Type.ALL || attr.type() == type)) {
this.fields.add(new Object[]{field, attr});
}
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
this.sheet = sheet;
//如果没有标题,只有表头的话,这里的 cell.getRowIndex() == 1
if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
/**
* 保证每个cell被合并一次,如果不加上面的判断,因为是一个cell一个cell操作的,
* 例如合并A2:A3,当cell为A2时,合并A2,A3,但是当cell为A3时,又是合并A2,A3,
* 但此时A2,A3已经是合并的单元格了
*/
for (Map.Entry<String, List<RowRangeDtoUtil>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
//添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
rowRange.getEnd(), columnIndex, columnIndex));
});
}
}
}
/**
* @return
* @description: 表格样式
* @author
* @Modified By:
* @since 2020/11/20 9:40
*/
public static HorizontalCellStyleStrategy CellStyleStrategy() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
/**
* 浏览器导出excel表格
*
* @author Administrator
*/
public static <E> void getExcelWriterMerge(HttpServletResponse response,List<E> list, String claws) throws IOException {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和essence没有关系
String filename = URLEncoder.encode("" + System.currentTimeMillis(), "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
Class clazz = Class.forName(claws);
EasyExcel.write(response.getOutputStream(), clazz.newInstance().getClass())
.excelType(ExcelTypeEnum.XLSX).head(clazz.newInstance().getClass())
//.registerWriteHandler(new TitleSheetWriteHandler("我是一个小标题",2)) // 标题及样式,lastCol为标题第0列到底lastCol列的宽度
//设置默认样式及写入头信息开始的行数
.relativeHeadRowIndex(0)
// 设置样式
.registerWriteHandler(ExcelMergeUtil.CellStyleStrategy())
.registerConverter(new LocalDateTimeConverter()).sheet("测试")
.doWrite(list);
} catch (Exception e) {
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
response.getWriter().println("打印失败");
}
}
/**
* 导入方法
* 读取Excel(多个sheet可以用同一个实体类解析)
*
* @param excelInputStream
* @param fileName
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
ExcelListener excelListener = new ExcelListener();
ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}
/**
* 返回ExcelReader
*
* @param clazz 实体类
* @param excelListener
*/
private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
try {
if (filename == null ||
(!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
inputStream.close();
return excelReader;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
5:日期处理,这里使用的日期类型是LocalDateTime
package com.venus.util;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
/**
* @author JIANGMING
*/
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
6:合并单元格开始-结束
package com.venus.util;
/**
* 合并单元格开始-结束
*
* @author JIANGMING
*/
public class RowRangeDtoUtil {
private int start;
private int end;
public RowRangeDtoUtil(int start, int end) {
this.start = start;
this.end = end;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public int getEnd() {
return end;
}
public void setEnd(int end) {
this.end = end;
}
}
7:controller调用
/**
* 导出日志列表信息
*/
@ApiOperation("导出信息")
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
List<SystemLog> list = logList.getRecords();
String claws = "com.venus.system.entity.SystemLog";
ExcelMergeUtil.getExcelWriterMerge(response,list,claws);
}