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.

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.

• javascript
• angular
• xlsx
• sheetjs

• 1 Hi Igor sorry but I haven't used the library that much. Is there even a nested columns feature in Excel or do you want them to be "grouped columns"? I would probably just preprocess the object to flatten it like: let myObj = {'key1': 1, 'key2': 2, 'key3.nestedKey1': 3, 'key3.nestedKey2': 4} Before doing json_to_sheet if that would be OK for your use case – torno Commented Jun 15, 2019 at 8:10
• Hi, thank you for replying. Well I thought of flattening the object but I hoped that the library was able to do more plex things. I don't really like excel for a whole lot of reasons and thus I didn't use it much so I'm not sure what the structure above is called like. The columns are nested as far as I understand. I was given a sample of the table in xlsx format and I have not yet found out how to get the desired result using this lib. – GoBear Commented Jun 15, 2019 at 11:00
• Please read this github./SheetJS/js-xlsx/issues/1059#issuement-377824371 The library doesn't support plex Json structure – dota2pro Commented Jun 20, 2019 at 16:11
• 1 Is it required to use SheetJS? Is it possible to use another library like ExcelJS (github./exceljs/exceljs)? – Gosha_Fighten Commented Jun 20, 2019 at 17:14
• similar question asked here stackoverflow./questions/55732859/…, you may get an idea how there it's trying to achieved. – Harshana Commented Jun 23, 2019 at 18:15

3 Answers 3

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: "[email protected]",
  },
  {
    first_name: "JP",
    last_name: "Smith",
    email: "[email protected]",
  },
  {
    first_name: "Test",
    last_name: "Team",
    email: "[email protected]",
  },
];

//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

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
}

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./ajax/libs/react/16.6.3/umd/react.production.min.js"></script>
<script src="https://cdnjs.cloudflare./ajax/libs/react-dom/16.6.3/umd/react-dom.production.min.js"></script>
<script src="https://unpkg./axios/dist/axios.min.js"></script>
<script src="https://unpkg./xlsx/dist/xlsx.full.min.js"></script>

Output