java后端导出excel文件流，前端vue接收下载

港控/mmm° 2021-09-03 09:09 1046阅读 0赞

## 1.后端excel导出工具类 ##

**主要依赖**

<dependency>
                    <groupId>cn.hutool</groupId>
                    <artifactId>hutool-all</artifactId>
                    <version>5.3.4</version>
                </dependency>
                
                
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                    <version>4.0.0</version>
                </dependency>
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                    <version>4.0.0</version>
                </dependency>

import cn.hutool.core.collection.CollectionUtil;
    import cn.hutool.core.io.IORuntimeException;
    import cn.hutool.poi.excel.ExcelUtil;
    import cn.hutool.poi.excel.ExcelWriter;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    
    /** * @author: dxf * @description: excel导出工具类 * @date: 2020-12-12 21:53 * @modified by: */
    public class ExcelHelper { 
    
        public static ExcelWriter getWriter(LinkedList<String> headers, List<Map<String, String>> dataList){ 
            if (CollectionUtil.isEmpty(headers)){ 
                return null;
            }
    
            //通过工具类创建writer
            ExcelWriter writer = ExcelUtil.getWriter();
    
            //标题
            writer.writeRow(headers);
    
            int row = 0;
            for (Map<String, String> rowMap : dataList) { 
                row++;
                int col = 0;
                for (String header : headers) { 
                    writer.writeCellValue(col++, row, rowMap.get(header));
                }
            }
    
            //全列自适应
            writer.autoSizeColumnAll();
            return writer;
        }
    
    
        /** * 如果需要合并的话，就合并 */
        public static void mergeIfNeed(ExcelWriter writer, int firstRow, int lastRow, int firstColumn, int lastColumn, Object content) { 
            if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) { 
                writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
                int rows = lastRow -firstRow + 1;
                if(rows >0){ 
                    int height = Math.floorDiv(264,rows);
                    for(int i = 0; i < rows; i++){ 
                        writer.setRowHeight(firstRow + i,height);
                    }
                }
            } else { 
                writer.writeCellValue(firstColumn, firstRow, content);
                writer.setRowHeight(firstRow,264);
            }
    
        }
    
        public static void writeExcel(HttpServletResponse response, ExcelWriter writer, String fileName) { 
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    
            ServletOutputStream servletOutputStream = null;
            try { 
                servletOutputStream = response.getOutputStream();
    // Workbook workbook = writer.getWorkbook();
    // workbook.write(servletOutputStream);
                writer.flush(servletOutputStream);
                servletOutputStream.flush();
            } catch (IORuntimeException | IOException e) { 
                e.printStackTrace();
            } finally { 
                writer.close();
                try { 
                    if (servletOutputStream != null) { 
                        servletOutputStream.close();
                    }
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

导出数据装载

@GetMapping("/excel")
        @ApiOperation("excel导出")
        public void  excel( HttpServletResponse response) { 
            List<Oil> oils = oilService.list();
            List<AddressOil> addressOils = addressOilService.list();
            List<Address> addresses = addressService.list();
    
            LinkedList<String> headers = new LinkedList<>();
            //表头
            headers.add("地区");
            。。。。。。。。
            headers.add("成绩");
    
    
            List<Map<String, String>> dataList = new LinkedList<>();
            for (Address address : addresses) { 
                LinkedHashMap<String, String> rowMap = new LinkedHashMap<>(headers.size());
                //添加到对应表头的数据
                rowMap.put("地区", address.getAddress());
                rowMap.put("成绩", "数据");
    
                dataList.add(rowMap);
            }
    
            ExcelWriter writer = ExcelHelper.getWriter(headers, dataList);
            if (writer == null){ 
                throw new BizException("导出excel错误");
            }
            ExcelHelper.writeExcel(response, writer, "excelExport.xls");
        }

## 2.前端vue接收下载 ##

**封装api**

export function exportPost() { 
      return request({ 
        url: '/admin/ADSoil/excel',
        method: 'get',
        responseType: 'blob'
      })
    }

/** 导出按钮操作 */
        handleExport() { 
          this.$confirm('是否确认导出所有数据项?', "警告", { 
              confirmButtonText: "确定",
              cancelButtonText: "取消",
              type: "warning"
            }).then(function() { 
            return exportPost().then((res) => { 
            let blob = new Blob([res], { type: 'application/vnd.ms-excel'})
            let objectUrl = URL.createObjectURL(blob) 
            window.location.href = objectUrl 
        })
            }).catch(function() { });
        },

**这样就可以下载excel了，虽然文件名是随机的**