import * as XLSX from 'xlsx/xlsx.mini.js';
import fs from "file-saver";
// 一、根据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: '隶书', sz: 40, color: 'FF00FF88' },
fill: { fgColor: { rgb: 'FFFFAA00' } },
}
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyle: true,
defaultCellStyle: defaultCellStyle,
showGridLines: true,
}
const wbout = XLSX.write(wb, wopts)
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
fs.saveAs(blob, filename + '.xlsx');
}
function s2ab(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,会自动分析的。
// 二、根据json对象导出excel。
// 使用数据导出excel
// 根据json数组导出数据
// 使用数据导出excel
// 参数:
// data:json数组
// headers:excel的表头
// filename:导出的文件名(不用写扩展名)
export function excelExportUseJson(data, headers, filename) {
// 使用深克隆不影响原table数据的展示
const json = deepCopy(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()
console.log("json", json);
console.log("Object.values(headers)", Object.values(headers));
// 创建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' })
fs.saveAs(blob, filename + '.xlsx')
}
function deepCopy(obj) {
if (typeof obj !== 'object' || obj === null) {
return obj;
}
let copy;
if (Array.isArray(obj)) {
copy = [];
for (let i = 0; i < obj.length; i++) {
copy[i] = deepCopy(obj[i]);
}
} else {
copy = {};
for (let key in obj) {
if (obj.hasOwnProperty(key)) {
copy[key] = deepCopy(obj[key]);
}
}
}
return copy;
}
调用示例:
const books = [
{
id:"878911",
name:"三国演义"
},
{
id:"878912",
name:"西游记"
}
]
excelExportUseJson(books,{id:"编号",name:"书名"},"student02");
// 三、导入excel
// 导入:
// 参数:
// data:文件对象(用<input type=file /> 选择到file对象
// domId:导入的excel显示的容器
export function httpRequest(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
}
// 方式1(官网推荐、如果有报错就使用方式2):
// file.arrayBuffer().then(ab => {
// /* Parse file */
// const wb = XLSX.read(ab);
// const ws = wb.Sheets[wb.SheetNames[0]];
// /* Generate HTML */
// var output = document.getElementById(domId)
// output.innerHTML = XLSX.utils.sheet_to_html(ws)
// });
// 方式2:
const reader = new FileReader()
reader.readAsArrayBuffer(file, 'utf-8')
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)
/* Generate HTML */
var output = document.getElementById(domId)
output.innerHTML = XLSX.utils.sheet_to_html(ws)
}
}
<template>
<button @click="clickFn">导出</button>
</template>
<script setup>
function exportXLSX(jsonData,fileName){
if(!jsonData || jsonData.length==0){
return;
}
if(!fileName){
fileName="export.xlsx";
}
let one=jsonData[0];
let csvText="";
for(let key in one){
csvText+=key+","
}
csvText=trim(csvText,",")+"\n";
//增加\t为了不让表格显示科学计数法或者其他格式
for(let i = 0 ; i < jsonData.length ; i++ ){
let row="";
for(let item in jsonData[i]){
row+=`${jsonData[i][item] + '\t'},`;
}
csvText+=trim(row,",")+'\n';
}
//encodeURIComponent解决中文乱码
let uri = 'data:xlsx;charset=utf-8,\ufeff' + encodeURIComponent(csvText);
//通过创建a标签实现
let link = document.createElement("a");
link.href = uri;
//对下载的文件命名
link.download = fileName;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
//js实现类似php trim函数
function trim(str, char) {
if (char) {
str=str.replace(new RegExp('^\\'+char+'+|\\'+char+'+$', 'g'), '');
}
return str.replace(/^\s+|\s+$/g, '');
};
const data=[{id:"001",name:"111",sex:"NaN", school:"xxxx"}]
const clickFn = ()=>{
exportXLSX(data)
}
</script>