This requires an understanding of the table header encoding rules for Excel tables, and the current sample code only expands to 52 fields
/**
*json data Import Export Excel Table sample code
*
/var array_utils = require ('./utils-array ')
var xlsx = require ("xlsx");
Module.exports = {
Writeexcel:function (Headers,data,file,callback) {
if (Data.length ==0) {
var obj = {}
for (var V of headers) {
OBJ[V] = "
}
Data.push (obj)
}
_writeexcel (Headers,data,file,callback)
},
/**
* Get Excel Raw Information
* Path @param path file
*/
Info:function (path) {
return _info (PATH)
},
/**
* Formatting Excel Raw Information
* Path @param path file
*/
Formate_info:function (path) {
return Info_formate_info (path). formate
},
Info_formate_info:function (path) {
return Info_formate_info (PATH)
}
}
var _info = function (path) {
var k = xlsx.readfile (path, {type: ' base64 '});
var result = {}
K.sheetnames.foreach (function (sheetname) {
var worksheet = K.sheets[sheetname];
Result[sheetname] = XLSX.utils.sheet_to_json (worksheet);
});
return result
}
var info_formate_info = function (path) {
var info = _info (path)
var result = {}
for (var value in info) {
Result[value] = {}
}
for (var key_info in info) {
var array = Info[key_info]
if (Array_utils.isarray (array) | | array.length>0) {
var Keys_array = Object.keys (array[0])
var obj = {}
for (var value of Keys_array) {
Obj[value] = []
}
for (var key in obj) {
var subject_clone = Json.parse (json.stringify (array))
Subject_clone.filter ((v) =>{
For (var k in V) {
if (K!=key) {
Delete V[k]
}
}
return v;
})
var subject_key_value = Array.from (ARRAY_UTILS.ARRAYQC (subject_clone), V = V[key])
var ObA = []
for (Var V of Subject_key_value) {
var obk = {
Id:null,
V:v
}
for (Var ke in keys_array) {
var thisindex = Keys_array.findindex (X=>x==keys_array[ke])
var currentindex = Keys_array.findindex (X=>x==key)
if (Thisindex < Currentindex) {
try {
var thisobj = Array.find (x=>x[key] = = v)
Obk[keys_array[ke]] = thisobj[Keys_array[ke]]
}catch (e) {
Console.error (e)
}
}
}
Oba.push (OBK)
}
Obj[key] = ObA
}
result[key_info]= obj
}
}
return {
Info:info,
Formate:result
}
}
var _writeexcel = function (headers,data,file,callback) {
var _headers = headers
var _data = data;
var headers = _headers
Add a corresponding cell location for _headers
. Map ((v, i) = = Object.assign ({}, {
V:v,
Position:num (i) +1
}))
The structure required for conversion into worksheet
. reduce ((prev, next) = Object.assign ({}, prev, {[next.position]: {V:NEXT.V}}), {});
var data = _data
. Map ((v, i) = _headers.map ((k, j) = = Object.assign ({}, {
V:v[k],
Position:num (j) + (i+2)
})))
Reduced dimensional processing of the results (two-dimensional arrays into one-dimensional arrays)
. reduce ((prev, next) = Prev.concat (next))
The structure required for conversion into worksheet
. reduce ((prev, next) = Object.assign ({}, prev, {[next.position]: {V:NEXT.V}}), {});
Merging headers and data
Console.log ("Test Data", data)
var output = Object.assign ({}, headers, data);
Get the location of all cells
var outputpos = Object.keys (output);
Calculate the Range
var ref = Outputpos[0] + ': ' + outputpos[outputpos.length-1];
Building Workbook objects
var wb = {
Sheetnames: [' Sheet1 '],
Sheets: {
' Sheet1 ': Object.assign ({}, output, {'!ref ': ref})
}
};
Export Excel
Xlsx.writefileasync (file, wb,function (err) {
Callback (ERR)
});
}
Locating Excel Locations
var num=function (i) {
var n=parseint (i+65)
if (n>90) {
N=string.fromcharcode (+string.fromcharcode) (i+39)
return n
}else {
N=string.fromcharcode (N)
return n
}
}
JS Export Excel table Beyond the 26-bit English character solution ES6