【SpringBoot+Vue】简单实现导入到Excel

后端

一般在项目中使用需要拦截配置器放行该导出方法，然后在控制层调用【@CrossOrigin】注解

拦截配置器

package com.bw.note.config.interceptor;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

/**
 * 拦截配置器
 * <功能详细描述>
 */
@Configuration
public class WebConfigurer implements WebMvcConfigurer {

    @Autowired
    private LoginInterceptor loginInterceptor;

    // 这个方法是用来配置静态资源的，比如html，js，css，等等
    /*@Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
    }*/

    // 这个方法用来注册拦截器，我们自己写好的拦截器需要通过这里添加注册才能生效
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(loginInterceptor).excludePathPatterns(
                "/api/v1/note/merchant/exportExcel").addPathPatterns("/**");
    }
}

控制层

@CrossOrigin
@RestController
@RequestMapping("/api/v1/note/merchant")
public class MerchantController {

    @GetMapping("/exportExcel")
    public RestResult exportExcel(@RequestParam(value = "classifyName", required = false) String classifyName,
                              HttpServletResponse response) {
    return classifyService.exportExcel( classifyName, response );
}
}

实现层

private static ThreadLocal<DateFormat> threadLocal = new ThreadLocal<DateFormat>(){
    @Override
    protected DateFormat initialValue(){
        return new SimpleDateFormat("yyyy-MM-dd");
    }
};

@Override
public RestResult exportExcel(String classifyName, HttpServletResponse response) {
    
    //从数据库获取数据
    List<SpClassify> spClassifyList = classifyMapper.listSpClassify( classifyName );

    List<List<List<String>>> excelDatas = new ArrayList<>();
    List<String> sheetNames = new ArrayList<String>() {{
        add("分类信息");
    }};

    List<List<String>> excelData1 = new ArrayList<>();
    List<String> head1 = new ArrayList<>();
    head1.add("分类编码");
    head1.add("分类名称");
    head1.add("分类备注");
    head1.add("创建时间");
    excelData1.add(head1);


    spClassifyList.forEach( spClassify -> {
        List<String> strings = new ArrayList<>();
        strings.add(spClassify.getCode());
        strings.add(spClassify.getName());
        strings.add(spClassify.getNote());
        strings.add(threadLocal.get().format(spClassify.getCreateTime()));
        excelData1.add(strings);
    });

    excelDatas.add(excelData1);

    String date = threadLocal.get().format(new Date());

    try {
        ExcelUtilN.exportExcel(response, excelDatas, sheetNames, "info" + date + ".xlsx", 15);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}

工具类

/**
 * Excel表格导出
 *
 * @param response    HttpServletResponse对象
 * @param excelDatas  Excel表格的数据，封装为List<List<String>>
 * @param sheetNames  sheet的名字
 * @param fileName    导出Excel的文件名
 * @param columnWidth Excel表格的宽度，建议为15
 * @throws IOException 抛IO异常
 */
public static void exportExcel(HttpServletResponse response,
                               List<List<List<String>>> excelDatas,
                               List<String> sheetNames,
                               String fileName,
                               int columnWidth) throws IOException {

    //声明一个工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();

    XSSFCellStyle cellStyle = workbook.createCellStyle();

    //生成一个表格，设置表格名称
    for (int i = 0; i < sheetNames.size(); i++) {
        String sheetName = sheetNames.get(i);
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);
        //写入List<List<String>>中的数据
        int rowIndex = 0;
        List<List<String>> excelData = excelDatas.get(i);

        for (List<String> data : excelData) {

            //创建一个row行，然后自增1
            XSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int j = 0; j < data.size(); j++) {
                //创建一个单元格
                XSSFCell cell = row.createCell(j);

                    try {
                        Double value = Double.valueOf(data.get(j));
                        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("0"));
                        cell.setCellValue(value);
                    } catch (Exception e) {
                        //创建一个内容对象
                        XSSFRichTextString text = new XSSFRichTextString(data.get(j));
                        //将内容对象的文字内容写入到单元格中
                        cell.setCellValue(text);
                    }
            }
        }
    }


    //准备将Excel的输出流通过response输出到页面下载
    //八进制输出流
    response.setContentType("application/octet-stream");

    //设置导出Excel的名称
    response.setHeader("Content-disposition", "attachment;filename=" + fileName);

    //刷新缓冲
    response.flushBuffer();

    //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
    workbook.write(response.getOutputStream());

    //关闭workbook
    workbook.close();
}

前端

// 导出
exportBtn () {
  axios({
    method: 'GET',
    url: '/api/v1/note/merchant/exportExcel',
    params: {
      classifyName: this.search.name.trim(),
      pageSize: this.pageSize,
      pageNo: this.pageNo
    },
    responseType: 'blob'
  }).then(res => {
    let obj = res.headers
    let fileName
    for (const key in obj) {
      if (Object.hasOwnProperty.call(obj, key)) {
        if (key == 'content-disposition') {
          // fileName = obj[key].split("=")
          fileName = obj[key].slice(20)
        }
      }
    }
    let date = new Date().getTime()
    let url = window.URL.createObjectURL(new Blob([res.data]))
    let link = document.createElement('a')
    link.style.display = 'none'
    link.href = url
    link.setAttribute('download', fileName)
    document.body.appendChild(link)
    link.click()
    document.body.removeChild(link)
    this.$message({
      message: '导出提现记录成功',
      type: 'success'
    })
  }).catch(err => {
    console.log(err)
  })
}