A few days ago, I received a demand that the server side was an old project and difficult to maintain, so I needed the front end to read excel files and then pass them to the interface.

I thought it was really a dog, and it would take a long time for the front end to handle the large file, but PD said it didn’t matter, it didn’t matter, just start working

Let’s state that this project is actually written through JQ, 10,000 MMP ran, but who let us eat this bowl of rice, there is no way to start

dom

<input type="file" class="excel-file">
<button class="out">
Copy the code

The function parse excel

// first introduce JQ. Input type-file let list=[]; // Store the parsed excel content let titleList=[]; $('.excel-file'). Change (function (e: any) {var files = e.target.files; Var fileReader = new fileReader (); / / create instances for parsing the file stream (var sheet in workbook. Sheets) {if (workbook. Sheets. HasOwnProperty (sheet)) {fromTo = workbook.Sheets[sheet]['!ref']; list = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet])); break; / / if only take a piece of table, then uncomment this line}} titleList = Object. The keys (the list [0] | | {}); } / / and then we open the file in binary way. FileReader readAsBinaryString (files [0]). e.target.value = null; // Do whatever you want.......Copy the code

Function Download the processed Excel file to the local PC

$('.out').click(function (e: any) {let STR = "; titleList.forEach(element => { str += `<td>${element}</td>`; }); str = `<tr>${str}</tr>`; For (let I = 0; i < list.length; i++) { str += '<tr>'; for (let item in outlist[i]) { var cellvalue = outlist[i][item]; // add style="mso-number-format:'\@';" /*var reg = /^[0-9]+.? [0-9] * $/; if ((cellvalue.length>15) && (reg.test(cellvalue))){ // cellvalue = '="' + cellvalue + '"'; <td style="mso-number-format:'\@';" >${cellvalue}</td>`; // str+=`<td>${cellvalue}</td>`; } str += '</tr>'; Var uri = 'data:application/vnd.ms-excel; base64,'; Var template = '< HTML XMLNS :o=" Urn :schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><! --[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet> <x:Name>${worksheet}</x:Name> <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet> </x:ExcelWorksheets></x:ExcelWorkbook></xml><! [endif]--> </head><body><table>${str}</table></body></html>`; Function base64(s) {return window.btoa(unescape(encodeURIComponent(s)))} window.location.href = uri + base64(template); }Copy the code

This article is also borrowed from other articles, which specific article has not found ~