• 前言：不得不说，阿里出品，必是精品。在使用阿里的easyexcel表格导出之前，我是用poi导出，效率之慢，简直龟速。使用了easyexcel之后，简直像坐了火箭，嗖嗖嗖的起飞了。
• 项目背景：需求。需要导出在web项目中导出。但是不能延迟太久。此外，由于生产环境的限制，不能使用redis，所以抛弃了异步导出功能，只能使用同步导出。每次导出会有10万左右的数据。

使用poi文件导出测试：

条数	耗时
10000	41秒
20000	1分19秒

• 由此可见，如果一次要导出10万条数据，那将会耗时好久，服务器也有可能因此崩溃。在寻找解决方法的过程中，找到了阿里的easyexcel（https://github.com/alibaba/easyexcel） 抱着试一试的心态，用上了这个包，效果令我喜出望外！因此记录此次优化，方便今后查阅。

使用easyexcel文件导出测试：

条数	耗时
250000	51秒

• 以下为主要代码
• pom包：

<!--        阿里巴巴开源easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
        </dependency>

• web控制器：ExcelExportController

@RestController
@Api(value = "excel_export", tags = SwaggerConstant.TAG_FOR_EXCEL_EXPORT)
@RequestMapping(value = "preston/export", produces = "application/json;charset=utf-8")
public class ExcelExportController {

    final private ExcelExportService excelExportService;

    public ExcelExportController(ExcelExportService excelExportService) {
        this.excelExportService = excelExportService;
    }

    @ApiOperation(value = "搭配结果导出")
    @GetMapping("{version}/collocation")
    public void collocationList(
            @Valid CollocationListExportRequest request,
            @PathVariable("version") String version, HttpServletResponse response, HttpServletRequest httpServletRequest
    ) throws IOException {
        excelExportService.collocationList(response,request, httpServletRequest);
    }
}

• service：ExcelExportServiceImpl

@Service
public class ExcelExportServiceImpl implements ExcelExportService {

        /**
     * 搭配结果导出
     *
     * @param response
     * @param request
     * @param httpServletRequest
     * @throws IOException
     */
    @Override
    public void collocationList(HttpServletResponse response, CollocationListExportRequest request, HttpServletRequest httpServletRequest) throws IOException {
        CollocationListRequest search = new CollocationListRequest();
        BeanUtils.copyProperties(request, search);
        search.setPage(null);
        search.setPageSize(null);
        List<CollocationListResponse> listResponses = collocationMapper.findList(search);
        List<Object> datas = new ArrayList<>();
        int i = 1;
        for (CollocationListResponse listResponse : listResponses) {
            listResponse.setId(String.valueOf(i));
            if ("1".equals(listResponse.getStatus())) {
                listResponse.setStatus("待处理");
            } else if ("2".equals(listResponse.getStatus())) {
                listResponse.setStatus("已确认");
            } else {
                listResponse.setStatus("待确认");
            }
            datas.add(listResponse);
            i++;
        }
        HttpServletResponse response1 = ResponseUtils.exportFile(response, ExcelTlCon.COLLCOACTION_EXPORT, httpServletRequest);
        /*
        easyExcel核心代码
        */
        EasyExcel.write(response1.getOutputStream(), CollocationListExportResponse.class).sheet().doWrite(datas);
    }
}

• 文件名称配置：ExcelTlCon

public final class ExcelTlCon {
    //.............................搭配结果导出-start................................
    public static final String COLLCOACTION_EXPORT="搭配结果导出";
}

• 导出文件的标题头配置：CollocationListExportResponse

public class CollocationListExportResponse {

    @ExcelProperty("编号")
    @ApiModelProperty(value = "主键id")
    private String id;

    @ExcelProperty("一级")
    @ApiModelProperty(value = "一级")
    private String tagLevel1;

    @ExcelProperty("二级")
    @ApiModelProperty(value = "二级")
    private String tagLevel2;

    @ExcelProperty("评价词")
    @ApiModelProperty(value = "评价词")
    private String word;

    @ExcelProperty("状态")
    @ApiModelProperty(value = "状态：0待确认 1待处理 2已确认")
    private String status;

    public CollocationListExportResponse() {
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTagLevel1() {
        return tagLevel1;
    }

    public void setTagLevel1(String tagLevel1) {
        this.tagLevel1 = tagLevel1;
    }

    public String getTagLevel2() {
        return tagLevel2;
    }

    public void setTagLevel2(String tagLevel2) {
        this.tagLevel2 = tagLevel2;
    }

    public String getWord() {
        return word;
    }

    public void setWord(String word) {
        this.word = word;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

• 导出文件的hearder头配置：ResponseUtils

public class ResponseUtils {

    public static HttpServletResponse exportFile(HttpServletResponse response, String bookName, HttpServletRequest request) throws IOException {
        response.setContentType("application/force-download");
        /*
        告诉浏览器用什么软件可以打开此文件
         */
        response.setHeader("content-Type", "application/vnd.ms-excel");
        if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
            /*
            firefox浏览器
             */
            bookName = new String(bookName.getBytes(StandardCharsets.UTF_8), "ISO8859-1");
        } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0 ||
                request.getHeader("User-Agent").toLowerCase().contains("like gecko") ||
                request.getHeader("User-Agent").toUpperCase().indexOf("EDGE") > 0) {
            bookName = URLEncoder.encode(bookName, "UTF-8");
        } else if (request.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {
            /*
            谷歌
             */
            bookName = new String(bookName.getBytes(StandardCharsets.UTF_8), "ISO8859-1");
        } else {
            /*
            firefox浏览器
             */
            bookName = new String(bookName.getBytes(StandardCharsets.UTF_8), "ISO8859-1");
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + bookName + ".xlsx");
        return response;
    }
}