json-as-excel

About

Take json object as an argument and convert them into excel data.
Json object i.e.

[
  {
    "study": {
      "science": {
        "bio": {
          "pharmacy": "Kamran Bains",
          "mbbs": {
            "general": "Chloe-Ann Vega",
            "md": "Amayah Barajas"
          }
        },
        "math": {
          "pureMath": "Safa Blackburn",
          "engineering": {
            "computer": {
              "hardware": "Kezia Gonzalez",
              "software": "Boyd Mcbride"
            },
            "civil": "Leela Romero",
            "mechanical": "Mateusz Thornton"
          }
        }
      },
      "management": {
        "bba": "Amelie Bell",
        "bbs": "Jevon Myers"
      }
    }
  },
  {
    "study": {
      "science": {
        "bio": {
          "pharmacy": "Riley-James Duran",
          "mbbs": {
            "general": "Glen Churchill",
            "md": "Sachin Deacon"
          }
        },
        "math": {
          "pureMath": "Rufus Redfern",
          "engineering": {
            "computer": {
              "hardware": "Jonah Best",
              "software": "Zion Ingram"
            },
            "civil": "Matei Gibbs",
            "mechanical": "Kaelan Mcdonnell"
          }
        }
      },
      "management": {
        "bba": "Spike Peel",
        "bbs": "Zakariyah Gray"
      }
    }
  }
];

is converted to below like excel.

No need to manually merge cells now !! 😊 🤩

Installation

npm install json-as-excel

Usage

const excel = require('json-as-excel');
const data = [
        {
          study: {
            science: {
              bio: {
                pharmacy: 'Kamran Bains',
                mbbs: {
                  general: 'Chloe-Ann Vega',
                  md: 'Amayah Barajas',
                },
              },
              math: {
                pureMath: 'Safa Blackburn',
                engineering: {
                  computer: {
                    hardware: 'Kezia Gonzalez',
                    software: 'Boyd Mcbride',
                  },
                  civil: 'Leela Romero',
                  mechanical: 'Mateusz Thornton',
                },
              },
            },
            management: {
              bba: 'Amelie Bell',
              bbs: 'Jevon Myers',
            },
          },
        },
        {
          study: {
            science: {
              bio: {
                pharmacy: 'Riley-James Duran',
                mbbs: {
                  general: 'Glen Churchill',
                  md: 'Sachin Deacon',
                },
              },
              math: {
                pureMath: 'Rufus Redfern',
                engineering: {
                  computer: {
                    hardware: 'Jonah Best',
                    software: 'Zion Ingram',
                  },
                  civil: 'Matei Gibbs',
                  mechanical: 'Kaelan Mcdonnell',
                },
              },
            },
            management: {
              bba: 'Spike Peel',
              bbs: 'Zakariyah Gray',
            },
          },
        },
      ]
const workbook = excel.generateExcel([
    {
      title: 'First sheet',
      data: data,
    },
  ]);

generateExcel function returns exceljs workbook instance. Hence, File I/O can be achieved same as in exceljs. For example:

// write to a file
await workbook.xlsx.writeFile('sample.xlsx');

For the detail reference of File I/O

Method

generateExcel([{ title, data, delimiter, options }]). Method generateExcel accepts array of objects. Each object represents individual sheet. This method returns exceljs workbook instance.

title

Title is name for sheet.

data

Data is json object whose keys are generated as header in excel and values are placed as new row per object.

delimiter (optional)

. is used as a default delimiter. If json data consists key with '.', one need to change delimiter to any other delimiter.

generateExcel([{title:"firstSheet", data:data, delimiter:"%"}])

options (optional)

options are the exceljs available worksheet options i.e. Worksheet Properties, Page Setup, Headers and Footers
More detail can be obtained from exceljs

 const options = {
   properties:{
     outlineLevelCol:2,
     tabColor:{
       argb:'FF00FF00'
     },
     defaultRowHeight:15
   },
   pageSetup:{
     fitToPage: true,
     fitToHeight: 5, 
     fitToWidth: 7
   }
 };
 generateExcel([{title:"firstSheet", data:data, delimiter:"%", options:options}])

headerFormatter (optional)

headerFormatter is a function that accepts header as an argument and return computed header.

 generateExcel([{title:"firstSheet", data:data, delimiter:"%",headerFormatter: (header) => header.toUpperCase()}])

In above example, excel that has header, all with upper case will be generated.

Acknowledgments

1. exceljs
2. flat

Issues

If any issue is found, please raise issue in github.

Changelog

| Version | Changes | | ----------- | ----------- | | 1.0.4 | Installation guide update in Readme | | | 1.0.5 | Example updated in githubBug FixesFixed crash when sheet data is empty objectCheck mandatory title and data for sheet configuration. If not provided, error is thrown | | | 1.0.6 | Test updated for case when data has same nested keyBug FixesCell merge issue when sheet data has same nested key | | | 1.0.7 | FeatureFeature to change header format is now added. For reference, look at headerFormatter option above. | | | 1.0.8 | Readme updated | | | 1.0.9 | Bug FixesWorksheet name already exists issue fixed. | |

MIT License

Copyright (c) 2021