如何使用 xlsx npm 库将嵌套的 json 对象转换为 excel 表?

我有一个嵌套的 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');

但完全可以预料,它不会像我希望的那样处理嵌套对象。我不知道我应该怎么做才能得到想要的结果。

stack overflow How to convert a nested json object into an excel table using the xlsx npm library?
原文答案

答案:

作者头像

Just use flat library, which will flatten your object then pass that flatten object to the worksheet.

var flatten = require('flat')
 
flatten({
    key1: {
        keyA: 'valueI'
    },
    key2: {
        keyB: 'valueII'
    },
    key3: { a: { b: { c: 2 } } }
})

Output:

{
  'key1.keyA': 'valueI',
  'key2.keyB': 'valueII',
  'key3.a.b.c': 2
}
作者头像

它总是只会给你扁平化的结果。您可以尝试一些仅进行扁平化的在线转换器。 https://json-csv.com/

但是,如果您使用的是 kendo-grid,它会按照您的期望导出。

https://demos.telerik.com/kendo-ui/grid/excel-export

作者头像

使用 excel4node 我们可以轻松地做到这一点。

在这里显示我创建表的JSON数据,并根据我的要求转换JSON数据

这是解决方案

javascript-solving.blogspot.com

Json data shown as table form

Converted XL File

作者头像

使用 excel4node 我们可以轻松完成。

这里展示我创建的表的JSON数据,并根据我的要求转换JSON数据

这是节点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`);
});

Output

作者头像

使用 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>

Output