Vue下载excel,easyExcel
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 = downBlobFile3、使用
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
将导出的字段加上注解
@Excel(name = "名称" ,orderNum = "1")private String name;
Vue下载excel,easyExcel
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 = downBlobFile3、使用
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
将导出的字段加上注解
@Excel(name = "名称" ,orderNum = "1")private String name;