preface

In my previous work, I met the need to export Excel, which is sorted out here. Generally, when exporting Excel, the front end can be divided into two types: one is the return of binary data stream by the back end, and the other is the direct generation of pure front end (such as exporting a template). To export, you can submit a form, download a TAB, and open a window. The most commonly used tag is probably the A tag.

The body of the

Here mainly talks about two kinds of file download method, and the back end returns binary data stream, produces the garbled code, and the data amount is too large to lead to the network failure solution.

New: A pure front end is added to read and parse data

Important: There are two plug-ins that need to be used here,

import FileSaver from 'file-saver'
import XLSX from 'xlsx'
Copy the code

First we need to get the file to parse. We can use the Upload component in Element-UI (for example, there is no limit as long as you get the uploaded file) and then retrieve its file from Httprequest’s upload method.

Then, we take the file as a parameter to the parsing method, and we’re done

async uploadFile(file) { const _file = file const fileReader = new FileReader() fileReader.onload = (ev) => { try { const data = ev.target.result const workbook = XLSX.read(data, { type: 'binary'}) for (const sheet in workbook.sheets) {// Loop to read each file const sheetArray = Xlsx.utils.sheet_to_json (workbook.sheets [sheet]) Sheetarray. length === 0) {continue} const arr = [] Sheetarray. forEach(e => {const rowTable = {} for (const item in e) {if (item === 'item ') {sheetArray.forEach(e => {const rowTable = {} for (const item in e) {if (item ===' item ') { RowTable ['productNo'] = E [item]} else if (item === 'barCode') {rowTable['barCode'] = e[item]} else {rowTable['safeQty'] = E [item]}} arr.push(rowTable)})} Catch (e) {this.$message. Warning (' File parsing error! ') } } fileReader.readAsBinaryString(_file) },Copy the code

In a method, you can get parsed data in the form of key-value pairs, and then you can manipulate it yourself. The arR in a method is the array that defines the operation, and you can manipulate it yourself. However, it should be noted that when the amount of imported data is too large, such as 3-5W, it may take a long time, which needs to be controlled by ourselves

1. Pure front-end EXcel generation, here update, add export XLSX format method

This method is not used much, and generally file downloads rely on the back end to return a binary data stream. This method is usually used to export templates in JSON format

` ` ` let STR = 'const jsonData = [{' warehouse code' : ', 'the article number of' : ', 'size' : ', 'barcode' : ', 'cut quantity: "', 'discount' : '' }] console.log(jsonData) for (var k in jsonData[0]) { str += k + ',' } str = str.slice(0, Str.length-1) + '\n' console.log(STR) // add \t to the table to not display scientific notation or other formats for (let I = 0; i < jsonData.length; i++) { for (const item in jsonData[i]) { str += `${jsonData[i][item] + '\t'},` } str += '\n' } // Const uri = 'data:application/vnd.ms-excel; Const link = document.createElement('a') link.href = uri // To download a file named link. Download = 'order templates. XLS' document. The body. The appendChild (link) link. Click (). The document body. RemoveChild (link) ` ` `Copy the code

Export the XLSX format

Step 1: Install file-Saver and XLSX

 npm i file-saver xlsx -s
Copy the code
Step 2: Introduce in the component you want to useCopy the code
  import FileSaver from 'file-saver'
 import XLSX from 'xlsx'
Copy the code
<el-table id="table" :data="tableData" style="width: 100%"> <el-table-column prop="date" label=" date" width="180"> </el-table-column> <el-table-column prop="name" label=" name" Width ="180"> </el-table-column> <el-table-column prop="address" label=" address" > </el-table-column> </el-table>Copy the code

Step 3: Bind the call method of the export button

getXlsx() { let wb = XLSX.utils.table_to_book(document.querySelector('#table')); Write (wb, {bookType: 'XLSX ', bookSST: true, type: 'array'}); SaveAs (new Blob([wbout], {type: 'application/octet-stream'}), 'XLSX '); } catch (e) { if (typeof console ! == 'undefined') console.log(e, wbout) } return wbout },Copy the code

2. The back end returns the binary data stream to generate Excel

For exporting data, returning binary stream files is the most common, and there are generally three ways to download Excel files by opening links at the front end. The first way is form form and synchronous download, direct download. The advantage of this approach is that there is no need to transform the returned data and the browser automatically parses it. However, the disadvantage is that the returned result cannot be operated. For example, in general work, the request needs to be authenticated. At this time, the request header cannot be loaded with token in form download, and the backend can only obtain token from cookie. , the second is a label downloads will return to process the results into a new link, by creating a TAB, the advantages of this approach is the internal request don't need to the authentication do extra processing, can also be to return a result, the downside is that do not pay attention to between will produce noise, and the amount of data is too large to network failure. This.$axios({method: params.method, url: params.url, data: params. params.data, responseType: 'blob' // specifies the return format. Note here that if EXCEL is exported as [object blob], }). Then (res => {console.log(res) // returns the result // in particular, '\ufeff' is used to solve the problem of garbled characters. Const blob = new blob (['\ufeff' + res.data], {type: 'text/ CSV; const blob = new blob (['\ufeff' + res.data], {type: 'text/ CSV; Charset = utf-8 '}) const url = window. Url. CreateObjectURL (blob) / / by creating a label to realize const link = document. The createElement method (' a ') Href = url // Name the downloaded file, if the name returned by the backend is garble, Need to the back-end code. Link. Download = decodeURI (res) headers [' content - disposition]. The split (' = ') [1]) | | 'export invoice data tables. CSV' document.body.appendChild(link) link.click() document.body.removeChild(link) }) ```Copy the code

conclusion

Function is not difficult, need to pay attention to the conversion of binary stream, and a tag address is too long to cause network failure on the line.