In this article we will learn How to convert JSON to Html table, the schema is coming to SQL or MySQL by using key/property from JSON we will create dynamic HTML a table
Introduction:
JSON schema structure is a collection of key/property, JSON stands for “JavaScript Object Notation” it is a simple data interchange format.
The requirement for converting JSON to HTML Table
- Create a table in your database and return the entire table format as JSON file
- implementing a javascript function for creating Dynamic table from a JSON object
Step 1: Create a table in your database and return the entire table format as JSON file
Open your SQL server or my SQL server create a table in your database name like Customers and dump some dummy data in your table
If you are using SQL server executes the given below SQL query
CREATE TABLE [dbo].[Customer]( [Id] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](40) NOT NULL, [LastName] [nvarchar](40) NOT NULL, [City] [nvarchar](40) NULL, [Country] [nvarchar](40) NULL, [Phone] [nvarchar](20) NULL, [Message] [nvarchar](200) NULL )
Create one stored procedure and return table data as a JSON format by using given below SQL query
Create proc [dbo].[tablefields](@tablename nvarchar(30))
as
begin
declare @tablfields nvarchar(MAX);
set @tablfields=('select *from '[email protected]+' FOR JSON AUTO')
exec(@tablfields);
end
GO
Execute the above procedure by passing the table name as parameter
Exec [dbo].[tablefields] @tablename='Customer'
It will return clear format of JSON schema see given JSON format
[
{
"Id": 1,
"FirstName": "Maria",
"LastName": "Anders",
"City": "Berlin",
"Country": "Germany",
"Phone": "030-0074321"
},
{
"Id": 2,
"FirstName": "Ana",
"LastName": "Trujillo",
"City": "México D.F.",
"Country": "Mexico",
"Phone": "(5) 555-4729"
},
{
"Id": 3,
"FirstName": "Antonio",
"LastName": "Moreno",
"City": "México D.F.",
"Country": "Mexico",
"Phone": "(5) 555-3932"
},
{
"Id": 4,
"FirstName": "Thomas",
"LastName": "Hardy",
"City": "London",
"Country": "UK",
"Phone": "(171) 555-7788"
},
{
"Id": 5,
"FirstName": "Christina",
"LastName": "Berglund",
"City": "Luleå",
"Country": "Sweden",
"Phone": "0921-12 34 65"
},
{
"Id": 6,
"FirstName": "Hanna",
"LastName": "Moos",
"City": "Mannheim",
"Country": "Germany",
"Phone": "0621-08460"
},
{
"Id": 7,
"FirstName": "Frédérique",
"LastName": "Citeaux",
"City": "Strasbourg",
"Country": "France",
"Phone": "88.60.15.31"
},
{
"Id": 8,
"FirstName": "Martín",
"LastName": "Sommer",
"City": "Madrid",
"Country": "Spain",
"Phone": "(91) 555 22 82"
},
{
"Id": 9,
"FirstName": "Laurence",
"LastName": "Lebihan",
"City": "Marseille",
"Country": "France",
"Phone": "91.24.45.40"
}
]
Create an HTML page Add the following Html code in your HTML page
<br /><br />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>l
<script src="assets/DynamicTableJs.js"></script>
<link href="assets/DynamicTableCss.css" rel="stylesheet" />
<div class="container">
<h1><a href="https://tutorialshelper.com/"> Dynamic table from json schema</a></h1>
<div class="tables" id="showData"></div>
</div>
<script>
$(document).ready(function () {
dynamic_table("http://dummy.restapiexample.com/api/v1/employees");
});
</script>
Step2: implementing a javascript function for creating Dynamic table from JSON schema
Add in your project js file and create javascript function name like dynamic_table();
If we want to create a Dynamic table from JSON object must and should be we will read that JSON object by using ajax call
We will pass to dynamic_table() javascript function JSON created URL as a parameter ( controller or directly some dummy test APIs)
After ajax success, we will extract JSON values for HTML table headers then after we will create HTML dynamic table by using JSON Schema
Following the given below implemented javascript function for creating Dynamic HTML table from JSON schema
function dynamic_table(url) {
$.ajax({
url: url,
data: {
format: 'json'
},
error: function () {
$('#info').html('<p>An error has occurred</p>');
},
dataType: 'json',
success: function (data) {
var col = [];
window.cols = col;
for (var i = 0; i < data.length; i++) {
for (var key in data[i]) {
if (col.indexOf(key) === -1) {
col.push(key);
}
}
}
col.push("Actons");
var table = document.createElement("table");
table.setAttribute('class', 'table blueTable');
var tr = table.insertRow(-1);
for (var i = 0; i < col.length; i++) {
var th = document.createElement("th");
th.innerHTML = col[i];
tr.appendChild(th);
}
for (var i = 0; i < data.length; i++) {
tr = table.insertRow(-1);
for (var j = 0; j < col.length; j++) {
var tabCell = tr.insertCell(-1);
tabCell.innerHTML = data[i][col[j]];
}
var d = data[i].id;
tabCell.innerHTML = '<a href="#" onclick="return EditTable(' + data[i].id + ' )">Edit</a> | <a href="#" onclick="DeleteTable(' + data[i].id + ')">Delete</a></td>'
}
var divContainer = document.getElementById("showData");
divContainer.innerHTML = "";
divContainer.appendChild(table);
jQuery('.tables').before('<input type="submit" onclick="return CreateDynamicModelPopup();" class="addme" value="Add New!"></div>');
},
type: 'GET'
});
};
table CSS add given below CSS file in your project
.col-sm-12.modeldata {
padding-top: 19px;
padding-bottom: 7px;
}
.addme {
margin-bottom: 11px;
background-color: #d0e4f5;
border: 0px solid #ccc;
padding: 12px;
box-shadow: 0 0 1px black;
}
.modal-body {
padding: 0px !important;
}
.modal-header {
background: #a2c1dc;
}
table.blueTable {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 100%;
text-align: left;
border-collapse: collapse;
}
table.blueTable td, table.blueTable th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table.blueTable tbody td {
font-size: 13px;
}
table.blueTable tr:nth-child(even) {
background: #D0E4F5;
}
table.blueTable thead {
background: #1C6EA4;
background: -moz-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
background: -webkit-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
background: linear-gradient(to bottom, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
border-bottom: 2px solid #444444;
}
table.blueTable thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table.blueTable thead th:first-child {
border-left: none;
}
table.blueTable tfoot {
font-size: 14px;
font-weight: bold;
color: #FFFFFF;
background: #D0E4F5;
background: -moz-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
background: -webkit-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
background: linear-gradient(to bottom, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
border-top: 2px solid #444444;
}
table.blueTable tfoot td {
font-size: 14px;
}
table.blueTable tfoot .links {
text-align: right;
}
table.blueTable tfoot .links a {
display: inline-block;
background: #1C6EA4;
color: #FFFFFF;
padding: 2px 8px;
border-radius: 5px;
}
Try run your HTML page …
Tips and Tricks:
If you are using APIs in your project you will pass API to javascript function as a parameter, Instead of creating SQL table and return JSON simple you will use some test APIs also
Rest Apis for text javascript function :
<script>
$(document).ready(function () {
dynamic_table("http://dummy.restapiexample.com/api/v1/employees");
//dynamic_table("https://jsonplaceholder.typicode.com/posts");
});
</script>
output:
recommended the previous article for more details https://tutorialshelper.com/dynamic-ui-from-json-object-using-web-api-with-jquery/
So we have completed convert JSON object to Html table.
Summary:
In this article, we have seen How to convert JSON to Html table.
Hope you all liked it.
Thanks..
I like to share my work experience, research, and knowledge through my blog.
I love developing the applications in Microsoft technologies including asp.net mvc,c#.net, windfarms, SQL server,entityfrawork,Ajax,Jquery,WEbApi,Asp.net core, SEO, WordPress, UI..more
- Asp.net core 3.1 crud operation with Ado.net - June 9, 2020
- how to upload files in asp.net core using web API - May 27, 2020
- Setup Angular 8in visual studio 2019 - May 26, 2020
its make me easy doing my task thanks nice article