vue中或者react中的excel导入和导出

excel导入和导出是后台管理系统常见的功能。

       当我们把信息化系统给用户使用时，用户经常需要把以前在excel里录入的数据导入的信息化系统里，这样为用户提供了很大的方便。

        在用户使用信息化系统时，也需要把网页表格里的数据导出到excel里，方便进行打印，排版等等。

一、安装依赖的模块：

1、file-saver 用于文件操作

2、xlsx用于excel文件处

npm i xlsx@0.17.0 -Snpm 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;
}

调用示例：

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()// 创建sheetconst ws = XLSX.utils.json_to_sheet(json, { header: Object.values(headers) })// excel 添加sheet名称wb.SheetNames.push(filename)// excel 添加sheetwb.Sheets[filename] = wsconst 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 = dataconst 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.resultconsole.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 = dataconst 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.resultconsole.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.resultconsole.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()// 创建sheetconst ws = XLSX.utils.json_to_sheet(json, { header: Object.values(headers) })// excel 添加sheet名称wb.SheetNames.push(filename)// excel 添加sheetwb.Sheets[filename] = wsconst 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 = dataconst 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.resultconsole.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 = dataconst 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.resultconsole.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.resultconsole.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.$routeconst 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 errorconsole.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 UploadRawFileupload.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>