excel导入和导出是后台管理系统常见的功能。
当我们把信息化系统给用户使用时,用户经常需要把以前在excel里录入的数据导入的信息化系统里,这样为用户提供了很大的方便。
在用户使用信息化系统时,也需要把网页表格里的数据导出到excel里,方便进行打印,排版等等。
一、安装依赖的模块:
1、file-saver
用于文件操作
2、xlsx用于excel文件处
npm i xlsx@0.17.0 -S npm i file-saver@2.0.5 -S
二、导出功能实现
在自己的项目中新建一个js文件模块。放入如下代码:
1、使用dom元素导出
// 1、根据dom元素导出成excel文件:
// 自动分析dom元素导出excel
// 参数:
// table:表格的dom元素对象
// filename:导出的文件名(不用写扩展名)
export function excelExport(table, filename) {
// workbook,
const wb = XLSX.utils.table_to_book(table);
console.log("wb", wb);
/* Export to file (start a download) */
const defaultCellStyle = {
font: { name: 'Verdana', sz: 13, color: 'FF00FF88' },
fill: { fgColor: { rgb: 'FFFFAA00' } },
}
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyle: true,
defaultCellStyle: defaultCellStyle,
showGridLines: false,
}
const wbout = XLSX.write(wb, wopts)
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
saveAs(blob, filename + '.xlsx')
}
function s2ab(s) {
console.log("s", s);
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) {
view[i] = s.charCodeAt(i) & 0xFF;
}
return buf;
}
调用示例:文章来源:https://www.uudwc.com/A/mNdqa/
excelExport(document.getElementById("t2"), "student");// t2是一个table标签的id。其实是包含表格标签的id,会自动分析的。
2、使用json数组数据导出
// 2、根据json数据(数组)导出成excel文件:
// 参数:
// data:json数组
// headers:excel的表头
// filename:导出的文件名(不用写扩展名)
export function excelExportUseJson(data, headers, filename) {
// 使用深克隆不影响原table数据的展示
const json = cloneDeep(data)
json.forEach(item => {
for (let key in item) {
if (headers.hasOwnProperty(key)) {
item[headers[key]] = item[key]
}
delete item[key]
}
})
// excel 对象
const wb = XLSX.utils.book_new()
// 创建sheet
const ws = XLSX.utils.json_to_sheet(json, { header: Object.values(headers) })
// excel 添加sheet名称
wb.SheetNames.push(filename)
// excel 添加sheet
wb.Sheets[filename] = ws
const defaultCellStyle = {
font: { name: 'Verdana', sz: 13, color: 'FF00FF88' },
fill: { fgColor: { rgb: 'FFFFAA00' } },
}
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyle: true,
defaultCellStyle: defaultCellStyle,
showGridLines: false,
}
const wbout = XLSX.write(wb, wopts)
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
saveAs(blob, filename + '.xlsx')
}
function cloneDeep(obj) {
if (typeof obj !== 'object' || obj === null) {
return obj;
}
let clonedObj = Array.isArray(obj) ? [] : {};
for (let key in obj) {
if (obj.hasOwnProperty(key)) {
clonedObj[key] = cloneDeep(obj[key]);
}
}
return clonedObj;
}
调用示例:
调用示例:
const books = [
{
id:"878911",
name:"三国演义"
},
{
id:"878912",
name:"西游记"
}
]
excelExportUseJson(books,{id:"编号",name:"书名"},"student02");
三、导入功能实现
// 1、导入成dom元素:
// 参数:
// data:文件对象(用<input type=file /> 选择到file对象
// domId:导入的excel显示的容器
export function httpRequestToHTML(data, domId) {
const file = data
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
(item) => item === types
)
if (!fileType) {
this.$message.error('格式错误!请重新选择')
return
}
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8');
return new Promise(function (resolve, reject) {
reader.onloadend = function (e) {
const data = e.target.result
console.log('data', data)
const wb = XLSX.read(data, {
type: 'buffer'
})
const ws = wb.Sheets[wb.SheetNames[0]]
console.log('ws', ws)
const htmlStr = XLSX.utils.sheet_to_html(ws)
resolve(htmlStr);
}
});
}
// 2、导入成json数据。
// 参数:
// data:文件对象(用<input type=file /> 选择到file对象
export function httpRequestToJSON(data) {
const file = data
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
(item) => item === types
)
if (!fileType) {
this.$message.error('格式错误!请重新选择')
return
}
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8');
return new Promise(function (resolve, reject) {
reader.onloadend = function (e) {
const data = e.target.result
console.log('data', data)
const wb = XLSX.read(data, {
type: 'buffer'
})
const ws = wb.Sheets[wb.SheetNames[0]]
let arr = XLSX.utils.sheet_to_json(ws);
resolve(arr);
}
});
}
// 封装:把excel转成html或者json。
// 参数:
// file:(excel)文件对象
// outtype:是导出的类型(取值:html,json)
// 调用示例:
// excelTo(文件对象,"html")
// excelTo(文件对象,"json")
export function excelImport(file,outtype="json") {
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
(item) => item === types
)
if (!fileType) {
this.$message.error('格式错误!请重新选择')
return
}
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8');
return new Promise(function (resolve, reject) {
reader.onloadend = function (e) {
const data = e.target.result
console.log('data', data)
const wb = XLSX.read(data, {
type: 'buffer'
})
const ws = wb.Sheets[wb.SheetNames[0]];
let result = "";
switch(outtype.toLocaleLowerCase()){
case "html":result = XLSX.utils.sheet_to_html(ws);break;
case "json":result = XLSX.utils.sheet_to_json(ws);break;
default:this.$message.error('输出类型错误,只能取值为 html或者json')
}
resolve(result);
}
});
}
调用示例:
const importFn=(e)=>{
if(e.target.files && e.target.files.length>0){
httpRequestToHTML(e.target.files[0],"TableContainer")
}
}
<input type="file" onChange={importFn} />
<div id="TableContainer">
</div>
四、如果想看完整代码的,在下面:
1、导入导出的工具库:excelUtils.js
// 此工具库是:excel的导入和导出
import * as XLSX from 'xlsx';
// import * as fs from 'file-saver';
import { saveAs } from "file-saver"
// 一、excel的导出:
// 1、根据dom元素导出成excel文件:
// 自动分析dom元素导出excel
// 参数:
// table:表格的dom元素对象
// filename:导出的文件名(不用写扩展名)
export function excelExport(table, filename) {
// workbook,
const wb = XLSX.utils.table_to_book(table);
console.log("wb", wb);
/* Export to file (start a download) */
const defaultCellStyle = {
font: { name: 'Verdana', sz: 13, color: 'FF00FF88' },
fill: { fgColor: { rgb: 'FFFFAA00' } },
}
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyle: true,
defaultCellStyle: defaultCellStyle,
showGridLines: false,
}
const wbout = XLSX.write(wb, wopts)
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
saveAs(blob, filename + '.xlsx')
}
function s2ab(s) {
console.log("s", s);
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) {
view[i] = s.charCodeAt(i) & 0xFF;
}
return buf;
}
// 2、根据json数据(数组)导出成excel文件:
// 参数:
// data:json数组
// headers:excel的表头
// filename:导出的文件名(不用写扩展名)
export function excelExportUseJson(data, headers, filename) {
// 使用深克隆不影响原table数据的展示
const json = cloneDeep(data)
json.forEach(item => {
for (let key in item) {
if (headers.hasOwnProperty(key)) {
item[headers[key]] = item[key]
}
delete item[key]
}
})
// excel 对象
const wb = XLSX.utils.book_new()
// 创建sheet
const ws = XLSX.utils.json_to_sheet(json, { header: Object.values(headers) })
// excel 添加sheet名称
wb.SheetNames.push(filename)
// excel 添加sheet
wb.Sheets[filename] = ws
const defaultCellStyle = {
font: { name: 'Verdana', sz: 13, color: 'FF00FF88' },
fill: { fgColor: { rgb: 'FFFFAA00' } },
}
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyle: true,
defaultCellStyle: defaultCellStyle,
showGridLines: false,
}
const wbout = XLSX.write(wb, wopts)
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
saveAs(blob, filename + '.xlsx')
}
function cloneDeep(obj) {
if (typeof obj !== 'object' || obj === null) {
return obj;
}
let clonedObj = Array.isArray(obj) ? [] : {};
for (let key in obj) {
if (obj.hasOwnProperty(key)) {
clonedObj[key] = cloneDeep(obj[key]);
}
}
return clonedObj;
}
// 二、从excel文件导入到项目里。
// 1、导入成dom元素:
// 参数:
// data:文件对象(用<input type=file /> 选择到file对象
// domId:导入的excel显示的容器
export function httpRequestToHTML(data, domId) {
const file = data
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
(item) => item === types
)
if (!fileType) {
this.$message.error('格式错误!请重新选择')
return
}
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8');
return new Promise(function (resolve, reject) {
reader.onloadend = function (e) {
const data = e.target.result
console.log('data', data)
const wb = XLSX.read(data, {
type: 'buffer'
})
const ws = wb.Sheets[wb.SheetNames[0]]
console.log('ws', ws)
const htmlStr = XLSX.utils.sheet_to_html(ws)
resolve(htmlStr);
}
});
}
// 2、导入成json数据。
// 参数:
// data:文件对象(用<input type=file /> 选择到file对象
export function httpRequestToJSON(data) {
const file = data
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
(item) => item === types
)
if (!fileType) {
this.$message.error('格式错误!请重新选择')
return
}
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8');
return new Promise(function (resolve, reject) {
reader.onloadend = function (e) {
const data = e.target.result
console.log('data', data)
const wb = XLSX.read(data, {
type: 'buffer'
})
const ws = wb.Sheets[wb.SheetNames[0]]
let arr = XLSX.utils.sheet_to_json(ws);
resolve(arr);
}
});
}
// 封装:把excel转成html或者json。
// 参数:
// file:(excel)文件对象
// outtype:是导出的类型(取值:html,json)
// 调用示例:
// excelTo(文件对象,"html")
// excelTo(文件对象,"json")
export function excelImport(file,outtype="json") {
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt'].some(
(item) => item === types
)
if (!fileType) {
this.$message.error('格式错误!请重新选择')
return
}
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8');
return new Promise(function (resolve, reject) {
reader.onloadend = function (e) {
const data = e.target.result
console.log('data', data)
const wb = XLSX.read(data, {
type: 'buffer'
})
const ws = wb.Sheets[wb.SheetNames[0]];
let result = "";
switch(outtype.toLocaleLowerCase()){
case "html":result = XLSX.utils.sheet_to_html(ws);break;
case "json":result = XLSX.utils.sheet_to_json(ws);break;
default:this.$message.error('输出类型错误,只能取值为 html或者json')
}
resolve(result);
}
});
}
2、组件代码:
<template>
<el-button @click="drawer = true" v-if="crud.charAt(0) === '1'">添加轮播图</el-button>
<el-button @click="toExcel01">excel导出(用dom元素)</el-button>
<el-button @click="toExcel02">excel导出(用json数组)</el-button>
<!-- <el-button @click="fromExcel">excel导入</el-button> -->
<!-- <input type="file" @change="fromExcel" /> -->
<el-upload ref="upload" class="upload-demo" :limit="1" :on-change="changeFn" :on-exceed="handleExceed" :auto-upload="false" :show-file-list="false">
<template #trigger>
<el-button type="primary">excel导入</el-button>
</template>
</el-upload>
<el-button @click="batchSave">批量提交</el-button>
<el-button @click="clearAll">清空轮播图数据</el-button>
<el-divider />
<el-table ref="table" id="table01" :data="bannerList" height="600px" style="width: 100%">
<el-table-column prop="bannerid" label="编号" width="180" />
<el-table-column label="图片" width="180">
<template #default="scope">
<img class="img" :src="scope.row.img" />
</template>
</el-table-column>
<el-table-column label="是否启用" v-if="crud.charAt(2) === '1'">
<template #default="scope">
<el-switch v-model="scope.row.flag" />
</template>
</el-table-column>
<el-table-column label="跳转连接">
<template #default="scope">
<el-link :href="scope.row.link" type="primary" target="_blank">跳转连接</el-link>
</template>
</el-table-column>
<el-table-column prop="alt" label="图片提示" />
<el-table-column label="操作" v-if="crud.charAt(2) === '1' || crud.charAt(3) === '1'">
<template #default="scope">
<el-button type="danger" v-if="crud.charAt(3) === '1'" >删除</el-button>
<el-button type="danger" v-if="crud.charAt(2) === '1'">修改</el-button>
</template>
</el-table-column>
</el-table>
<el-drawer v-model="drawer" title="添加轮播图" direction="rtl" :before-close="handleClose">
<AddBannerVue ref="addBannerRef" @ok="okHandle" @cancel="closeDrawer"></AddBannerVue>
</el-drawer>
<hr />
<div id="container"></div>
</template>
<script lang="ts" setup>
import { useRoute } from "vue-router";
import { getBannerApi, addBannerApi,clearBannerApi } from "@/api/banner";
import { onMounted, reactive, ref } from "vue";
import { ElMessageBox } from 'element-plus'
import type { UploadInstance, UploadProps, UploadRawFile,UploadFile } from 'element-plus'
import AddBannerVue from "./AddBanner.vue";
import type { IBanner } from "@/myTypes"
import { excelExport, excelExportUseJson, excelImport } from "@/utils/excelUtils";
const $route = useRoute();//this.$route
const crud: string = $route.meta.crud as string;
const bannerList = reactive<Array<IBanner>>([]);
// 清空轮播图
const clearAll=()=>{
clearBannerApi().then(res=>{
if(res.data.code==="200"){
ElMessageBox.alert("清空成功", "提示", {
confirmButtonText: "确定",
callback: () => {
getBannerList();
}
});
}
}).catch(err=>{
console.log("清空失败",err);
})
}
// 获取轮播图的数据
function getBannerList() {
getBannerApi().then(res => {
if (res.data.code === "200") {
bannerList.length = 0;
bannerList.push(...res.data.data);
}
}).catch(err => {
console.log("获取轮播图失败,err", err);
})
}
// 在初次渲染完毕后,获取轮播图数据
// onMounted(()=>getBannerList());
getBannerList();
// 添加相关:
//
const drawer = ref(false);
function okHandle() {
closeDrawer();
getBannerList();
}
// 关闭抽屉
const closeDrawer = () => {
drawer.value = false;
}
const addBannerRef = ref();
const handleClose = (done: () => void) => {
ElMessageBox.confirm('亲,您真的要关闭吗?')
.then(() => {
console.log("点了确定");
addBannerRef.value.clearData();
done();
})
.catch(() => {
// catch error
console.log("点了取消");
})
}
onMounted(() => console.log("父组件:mounted"))
const toExcel01 = () => {
excelExport(document.getElementById("table01"), "banners");
}
const toExcel02 = () => {
// const arr = bannerList.map(item => ({
// bannerid: item.bannerid,
// img: item.img,
// flag: item.flag ? "是" : "否"
// }))
// excelExportUseJson(arr, {
// bannerid: "编号",
// img: "图片",
// flag: "是否启用"
// }, "bannerList");
excelExportUseJson(bannerList, {
bannerid: "编号",
img: "图片",
flag: "是否启用",
link: "跳转连接",
alt: "图片提示"
}, "bannerList");
}
// 从excel文件中导入
const fromExcel = (e: any) => {
if (e.target.files && e.target.files.length > 0) {
excelImport(e.target.files[0],)
.then((arr: any) => {
bannerList.length = 0;
arr.forEach((item: any) => {
bannerList.push({
bannerid: item["编号"],
img: item["图片"],
flag: item["是否启用"],
link: item["跳转连接"],
alt: item["图片提示"]
});
})
})
}
}
function batchSave() {
let count = 0;
bannerList.forEach((item: any) => {
addBannerApi(item).then(() => {
console.log(`添加成功了${++count}条`);
})
})
}
const upload = ref<UploadInstance>();
const handleExceed: UploadProps['onExceed'] = (files) => {
upload.value!.clearFiles()
const file = files[0] as UploadRawFile
upload.value!.handleStart(file);
}
const changeFn=(file:UploadFile)=>{
console.log("excelImport:file",file);
excelImport(file.raw as File)
.then((arr: any) => {
bannerList.length = 0;
arr.forEach((item: any) => {
bannerList.push({
bannerid: item["编号"],
img: item["图片"],
flag: item["是否启用"],
link: item["跳转连接"],
alt: item["图片提示"]
});
})
})
}
</script>
<style lang="scss" scoped>
.img {
width: 100%;
height: 100px;
}
</style>
文章来源地址https://www.uudwc.com/A/mNdqa/