我有一个嵌套的json对象。为简单起见：
data = {'key1': 1, 'key2': 2, 'key3': {'nestedKey1': 3, 'nestedKey2': 4}}
一个真实的对象更大，但关键是它是一个嵌套的对象。值可以是字符串、数字或其他对象。现在我需要得到一个 excel 表(对于这个特定的 json 看起来像这样(：
|----------------------------------------
| key1 | key2 | key3 |
| | |--------------------------
| | | nestedKey1 | nestedKey2 |
|----------------------------------------
| 1 | 2 | 3 | 4 |
|----------------------------------------
为了做到这一点，我尝试使用 xlsx 库。我按如下方式导入库：
import { WorkSheet, WorkBook, utils, writeFile } from 'xlsx';
在我的方法中，我定义了对象：
let myObj = {'key1': 1, 'key2': 2, 'key3': {'nestedKey1': 3, 'nestedKey2': 4}}
然后我创建一个工作表和工作簿：
const workSheet: WorkSheet = utils.json_to_sheet([myObj]);
const workBook: WorkBook = utils.book_new();
utils.book_append_sheet(workBook, workSheet, 'object_to_save');
最后将其写入文件：
writeFile(workBook, 'Tests.xlsx');
但很明显，它不会按照我想要的方式处理嵌套对象。我不知道我应该怎么做才能得到想要的结果。
使用excel4node我们可以轻松做到这一点。
在这里显示我创建的 JSON，并根据我的要求转换 JSON。
以下是node.js中的解决方案：
const express = require("express");
const excelgenerate = require("excel4node");
const app = express();
//To serve static files such as images, CSS files, and JavaScript files
app.use(express.static('./public'));
// Parse JSON bodies (as sent by API clients)
app.use(express.json());
// Parse URL-encoded bodies (as sent by HTML forms)
app.use(express.urlencoded({ extended: true }));
//Create a new instance of a Workbook class
const wb = new excelgenerate.Workbook();
const heading = ["Full Name", "Email"];
const heading1 = ["First Name", "Last name", " "];
const data = [
{
first_name: "Johns",
last_name: "Does",
email: "johndoess@yopmail.com",
},
{
first_name: "JP",
last_name: "Smith",
email: "jpmorgan@yopmail.com",
},
{
first_name: "Test",
last_name: "Team",
email: "test.team@yopmail.com",
},
];
//creating dynamik tr td for table
let datas = [];
datas.push(`<tr>
<th colspan="2">${heading[0]}</th>
<th>${heading[1]}</th>
</tr>`);
datas.push(`<tr>
<th>${heading1[0]}</th>
<th>${heading1[1]}</th>
<th>${heading1[2]}</th>
</tr>`);
data.map((value) => {
datas.push(`<tr>
<td>${value.first_name}</td>
<td>${value.last_name}</td>
<td>${value.email}</td>
</tr>`);
});
//remove , from array
datas = datas.join("");
app.get("/", (req, res) => {
try {
res.send(`
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
</style>
<div style="margin:100px">
<form action="/convert" method="post">
<div class="form-group">
<input type="submit" value="Convert To Excel!" class="btn btn-default">
<table>
${datas}
</table>
</div>
</form>
</div>
`);
} catch (error) {
throw error;
}
});
app.post("/convert", (req, res) => {
try {
// Add Worksheets to the workbook
const ws = wb.addWorksheet("Sheet 1");
// Create a reusable style
var style = wb.createStyle({
font: {
size: 12,
},
});
ws.cell(1, 1, 1, 2, true).string(heading[0]).style({ font: { size: 14 } });
ws.cell(1, 3).string(heading[1]).style({ font: { size: 14 } });
for (let index = 1; index < 4; index++) {
ws.column(index).setWidth(25);
ws.cell(2, index)
.string(heading1[index - 1])
.style(style)
.style({ font: { size: 14 } });
}
for (let index = 0; index < data.length; index++) {
ws.cell(index + 3, 1)
.string(data[index].first_name)
.style(style);
ws.cell(index + 3, 2)
.string(data[index].last_name)
.style(style);
ws.cell(index + 3, 3)
.string(data[index].email)
.style(style);
}
wb.write("public/files/Excel.xlsx");
let fname = "Excel.xlsx";
res.send(`<div style="margin:100px">
<a href="ms-excel:ofe|u|file:///E:/javascript-jquery/multiselect-master/${fname}">
<input type="button" value="Open In App" class="btn btn-default">
</a>
<br>
<br>
<a href="/files/${fname}" target="_blank">
<input type="button" value="Download" class="btn btn-default">
</a>
</div>`);
} catch (e) {
throw e;
}
});
app.listen(3000, () => {
console.log(`App running at http://localhost:3000`);
});输出
只需使用flat库，它将展平您的对象，然后将该平展对象传递给工作表。
var flatten = require('flat')
flatten({
key1: {
keyA: 'valueI'
},
key2: {
keyB: 'valueII'
},
key3: { a: { b: { c: 2 } } }
})
输出：
{
'key1.keyA': 'valueI',
'key2.keyB': 'valueII',
'key3.a.b.c': 2
}
使用 xlsx npm，我们可以轻松做到这一点。为此，我们必须将对象转换为数组。
这是将根据我的要求转换为 xlsx 文件的数组数据。
这是 react js 中的解决方案：
var data = [
["id", "items"],
["","name"],
["nick","ball"],
["nick","phone"],
["jack","pen"],
["jack","doll"]
];
// merge cells(s) r = row, c = column
var merge = [{ s: {r:2, c:0}, e: {r:3, c:0} },{ s: {r:4, c:0}, e: {r:5, c:0} }];
var ws = XLSX.utils.aoa_to_sheet(data);
if(!ws['!merges']) ws['!merges'] = [];
for (const iterator of merge) {
ws['!merges'].push(iterator);
}
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "sheet1");
//console.log(wb)
// Generate buffer
let arrayBuffer = XLSX.write(wb, { bookType: "xlsx", type: "buffer" });
// Binary string
XLSX.write(wb, { bookType: "xlsx", type: "binary" });
console.log(wb)
//for download in react file folder
let wbout = XLSX.writeFile(wb, "studentsData.xlsx");<script src="https://cdnjs.cloudflare.com/ajax/libs/react/16.6.3/umd/react.production.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/react-dom/16.6.3/umd/react-dom.production.min.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>输出