nodejs服务端+vue导出excel, 单独html文件导出
// 链接数据库表导出
router.post("/download", async (ctx) => {
// 创建Excel工作簿
const sql = "SELECT *FROM iap_device_1400 LIMIT 10";
const results = await sqlConnection(sql, []);
// console.log(results)
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet("Sheet 1");
// // 添加表头
const headers = Object.keys(results[0]);
worksheet.addRow(headers);
// 添加数据行
results.forEach((row) => {
const values = Object.values(row);
worksheet.addRow(values);
});
const buffer = new ArrayBuffer(10 * 1024 * 1024); // 分配足够大的内存空间
const bufferContent = await workbook.xlsx.writeBuffer(buffer);
new result({ ctx, data: bufferContent }).download();
});
router.post("/downloadTest", async (ctx) => {
const workbook = new ExcelJS.Workbook();
// 添加一个新的工作表，命名为'hong'
const worksheet = workbook.addWorksheet("hong");
// 定义工作表的列结构
worksheet.columns = [
{ header: "ID", key: "id", width: 20 },
{ header: "姓名", key: "name", width: 40 },
{ header: "年龄", key: "age", width: 20 },
];
// 数据数组，用于填充工作表
const data = [
{ id: 1, name: "宝玉", age: 18 },
{ id: 2, name: "宝钗", age: 19 },
{ id: 3, name: "黛玉" },
];
worksheet.addRows(data);
// 将工作簿写入 ArrayBuffer 并下载
const buffer = new ArrayBuffer(10 * 1024 * 1024); // 分配足够大的内存空间
const bufferContent = await workbook.xlsx.writeBuffer(buffer);
// new result({ ctx, data: bufferContent }).download();
ctx.body=bufferContent
});
前端 vue
function downloadsexls(){
proxy.download("/api/download", {
}, `config_${new Date().getTime()}.xlsx`);
}
requust.js
import axios from 'axios'
// 是否显示重新登录
export let isRelogin = { show: false };
axios.defaults.headers['Content-Type'] = 'application/json;charset=utf-8'
// 创建axios实例
const service = axios.create({
// axios中请求配置有baseURL选项，表示请求URL公共部分
baseURL: import.meta.env.VITE_APP_BASE_API,
// 超时
timeout: 10000
})
// 通用下载方法
export function download(url, params, filename, config) {
downloadLoadingInstance = ElLoading.service({ text: "正在下载数据，请稍候", background: "rgba(0, 0, 0, 0.7)", })
return service.post(url, params, {
transformRequest: [(params) => { return tansParams(params) }],
headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
responseType: 'blob',
...config
}).then(async (data) => {
const isBlob = blobValidate(data);
if (isBlob) {
const blob = new Blob([data])
saveAs(blob, filename)
} else {
const resText = await data.text();
const rspObj = JSON.parse(resText);
const errMsg = errorCode[rspObj.code] || rspObj.msg || errorCode['default']
ElMessage.error(errMsg);
}
downloadLoadingInstance.close();
}).catch((r) => {
console.error(r)
ElMessage.error('下载文件出现错误，请联系管理员！')
downloadLoadingInstance.close();
})
}
export default service
html 文件直接导出
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<script src="https://unpkg.com/exceljs@4.4.0/dist/exceljs.min.js"></script>
</head>
<body>
<script>
const { Workbook } = ExcelJS;
async function main() {
// 创建一个新的工作簿
const workbook = new Workbook();
// 添加一个新的工作表，命名为'hong'
const worksheet = workbook.addWorksheet('hong');
// 定义工作表的列结构
worksheet.columns = [
{ header: 'ID', key: 'id', width: 20 },
{ header: '姓名', key: 'name', width: 40 },
{ header: '年龄', key: 'age', width: 20 },
];
// 数据数组，用于填充工作表
const data = [
{ id: 1, name: '宝玉', age: 18 },
{ id: 2, name: '宝钗', age: 19 },
{ id: 3, name: '黛玉' },
];
worksheet.addRows(data);
// 将工作簿写入 ArrayBuffer 并下载
const buffer = new ArrayBuffer(10 * 1024 * 1024); // 分配足够大的内存空间
const res = await workbook.xlsx.writeBuffer(buffer);
download(res.buffer);
}
function download(arrayBuffer) {
const link = document.createElement('a');
// 创建 Blob 对象并生成 URL
const blob = new Blob([arrayBuffer]);
const url = URL.createObjectURL(blob);
link.href = url;
// 设置下载的文件名
link.download = 'honglou.xlsx';
// 插入链接到页面并触发点击下载
document.body.appendChild(link);
link.click();
// 下载完成后移除链接
link.addEventListener('click', () => {
link.remove();
});
}
main();
// 在页面加载完毕后执行
// window.onload = generateExcel;
</script>
</body>
</html>
