preface
Recently, WHEN I was sorting out the business requirements involved in daily development, I happened to think of the analysis and uploading of Excel, which are quite common in development. Let’s take advantage of the weekend to sort out and learn
Basic introduction
Mainly based on Vue+ Element file analysis and export, using the plug-in is XLSX, inside the specific method, interested to study, the basic style, configuration will not be described, is relatively simple, we directly on the staple food
Code implementation
The basic structure
The user clicks file upload, and the Excel form is displayed in JSON format on the page. The user performs operations, checks the data, and submits the service. The Upload component in Element is used for uploading operations
<! Upload file button -->
<div class="buttonBox">
<el-upload
action
accept=".xlsx, .xls"
:auto-upload="false"
:show-file-list="false"
:on-change="handle"
>
<el-button type="primary" slot="trigger">Select EXCEL file</el-button>
</el-upload>
<el-button type="success" @click="submit" :disabled="disable">Collection data submission</el-button>
</div>
<! -- Parsed data -->
<div class="tableBox" v-show="show">
<h3>
<i class="el-icon-info"></i>Small master, the following is the collected data, please check and click the "Collected data submit" button to upload to the server!</h3>
<el-table :data="tempData" style="width: 100%" :height="height" border>
<el-table-column prop="name" label="Name" min-width="50%"></el-table-column>
<el-table-column prop="phone" label="Telephone" min-width="50%"></el-table-column>
</el-table>
</div>
Copy the codeUpload the parsing
- The Upload component allows you to get the stream of uploaded files (figure below)
-
To convert the file to binary, here we can add corresponding methods to the utils file (as follows)
// Read the file in binary export function readFile(file) { return new Promise(resolve= > { let reader = new FileReader(); reader.readAsBinaryString(file); reader.onload = ev= > { resolve(ev.target.result); }; }); } Copy the code -
Convert binary six to JSON via XLSX so that it can be displayed
// Read data from FILE (JSON format) let data = await readFile(file); let workbook = xlsx.read(data, { type: "binary" }), worksheet = workbook.Sheets[workbook.SheetNames[0]]; data = xlsx.utils.sheet_to_json(worksheet); // Print the result with the following figure console.log(workbook); Copy the code
-
To change the read data into the last data that can be passed to the server, we need to encapsulate a mapping table to match the format passed to the back end (as follows)
// The field corresponds to the table export let character = { name: { text: "Name".type: 'string' }, phone: { text: "Telephone".type: 'string'}};Copy the code -
Converting data format
let arr = []; data.forEach(item= > { let obj = {}; for (let key in character) { if(! character.hasOwnProperty(key))break; let v = character[key], text = v.text, type = v.type; v = item[text] || ""; type === "string" ? (v = String(v)) : null; type === "number" ? (v = Number(v)) : null; obj[key] = v; } arr.push(obj); }); Copy the code -
Send to the server
We need to see that the server supports multiple files to be sent together, if it does not support our front end can be sent recursively, the specific situation can be communicated with the back end, we use the recursive way for transmission
// Submit data to the server
async submit() {
if (this.tempData.length <= 0) {
this.$message({
message: "Small Lord, please choose EXCEL file first!".type: "warning".showClose: true
});
return;
}
this.disable = true;
let loadingInstance = Loading.service({
text: "Small Lord, please wait for a moment, slave home is playing with life in processing!".background: "rgba(0,0,0,.5)"
});
// Finish the post-processing
let complate = () = > {
this.$message({
message: "Small Lord, slave home has helped you upload the data!".type: "success".showClose: true
});
this.show = false;
this.disable = false;
loadingInstance.close();
};
// The data needs to be passed to the server one by one
let n = 0;
let send = async() = > {if (n > this.tempData.length - 1) {
// It's all done
complate();
return;
}
let body = this.tempData[n];
/ / interface
let result = await createAPI(body);
if (parseInt(result.code) === 0) {
/ / success
n++;
}
send();
};
send();
}
Copy the codeThe above is the summary of Excel file parsing and uploading. In fact, it is not very difficult, because it is often involved in daily development. Next, let’s look at the Excel export
Excel export
The basic structure
Enter the page to get the file just uploaded, and then display in the table, and then do a page……. Without further discussion, let’s start by clicking the Export Excel button to take a look at the page structure
<div class="container">
<! Upload button -->
<div class="buttonBox">
<router-link to="/upload">
<el-tooltip content="EXCEL Data Acquisition" placement="top">
<el-button type="primary" icon="el-icon-edit" circle></el-button>
</el-tooltip>
</router-link>
</div>
<! -- Search area -->
<div class="searchBox">
<el-input v-model="search" placeholder="Fuzzy search based on name and phone." @change="searchHandle"></el-input>
<el-button type="success" @click="submit" :disabled="disabled">Export the selected data</el-button>
</div>
<! -- List area -->
<div class="tableBox">
<el-table
:data="tableData"
:height="height"
style="width: 100%"
v-loading="loading"
element-loading-text="Small master, slave home is trying to load..."
@selection-change="selectionChange"
>
<el-table-column type="selection" width="50" align="center"></el-table-column>
<el-table-column prop="id" label="Number" min-width="10%"></el-table-column>
<el-table-column prop="name" label="Name" min-width="20%"></el-table-column>
<el-table-column prop="phone" label="Telephone" min-width="20%"></el-table-column>
<el-table-column prop="time" label="Creation time" min-width="25%" :formatter="formatter"></el-table-column>
</el-table>
</div>
<! -- Paging area -->
<div class="pageBox">
<el-pagination
background
hide-on-single-page
layout="total, sizes, prev, pager, next"
:page-size="pageSize"
:current-page="page"
:total="total"
@size-change="sizeChange"
@current-change="prevNext"
@prev-click="prevNext"
@next-click="prevNext"
></el-pagination>
</div>
</div>
Copy the codeExport Excel
Convert the JSON data into sheet data, create a new table, insert a sheet into the table, and write the file using XLSX’s writeFile method
// Export data
submit() {
if (this.selectionList.length <= 0) {
this.$message({
message: "Small Lord, please choose the data you want to export first!".type: "warning".showClose: true
});
return;
}
this.disabled = true;
let loadingInstance = Loading.service({
text: "Small Lord, please wait for a moment, slave home is playing life processing...".background: "rgba(0,0,0,.5)"
});
let arr = this.selectionList.map(item= > {
return{id: item.id, name: item.name, phone: item.phone}; });// Change json data to sheet data
let sheet = xslx.utils.json_to_sheet(arr),
// Create a new table
book = xslx.utils.book_new();
// Insert a sheet into the table
xslx.utils.book_append_sheet(book, sheet, "sheet1");
// Write files using XLSX's writeFile method
xslx.writeFile(book, `userThe ${new Date().getTime()}.xls`);
loadingInstance.close();
this.disabled = false;
}
Copy the code** The above is related to Excele operation, file upload parsing is a common requirement, if front-end partners interested in large file upload and power continuation sensor can refer to my article, large file upload and breakpoint continuation, today is over here, finally send front-end partners a word
Husband learning must be static also, only to learn also, not learn beyond wide only, not beyond into learning. Slow sex can not stimulate the essence, risk impetuous can not cure sex. Year and time chi, meaning and day go, then withered