To use jS-xlsx, you need to download it at github.com/SheetJS/js-…
Since it is an old project, I use jquery and the CSS library is Bootsharp.
The requirement is to click the button to upload Excel into the table, the code is as follows
html
<input class="btn btn-file" type="file" data-type="2" value="Import Excel"></input>
<button class="btn btn-danger btn-express" type="button"<table id="demo" class="table"></table>
Copy the codejs
$(".btn-express").on("click".function(e) {// Hide the input[file], click the button to trigger the upload file $(".btn-file").trigger("click"The $()})".btn-file").on("change".function(ev) {// Console. log(ev.target.files) var reader = new FileReader(); reader.onload =function (e) {
try {
var data = e.target.result;
var workbook = XLSX.read(data, { type: 'binary'}); var sheetNames = workbook.SheetNames; Var worksheet = workbook.sheets [sheetNames[0]]; Var HTML = xlsx.utils.sheet_to_html (worksheet); HTML console.log(HTML) $("#demo").html(HTML)// Put parsed HTML into a table} catch (err) {console.log(err) anim.msg ()"Incorrect file type");
return false; }}; reader.readAsBinaryString(ev.target.files[0]); })Copy the codeparsing
The xlsx.read (data, {type: type}) method returns an object called WorkBook. The values of type are as follows:
- Base64: Reads data in base64 mode.
- Binary: byte n is data.charcodeat (n)
- String: UTF8 encoded character string.
- Buffer: nodejs buffer;
- Array: Uint8Array, 8-bit unsigned array;
- File: indicates the path of a file (supported only under nodejs).
The return format is shown below:
The worksheet can be parsed using the xlsx. utils utility class written by the plug-in itself.
- Xlsx.utils. sheet_to_csv: Generates the CSV format
- Xlsx.utils.sheet_to_txt: Generates plain text format
- Xlsx.utils.sheet_to_html: Generates HTML format
- Xlsx.utils. sheet_TO_json: Outputs the JSON format
Sheet_to_json parsing will return arrays, and HTML will return HTML, but will also print out HTML,meta, and body
The project uses JQ +layUI complete code as follows:
html:
<div class="title"> <span> <span> <input class="btn btn-file" name="upfile" type="file" data-type="2" value="Import Excel"></input>
<button class="btn btn-danger btn-express" type="button"<span style = "box-sizing: border-box! Important; word-wrap: break-word! Important;"download">
<a href="__PUBLIC__/static/images/ mailing template.xlsx"</a> </span> </div> <table id="demo" class="table table-bordered">
<thead></thead>
<tbody></tbody>
</table>
<div class="box-footer"< p style= "max-width: 100%; clear: both; min-height: 1em"color: red"><span class="sumNum">0</span> yuan </span></div> <div style="text-align: center;">
<button class="btn btn-footer reset" type="button"</button> <button class="btn btn-footer save" type="button"</button> </div>Copy the codejs:
var fileExcel = null; File var skipType=1 var tbodyArr=null".btn-express").on("click".function (e) {
$(".btn-file").trigger("click"}) // Trigger upload excel $(".btn-file").on("change".function (ev) {
var files = new FormData();
files.append("file", ev.target.files[0])
files.append("kdid", $("#expressType").val())
$.ajax({
url: "{upload interface}".type: "POST",
data: files,
async: false,
cache: false,
contentType: false,
processData: false,
success: function (returndata) {
if (returndata.success == false) {layer. MSG (returndata.m, {time: 1000 //1 seconds off (default 3 seconds if not configured)},function () {
location.reload();
});
} else{var arr=returndata.d tbodyArr=returndata.d var shipSum=0// total freight // console.log(arr)for(var i=0; i<arr.length; i++){ shipSum+=parseFloat(arr[i].M) } $(".sumNum").text(shipSum)
}
},
error: function (returndata) {
}
});
// return false;
var files = ev.target.files
var rawFile = files[0] // only use files[0]
fileExcel = files[0]
if(! rawFile)return
var reader = new FileReader();
reader.onload = function (e) {
var msg = ""Loading = layer.load(1, {shade: [0.1,'# 000'] //1 Transparent white background}); try { var data = e.target.result; var workbook = XLSX.read(data, {type: 'binary'}); var firstSheetName = workbook.SheetNames[0] var worksheet = workbook.Sheets[firstSheetName] var headers = GetHeaderRow (worksheet)// Header var results = xlsx.utils.sheet_to_json (worksheet); / / tablesetThead(headers)
setTbody(tbodyArr)
$(".box-footer").show()
if (reader.onload) {
reader.onload = null
}
// console.log(headers, results)
layer.closeAll('loading');
} catch (err) {
console.log(err)
Anim.msg("Incorrect file type");
layer.closeAll('loading');
return false; }}; reader.readAsBinaryString(rawFile); }) // render the table theadfunction setThead(headers) {
headers.push("Freight"The $()"#demo thead").html("")
if (headers.length == 0) {
return false
}
var tr = $("<tr></tr>")
for (var i = 0; i < headers.length; i++) {
var td = $("<td data-index=" + i + ">" + headers[i] + "</td>")
tr.append(td)
}
$("#demo thead".appEnd (tr)} // Render table tBodyfunction setTbody(data) {
$("#demo tbody").html("")
if (data.length == 0) {
return false
}
for (var j = 0; j < data.length; j++) {
var tr = $("<tr data-id=" + j + "></tr>")
for (key indata[j]) { var item=data[j][key]? data[j][key]:' '
var td = $("<td>" + item+ "</td>")
tr.append(td)
}
$("#demo tbody".appEnd (tr)}} // Filter datafunction getHeaderRow(sheet) {
var headers = []
var range = XLSX.utils.decode_range(sheet['! ref'])
var C
var 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
}
Copy the codeThe final result is as follows:
Please forgive me if there is something wrong with this article. (world leaders ˍ.