Vue+SpringBoot 实现Excel表下拉框与模板文件下载

最近做的项目需要实现该功能，经过查找相关博客，故在此做整理。

一、前端

1、在A.service.js文件中

import axios from 'axios'
// 业务服务上下文
const baseUrl = process.env.VUE_APP_BASE_URL
//模板文件下载
const uploadTemplateDownload = () => {
  return axios({
    method: 'post',
    url: `${baseUrl}/A/templateDownload`,
    responseType: 'blob',
    headers: { 'content-type': 'application/JSON;charset=UTF-8' }
  })
}
export { uploadTemplateDownload }

2、在A-condition.vue文件中引入，调用

<template>
	<el-button type="primary" @click="uploadTemplateDownloadBtn">导入模板下载</el-button>
</template>
import { uploadTemplateDownload } from './A.service.js'
export default {
methods: {
    //导入模板文件下载
    uploadTemplateDownloadBtn() {
      uploadTemplateDownload()
        .then(res => {
          console.log(res)
          const blob = new Blob([res])
          const fileName = 'A.xlsx'
          const elink = document.createElement('a')
          elink.download = fileName
          elink.style.display = 'none'
          elink.href = URL.createObjectURL(blob)
          document.body.appendChild(elink)
          elink.click()
          URL.revokeObjectURL(elink.href) // 释放URL对象
          document.body.removeChild(elink)
        })
        .catch(() => {
          this.$message.error('导出失败，请检查网络！')
        })
    }
}

二、后端

1、在A.java控制层文件中

@RestController
@RequestMapping("/A")
@PostMapping("/templateDownload")
    public void templateDownload(HttpServletResponse response) throws IOException{
        try{
            //用于输出字符流数据或者二进制的字节流数据
            OutputStream out = response.getOutputStream();
            //通知浏览器直接下载文件数据,并制定扩展名
            response.setHeader("content-disposition",
                    "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls", "utf-8"));

            //此数组存储Excel表格中的列名，将会在Excel中展示
            String[] titles = {"序号","身份证号","姓名","性别","城市","工资"};

            //创建一个POI的Excel工作簿
            HSSFWorkbook wb = new HSSFWorkbook();

            //在工作簿中创建一个表格
            HSSFSheet sheet1 = wb.createSheet("Sheet1");
            int rowCount = 0;
            //创建表格中的行，方法传参需要当前行数
            Row row = sheet1.createRow(rowCount);
            rowCount++;
            Cell cell = null;
            //添加列名
            for (int i = 0; i < titles.length; i++) {
                //在此行中获取一个单元格
                cell = row.createCell(i);
                //写入单元格内容
                cell.setCellValue(titles[i]);
            }
            //为第一行赋值
            row = sheet1.createRow(rowCount);
            cell = row.createCell(0);
            cell.setCellValue(1);
            cell = row.createCell(1);
            cell.setCellValue("123456789");
            cell = row.createCell(2);
            cell.setCellValue("王五");
            cell = row.createCell(3);
            cell.setCellValue("男");
            cell = row.createCell(4);
            cell.setCellValue("杭州");
            cell = row.createCell(5);
            cell.setCellValue("8000");
            //城市
            String[] textlist1 = { "广州", "深圳","上海",
                    "北京"};
            sheet1 = setHSSFValidation(sheet1, textlist1, 1, 500, 4, 4);
            //写出
            try {
                wb.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (out != null) {
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
    
/**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol   开始列
     * @param endCol  结束列
     * @return 设置好的sheet.
     */
    public static HSSFSheet setHSSFValidation(HSSFSheet sheet,
                                              String[] textlist, int firstRow, int endRow, int firstCol,
                                              int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }

这样子就可以实现Excel表下拉框功能、下载模板文件功能