10

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.

dota2pro
  • 5,432
  • 5
  • 25
  • 52
GoBear
  • 298
  • 2
  • 16
  • 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 Jun 15 '19 at 08:10
  • Hi, thank you for replying. Well I thought of flattening the object but I hoped that the library was able to do more complex 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 Jun 15 '19 at 11:00
  • Please read this https://github.com/SheetJS/js-xlsx/issues/1059#issuecomment-377824371 The library doesn't support complex Json structure – dota2pro Jun 20 '19 at 16:11
  • Is it required to use SheetJS? Is it possible to use another library like ExcelJS (https://github.com/exceljs/exceljs)? – Gosha_Fighten Jun 20 '19 at 17:14
  • similar question asked here https://stackoverflow.com/questions/55732859/convert-xlsx-file-with-merged-cells-to-json-with-sheetjs, you may get an idea how there it's trying to achieved. – Harshana Jun 23 '19 at 18:15
  • @GoBear Have you got any solution for this issue? – Ashok Mar 02 '20 at 11:26

1 Answers1

0

It will always give you flatten result only. You may try some online converters which are doing flatten only. https://json-csv.com/

But if you are using kendo-grid, it exports as per your expectation.

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

Nishant Thakkar
  • 61
  • 4