1.引入iView
在main.js中添加
import iView from 'iview'
import 'iview/dist/styles/iview.css'
Vue.use(iView)2.excel.js
项目中新建excel.js，位置随意，方便引用最好
网页中使用示例：import excel from '@/utils/excel'
/* 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
}3.npm安装依赖
npm install --save iview
npm install --save xlsx4.网页中使用
<template>
<div>
<Card title="导入EXCEL">
<Row>
<Upload :before-upload="handleBeforeUpload" action="" accept=".xls, .xlsx">
<Button :loading="uploadLoading" icon="ios-cloud-upload-outline" @click="handleUploadFile">上传文件</Button>
</Upload>
</Row>
<Row>
<div v-if="file !== null" class="ivu-upload-list-file">
<Icon type="ios-stats"/>
{{ file.name }}
<Icon v-show="showRemoveFile" type="ios-close" class="ivu-upload-list-remove" @click.native="handleRemove()"/>
</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"/>
<span>成功</span>
</div>
</Progress>
</transition>
</Row>
</Card>
<Row class="margin-top-10">
<Table :columns="tableTitle" :data="tableData" :loading="tableLoading"/>
</Row>
</div>
</template>
<script>
import excel from '@/utils/excel'
export default {
name: 'DataQualityInspectionStatistics',
data() {
return {
uploadLoading: false,
progressPercent: 0,
showProgress: false,
showRemoveFile: false,
file: null,
tableData: [],
tableTitle: [],
tableLoading: false
}
},
methods: {
initUpload() {
this.file = null
this.showProgress = false
this.loadingProgress = 0
this.tableData = []
this.tableTitle = []
},
handleUploadFile() {
this.initUpload()
},
handleRemove() {
this.initUpload()
this.$Message.info('上传的文件已删除！')
},
handleBeforeUpload(file) {
const fileExt = file.name.split('.').pop().toLocaleLowerCase()
if (fileExt === 'xlsx' || fileExt === 'xls') {
this.readFile(file)
this.file = file
} else {
this.$Notice.warning({
title: '文件类型错误',
desc: '文件：' + file.name + '不是EXCEL文件，请选择后缀为.xlsx或者.xls的EXCEL文件。'
})
}
return false
},
// 读取文件
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
}
}
}
}
</script>