基于阿里easyexcel实现的导出工具EasyExcelUtils

一、模板样式

本工具类使用于简单的模板导出，如下图：

引入的maven：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beat1</version>
</dependency>

二、工具类型实现

 使用方式

list:导出的数据列表

ExportModel：导出的表头信息

response：HttpServletResponse

EasyExcelUtils.baseExportExcel("导出列表",list , ExportModel.class , response);

实现方式

ExportModel类：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.time.LocalDateTime;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class ExportModel extends BaseRowModel implements Serializable {

    @ExcelProperty(index = 0 , value ="id")
    private String id;

    @ExcelProperty(index = 1 , value ="动作")
    private String action;

    @ExcelProperty(index = 2 , value ="用户名")
    private String userName;

    @ExcelProperty(index = 3 , value ="脱网时间")
    private LocalDateTime outDate;

    @ExcelProperty(index = 4 , value ="操作时间")
    private LocalDateTime operateDate;

    @ExcelProperty(index = 5 , value ="描述")
    private String description;
}

 

EasyExcelUtils类：

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.hdvon.common.lang.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.springframework.beans.BeanUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author allen小哥 2019/7/5 18:03
 * 阿里组件 easyexcel
 **/
public class EasyExcelUtils {

    protected static FastDateFormat fastDateFormat = FastDateFormat.getInstance("yyyyMMddHH:mm:ss");

    /**
     *
     * @param fileName 文件名
     * @param cameraReportStopList 普通vo类
     * @param entityModel 继承BaseRowModel的模型class
     * @param response
     * @param <T>
     */
    public static <T extends BaseRowModel> void baseExportExcel(String fileName , List cameraReportStopList , Class<T> entityModel , HttpServletResponse response) {
        List<BaseRowModel> modelList = new ArrayList<>();
        if (StringUtils.isEmpty(cameraReportStopList)) return;
        cameraReportStopList.forEach(cameraReportStopVo -> {
            try{
                BaseRowModel cameraReportStopModel = entityModel.newInstance();
                BeanUtils.copyProperties(cameraReportStopVo , cameraReportStopModel);
                modelList.add(cameraReportStopModel);
            } catch (Exception e){

            }
        });

        try{
            writeExcel(response , modelList , fileName+fastDateFormat.format(new Date()) , "第一页");
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list 数据 list，每个元素为一个 BaseRowModel
     * @param fileName 导出的文件名
     * @param sheetName 导入文件的 sheet 名
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                  String fileName, String sheetName) throws Exception {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Class clazz = null;
        if (list.size() > 0) {
            clazz = list.get(0).getClass();
        } else {
            clazz = BaseRowModel.class;
        }
        Sheet sheet = new Sheet(1, 0, clazz);
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
    }

    /**
     * 导出文件时为Writer生成OutputStream
     *
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

}

三、总结

   这篇文章简单，侧重代码实现，只是方便自己的基础代码实现，为以后直接使用。