EasyPOI（二）模板导出Demo

EasyPOI 是一款Excel导入/导出操作的工具包，特点在于减少Java代码。

• 官方文档1：http://easypoi.mydoc.io/
• 官方文档2：http://doc.wupaas.com/docs/easypoi/easypoi-1c0u6ksp2r091
• 官方源码：https://gitee.com/lemur/easypoi.git
• 官方测试案例：https://gitee.com/lemur/easypoi-test.git

1. Maven坐标

        <!-- EasyPOI -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.3.0</version>
        </dependency>

2. Controller

import com.tsingsoft.modules.report.service.ExportTestService;
import lombok.AllArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

/**
 * <p> @Title ExportTestController
 * <p> @Description 模板导出测试
 *
 * @author ACGkaka
 * @date 2020/4/19 15:44
 */
@RestController
@AllArgsConstructor
@RequestMapping("/export")
public class ExportController {
    private ExportTestService exportTestService;

    @PostMapping("/by-response")
    public void exportByResponse(HttpServletResponse response) {
        try {
            exportTestService.exportByResponse(response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @PostMapping("/by-byte")
    public ResponseEntity exportByByte() {
        return exportTestService.exportByByte();
    }
}

3. Service

import org.springframework.http.ResponseEntity;
import javax.servlet.http.HttpServletResponse;

/**
 * <p> @Title ExportService
 * <p> @Description 导出测试Service
 *
 * @author ACGkaka
 * @date 2020/4/19 15:46
 */
public interface ExportService {

    /**
     * 通过HttpServiceResponse导出文件
     *
     * @param response HttpServletResponse
     */
    void exportByResponse(HttpServletResponse response);

    /**
     * 通过字节流导出文件
     * 
     * @return ResponseEntity
     */
    ResponseEntity exportByByte();
}

4. ServiceImpl

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p> @Title ExportServiceImpl
 * <p> @Description 导出测试ServiceImpl
 *
 * @author ACGkaka
 * @date 2020/4/19 15:43
 */
@Service
@AllArgsConstructor
public class ExportServiceImpl implements ExportService {

    UserMapper userMapper;

    @Override
    public void exportByResponse(HttpServletResponse response) {
        List<User> list = userMapper.queryAll();

        Map<String, Object> map = new HashMap<>();
        map.put("entityList", list);

        // 导出, 模板文件在 resources/file/demo 下
        // 这样存储的好处是, 如果打成jar包不影响模板文件的定位
        TemplateExportParams params = new TemplateExportParams("file/demo/" + "excel_template.xlsx");
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        try {
            response.setHeader(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("导出文件.xlsx", "utf-8"));
            // 让前端才可以获取到Content-Disposition
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException("临时文件写入失败");
        }
    }

    @Override
    public ResponseEntity exportByByte() {
        List<User> list = userMapper.queryAll();

        Map<String, Object> map = new HashMap<>();
        map.put("entityList", list);

        // 导出, 模板文件在 resources/file/demo 下
        // 这样存储的好处是, 如果打成jar包不影响模板文件的定位
        TemplateExportParams params = new TemplateExportParams("file/demo/" + "excel_template.xlsx");
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        // 字节缓冲区, 是内存读写流, 不同于指向硬盘的流, 字节数组是成员变量, 当数组不再使用的时候, GC会自动回收, 不用手动关闭流
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            workbook.write(out);
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            headers.setContentDispositionFormData("attachment", URLEncoder.encode("导出文件.xlsx", "utf-8"));
            // 让前端才可以获取到Content-Disposition
        	response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            return new ResponseEntity<>(out.toByteArray(), headers, HttpStatus.CREATED);
        } catch (IOException e) {
            throw new RuntimeException("临时文件写入失败");
        }
    }
}

注意：
1.这里使用 ByteArrayOutputStream, 不会在服务器本地生成冗余文件；
2.这里使用相对路径 file/demo, 即使打成jar包也不会影响模板文件的读取。

5. export.html

<html>
<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script type="text/javascript">
function exportExcel() {

    var url = "http://localhost:8081/export?type=1";

    var xhr = new XMLHttpRequest();
    xhr.open('GET', url, true);    //也可以使用POST方式，根据接口
    xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
    xhr.setRequestHeader("accessToken","123456");
    xhr.responseType = "blob";   //返回类型blob
    xhr.onload = function () {
        //定义请求完成的处理函数
        if (this.status === 200) {
			var blob = this.response;
			var fileName = this.getResponseHeader("content-disposition").split(";")[1].split("filename=")[1];
            if(blob.size>0){
                var reader = new FileReader();
                reader.readAsDataURL(blob);   // 转换为base64，可以直接放入a标签href
                reader.onload = function (e) {
                    // 转换完成，创建一个a标签用于下载
                    var a = document.createElement('a');
					a.target = '_blank'; // 新开窗口打开
                    a.download = decodeURI(fileName);
                    a.href = e.target.result;
                    $("body").append(a);    // 修复firefox中无法触发click
                    a.click();
                    $(a).remove();
                }
            }else{
                window.location.reload();
            }
        }
    };
    xhr.send();
}

</script>
<input type="button" onclick="exportExcel()" value="导出"/>
</html>