Vue下载excel,easyExcel

小青蛙呱呱跳

已于 2022-08-19 09:13:23 修改

阅读量715

点赞数

分类专栏：前端文章标签： vue.js javascript

于 2022-08-19 09:09:12 首次发布

本文链接：https://blog.youkuaiyun.com/xiaohanshasha/article/details/126417480

版权

前端专栏收录该内容

5 篇文章

订阅专栏

1、在js里面增加导出方法

例如，在@/api/demo/index.js

/**
 *
 * @param url 目标下载接口
 * @param query 查询参数
 * @param fileName 文件名称
 * @returns {*}
 */
export function downBlobFile(url, query, fileName) {
  return request({
    url: url,
    method: "get",
    responseType: "blob",
    params: query
  }).then(response => {
    // 处理返回的文件流
    const blob = response.data;
    if (blob && blob.size === 0) {
      this.$notify.error("内容为空，无法下载");
      return;
    }
    const link = document.createElement("a");
//方法一
    //link.href = window.URL.createObjectURL(blob);
//方法二
    const binaryData = []
    binaryData.push(blob)
    link.href = window.URL.createObjectURL(new Blob(binaryData, { type: 'application/octet-stream;chartset=UTF-8' }))
    link.download = fileName;
    document.body.appendChild(link);
    link.click();
    window.setTimeout(function() {
      window.URL.revokeObjectURL(blob);
      document.body.removeChild(link);
    }, 0);
  });
}

2、main.js

import { downBlobFile } from './api/demo/index'
Vue.prototype.downBlobFile = downBlobFile

3、使用

this.downBlobFile('/url', this.searchForm, 'xxx.xlsx')

参数1：后台接口

参数2：查询条件

参数3：导出后文件名称

4、扩展java后端

1）、后台接口可以直接返回list数组

2）、后台可以使用easypoi

<!-- easypoi导入导出excel -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>

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

新建 ExcelUtils

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.itl.iap.common.base.dto.MesFilesVO;
import com.itl.iap.common.base.exception.CommonException;
import com.itl.iap.common.base.exception.CommonExceptionDefinition;
import com.itl.iap.common.base.model.FastDFSFile;
import com.itl.iap.common.base.response.ResponseData;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.UUID;
import java.util.function.Consumer;
import java.util.function.Function;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtils {
    public ExcelUtils() {
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws CommonException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws CommonException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws CommonException {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws CommonException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws CommonException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException var4) {
            throw new CommonException(var4.getMessage(), CommonExceptionDefinition.EXCEI_EXCEPTION);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws CommonException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws CommonException {
        if (StrUtil.isBlank(filePath)) {
            return null;
        } else {
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List list = null;

            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
                return list;
            } catch (NoSuchElementException var7) {
                throw new CommonException("模板不能为空", CommonExceptionDefinition.EXCEI_EXCEPTION);
            } catch (Exception var8) {
                throw new CommonException(var8.getCause().getMessage(), CommonExceptionDefinition.EXCEI_EXCEPTION);
            }
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws CommonException {
        if (file == null) {
            return null;
        } else {
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List list = null;

            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
                return list;
            } catch (NoSuchElementException var7) {
                throw new CommonException("excel文件不能为空", CommonExceptionDefinition.EXCEI_EXCEPTION);
            } catch (Exception var8) {
                throw new CommonException(var8.getCause().getMessage(), CommonExceptionDefinition.EXCEI_EXCEPTION);
            }
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer sheet, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws CommonException {
        if (file == null) {
            return null;
        } else {
            if (sheet == null) {
                sheet = 0;
            }

            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            params.setStartSheetIndex(sheet);
            List list = null;

            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
                return list;
            } catch (NoSuchElementException var8) {
                throw new CommonException("excel文件不能为空", CommonExceptionDefinition.VERIFY_EXCEPTION);
            } catch (Exception var9) {
                throw new CommonException(var9.getCause().getMessage(), CommonExceptionDefinition.VERIFY_EXCEPTION);
            }
        }
    }

    public static void exportExcelWithImg(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, Short height, HttpServletResponse response) throws CommonException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        exportParams.setHeight(height);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static <T> Map<String, Object> importExcel(InputStream inputStream, Class<T> pojoclass, ImportParams params, String failFilePath, Consumer<List<T>> consumer, Function<FastDFSFile, ResponseData<MesFilesVO>> func) {
        FileOutputStream fileOutputStream = null;
        ExcelImportResult<T> result = null;
        HashMap map = new HashMap(16);

        try {
            result = ExcelImportUtil.importExcelMore(inputStream, pojoclass, params);
            if (result.isVerfiyFail()) {
                Workbook failWorkbook = result.getFailWorkbook();
                String str = DateUtil.format(new Date(), "yyyy-MM-dd");
                String fileDirPath = failFilePath + str.split("-")[0] + "/" + str.split("-")[1] + "/" + str.split("-")[2] + "/";
                FileUtil.mkdir(fileDirPath);
                String fileName = UUID.randomUUID().toString();
                String filePath = fileDirPath + fileName + ".xls";
                fileOutputStream = new FileOutputStream(FileUtil.file(filePath));
                failWorkbook.write(fileOutputStream);
                map.put("failCount", result.getFailList().size());
                map.put("failFilePath", filePath);
                if (func != null && StrUtil.isNotBlank(filePath)) {
                    FastDFSFile fastDFSFile = new FastDFSFile(fileName, FileUtil.readBytes(filePath), ".xls");
                    ResponseData<MesFilesVO> apply = (ResponseData)func.apply(fastDFSFile);
                    if (apply.isSuccess()) {
                        MesFilesVO data = (MesFilesVO)apply.getData();
                        map.put("failFilePath", data.getFilePath());
                    }
                }
            }
        } catch (Exception var25) {
            var25.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException var24) {
                    var24.printStackTrace();
                }
            }

        }

        if (result != null && CollUtil.isNotEmpty(result.getList())) {
            consumer.accept(result.getList());
            map.put("successCount", result.getList().size());
        }

        return map;
    }
}

使用

public void export(Demo demo, HttpServletRequest request, HttpServletResponse response) {
		List<Demo > list= demoService.selectList(demo);
		ExcelUtils.exportExcel(list, "title", "sheetName", Demo .class, "xxxx.xls", true, response);
	}

Demo.java

将导出的字段加上注解