vue3实现纯前端表格数据的导出与导入
项目场景：我期望告竣在纯前端环境下举行 excel 表格文件的下载操作，与此同时，对所下载的表格文件的样式加以调整与优化，使其出现出更符合需求的外观和格式结构，从而满意特定的展示与使用要求。
表格数据导出
https://i-blog.csdnimg.cn/direct/4191a76bc8004b529aeb410a0f4f55b9.png#pic_center
1、安装 xlsx-js-style
提示：这里形貌项目中碰到的问题：
先安装库 xlsx-js-style
npm install xlsx-js-style
2、自界说组件
自界说组件，这里附全部代码，设置单元格格式代码也在其中
<script lang="ts" setup>
import { computed, nextTick, ref, watch } from "vue"
import * as XLSX from "xlsx-js-style"
interface Props {
modelValue: boolean
downloadList: any
columnList: any
}
const props = withDefaults(defineProps<Props>(), {
modelValue: false,
downloadList: [],
columnList: []
})
const downloadList = computed(() => props.downloadList)
const emit = defineEmits<{
(e: "update:modelValue", value: boolean): boolean
}>()
const columnList = computed(() => props.columnList)
//#region 导出
const tableRef = ref<any>(null)
// 导出为 Excel
const exportToExcel = () => {
// 获取 el-table 的引用
tableRef.value = document.querySelector("#download-table")
// 将 el-table 数据转换为二维数组
const dataArray = []
const headers: any = []
tableRef.value.querySelectorAll(".el-table__header-wrapper th").forEach((th: any) => {
headers.push(th.textContent.trim())
})
dataArray.push(headers)
const rowsToExport = tableRef.value.querySelectorAll(".el-table__body-wrapper tbody tr")
console.log(rowsToExport, "rowsToExport")
rowsToExport.forEach((row: any) => {
const rowData: any = []
row.querySelectorAll("td").forEach((cell: any) => {
rowData.push(cell.textContent.trim())
})
dataArray.push(rowData)
})
// 创建一个新的工作簿
const workbook = XLSX.utils.book_new()
// 创建一个新的工作表
const worksheet = XLSX.utils.aoa_to_sheet(dataArray)
console.log(dataArray, "dataArray")
// 设置列宽
const columnWidth = 20 // 列宽为20个字符
const numColumns = dataArray.length // 获取列数
worksheet["!cols"] = Array(numColumns).fill({ wch: columnWidth })
// // 设置列高
const rowHeight = 105 // 行高为110像素
const numRows = dataArray.length // 获取行数
worksheet["!rows"] = Array(numRows).fill({ hpx: rowHeight })
worksheet["!rows"] = { hpx: 15 } // 单独设置第一行的行高
// 设置单元格样式
const cellStyle = {
font: {
name: "微软黑体", // 字体名称
sz: 13, // 字体大小
color: { rgb: "000000" }, // 字体颜色
bold: false // 字体不加粗
},
border: {
top: { style: "thin", color: { rgb: "000000" } },
bottom: { style: "thin", color: { rgb: "000000" } },
left: { style: "thin", color: { rgb: "000000" } },
right: { style: "thin", color: { rgb: "000000" } }
},
alignment: {
horizontal: "center",
vertical: "center",
wrapText: true // 设置文字自动换行
}
}
// 遍历数据数组，为每个单元格应用样式
dataArray.forEach((row, rowIndex) => {
row.forEach((cell: any, columnIndex: any) => {
const cellAddress = XLSX.utils.encode_cell({ c: columnIndex, r: rowIndex })
worksheet.s = cellStyle
})
})
// 表头颜色加粗
if (dataArray) {
dataArray.forEach((cell: any, columnIndex: any) => {
const cellAddress = XLSX.utils.encode_cell({ c: columnIndex, r: 0 })
worksheet.s = { ...cellStyle, font: { ...cellStyle.font, bold: true } } // 确保表头字体加粗
})
}
// 将工作表添加到工作簿
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1")
// 将工作簿保存为 Excel 文件
const timestamp = new Date().getTime()
const fileName = `宝贝统计_${timestamp}.xlsx`
XLSX.writeFile(workbook, fileName)
emit("update:modelValue", false)
}
//#endregion
watch(
() => props.modelValue,
(value: any) => {
if (value) {
console.log(downloadList.value)
nextTick(() => {
exportToExcel()
})
}
}
)
</script>
<template>
<el-table style="display: none" :data="downloadList" id="download-table">
<el-table-column
v-for="(item, index) in columnList"
:key="index"
:prop="item.prop"
:label="item.label"
align="center"
/>
</el-table>
</template>
<style lang="scss" scoped></style>
3、使用
使用是时候是相当于在页面上设置个不可间=见的表格举行下载 将需要下载的数据传入即可，可以举行改进，表单项也传入，这样就可以随意下载任何数据了
import DownloadExcel from "@/components/DownloadExcel/index.vue"
const columnList = [
{
prop: "index",
label: "序号"
},
{
prop: "username",
label: "用户名"
},
{
prop: "roles",
label: "角色"
},
{
prop: "phone",
label: "手机号"
},
{
prop: "email",
label: "邮箱"
},
{
prop: "status",
label: "状态"
},
{
prop: "createTime",
label: "创建时间"
}
]
const downloadList = ref<any[]>([])
const isDownload = ref<boolean>(false)
const exportToExcel = () => {
if (multipleSelection.value.length == 0) {
ElMessage.error("请选择要下载的宝贝")
return
}
downloadList.value = multipleSelection.value.map((item: any, index: number) => {
return {
index: index, // 序号
username: item.username, // 用户名
roles: item.roles, // 角色
phone: item.phone, // 手机号
email: item.email, // 邮箱
status: item.status ? "启用" : "禁用", // 状态
createTime: item.createTime // 创建时间
}
})
isDownload.value = true
}
<download-excel v-model="isDownload" :downloadList="downloadList" :columnList="columnList" />
<div>
<el-tooltip content="下载">
<el-button type="primary" :icon="Download" circle @click="exportToExcel" />
</el-tooltip>
</div>
表格数据导入
https://i-blog.csdnimg.cn/direct/8d536cee0ff040e6a64960f6e796afb3.png#pic_center
1、安装 xlsx
npm install xlsx --save
2、引入
import * as XLSX from "xlsx"
3、使用
<el-upload
class="upload-demo"
ref="upload"
action=""
:auto-upload="false"
accept=""
:on-change="analysisExcel"
multiple
:show-file-list="false"
>
<el-button type="primary" :icon="Link">导入文件列表</el-button>
</el-upload>
import * as XLSX from "xlsx"
const loading = ref<boolean>(false)const tableData = ref<any[]>([])const analysisExcel = (file: any) => {loading.value = trueconsole.log(file)// 只能上传一个Excel，重复上传会覆盖之前的file = file.rawconst reader = new FileReader()reader.readAsArrayBuffer(file)reader.onload = function () { const buffer: any = reader.result const bytes = new Uint8Array(buffer) const length = bytes.byteLength let binary = "" for (let i = 0; i < length; i++) { binary += String.fromCharCode(bytes) } const wb = XLSX.read(binary, { type: "binary" }) const outdata = XLSX.utils.sheet_to_json(wb.Sheets]) console.log(outdata) let da = [...outdata] // 这里是把表格里面的名称改成表格里面的字段 da = da.map((item: any) => { const newItem = { index: Number(item["序号"]), // 序号 username: item["用户名"], // 用户名 roles: item["脚色"], // 脚色 phone: item["手机号"], // 手机号 email: item["邮箱"], // 邮箱 status: item["状态"], // 状态 createTime: item["创建时间"] // 创建时间 } return newItem }) console.log(da) tableData.value = da loading.value = false}}
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]