How to convert a nested json object into an excel table using the xlsx npm library?

Question

I've got a nested json object. For the sake of simplicity:

data = {'key1': 1, 'key2': 2, 'key3': {'nestedKey1': 3, 'nestedKey2': 4}}

A real object is bigger but the point is that it's a nested one. Values may be strings, numbers or other objects. Now I need to get an excel table that (for this specific json would look like this):

|----------------------------------------
| key1 | key2 |           key3          |
|      |      |--------------------------
|      |      | nestedKey1 | nestedKey2 |
|----------------------------------------
|  1   |  2   |     3      |      4     |
|----------------------------------------

In order to do this I tried using the xlsx library. I import the lib as follows:

import { WorkSheet, WorkBook, utils, writeFile } from 'xlsx';

Inside my method I define the object:

let myObj = {'key1': 1, 'key2': 2, 'key3': {'nestedKey1': 3, 'nestedKey2': 4}}

then I create a worksheet and workbook:

const workSheet: WorkSheet = utils.json_to_sheet([myObj]);
const workBook: WorkBook = utils.book_new();
utils.book_append_sheet(workBook, workSheet, 'object_to_save');

and finally write it to a file:

writeFile(workBook, 'Tests.xlsx');

But quite expectedly it doesn't process the nested object the way I would like it to. I can't figure out what I should do to get the desired result.

Answer 1

Using excel4node we can do it easily.

Here to display the JSON I created the table, and convert the JSON according to my requirement.

Here is the solution in 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`);
});

Output

Answer 2

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
}

Answer 3

Using xlsx npm we can do it easily. For that, we have to convert the object into the array.

Here is the array data that will convert into xlsx file according to my requirement.

Here is the solution in 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