springboot+vue 实现下载excel模板功能

Question

前端代码

const downloadExcel2 =  async () => {
    try {
      const token = getToken();
      const response = await axios.get('/api/downloadTemp', {
        responseType: 'blob',
        headers: {
          'Authorization': `Bearer ${token}`,
        },
      });
      const url = window.URL.createObjectURL(new Blob([response.data]));
      const link = document.createElement('a');
      link.href = url;
      link.setAttribute('download', 'export.xlsx'); // 设置下载文件名
      document.body.appendChild(link);
      link.click();
    } catch (error) {
      console.error('导出失败', error);
    }
  };

后端代码

package com.example.membersteward.controller;

import cn.idev.excel.EasyExcel;
import cn.idev.excel.ExcelWriter;
import com.example.membersteward.common.CommonResult;
import com.example.membersteward.service.FileService;
import com.example.membersteward.util.ExcelUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.springframework.http.MediaType;

import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class FileController {

    @Resource
    FileService fileService;

    @GetMapping("/api/downloadTemp")
    public void downloadTemp(HttpServletResponse response) throws IOException  {
        // 创建一个新的Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // 创建行和单元格并设置值
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Hello, Excel!");

        // 设置响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=export.xlsx");

        // 将Excel写入响应输出流
        workbook.write(response.getOutputStream());
        workbook.close();
    }
}

点击下载模板之后打开excel报错

Answer 1

让阿豪来帮你解答，本回答参考chatgpt3.5编写提供，如果还有疑问可以评论或留言

问题分析：

1. 前端代码中调用后端接口下载Excel文件，后端返回application/vnd.openxmlformats-officedocument.spreadsheetml.sheet类型的Excel文件。
2. 后端使用Apache POI生成Excel文件，后端没有设置Excel文件的编码格式，导致下载的Excel文件可能存在乱码问题。
3. Excel文件是二进制流，前端使用Blob对象创建Blob URL下载文件。 解决方案：
4. 后端设置Excel文件编码格式为UTF-8。
5. 前端通过Blob URL下载Excel文件。 后端代码修改如下：

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class FileController {
    @GetMapping("/api/downloadTemp")
    public ResponseEntity<byte[]> downloadTemp() {
        try {
            // 创建一个新的Excel工作簿
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Hello, Excel!");
            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("filename", "export.xlsx");
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            workbook.close();
            return new ResponseEntity<>(outputStream.toByteArray(), headers, HttpStatus.OK);
        } catch (IOException e) {
            e.printStackTrace();
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
}

前端代码修改如下：

const downloadExcel2 = async () => {
    try {
        const token = getToken();
        const response = await axios.get('/api/downloadTemp', {
            responseType: 'blob',
            headers: {
                'Authorization': `Bearer ${token}`,
            },
        });
        const url = window.URL.createObjectURL(new Blob([response.data]));
        const link = document.createElement('a');
        link.href = url;
        link.setAttribute('download', 'export.xlsx');
        document.body.appendChild(link);
        link.click();
    } catch (error) {
        console.error('导出失败', error);
    }
};

这样修改后，应该可以正常下载Excel文件并在Excel中显示文本"Hello, Excel!"，同时避免了可能的乱码问题。