1.后端excel导出工具类

主要依赖

           <!--工具包-->
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.3.4</version>
            </dependency>
            <!-- excel解析包 -->
            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
            <!--处理2003 excel-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.0</version>
            </dependency>
            <!--处理2007 excel-->
            <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了，虽然文件名是随机的