最近做的项目需要实现该功能,经过查找相关博客,故在此做整理。
一、前端
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表下拉框功能、下载模板文件功能