阿里巴巴的组件easyExcel

之前没有接触过导出报表大数据量的相关技术，上班后，涉及到需要导出大数据量的功能，因为接触了阿里的组件easyExcel。

easyExcel相比之前的excelpoi 占用内存很小，我们知道excelpoi在处理特别多的数据的时候，速度慢且经常有异常发生，因为用阿里研发的easyExcel较为靠谱些。

easyExcel主要功能：

1.支持Excel导入与导出，同时支持xls和xlsm，即07版本和03版本(官方建议03版本不要超过2000行）的Excel文件格式。

2.支持pojo注释时，映射成为java实体模型。

3.支持多个sheet，同时一个sheet支持多张表。

4.支持自定义Excel样式：字体，加粗,表头颜色，数据内容颜色等。

5.可以设置是否需要写表头。
 

使用easyExcel需要导入依赖（案例中使用的是2.1.6版本）

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.1.6</version>
</dependency>

该案例是基于若依管理系统进行了改造（用户管理，导出功能）

easyExcel可以分为有Java模型和无Java模型，有Java模型需要继承BaseRowModel

@Log(title = "用户管理", businessType = BusinessType.EXPORT)
    @PreAuthorize("@ss.hasPermi('system:user:export')")
    @GetMapping("/export")
    public AjaxResult export(SysUser user, HttpServletResponse response, HttpServletRequest request)
    {
        AjaxResult ajaxResult = null;
        try {
            List<SysUser> exportList = new ArrayList<>();
            List<SysUser> list = userService.selectUserList(user);
            //组装导出数据
            list.stream().forEach(x -> {
                SysUser sysUser = new SysUser();
                sysUser.buildSysUser(sysUser,x);
                exportList.add(sysUser);
            });
            ajaxResult  = ExportExcelUtil.generateExcel(new ArrayList<>(), exportList, response, request);
        }catch (Exception e){
            e.printStackTrace();
            log.error("daochu error", e);
        }
        return ajaxResult;
    }

SysUser （BaseEntity里面继承了BaseRowModel）

package com.ruoyi.project.system.domain;

import java.util.Date;
import java.util.List;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

import com.alibaba.excel.annotation.ExcelProperty;
import com.ruoyi.project.system.converter.SexConverter;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import com.ruoyi.framework.aspectj.lang.annotation.Excel.ColumnType;
import com.ruoyi.framework.aspectj.lang.annotation.Excel.Type;
import com.ruoyi.framework.aspectj.lang.annotation.Excels;
import com.ruoyi.framework.web.domain.BaseEntity;

/**
 * 用户对象 sys_user
 * 
 * @author ruoyi
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class SysUser extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 用户ID */
    @ExcelProperty(value = "用户序号",index = 0)
    private Long userId;

    /** 部门ID */
    private Long deptId;

    /** 用户账号 */
    @NotBlank(message = "用户账号不能为空")
    @Size(min = 0, max = 30, message = "用户账号长度不能超过30个字符")
    @ExcelProperty(value = "登录名称",index = 1)
    private String userName;

    /** 用户昵称 */
    @Size(min = 0, max = 30, message = "用户昵称长度不能超过30个字符")
    @ExcelProperty(value = "用户名称",index = 2)
    private String nickName;

    /** 用户邮箱 */
    @Email(message = "邮箱格式不正确")
    @Size(min = 0, max = 50, message = "邮箱长度不能超过50个字符")
    @ExcelProperty(value = "用户邮箱",index = 3)
    private String email;

    /** 手机号码 */
    @Size(min = 0, max = 11, message = "手机号码长度不能超过11个字符")
    @ExcelProperty(value = "手机号码",index = 4)
    private String phonenumber;

    /** 用户性别 */
    @ExcelProperty(value = "用户性别",index = 5)
    private String sex;

    /** 用户头像 */
    private String avatar;

    /** 密码 */
    @JsonProperty
    private String password;

    /** 盐加密 */
    private String salt;

    /** 帐号状态（0正常 1停用） */
    @ExcelProperty(value = "帐号状态",index = 6)
    private String status;

    /** 删除标志（0代表存在 2代表删除） */
    private String delFlag;

    /** 最后登陆IP */
    @ExcelProperty(value = "最后登陆IP",index = 7)
    private String loginIp;

    /** 最后登陆时间 */
    @ExcelProperty(value = "最后登陆时间",index = 8)
    private Date loginDate;

    /** 部门名称 */
    @ExcelProperty(value = "部门名称",index = 9)
    private String deptName;

    /** 负责人 */
    @ExcelProperty(value = "部门负责人", index = 10)
    private String leader;

    /** 部门对象 */
    private SysDept dept;

    /** 角色对象 */
    private List<SysRole> roles;

    /** 角色组 */
    private Long[] roleIds;

    /** 岗位组 */
    private Long[] postIds;

    public SysUser(Long userId)
    {
        this.userId = userId;
    }



    public boolean isAdmin()
    {
        return isAdmin(this.userId);
    }

    public static boolean isAdmin(Long userId)
    {
        return userId != null && 1L == userId;
    }


    public void buildSysUser(SysUser sysUser, SysUser x) {
        sysUser.setUserId(x.getUserId());
        sysUser.setUserName(x.getUserName());
        sysUser.setNickName(x.getNickName());
        sysUser.setEmail(x.getEmail());
        sysUser.setPhonenumber(x.getPhonenumber());
        sysUser.setSex(x.getSex());
        sysUser.setStatus(x.getStatus());
        sysUser.setLoginIp(x.getLoginIp());
        sysUser.setLoginDate(x.getLoginDate());
        sysUser.setDeptName(x.getDept().getDeptName());
        sysUser.setLeader(x.getDept().getLeader());
    }
}

ExportExcelUtil类 

package com.ruoyi.common.utils.poi;


import com.alibaba.excel.EasyExcelFactory;
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.ruoyi.common.exception.BaseException;
import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.framework.web.domain.AjaxResult;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.UUID;

@Slf4j
public class ExportExcelUtil {
    
    /**
     * 生成带数据的excel
     * @param templateDetails
     * @param dataList
     * @param
     */
    public static AjaxResult generateExcel(List<Object> templateDetails, List<? extends BaseRowModel> dataList, HttpServletResponse response, HttpServletRequest request) throws Exception{
        if (CollectionUtils.isEmpty(dataList)){
            throw new BaseException("导出数据不能为空");
        }
        String sheetName = "用户数据";
        String filename = encodingFilename(sheetName);
        //初始化数据
        generateDatasExcel(templateDetails, dataList,filename);
        return AjaxResult.success(filename);
    }

    /**
     * 初始化数据
     * @param templateDetails
     * @param dataList
     * @param path
     */
    private static void generateDatasExcel(List<Object> templateDetails, List<? extends BaseRowModel> dataList,String path) throws Exception{
        try(OutputStream out = new FileOutputStream(getAbsoluteFile(path))){
            ExcelWriter excelWriter = EasyExcelFactory.getWriterWithTempAndHandler(null, out,ExcelTypeEnum.XLSX, true,null);
            Class<? extends BaseRowModel> a = dataList.get(0).getClass();
            Sheet sheet1 = new Sheet(1,1,a);
            sheet1.setSheetName("用户数据");
            excelWriter.write(dataList,sheet1);
            excelWriter.finish();
        }catch (IOException e){
            log.error("导出生成文件失败", e);
        }
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public static String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }
    

    /**
     * 编码文件名
     */
    public static String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }
}

 fileDownload方法（打断点走到了这个方法里面对文件进行下载，不知道在哪里调用的？有知道的小伙伴可以在评论里留言）

package com.ruoyi.project.common;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileUploadUtils;
import com.ruoyi.common.utils.file.FileUtils;
import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.framework.config.ServerConfig;
import com.ruoyi.framework.web.domain.AjaxResult;

/**
 * 通用请求处理
 * 
 * @author ruoyi
 */
@RestController
public class CommonController
{
    private static final Logger log = LoggerFactory.getLogger(CommonController.class);

    @Autowired
    private ServerConfig serverConfig;

    /**
     * 通用下载请求
     * 
     * @param fileName 文件名称
     * @param delete 是否删除
     */
    @GetMapping("common/download")
    public void fileDownload(String fileName, Boolean delete, HttpServletResponse response, HttpServletRequest request)
    {
        try
        {
            if (!FileUtils.isValidFilename(fileName))
            {
                throw new Exception(StringUtils.format("文件名称({})非法，不允许下载。 ", fileName));
            }
            String realFileName = System.currentTimeMillis() + fileName.substring(fileName.indexOf("_") + 1);
            String filePath = RuoYiConfig.getDownloadPath() + fileName;

            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition",
                    "attachment;fileName=" + FileUtils.setFileDownloadHeader(request, realFileName));
            FileUtils.writeBytes(filePath, response.getOutputStream());
            if (delete)
            {
                FileUtils.deleteFile(filePath);
            }
        }
        catch (Exception e)
        {
            log.error("下载文件失败", e);
        }
    }

    /**
     * 通用上传请求
     */
    @PostMapping("/common/upload")
    public AjaxResult uploadFile(MultipartFile file) throws Exception
    {
        try
        {
            // 上传文件路径
            String filePath = RuoYiConfig.getUploadPath();
            // 上传并返回新文件名称
            String fileName = FileUploadUtils.upload(filePath, file);
            String url = serverConfig.getUrl() + fileName;
            AjaxResult ajax = AjaxResult.success();
            ajax.put("fileName", fileName);
            ajax.put("url", url);
            return ajax;
        }
        catch (Exception e)
        {
            return AjaxResult.error(e.getMessage());
        }
    }

    /**
     * 本地资源通用下载
     */
    @GetMapping("/common/download/resource")
    public void resourceDownload(String name, HttpServletRequest request, HttpServletResponse response) throws Exception
    {
        // 本地资源路径
        String localPath = RuoYiConfig.getProfile();
        // 数据库资源地址
        String downloadPath = localPath + StringUtils.substringAfter(name, Constants.RESOURCE_PREFIX);
        // 下载名称
        String downloadName = StringUtils.substringAfterLast(downloadPath, "/");
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition",
                "attachment;fileName=" + FileUtils.setFileDownloadHeader(request, downloadName));
        FileUtils.writeBytes(downloadPath, response.getOutputStream());
    }
}