大家好，又见面了，我是你们的朋友全栈君。
前言： 因为做微信小程序云开发，在云开发导入数据需要json格式的，然鹅我们市场部的小姐姐给我的文档是excel文件，导致我需要去手动录入，后来在网上搜了下。 有通过复制excel文件内容粘贴后生成的：http://www.jsonla.com/excel2json/ 有通过上传excel文件后生成的json文件下载却需要付费的：http://www.yzcopen.com/doc/exceljson
搞得那么难受不如自己写一个，效果图如下：
使用地址：https://test-mars.qtshe.com/tool/excelToJson
首先 我们推荐下两个插件， 一个是复制的插件：vue-clipboard2 一个处理xlsx的插件：XLSX
先给它安排上：
代码语言：javascript复制 cnpm i xlsx --save-dev 安装后在当前页面引入：
代码语言：javascript复制import XLSX from "xlsx"; //引入这里我使用了iview的框架，就是为了好看用，没其他效果。 我们先编写页面布局样式：
代码语言：javascript复制<template>
<div>
<Card title="导入EXCEL">
<Row>
<Upload action="" :before-upload="handleBeforeUpload" accept=".xls, .xlsx">
<Button icon="ios-cloud-upload-outline" :loading="uploadLoading" @click="handleUploadFile">上传文件</Button>
</Upload>
</Row>
<Row>
<div class="ivu-upload-list-file" v-if="file !== null">
<Icon type="ios-stats"></Icon>
{
{ file.name }}
<Icon v-show="showRemoveFile" type="ios-close" class="ivu-upload-list-remove" @click.native="handleRemove()"></Icon>
</div>
</Row>
<Row>
<transition name="fade">
<Progress v-if="showProgress" :percent="progressPercent" :stroke-width="2">
<div v-if="progressPercent == 100">
<Icon type="ios-checkmark-circle"></Icon>
<span>成功</span>
</div>
</Progress>
</transition>
</Row>
</Card>
<Row class="margin-top-10">
<Table :columns="tableTitle" :height="300" :data="tableData" :loading="tableLoading"></Table>
</Row>
<Row class="margin-top-10" v-if="content">
<Input v-model="content" :rows="10" type="textarea" placeholder="Enter something..." />
<Button type="info" style="margin-top: 20px;" @click="copy(content)">复制</Button>
<Button type="success" style="margin-top: 20px; margin-left: 10px;" @click="saveJSON">点击下载</Button>
</Row>
</div>
</template>引入excel.js文件，以下代码为该文件内容 import excel from ‘@/libs/excel’
代码语言：javascript复制/* eslint-disable */
import XLSX from 'xlsx';
function auto_width(ws, data){
/*set worksheet max width per col*/
const colWidth = data.map(row => row.map(val => {
/*if null/undefined*/
if (val == null) {
return {'wch': 10};
}
/*if chinese*/
else if (val.toString().charCodeAt(0) > 255) {
return {'wch': val.toString().length * 2};
} else {
return {'wch': val.toString().length};
}
}))
/*start in the first row*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i ) {
for (let j = 0; j < colWidth[i].length; j ) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
function json_to_array(key, jsonData){
return jsonData.map(v => key.map(j => { return v[j] }));
}
// fix data,return string
function fixdata(data) {
let o = ''
let l = 0
const w = 10240
for (; l < data.byteLength / w; l) o = String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w w)))
o = String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
return o
}
// get head from excel file,return array
function get_header_row(sheet) {
const headers = []
const range = XLSX.utils.decode_range(sheet['!ref'])
let C
const R = range.s.r /* start in the first row */
for (C = range.s.c; C <= range.e.c; C) { /* walk every column in the range */
var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */
var hdr = 'UNKNOWN ' C // <-- replace with your desired default
if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
headers.push(hdr)
}
return headers
}
export const export_table_to_excel= (id, filename) => {
const table = document.getElementById(id);
const wb = XLSX.utils.table_to_book(table);
XLSX.writeFile(wb, filename);
/* the second way */
// const table = document.getElementById(id);
// const wb = XLSX.utils.book_new();
// const ws = XLSX.utils.table_to_sheet(table);
// XLSX.utils.book_append_sheet(wb, ws, filename);
// XLSX.writeFile(wb, filename);
}
export const export_json_to_excel = ({data, key, title, filename, autoWidth}) => {
const wb = XLSX.utils.book_new();
data.unshift(title);
const ws = XLSX.utils.json_to_sheet(data, {header: key, skipHeader: true});
if(autoWidth){
const arr = json_to_array(key, data);
auto_width(ws, arr);
}
XLSX.utils.book_append_sheet(wb, ws, filename);
XLSX.writeFile(wb, filename '.xlsx');
}
export const export_array_to_excel = ({key, data, title, filename, autoWidth}) => {
const wb = XLSX.utils.book_new();
const arr = json_to_array(key, data);
arr.unshift(title);
const ws = XLSX.utils.aoa_to_sheet(arr);
if(autoWidth){
auto_width(ws, arr);
}
XLSX.utils.book_append_sheet(wb, ws, filename);
XLSX.writeFile(wb, filename '.xlsx');
}
export const read = (data, type) => {
/* if type == 'base64' must fix data first */
// const fixedData = fixdata(data)
// const workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
const workbook = XLSX.read(data, { type: type });
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const header = get_header_row(worksheet);
const results = XLSX.utils.sheet_to_json(worksheet);
return {header, results};
}
export default {
export_table_to_excel,
export_array_to_excel,
export_json_to_excel,
read
}点击上传前校验下文件格式，如果格式正确则进行转换操作：
代码语言：javascript复制handleBeforeUpload(file) {
const fileExt = file.name.split('.').pop().toLocaleLowerCase()
if (fileExt === 'xlsx' || fileExt === 'xls') {
this.readFile(file)
this.readExcel(file)
this.file = file
} else {
this.$Notice.warning({
title: '文件类型错误',
desc: '文件：' file.name '不是EXCEL文件，请选择后缀为.xlsx或者.xls的EXCEL文件。'
})
}
return false
},接着咱们开始写点击上传后读取的操作：
代码语言：javascript复制// 读取文件
readFile(file) {
const reader = new FileReader()
reader.readAsArrayBuffer(file)
reader.onloadstart = e => {
this.uploadLoading = true
this.tableLoading = true
this.showProgress = true
}
reader.onprogress = e => {
this.progressPercent = Math.round(e.loaded / e.total * 100)
}
reader.onerror = e => {
this.$Message.error('文件读取出错')
}
reader.onload = e => {
this.$Message.info('文件读取成功')
const data = e.target.result
const {
header,
results
} = excel.read(data, 'array')
const tableTitle = header.map(item => {
return {
title: item,
key: item
}
})
this.tableData = results
this.tableTitle = tableTitle
this.uploadLoading = false
this.tableLoading = false
this.showRemoveFile = true
}
},
readExcel(file) {
// 表格导入
const fileReader = new FileReader()
fileReader.onload = ev => {
try {
const data = ev.target.result
const workbook = XLSX.read(data, {
type: "binary"
})
const wsname = workbook.SheetNames[0] // 取第一张表
const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) // 生成json表格内容
this.jsonData = ws
this.content = JSON.stringify(ws)
} catch (e) {
return false
}
}
fileReader.readAsBinaryString(file)
},最后加上复制的操作，以及点击下载生成后的json文件。
代码语言：javascript复制copy(content) {
this.$copyText(content).then((e) => {
this.$Message.success('复制成功')
}, function(e) {
this.$Message.error('复制失败，请手动复制')
})
},
saveJSON() {
if (!this.jsonData) {
alert('data is null')
return
}
let filename = 'data.json'
if (typeof this.jsonData === 'object') {
this.jsonData = JSON.stringify(this.jsonData, undefined, 4)
}
var blob = new Blob([this.jsonData], {
type: 'text/json'
})
var e = document.createEvent('MouseEvents')
var a = document.createElement('a')
a.download = filename
a.href = window.URL.createObjectURL(blob)
a.dataset.downloadurl = ['text/json', a.download, a.href].join(':')
e.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null)
a.dispatchEvent(e)
}以上就是一个完整的在vue里实现excel转json文件的方案，如有不懂的可以下面留言，可提供完整代码。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/143702.html原文链接：https://javaforall.cn
