Import of Excel table:
Upload the Excel file, read out the content of the Excel file, restore it to the most basic column structure, and send it back according to the interface requirements of the back end
Through the XLSX plug-in to achieve the import of Excel
1. Install the plug-in
npm install xlsx -S
Copy the code2. Create a file named UploadExcel/index.vue and register it as a global component
<template>
<div>
<input
ref="excel-upload-input"
class="excel-upload-input"
type="file"
accept=".xlsx, .xls"
@change="handleClick"
>
<div class="drop" @drop="handleDrop" @dragover="handleDragover" @dragenter="handleDragover">Drop excel file here or // Prompt text<el-button :loading="loading" style="margin-left:16px;" size="mini" type="primary" @click="handleUpload">
Browse
</el-button>
</div>
</div>
</template>
<script>
import XLSX from 'xlsx' // Introduce the installed plug-in XLSX
export default {
props: { // The function to be passed in
beforeUpload: Function.// eslint-disable-line
onSuccess: Function// eslint-disable-line
},
data() {
return {
loading: false.excelData: {
header: null.results: null}}},methods: {
generateData({ header, results }) {
this.excelData.header = header
this.excelData.results = results
this.onSuccess && this.onSuccess(this.excelData)
},
handleDrop(e) {
e.stopPropagation()
e.preventDefault() // Prevent default behavior
if (this.loading) return
const files = e.dataTransfer.files
if(files.length ! = =1) { // The file length is not equal to 1
this.$message.error('Only support uploading one file! ') // Only one file can be uploaded
return
}
const rawFile = files[0] // Only use Files [0] Use uploaded files
if (!this.isExcel(rawFile)) { // Check if the file is excel
this.$message.error('Only supports upload .xlsx, .xls, .csv suffix files')
return false
}
this.upload(rawFile) // Verify that the uploaded file format is correct and upload through layer upon layer filtering
e.stopPropagation()
e.preventDefault()
},
handleDragover(e) {
e.stopPropagation()
e.preventDefault()
e.dataTransfer.dropEffect = 'copy'
},
handleUpload() {
this.$refs['excel-upload-input'].click() // Click the button to call the hidden INPUT to upload the file
},
handleClick(e) { // Input click event
const files = e.target.files
const rawFile = files[0] // only use files[0]
if(! rawFile)return // Return if empty
this.upload(rawFile) // No empty upload
},
upload(rawFile) { // Process the uploaded file
this.$refs['excel-upload-input'].value = null // fix can't select the same excel
if (!this.beforeUpload) {
this.readerData(rawFile)
return
}
const before = this.beforeUpload(rawFile)
if (before) {
this.readerData(rawFile) // Read the file}},readerData(rawFile) {
this.loading = true
return new Promise((resolve, reject) = > {
const reader = new FileReader()
reader.onload = e= > {
const data = e.target.result
const workbook = XLSX.read(data, { type: 'array' })
const firstSheetName = workbook.SheetNames[0]
const worksheet = workbook.Sheets[firstSheetName]
const header = this.getHeaderRow(worksheet)
const results = XLSX.utils.sheet_to_json(worksheet)
this.generateData({ header, results })
this.loading = false
resolve()
}
reader.readAsArrayBuffer(rawFile)
})
},
getHeaderRow(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 */
const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
/* find the cell in the first row */
let hdr = 'UNKNOWN ' + C // <-- replace with your desired default
if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
headers.push(hdr)
}
return headers
},
isExcel(file) { // Regular validation method for file names
return /\.(xlsx|xls|csv)$/.test(file.name)
}
}
}
</script>
Copy the code3. Import UploadExcel and register it as global
import Vue from 'vue'
import UploadExcel from '@/components/UploadExcel'
const Tools = {
install(Vue) {
Vue.component('UploadExcel', UploadExcel)
}
}
Vue.use(Tools)
Copy the codeIf you open a separate page register, put it in the main.js file
4. Use components
<template>
<upload-excel :on-success="handleSuccess" /><! </template> methods: {// ↓{header, results} the actual parameter is an object
handleSuccess({ header, results }) { // The result of a successful data upload
// console.log(header)
// console.log(results) Results of data processing in the table in the [{},{}] format}},Copy the codeThe plug-in converts date-format data in an Excel table into a string of numbers that need to be converted
// Convert the date format in excel files back to standard time
// https://blog.csdn.net/qq_15054679/article/details/107712966
export function formatExcelDate(numb, format = '/') {
const time = new Date((numb - 25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000)
time.setYear(time.getFullYear())
const year = time.getFullYear() + ' '
const month = time.getMonth() + 1 + ' '
const date = time.getDate() + ' '
if (format && format.length === 1) {
return year + format + month + format + date
}
return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0'+ date: date)} this method is used to restore the date format converted to a numberCopy the codeExport to Excel table
You also need to install dependent plug-ins
npm install xlsx -S
npm install file-saver script-loader
Copy the codeCreate a file SRC/vendor/Export2Excel. Js
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if(cellValue ! = ="" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function (range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null); }});//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1.c: outRow.length + colspan - 1}}); };//Handle ValueoutRow.push(cellValue ! = ="" ? cellValue : null);
//Handle Colspan
if (colspan)
for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899.11.30)))/(24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {
s: {
c: 10000000.r: 10000000
},
e: {
c: 0.r: 0}};for (var R = 0; R ! = data.length; ++R) {for (var C = 0; C ! = data[R].length; ++C) {if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = {
v: data[R][C]
};
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's'; ws[cell_ref] = cell; }}if (range.s.c < 10000000) ws['! ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if(! (this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
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;
}
export function export_table_to_excel(id) {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['! merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: 'xlsx'.bookSST: false.type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), "test.xlsx")}// The ↓ method was used
export function export_json_to_excel({
multiHeader = [],
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
data.unshift(header);
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
if (merges.length > 0) {
if(! ws['! merges']) ws['! merges'] = [];
merges.forEach(item= > {
ws['! merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/* Sets the maximum width of each column of the worksheet */
const colWidth = data.map(row= > row.map(val= > {
/* Check whether the value is null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/* Check whether it is Chinese */
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length }; }}))/* starts with the first behavior */
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;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false.type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
Copy the codeuse
Define trigger buttons on the desired page to bring in the created file// Export button
exportExcel() {
// Export as needed
import('@/vendor/Export2Excel').then(excel= > { // Lazy loading, only when clicked will the file be introduced
// Excel represents the imported module object
console.log(excel)
excel.export_json_to_excel({
header: ['name'.'wages'].// The header is mandatory
data: [['liu bei'.100],
['guan yu'.500]],// Specific data Mandatory Export the Excel table with the specified style by modifying the header and data attributes
filename: 'excel-list'.// File name
autoWidth: true.// Whether the width is adaptive
bookType: 'xlsx' // The generated file type})})}Copy the codeThe exported file is as follows
