下载xlsx
npm install --save xlsx
封装导入方法
upload.js
import * as xlsx from 'xlsx'
/**
* xlsx版本 : "xlsx": "0.18.5"
* 在页面中调用 : this.$uploadData(this, event);
* 方法return fileName : 上传的excel文件名
* titleData : 转换后的表头数据
* tableData : 转换后的表格数据
* 注 :封装的方法默认读取excel第1个sheet页数据 默认将excel文件中第一行数据转为表头
* uploadData()方法中 xlsx.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]); wb.SheetNames[0]可修改读取第几个sheet页数据 也可循环传参读取多个sheet页数据
* getHeader()方法中 const R = range.s.r 默认是从第一行开始渲染 如若想跳过excel文件第一行数据 可设置 const R = 1 来进行读取数据
*
*/
export default function uploadData(_this, event) {
return new Promise((resolve, reject) => {
let _data = {};
if (!event.currentTarget.files.length) {
reject('No files selected');
}
const that = _this;
var f = event.currentTarget.files[0];
var reader = new FileReader();
reader.onload = function (e) {
var binary = '';
var wb;
var outdata;
var bytes = new Uint8Array(reader.result);
var length = bytes.byteLength;
for (var i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i]);
}
wb = xlsx.read(binary, { type: 'binary' });
outdata = xlsx.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
const excellist = [];
for (var i = 0; i < outdata.length; i++) {
excellist.push(outdata[i]);
}
that.$emit('getResult', outdata);
const a = wb.Sheets[wb.SheetNames[0]];
const headers = getHeader(a);
_data = setTable(headers, excellist);
if (!_data) {
reject('导入的excel数据为空,请检查导入的excel文件');
} else {
_data.fileName = f.name;
resolve(_data);
}
};
reader.readAsArrayBuffer(f);
});
}
const setTable = (headers, excellist) => {
const titleData = [] // 存储表格表头数据
const tableMapTitle = {} // 设置表格内容中英文对照用
headers.forEach((_, i) => {
tableMapTitle[_] = trimAll(_);
titleData.push({
prop: trimAll(_),
label: _,
})
})
// 映射表格内容属性名为英文
const tableData = []
excellist.forEach(_ => {
const newObj = {}
Object.keys(_).forEach(key => {
newObj[tableMapTitle[key]] = _[key]
})
tableData.push(newObj)
})
if (tableData.length > 0) {
return { titleData, tableData }
} else {
return null; // 返回空数据
}
}
// 获取表格表头
const getHeader = (sheet) => {
const XLSX = xlsx
const headers = []; //存放表格表头数组
const range = XLSX.utils.decode_range(sheet["!ref"]) // worksheet['!ref'] 是工作表的有效范围
/**
* range读到的的是sheet表格有多少行多少列的一个数组,里面有 s(表示start),c(表示列),e(表示结束值),
* 比如 range.s.c 为获取当前表格列从那个下标开始(一般从0开始),sheetRange.e.c 表示该表格最后一列的下标(即有多少列)
*/
let C
/* 获取单元格值 start in the first row */
const R = range.s.r
let i = 0
for (C = range.s.c; C <= range.e.c; ++C) {
var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* 根据地址得到单元格的值 */
var hdr = "UNKNOWN" + C // 如果有空表头,会替换为您想要的默认值
// XLSX.utils.format_cell 生成单元格文本值
if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
if (hdr.indexOf('UNKNOWN') > -1) {
if (!i) {
// hdr = '__EMPTY' 如果表头为空 默认值
} else {
hdr = '__EMPTY_' + i
}
i++
}
headers.push(hdr)
}
return headers
}
/** 处理字符 */
const trimAll = (ele) => {
if (typeof ele === 'string') {
let newStr = ele.split(/[\t\r\f\n\s]*/g).join('');
return newStr.toLowerCase();
} else {
console.error(`${typeof ele} is not the expected type, but the string type is expected`)
}
}
全局挂载
main.js
import uploadData from '@/utils/upload.js'
Vue.prototype.$uploadData = uploadData;
组件中使用
// template
<p class="upload_btn">
<input ref="inputFile" class="input-file" type="file" @change="readExcel"
accept=".csv, application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet,
application/vnd.ms-excel" />
<el-button size="small" type="primary" @click="handleUpload">
导入EXCEL
<i class="el-icon-upload el-icon--right"></i>
</el-button>
</p>
<p class="fileName_p" v-show="fileName">
<i class="el-icon-paperclip"></i>
<span style="margin-left: 5px;">{{ fileName }}</span>
</p>
//script
/** 读取excel */
async readExcel(event) {
try {
this.loading = true;
const data = await this.$uploadData(this, event);
console.log('data', data);
} catch (error) {
console.log('error', error);
} finally {
setTimeout(() => {
this.loading = false;
const input = document.querySelector('.input-file');
if (input) {
input.value = '';
}
}, 100);
}
},
//style
.fileName_p {
margin: 0;
color: #7f7f7f;
}
.input-file {
display: none;
}