欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-20 16:31:04
...

后端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;
    }
 
}

前端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
  })
}

office 所有后缀对应的 content-type
https://blog.csdn.net/xiaoranzhizhu/article/details/70473734

相关标签: vue