CODEWITHSUNDEEP
javascripthtmljson
Naresh Kumar
Naresh Kumar

Reputation: 305

convert JSON with html table tag using JavaScript

I am trying to convert output from SQL Query which comes as a table. I have converted the table as JSON. Now I am in a process of converting the JSON to HTML Table so that I can use it for reporting.

Script is given below,

var value = '{"root":{"row":[{"DatabaseID":"21","fileid":"1","databaseName":"AutomationPortal","FileLogicalName":"AutomationPortal","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomationPortal.mdf","FileSizeMB":"100.00","SpaceUsedMB":"10.25","MaxfileSizeMB":"-0.01","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"},{"DatabaseID":"21","fileid":"3","databaseName":"AutomationPortal","FileLogicalName":"AutomatioPortal_01","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomatioPortal_01.ndf","FileSizeMB":"100.00","SpaceUsedMB":"0.06","MaxfileSizeMB":"130.00","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"}]}}'
        var data = JSON.parse(value);
        
        var tablearray = [];
        tablearray.push("<table><tr>")
        var queryRow = data.root.row.length;
        
        var headerProperty = Object.keys(data.root.row[0]);
        
        for (i=0;i<headerProperty.length;i++){
            tablearray.push("<th>"+headerProperty[i]+"</th>");
        }
        tablearray.push("</tr>");
        //console.log(tablearray);
        for (i=0;i<queryRow;i++){
            tablearray.push("<tr>")
            for (j=0;j<headerProperty.length;j++){
                // console.log(headerProperty[j]);
                // console.log(data.root.row[0].DatabaseID);
                // console.log(data.root.row[i].headerProperty[j]);
        tablearray.push("<td>"+data.root.row[i].headerProperty[j]+"</td>");
            }
            tablearray.push("</tr>");
        }
        tablearray.push("</table>");
        tablearray.join('');

When I run the above script it gives me the following error, I am unable to fix the issue.

tablearray.push(""+data.root.row[i].headerProperty[j]+""); ^

TypeError: Cannot read property '0' of undefined at Object. (C:\Users\convertjsontohtml.js:21:55) at Module._compile (internal/modules/cjs/loader.js:678:30) at Object.Module._extensions..js (internal/modules/cjs/loader.js:689:10) at Module.load (internal/modules/cjs/loader.js:589:32) at tryModuleLoad (internal/modules/cjs/loader.js:528:12) at Function.Module._load (internal/modules/cjs/loader.js:520:3) at Function.Module.runMain (internal/modules/cjs/loader.js:719:10) at startup (internal/bootstrap/node.js:228:19) at bootstrapNodeJSCore (internal/bootstrap/node.js:575:3)

The Output I am expecting is like ""

Upvotes: 0

Views: 3531

Answers (5)

Rahul Ravi
Rahul Ravi

Reputation: 21

This is my generic code to convert any json (containing Object add/or List). Building a table like this can be used to visualize our json and at times even use it in production code for debugging support.

const getType = obj => Object.prototype.toString.call(obj).slice(8, -1);
const isArray = obj => getType(obj) === 'Array';
const isMap = obj => getType(obj) === 'Object';
var buildList = function(data) {
  const keys = new Set();
  var str = "<table border==\"0\"><tr>";
  for (var i = 0; i < data.length; i++) {
    for (key in data[i]) {
      keys.add(key);
    }
  }
  for (key of keys) {
    str = str.concat('<td>' + key + '</td>');
  }
  str = str.concat("</tr>");
  for (var i = 0; i < data.length; i++) {
    str = str.concat('<tr>');
    for (key of keys) {
      str = str.concat('<td>');
      if (data[i][key]) {
        if (isMap(data[i][key])) {
          str = str.concat(buildMap(data[i][key]));
        } else if (isArray(data[i][key])) {
          str = str.concat(buildList(data[i][key]));
        } else {
          str = str.concat(data[i][key]);
        }
      }
      str = str.concat('</td>');
    }
    str = str.concat('</tr>');
  }
  str = str.concat("</table>");
  return str;
}
var buildMap = function(data) {
  var str = "<table border==\"0\">";
  for (const key in data) {
    str = str.concat('<tr><td>' + key + '</td><td>');
    if (isArray(data[key])) {
      str = str.concat(buildList(data[key]));
    } else if (isMap(data[key])) {
      str = str.concat(buildMap(data[key]));
    } else {
      str = str.concat(data[key]);
    }
    str = str.concat('</td></tr>');
  }
  str = str.concat("</table>");
  return str;
}

var value = '{"root":{"row":[{"DatabaseID":"21","fileid":"1","databaseName":"AutomationPortal","FileLogicalName":"AutomationPortal","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomationPortal.mdf","FileSizeMB":"100.00","SpaceUsedMB":"10.25","MaxfileSizeMB":"-0.01","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"},{"DatabaseID":"21","fileid":"3","databaseName":"AutomationPortal","FileLogicalName":"AutomatioPortal_01","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomatioPortal_01.ndf","FileSizeMB":"100.00","SpaceUsedMB":"0.06","MaxfileSizeMB":"130.00","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"}]}}';
var data = JSON.parse(value);
var outHtml = "No Data found";
if (isArray(data)) {
  outHtml = buildList(data);
} else if (isMap(data)) {
  outHtml = buildMap(data);
}
document.getElementById("main").innerHTML = outHtml;</script>
<div id="main"></div>

Upvotes: 2

Arif
Arif

Reputation: 1645

Instead of working with strings you might want to create elements using document.createElement

const value = '{"root":{"row":[{"DatabaseID":"21","fileid":"1","databaseName":"AutomationPortal","FileLogicalName":"AutomationPortal","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomationPortal.mdf","FileSizeMB":"100.00","SpaceUsedMB":"10.25","MaxfileSizeMB":"-0.01","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"},{"DatabaseID":"21","fileid":"3","databaseName":"AutomationPortal","FileLogicalName":"AutomatioPortal_01","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomatioPortal_01.ndf","FileSizeMB":"100.00","SpaceUsedMB":"0.06","MaxfileSizeMB":"130.00","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"}]}}'
const data = JSON.parse(value);
console.log(data);

const $table = document.createElement('table');
const $head = document.createElement('thead');
const $body = document.createElement('tbody');

for (let i = 0; i < data.root.row.length; i++) {
  const $tr = document.createElement('tr');

  Object.keys(data.root.row[0]).forEach((colName) => {
    if (i === 0) {
      const $th = document.createElement('th');
      $th.innerText = colName;
      $head.appendChild($th);
    }
    const $td = document.createElement('td');
    $td.innerText = data.root.row[0][colName];
    $tr.appendChild($td);
  });

  $body.appendChild($tr);
}

$table.appendChild($head);
$table.appendChild($body);

document.getElementById('table').appendChild($table);
<div id="table"></div>

Upvotes: 0

user3210641
user3210641

Reputation: 1631

You can build the table by looping thought each value like this:

const input = '{"root":{"row":[{"DatabaseID":"21","fileid":"1","databaseName":"AutomationPortal","FileLogicalName":"AutomationPortal","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomationPortal.mdf","FileSizeMB":"100.00","SpaceUsedMB":"10.25","MaxfileSizeMB":"-0.01","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"},{"DatabaseID":"21","fileid":"3","databaseName":"AutomationPortal","FileLogicalName":"AutomatioPortal_01","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomatioPortal_01.ndf","FileSizeMB":"100.00","SpaceUsedMB":"0.06","MaxfileSizeMB":"130.00","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"}]}}';

// Parse given JSON
const parsed = JSON.parse(input);

// Get keys (=cells) of each items
const keys = Object.keys(parsed.root.row[0]);

// Build the table header
const header = `<thead><tr>` + keys
  .map(key => `<th>${key}</th>`)
  .join('') + `</thead></tr>`;
  
// Build the table body
const body = `<tbody>` + parsed.root.row
  .map(row => `<tr>${Object.values(row)
    .map(cell => `<td>${cell}</td>`)
    .join('')}</tr>`
  ).join('');
  
// Build the final table
const table = `
<table>
  ${header}
  ${body}
</table>
`;
  
// Append the result into #root element
document.getElementById('root').innerHTML = table;
<div id="root"></div>

Upvotes: 4

ADyson
ADyson

Reputation: 61784

The problem is that your rows don't have a property called "headerProperty". I think you are wanting to use the value inside headerProperty[j] as a dynamic property name?

For that you have to use "bracket notation" to write the property accessor - this allows you to use any string value as the property name at runtime:

data.root.row[i][propertyHeader[j]]

See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Property_accessors for a bit more info.

Demo - I hope this now outputs what you expected:

var value = '{"root":{"row":[{"DatabaseID":"21","fileid":"1","databaseName":"AutomationPortal","FileLogicalName":"AutomationPortal","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomationPortal.mdf","FileSizeMB":"100.00","SpaceUsedMB":"10.25","MaxfileSizeMB":"-0.01","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"},{"DatabaseID":"21","fileid":"3","databaseName":"AutomationPortal","FileLogicalName":"AutomatioPortal_01","FileFullPath":"D:\\\\MSSQL13.MSSQLSERVER\\\\MSSQL\\\\DATA\\\\AutomatioPortal_01.ndf","FileSizeMB":"100.00","SpaceUsedMB":"0.06","MaxfileSizeMB":"130.00","SPaceOnVolumeMB":"95110.38","AutogrowSetting":"8192"}]}}'
var data = JSON.parse(value);

var tablearray = [];
tablearray.push("<table><tr>")
var queryRow = data.root.row.length;

var headerProperty = Object.keys(data.root.row[0]);

for (i = 0; i < headerProperty.length; i++) {
  tablearray.push("<th>" + headerProperty[i] + "</th>");
}
tablearray.push("</tr>");
//console.log(tablearray);

for (i = 0; i < queryRow; i++) {
  tablearray.push("<tr>")
  for (j = 0; j < headerProperty.length; j++) {
    // console.log(headerProperty[j]);
    // console.log(data.root.row[0].DatabaseID);
    // console.log(data.root.row[i].headerProperty[j]);
    tablearray.push("<td>" + data.root.row[i][headerProperty[j]] + "</td>");
  }
  tablearray.push("</tr>");
}
tablearray.push("</table>");
document.write(tablearray.join(''));

Upvotes: 1

P.B.UDAY
P.B.UDAY

Reputation: 483

There is no headerProperty available inside the data.root.row[0]

Upvotes: 1

Related Questions