【导出功能】使用easypoi笔记

前端

vue 触发导出注意:

1. 请求时： responseType: ‘blob’
2. 响应时：需要模拟a标签点击下载

示例

    /**
     * 导出excel
     */
    exportExcel(page) {
        this.dataListLoading = true
        this.$http({
          url: this.$http.adornUrl('/xxx/request-url'),
          method: 'GET',
          params: this.$http.adornParams(
            Object.assign(
              {
                current: page == null ? this.page.currentPage : page.currentPage,
                size: page == null ? this.page.pageSize : page.pageSize
              },
              this.searchForm
            )
          ),
          responseType: 'blob' // 解决文件下载乱码问题
        }).then(({ data }) => {
          console.log('响应结果', data)
          var blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' })
          let fileName += new Date().Format("yyyy-MM-dd HH:mm:ss") + ".xlsx"
          const elink = document.createElement('a')
          if ('download' in elink) { // 非IE下载
            elink.download = fileName
            elink.style.display = 'none'
            elink.href = URL.createObjectURL(blob)
            document.body.appendChild(elink)
            elink.click()
            URL.revokeObjectURL(elink.href) // 释放URL 对象
            document.body.removeChild(elink)
          } else { // IE10+下载
            navigator.msSaveBlob(blob, fileName)
          }
          this.dataListLoading = false
        })
    }
	// 文件名字使用格式化时间
	Date.prototype.Format = function (fmt) {
	  var o = {
	    "M+": this.getMonth() + 1, //月份
	    "d+": this.getDate(), //日
	    "H+": this.getHours(), //小时
	    "m+": this.getMinutes(), //分
	    "s+": this.getSeconds(), //秒
	    "q+": Math.floor((this.getMonth() + 3) / 3), //季度
	    "S": this.getMilliseconds() //毫秒
	  };
	  if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
	  for (var k in o)
	    if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
	  return fmt;
	}

后端

spring boot 项目 使用 easypoi

第一步 引入依赖

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>

第二步 引用工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

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

/**
 * @author hz
 * @date 2021/4/24 4:32 PM
 */
@Component
public class ExcelExportUtils {

    @Autowired
    private HttpServletResponse response;

    /**
     * 导出excel
     * @param list 泛型数据
     * @param title 标题
     * @param sheetName sheet的名称
     * @param pojoClass 需要导出的对象
     * @param fileName 文件名称
     * @param isCreateHeader 是否创建表头
     * @throws IOException void
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String title, String sheetName, String fileName,
                            boolean isCreateHeader) throws IOException {
        final ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        baseExport(list, pojoClass, fileName, exportParams);
    }

    /**
     * 导出excel
     * @param list 泛型数据
     * @param title 标题
     * @param sheetName sheet的名称
     * @param pojoClass 需要导出的对象
     * @param fileName 文件名称
     * @throws IOException void
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String title, String sheetName, String fileName)
            throws IOException {
        baseExport(list, pojoClass, fileName, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 导出excel
     * @param list 泛型数据
     * @param pojoClass 需要导出的对象
     * @param fileName 文件名称
     * @param exportParams 文件书香
     * @throws IOException void
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams)
            throws IOException {
        baseExport(list, pojoClass, fileName, exportParams);
    }

    /**
     * 多个sheet导出
     * @param list
     * @param fileName
     * @throws IOException void
     */
    public void exportExcel(List<Map<String, Object>> list, String fileName) throws IOException {
        baseExport(list, fileName);
    }

    /**
     * 最基础的对象导出
     * @param list 数据列表
     * @param pojoClass 导出对象
     * @param fileName 文件名称
     * @param exportParams 导出文件属性
     * @throws IOException void
     */
    private void baseExport(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams)
            throws IOException {
        final Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, workbook);
    }

    /**
     * 最基础的多sheet导出
     * @param list 多个不同数据对象的列表
     * @param fileName 文件名称
     * @throws IOException void
     */
    private void baseExport(List<Map<String, Object>> list, String fileName) throws IOException {
        final Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, workbook);
    }

    /**
     * 文件下载
     * @param fileName 文件名称
     * @param workbook exce对象
     * @throws IOException void
     */
    private void downLoadExcel(String fileName, Workbook workbook) throws IOException {
        ServletOutputStream output = null;
        try {
            final String downloadName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + downloadName);
            output = response.getOutputStream();
            workbook.write(output);
        }
        catch (final Exception e) {
            throw new IOException(e.getMessage());
        }
        finally {
            if (output != null) {
                output.flush();
                output.close();
            }
        }
    }
}

第三步 编写Excel列实体

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;

/**
 * Excel模板
 * @author hz
 * @date 2021/4/22 5:49 PM
 */
@Data
@ExcelTarget("textExcel")
public class TextExcelEntity implements Serializable {

    /**
     * 姓名
     */
    @Excel(name = "姓名", height = 20, width = 30)
    private String name;

    /**
     * 创建时间
     */
    @Excel(name = "创建时间", height = 20, width = 30)
    private String createTime;
}

第四步 导出接口

    @GetMapping("/export")
    @ApiOperation(value = "导出信息", notes = "导出信息")
    public void export() {
    	// 你的数据
         List<TextExcelEntity> data = this.assembleXmlyExcelData();
         excelExportUtils.exportExcel(data, TextExcelEntity.class, "title", "sheetName", "fileName");
	}