preface

In our daily development will certainly encounter such a batch import function.

Back end :” Xiao Ling, you batch export do so excellent, batch import also do bai?” .

Me :” Why? Is this usually done on the back end?”

Back end :” Think about it, isn’t it 300-400K for the user to upload a file? If your front-end directly converted JSON data to me, wouldn’t it reduce a lot of data transmission? (mainly back-end want to save data directly, do not write logic)

Committed to solving problems and improving myself, I decided to take this task.

The flow chart

Component design

Install dependencies

npm install -S xlsx
Copy the code

Uploaded components

ExcelImport.vue

<template> <div class="excelImport"> <input type="file" ref="upload" v-show="false" :accept="accept" /> </div> </template> <script> import XLSX from "xlsx"; Export default {name: "excelImport", props: {ACCEPT: {// File format that is allowed to be uploaded Type: String, default: ".xls,.xlsx",}, dataRow: Type: Number, default: 0, validator: (val) => {return val >= 0; Type: Array,},}, data() {return {}; }, methods: {cleanUpload() {this.$refs.upload.value = null; }, // File upload event handleFileChange(event) {// This can also be wrapped into multiple file upload form is currently a single file this.Handlereadexcel (event.target.files[0]); Const fileReader = new fileReader (); const fileReader = new fileReader (); fileReader.onload = (ev) => { try { const fileData = ev.target.result; const workbook = XLSX.read(fileData, { type: "binary", }); const wsname = workbook.SheetNames[0]; Const snArr = xlsx.utils.sheet_to_json (workbook.sheets [wsname]); // Select first table const snArr = xlsx.utils.sheet_to_json (workbook.sheets [wsname]); // Generate the content of the JSON table this.excelDataToJson(snArr); } catch (e) { console.log(e); return false; }}; fileReader.readAsBinaryString(file); }, // Table data transform excelDataToJson(snArr) {let retn = []; If (this.datarow) {snArr = snarr. slice(this.datarow, snarr.length); } // Return the original data if (! Enclosing valueMatch | | this. ValueMatch. Length = = = 0) {/ / output the original data in the component itself for processing this. $emit (" changeExcel, "snArr); SnKeys = object.keys (snArr[0]); snArr.forEach((snItem) => { let pushItem = {}; ForEach ((item, index) => {pushItem[item] = snItem[snKeys[index]]; }); retn.push(pushItem); }); $emit("changeExcel", retn); {}, upload / / open the open () enclosing $nextTick (() = > {this. $refs. Upload. Click (); }); }}, mounted () {/ / binding change events to monitor this. $nextTick (() = > {this. $refs. Upload. RemoveEventListener (" change ", this.handleFileChange); this.$refs.upload.addEventListener("change", this.handleFileChange); }); }, beforeDestroy () {/ / remove this change event listeners. $refs. Upload the removeEventListener (" change ", enclosing handleFileChange); }}; </script>Copy the code

Use the sample

ExcelImportPage.vue

<template> <div class="excelImport"> < button@click ="toImport"> @changeExcel="uploadExamFile" accept=".xls,.xlsx" :dataRow="1" :valueMatch="valueMatch" ></excel-import> <! <table border v-if="dataList && dataList. Length > 0"> <! - meter - > < tr > < th v - for = "hItem valueTitle in:" key = "hItem" > {{hItem}} < / th > < / tr > <! --> <tr v-for="(item, index) in dataList" :key="index"> <td v-for="jItem in valueMatch" :key="jItem">{{ item[jItem] }}</td> </tr> </table> </div> </template> <script> import ExcelImport from "./ExcelImport"; export default { name: "excelImportPage", data() { return { valueMatch: ["name", "sex", "age", "phone", "department", "position"], // The data column must correspond to the table column order in Excel [" name ", "gender", "age", "contact", "department", "position"], dataList: [],}; }, methods: {uploadExamFile(data) {console.log(" data ", data); this.dataList = data; }, toImport() { this.$refs.uploadExamFile.open(); }, }, components: { ExcelImport, }, }; </script>Copy the code

Use effect

Use attention

1. The header must be

Imported table header must! Imported table header must! Imported table header must!

The table must have the first row, otherwise the data will be missing one row

2. The definition of a dataRow

The input parameter is 0(default)

1 in the following cases

Component document

The input parameters

parameter role The default value
accept Controls the type of files to be uploaded .xls,.xlsx
valueMatch Table columns match parameter names such as [‘name’, ‘age’] null
dataRow Data opening row 0

Binding approach

The method name role Output parameters
changeExcel Trigger when Excel parsing is complete Parse the finished data

Active trigger event

The method name role The input parameters
open Open the uploaded file There is no
cleanUpload Clear uploaded files There is no

Combined with the business

Users upload commodity information with pictures in batches

We can combine the export functionality we implemented earlier.

Export file reference article: front-end export Excel, let the back end sit up and take notice

1. Upload static resources to the OSS.

2. The OSS saves the static image address list to the backend.

3. Export the static resource list as Excel.

4. Add other fields, such as the product name and price.

5. Import product information into Excel and parse it into JSON format.

6. Upload data to the backend through the interface.

The project address

Project address :github.com/FireSmallPa…

Excel import component address: Import Excel component data