Springboot整合Easyexcel进行文件的查询并下载的操作

众所周知，现在easyexcel可谓是大大提高了我们对数据进行导出的操作。相比于我们以前常用的POI操作，easyexcel具有以下三个优点：1、开发更为简便。2、消耗的内存资源更少。3、导出的文件更小。所以我们以前常用的POI在现在easyexcel面前简直是重量级的存在。

首先我们要在maven仓库中新增配置文件

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

随后我们可以创建一个ExcelFactory作为一个实例工厂，用于创建Excel导出的实例工具类。

import com.alibaba.excel.metadata.BaseRowModel;
import org.springframework.stereotype.Component;

/**
 * excel工具实例工厂,这样我们可以直接使用@Autowired注解把这个实例对象拿出来了
 */
@Component
public class ExcelFactory<T extends BaseRowModel> {

    public ExportExcelUtil<T> createExportExcel() {
        return new ExportExcelUtil<>();
    }
    
}

创建用于导出Excel的工具类

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

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

/**
 * excel导出工具类,这里是直接通过response的方式返回我们创建好的excel对象。
 * 这样的话是直接通过网页的方式直接下载返回文件的。
 */
public class ExportExcelUtil<T extends BaseRowModel> {

    public ExportExcelUtil() {
    }

    /**
     * 导出excel表格
     */
    public void exportExcel(HttpServletResponse response, List<T> data, Class<T> clazz, String fileName) throws IOException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        // 注意，这里filename*=utf-8''是为了避免导出的文件名乱码！！！
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "utf-8"));
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, clazz);
            sheet1.setSheetName("sheet1");
            writer.write(data, sheet1);
            writer.finish();
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }
    
}

service层调用

@Resource
private ExcelFactory excelFactory;

@Override
    public void export(OrderVO orderVO, HttpServletResponse response) throws IOException {
        
        // .......查询重要数据的过程直接省略，这里我们只需要返回一个List集合进行遍历封装即可。
        
        List<OrderListModel> data = new ArrayList<>();
        // 转换数据
        orderList.forEach(orderVO -> {
            OrderListModel model = new OrderListModel();
            ExpandBeanUtils.copyProperties(orderVO, model);
			model.setOrderStatus(....);
            model.setPayStatus(....);
         	// 封装的内容自定义即可
            data.add(model);
        });
        String name = "==============";
        excelFactory.createExportExcel().exportExcel(response, data, OrderListModel.class, name);
    }

ExpandBeanUtils.java工具类文件

import org.apache.commons.lang3.ArrayUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.util.CollectionUtils;

import java.beans.PropertyDescriptor;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

public class ExpandBeanUtils extends BeanUtils {
    public static String[] getNullPropertyNames (Object source) {
        final BeanWrapper src = new BeanWrapperImpl(source);
        PropertyDescriptor[] pds = src.getPropertyDescriptors();

        Set<String> emptyNames = new HashSet<String>();
        for(PropertyDescriptor pd : pds) {
            Object srcValue = src.getPropertyValue(pd.getName());
            if (srcValue == null) {
                emptyNames.add(pd.getName());
            }
        }
        String[] result = new String[emptyNames.size()];
        return emptyNames.toArray(result);
    }

    public static <T> T copyPropertiesIgnoreNull(Object src, T target){
        BeanUtils.copyProperties(src, target, getNullPropertyNames(src));
        return target;
    }

    public static <T> T copyPropertiesIgnoreNull(Object src, T target, String... ignoreProperties){
        String[] newIgnoreProperties = ArrayUtils.addAll(getNullPropertyNames(src), ignoreProperties);
        BeanUtils.copyProperties(src, target, newIgnoreProperties);
        return target;
    }


    public static void copyProperties(Object src, Object target, String... properties) {
        List list = CollectionUtils.arrayToList(properties);
        String[] excludedProperties =
                Arrays.stream(BeanUtils.getPropertyDescriptors(src.getClass()))
                        .map(PropertyDescriptor::getName)
                        .filter(name -> !list.contains(name))
                        .toArray(String[]::new);

        BeanUtils.copyProperties(src, target, excludedProperties);
    }

    public static <T, R> List<R> dueList(List<T> list, Function<? super T, ? extends R> mapper){
        return list.stream().map(mapper).collect(Collectors.toList());
    }
}

ExcelModel实体类：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.yixinli.admin.util.serialization.JsonDateDaySerializer;
import com.yixinli.admin.util.serialization.JsonDateSerializer;
import com.yixinli.admin.util.serialization.JsonDateTimeSerializer;
import lombok.Data;

import java.util.Date;

@Data
public class OrderListModel extends BaseRowModel {

    @ExcelProperty(value = "订单号", index = 0)
    private String orderNo;

    @ExcelProperty(value = "订单时间", index = 1)
    @JsonSerialize(using = JsonDateTimeSerializer.class)
    private Date created;

    // ......
}

controller层代码

@RequestMapping(value = "/export", method = RequestMethod.GET)
    @ResponseBody
    public ResultJson export(OrderVO orderVO, HttpServletResponse response) throws IOException {
        orderService.export(orderVO, response);
        return ResultJson.success();
    }