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);
    }