vue项目中实现下载后端返回的excel数据表格

查看更多资源

1. 案例简介：

            vue项目中，客户端需要实现以excel表格形式下载数据，后端为java，接受请求 >> 读取数据库 >> 生成excel >> 返回；前端为vue：发送请求 >> 接受响应 >> 实现excel下载。 

2. 案例图示：

3. 后端java代码：

/**
 * 导出失败数据excel
 */
@Override
public void exportFailExcel(BlackListUpload blackListUpload) {
    log.info("下载失败导入失败的数据{}", blackListUpload);
    String blackListUploadId = blackListUpload.getId();
    HashMap<String, Object> queryMap = new HashMap<>();
    queryMap.put(BlackListExcel.STATUS.getCode(), BlackListUploadStatus.IMPORT_FAIL);
    queryMap.put("blackListUploadId", blackListUploadId);
    //查询导入失败数据
    ArrayList<HashMap<String, Object>> list = (ArrayList<HashMap<String, Object>>) mongoDataDao.findList(queryMap, MongoCollectionName.EXCEL_IMPORT, HashMap.class);
    ArrayList<HashMap<String, Object>> excelList = new ArrayList<>();
    //获取导入到excel中数据
    for (HashMap<String, Object> map : list) {
        HashMap<String, Object> data = (HashMap<String, Object>) map.get("data");
        excelList.add(data);
    }
    try {
        //生成excel
        ExcelUtil.writeExcelByMaps(response, excelList);
    } catch (Exception e) {
        log.error("生成excel失败:{}", blackListUpload);
        e.printStackTrace();
    }
}

/*
 * @Description: 导入导出excel工具类
 */
public class ExcelUtil {
    /**
     * excel文件后缀
     */
    public static final String EXCEL_SUFFIX = ".xlsx";

    public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls, String excelName, String sheetName) throws IOException {
        Field[] fields = cls.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null && annotation.col() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        col = annotation.col();
                    }
                    return col;
                })).collect(Collectors.toList());

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);
        AtomicInteger ai = new AtomicInteger();
        {
            Row row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger aj = new AtomicInteger();
            //写入头部
            fieldList.forEach(field -> {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = "";
                if (annotation != null) {
                    columnName = annotation.value();
                }
                Cell cell = row.createCell(aj.getAndIncrement());

                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);

                Font font = wb.createFont();
                font.setBold(true);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);
            });
        }
        CellStyle cellStyleDate = wb.createCellStyle();
        CreationHelper creationHelper = wb.getCreationHelper();
        cellStyleDate.setDataFormat(
                creationHelper.createDataFormat().getFormat("yyyy-MM-dd  hh:mm:ss")
        );
        if (CollectionUtils.isNotEmpty(dataList)) {
            dataList.forEach(t -> {
                Row row1 = sheet.createRow(ai.getAndIncrement());
                AtomicInteger aj = new AtomicInteger();
                fieldList.forEach(field -> {
                    Class<?> type = field.getType();
                    Object value = "";
                    try {
                        value = field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    Cell cell = row1.createCell(aj.getAndIncrement());
                    if (value != null) {
                        if (type == Date.class) {
                            cell.setCellStyle(cellStyleDate);
                            cell.setCellValue(value.toString());
                        } else {
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellValue(value.toString());
                    }
                });
            });
        }
        //冻结窗格
        wb.getSheet(sheetName).createFreezePane(0, 1, 0, 1);
        //浏览器下载excel
        buildExcelDocument(wb, response);
        //生成excel文件
        buildExcelFile(excelName + EXCEL_SUFFIX, wb);
    }


    /**
     * 根据map列表生成excel
     *
     * @param maps
     */
    public static void writeExcelByMaps(HttpServletResponse response, List<HashMap<String, Object>> maps) throws IOException {
        Workbook wb = new XSSFWorkbook();
        String sheetName = "导入失败";
        Sheet sheet = wb.createSheet(sheetName);
        AtomicInteger rowNum = new AtomicInteger();
        if (maps.size() > 0) {
            HashMap<String, Object> stringObjectHashMap = maps.get(0);
            HashMap<Integer, Object> columnMap = new HashMap<>();
            AtomicInteger atomicInteger = new AtomicInteger();
            for (Map.Entry<String, Object> entry : stringObjectHashMap.entrySet()) {
                int i = atomicInteger.getAndIncrement();
                columnMap.put(i, entry.getKey());
            }
            //写第一行
            Row row = sheet.createRow(rowNum.getAndIncrement());
            columnMap.entrySet().forEach(integerObjectEntry -> {
                Integer columnNum = integerObjectEntry.getKey();
                Cell cell = row.createCell(columnNum);
                String value = String.valueOf(integerObjectEntry.getValue());
                cell.setCellValue(value);
            });
            //写数据行
            maps.forEach(map -> {
                Row sheetRow = sheet.createRow(rowNum.getAndIncrement());
                columnMap.entrySet().forEach(integerObjectEntry -> {
                    Integer columnNum = integerObjectEntry.getKey();
                    String value = String.valueOf(integerObjectEntry.getValue());
                    Cell cell = sheetRow.createCell(columnNum);
                    cell.setCellValue(String.valueOf(map.get(value)));
                });

            });
        }

        //冻结窗格
        wb.getSheet(sheetName).createFreezePane(0, 1, 0, 1);
        //浏览器下载excel
        buildExcelDocument(wb, response);
        //生成excel文件
        buildExcelFile("test" + EXCEL_SUFFIX, wb);

    }

    /**
     * 浏览器下载excel
     *
     * @param wb
     * @param response
     */
    private static void buildExcelDocument(Workbook wb, HttpServletResponse response) throws IOException {
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        //response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        response.flushBuffer();
        wb.write(response.getOutputStream());
    }

    /**
     * 生成excel文件
     *
     * @param path 生成excel路径
     * @param wb
     */
    private static void buildExcelFile(String path, Workbook wb) {

        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        try {
            wb.write(new FileOutputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析excel
     *
     * @param file
     * @return
     */
    public static ArrayList<HashMap<String, Object>> readExcel(File file) {
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook(file);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        //存放数据
        ArrayList<HashMap<String, Object>> dataList = new ArrayList<>();
        //获取sheet页数量
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int z = 0; z < numberOfSheets; z++) {
            //取第一个sheet
            Sheet sheet = workbook.getSheetAt(z);
            int num = sheet.getLastRowNum() - sheet.getFirstRowNum();
            //将列名与列标对应
            Row firstRow = sheet.getRow(0);
            short lastCellNum = firstRow.getLastCellNum();
            HashMap<Integer, String> columnNameMap = new HashMap<>();
            for (int i = 0; i < lastCellNum; i++) {
                String cellValue = firstRow.getCell(i).getStringCellValue();
                columnNameMap.put(i, cellValue);
            }

            //读数据，放入list
            for (int i = 0; i < num; i++) {
                Row row = sheet.getRow(i + 1);
                HashMap<String, Object> map = new HashMap<>();
                for (int j = 0; j < lastCellNum; j++) {
                    map.put(columnNameMap.get(j), String.valueOf(row.getCell(j)));
                }
                dataList.add(map);
            }
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return dataList;
    }

}

4. 前端vue代码： 

<!-- 绑定事件 -->
    <el-form-item class="mb-48">
        <el-button type="primary"  
        @click="downLoadImportResult">下载导入结果</el-button>
        <el-button   @click="handleClose">返 回</el-button>
    </el-form-item>

methods:{
   downLoadImportResult() {
        let params = {   // 请求参数 要下载Excel的id
             'id':this.excelId
        };  

       downloadFailExcel(params).then(res => { // 调用接口  
            
           console.log(res); // 此处res为bolb类文件对象 Blob(4412) {size: 4412, type: "application/octet-stream"}   
   
           var blob = new Blob([res], {type: 'application/vnd.openxmlformats-  officedocument.spreadsheetml.sheet;charset=utf-8'}); //type这里表示xlsx类型

            　　var downloadElement = document.createElement('a');
            　　var href = window.URL.createObjectURL(blob); //创建下载的链接
            　　downloadElement.href = href;
            　　downloadElement.download = 'result.xlsx'; //下载后文件名
            　　document.body.appendChild(downloadElement);
            　　downloadElement.click(); //点击下载
            　　document.body.removeChild(downloadElement); //下载完成移除元素
            　　window.URL.revokeObjectURL(href); //释放掉blob对象 
            }).catch(err => {
                this.$message({
                    message:'下载失败！',
                    type:'error',
                    showClose:true
                })
            })
        }
}

export function downloadFailExcel(params) {
  return request({
    url: '/blackListUpload/downloadFailExcel',
    method: 'post',
    data:params,
    responseType: 'blob', // 设置响应数据类型为 blob
  })
}

更多资源 - 免费分享