如何格式化带有空行的excel文件以具有嵌套数组并匹配需要渲染的JSON对象？ [英] How can I format an excel file with empty rows to have nested arrays and match a JSON object that I need to render?
问题描述
我遇到了Excel文件问题。我从数据库收到了一些数据，用户应该可以用如下所示的电子表格替换该数据：
I am facing an issue with an excel file. I receive some data from the DB and the user should be able to replace that data with a spreadsheet that looks like this:
这是来自数据库的数据以及excel文件应如何最终格式化：
This is how the data comes from the DB and how the excel file should be finally formatted:
"employers": [{
"id": "4147199311345513",
"shifts": [{
"url": "https://zoom.com/983493unsdkd/",
"days": "Mon,Tue,Wed,Thu,Fri",
"name": "Morning",
"endTime": "12:00",
"timezone": "CST",
"startTime": "8:00"
}, {
"url": "https://zoom.com/983493unsdkd/",
"days": "Mon,Tue,Wed,Thu,Fri",
"name": "Afternoon",
"endTime": "12:00",
"timezone": "CST",
"startTime": "8:00"
}],
"employerUrl": "http://www.google.com",
"employerName": "AT&T",
"employerUrlText": "URL Text",
"employerLogoSmall": "assets/images/att-logo.png",
"employerDescription": "AT&T is a world premier employer with a bunch of stuff here and there."
}, {
"id": "3763171269270198",
"shifts": [{
"url": "https://zoom.com/983493unsdkd/",
"days": "Mon,Tue,Wed,Thu,Fri",
"name": "Morning",
"endTime": "12:00",
"timezone": "CST",
"startTime": "8:00"
}, {
"url": "https://zoom.com/983493unsdkd/",
"days": "Mon,Tue,Wed,Thu,Fri",
"name": "Afternoon",
"endTime": "12:00",
"timezone": "CST",
"startTime": "8:00"
}],
"employerUrl": "http://www.google.com",
"employerName": "AT&T",
"employerUrlText": "URL Text",
"employerLogoSmall": "assets/images/att-logo.png",
"employerDescription": "AT&T is a world premier employer with a bunch of stuff here and there."
}]
所以我需要获取该电子表格并将其格式化为JSON格式以上。所有这些都使用Javascript / React。
So I need to take that spreadsheet and format it to look like that JSON above. All of this with Javascript/React.
到目前为止，这是我格式化excel文件并进行渲染的方式：
This is what I have so far to format my excel file and render it:
const [excelData, setExcelData] = useState({ rows: [], fileName: "" });
const fileHandler = (event) => {
let fileObj = event.target.files[0];
ExcelRenderer(fileObj, (err, resp) => {
if (err) {
console.log(err);
} else {
let newRows = [];
let shiftRows = [];
console.log(resp.rows);
resp.rows.slice(1).map((row, index) => {
if (row && row !== "undefined") {
return newRows.push({
key: index,
employer: {
name: row[0],
description: row[1],
employerUrl: row[2],
employerUrlText: row[3],
shifts: shiftRows.push({ shift: row[2] }),
},
});
}
return false;
});
setExcelData({ rows: newRows, fileName: fileObj.name });
}
});
};
上面的console.log（console.log（resp.rows））返回：
其中第一行是excel文件的标题。
That console.log above (console.log(resp.rows)) returns this: Where the first row are the headers of the excel file.
上面的代码以这样，它应该与我提到的JSON完全一样：
And the code above ends up like this and it should be exactly as the JSON I mentioned:
rows: [
{
key: 0,
employer: {
name: 'AT&T',
description: 'AT&T is a world premier employer with a bunch of stuff here and there.',
shifts: 1
}
},
{
key: 1,
employer: {
shifts: 2
}
},
{
key: 2,
employer: {
shifts: 3
}
},
{
key: 3,
employer: {
shifts: 4
}
},
{
key: 4,
employer: {
name: 'Verizon',
description: 'Verizon is a world premier employer with a bunch of stuff here and there.',
shifts: 5
}
},
{
key: 5,
employer: {
shifts: 6
}
},
{
key: 6,
employer: {
shifts: 7
}
},
{
key: 7,
employer: {
shifts: 8
}
}
],
fileName: 'EmployerChats.xlsx',
false: {
rows: [
{
url: 'https://www.youtube.com/kdfjkdjfieht/',
title: 'This is a video',
thumbnail: '/assets/images/pages/5/links/0/link.png',
description: 'This is some text'
},
{
url: 'https://www.youtube.com/kdfjkdjfieht/',
title: 'This is a video',
thumbnail: '/assets/images/pages/5/links/1/link.png',
description: 'This is some text'
}
]
},
我正在使用此插件来帮助我渲染excel文件： https ：//www.npmjs.com/package/react-excel-renderer
I am using this plugin to help me render the excel file: https://www.npmjs.com/package/react-excel-renderer
关于我该怎么做才能将电子表格数据格式化为JSON吗？
Any ideas on what can I do to make format the spreadsheet data as the JSON?
请注意那些空行。
例如，每当有一个新的雇主名称时，即数组中的新行或项目，那么Shift Name下方和之后的所有列和行都是新的嵌套对象数组。因此，此文件包含一个长度为2的数组，然后在单击Shift Name列时包含另一个项目数组。
For example every time there is a new employer name, that's a new row or item in the array, then all of the columns and rows below and after Shift Name is a new nested array of objects. Hence, this file contains an array with a length of 2 and then it contains another array of items when it hits the Shift Name column.
是否清楚？
推荐答案
第1个-您无需遵循基于类的 setState 。在FC中，您可以仅使用两个单独的 useState 。
1st of all - you don't need to follow 'original', class based setState. In FC you can just use two separate useState.
const [rows, setRows] = useState([]);
const [fileName, setFileName] = useState("");
数据转换
I知道您需要一些不同的工作流程，但这可能很有用（通用点-数据结构）-作为转换指南，请继续阅读。
Data conversion
I know that you need a bit different workflow, but this can be usefull (common point - data structure), too - as conversion guide, read on.
您无需使用 ExcelRenderer 即可处理db中的数据并将其呈现为工作表。转换后的数据可以稍后导出到文件中。
You don't need to use ExcelRenderer to operate on data from db and render it as sheet. Converted data can be exported to file later.
您可以创建遵循预期视图的行数组（aoa）（行=行单元格数组）。为此，您需要一个非常简单的算法：
You can just create array of array (aoa) that follows expected view (rows = array of row cells array). To do this you need very easy algorithm:
-
let newData = [] -
每位（
emp）的emplyers映射：
let newData = []map over emplyers, for each (
emp):
- 设置标志
let first = true; -
将班次映射为每个（
shift）：
-
if（first）{newData。 push（[emp.name，emp.descr，shift.name，shift.timezone ...]）; first = false; -
}否则newData.push（[null，null，shift.name，shift.timezone ...] ）;
if( first ) { newData.push( [emp.name, emp.descr, shift.name, shift.timezone...]); first = false;} else newData.push( [null, null, shift.name, shift.timezone...]);
setRows（newData）;
< OutTable /> 对数据和进行操作列道具-与内部类似的结构状态。 'data 是我们的行，我们只需要 columns`道具，只是另一个状态值：
<OutTable/> operates on data and colums props - structures similar to internal state. 'datais ourrows, we only needcolumns` prop, just another state value:
const [columns, setColumns] = useState([
{ name: "Employer name", key: 0 },
{ name: "Employer description", key: 1 },
{ name: "Shift name", key: 2 },
// ...
]);
最后我们可以渲染它
return (
<OutTable data={rows] columns />
以后
用户可以在工作表视图上进行操作-fe使用 setRows（）插入行或简单转换后，将其下载为文件（ XLSX.writeFile（））：
Later
User can operate on sheet view - f.e. insert rows using setRows() or download this as file (XLSX.writeFile()) after simple conversion:
var ws = XLSX.utils.aoa_to_sheet( columns.concat( rows ) );
有您可以使用很多实用程序进行转换-请参见示例。
There is a lot of utils you can use for conversions - see samples.
我们从db加载了数据，数据以aoa格式呈现为工作表。了解您需要的格式，但是对于您的数据库格式转换很简单（与上面相反）-您可以按照它进行调整并根据需要进行调整。
We have data loaded from db, data in aoa form, rendered as sheet. I don't fully understand format you need, but for your db format conversion is simple (opposite to above) - you can follow it and adjust to your needs.
let newEmployers = [];
let empCounter = -1;
// itarate on rows, on each (`row`):
rows.map( (row) => {
// new employer
if( row[0] ) {
newEmployers.push( {
// id should be here
"employerName": row[0],
"employerDescription": row[1],
"shifts": [
{
"shiftName": row[3],
"shiftDescription": row[4],
// ...
}
]
} );
empCounter ++;
} else {
// new shift for current employer
newEmployers[empCounter].shifts.push(
{
"shiftName": row[3],
"shiftDescription": row[4],
// ...
}
);
}
});
// newEmployers can be sent to backend (as json) to update DB
这篇关于如何格式化带有空行的excel文件以具有嵌套数组并匹配需要渲染的JSON对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
