格式一：
/*
* @description TODO 前端处理excel表格数据为json
* @param {String} 文件路径
* @return {JSON} 表格json
* @author Albert_Luo
* @date 2024/7/17 13:30
*/
ExcelToJson:function(fileUrl,fileName,callback){
LuckyExcel.transformExcelToLuckyByUrl(fileUrl, fileName, function (exportJson, luckysheetfile) {
if (exportJson.sheets == null || exportJson.sheets.length == 0) {
layer.closeAll('loading');
layer.msg('数据获取失败，请重试！');
return;
}
var xlsxData = exportJson.sheets[0].celldata;
var thList = [];
xlsxData.forEach(function (item) {
var vv;
if (item.v.v !== undefined) {
vv = item.v.v;
} else if (item.v.ct && item.v.ct.s !== undefined) {
vv = item.v.ct.s[0].v;
} else {
vv = '';
}
if (item.r == 0) {
// 表头
thList[item.c] = vv;
}
});
var paiArr = [];
for (let i in thList) {
var obj = {};
obj[thList[i]] = i;
paiArr.push(obj);
}
var tableData = [];
var tableCategory =[];
var objTemp = {};
xlsxData.forEach(function (item, index) {
var vv;
if (item.v.v !== undefined) {
vv = item.v.v;
} else if (item.v.ct && item.v.ct.s !== undefined) {
vv = item.v.ct.s[0].v;
} else {
vv = '';
}
if (item.r != 0) {
// 表数据
for (let i in paiArr) {
var cName = Object.keys(paiArr[i])[0];
if (item.c == paiArr[i][cName]) {
objTemp[cName] = vv;
}
}
var nextIndex = index + 1;
if (nextIndex == xlsxData.length || xlsxData[nextIndex].r != item.r) {
// 没有下一个了，或 下一个是新一行开始
tableData.push({ ...objTemp });
objTemp = {};
}
}
});
// 处理成三级选择级联数据结构
var idCounter = 0;
var columnData = {};
var result = {
id: idCounter++,
name: "",
child: {}
};
// 初始化每一列的数据结构
thList.forEach((header, index) => {
columnData[header] = {};
});
tableData.forEach(row => {
var parentId = null;
thList.forEach((header, index) => {
var value = row[header];
if (!columnData[header][value]) {
columnData[header][value] = {
id: idCounter++,
name: value,
parentId: parentId,
child: {}
};
if (parentId !== null) {
var parentHeader = thList[index - 1];
var parentValue = row[parentHeader];
var parentIdKey = columnData[parentHeader][parentValue].id;
if (!columnData[parentHeader][parentValue].child[parentIdKey]) {
columnData[parentHeader][parentValue].child[parentIdKey] = [];
}
columnData[parentHeader][parentValue].child[parentIdKey].push(columnData[header][value]);
} else {
if (!result.child[result.id]) {
result.child[result.id] = [];
}
result.child[result.id].push(columnData[header][value]);
}
}
parentId = columnData[header][value].id;
});
});
// 结果数据结构
console.log(result);
if (result.child[0].length > 0){
tableCategory=result.child[0];
}
// console.log(tableData);
if (callback) {
callback(tableCategory);
}
});
}结果：
[
{
"id": 1,
"name": "西红柿",
"parentId": null,
"child": {
"1": [
{
"id": 2,
"name": "542",
"parentId": 1,
"child": {
"2": [
{
"id": 3,
"name": "12",
"parentId": 2,
"child": {
"3": [
{
"id": 4,
"name": "35",
"parentId": 3,
"child": {
"4": [
{
"id": 5,
"name": "456",
"parentId": 4,
"child": {}
}
]
}
}
]
}
}
]
}
},
{
"id": 6,
"name": "312",
"parentId": 1,
"child": {}
}
]
},
"LAY_TABLE_INDEX": 0
},
{
"id": 7,
"name": "西兰花",
"parentId": null,
"child": {
"7": [
{
"id": 8,
"name": "321",
"parentId": 7,
"child": {
"8": [
{
"id": 9,
"name": "122",
"parentId": 8,
"child": {
"9": [
{
"id": 10,
"name": "32",
"parentId": 9,
"child": {
"10": [
{
"id": 11,
"name": "523",
"parentId": 10,
"child": {}
}
]
}
}
]
}
}
]
}
}
]
},
"LAY_TABLE_INDEX": 1
}
]格式二
/*
* @description TODO 前端处理excel表格数据为json
* @param {String} 文件路径
* @return {JSON} 表格json
* @author Albert_Luo
* @date 2024/7/17 13:30
*/
ExcelToJson:function(fileUrl,fileName,callback){
LuckyExcel.transformExcelToLuckyByUrl(fileUrl, fileName, function (exportJson, luckysheetfile) {
if (exportJson.sheets == null || exportJson.sheets.length == 0) {
layer.closeAll('loading');
layer.msg('数据获取失败，请重试！');
return;
}
var xlsxData = exportJson.sheets[0].celldata;
var thList = [];
xlsxData.forEach(function (item) {
var vv;
if (item.v.v !== undefined) {
vv = item.v.v;
} else if (item.v.ct && item.v.ct.s !== undefined) {
vv = item.v.ct.s[0].v;
} else {
vv = '';
}
if (item.r == 0) {
// 表头
thList[item.c] = vv;
}
});
var paiArr = [];
for (let i in thList) {
var obj = {};
obj[thList[i]] = i;
paiArr.push(obj);
}
var tableData = [];
var objTemp = {};
xlsxData.forEach(function (item, index) {
var vv;
if (item.v.v !== undefined) {
vv = item.v.v;
} else if (item.v.ct && item.v.ct.s !== undefined) {
vv = item.v.ct.s[0].v;
} else {
vv = '';
}
if (item.r != 0) {
// 表数据
for (let i in paiArr) {
var cName = Object.keys(paiArr[i])[0];
if (item.c == paiArr[i][cName]) {
objTemp[cName] = vv;
}
}
var nextIndex = index + 1;
if (nextIndex == xlsxData.length || xlsxData[nextIndex].r != item.r) {
// 没有下一个了，或 下一个是新一行开始
tableData.push({ ...objTemp });
objTemp = {};
}
}
});
// console.log(tableData);
if (callback) {
callback(tableData);
}
});
}结果2
[
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "312",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西兰花",
"出现次数": "321",
"交易数": "122",
"同时出现次数": "32",
"同时交易数": "523"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
},
{
"商品": "西红柿",
"出现次数": "542",
"交易数": "12",
"同时出现次数": "35",
"同时交易数": "456"
}
]
评论区