Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Convert JSON to Excel in JavaScript
It is often necessary to change JSON data into Excel sheet, especially when exporting and reporting is concerned, in various web applications. Data is presented and analyzed with the help of the widely spread Excel files, and the transformation of the JSON data to excel enables the users to do analysis and manage data seamlessly. This paper looks at different ways of converting JSON data to Excel files in JavaScript environment through the use of libraries and through other manual means.
Approaches to Convert JSON to Excel in JavaScript
Using the SheetJS (xlsx) library
SheetJS is apparently one of the best libraries of JS that is primarily used for opening and creating excel files through a browser, and Node.js environment. It is compatible with different types of formats including XLSX, CSV and more and this makes it easy to transform JSON data to excel.
- Step 1 - Install SheetJS: You can include it directly via CDN or install it using npm.
// CDN <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script> // NPM npm install xlsx
Example Code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, initial-scale=1.0">
<title>Convert JSON to Excel</title>
<script src=
"https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js">
</script>
</head>
<body>
<button onclick="convertJsonToExcel()">
Download Excel
</button>
<script>
function convertJsonToExcel() {
// JSON data to be converted
const jsonData = [
{ "Name": "Amit Kumar", "Age": 29, "City": "Mumbai" },
{ "Name": "Priya Sharma", "Age": 25, "City": "Delhi" },
{ "Name": "Ravi Patel", "Age": 35, "City": "Ahmedabad" },
{ "Name": "Anjali Verma", "Age": 28, "City": "Pune" }
];
// Convert JSON to worksheet
const worksheet = XLSX.utils.json_to_sheet(jsonData);
// Create a new workbook and add the worksheet
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
// Export the Excel file
XLSX.writeFile(workbook, "data.xlsx");
}
</script>
</body>
</html>
Output
Manual HTML Table Export
If your JSON data is relatively simple, you can manually create an HTML table, convert it to an Excel file and download it. This approach doesn't require any libraries and can be done with pure JavaScript.
- Create an HTML Table: Convert JSON data to an HTML table format.
- Export to Excel: Use data URLs to trigger the download.
Example Code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, initial-scale=1.0">
<title>Convert JSON to Excel with HTML Table</title>
</head>
<body>
<button onclick="downloadExcel()">
Download Excel
</button>
<script>
function downloadExcel() {
// JSON data
const jsonData = [
{ "Name": "Amit Kumar", "Age": 29, "City": "Mumbai" },
{ "Name": "Priya Sharma", "Age": 25, "City": "Delhi" },
{ "Name": "Ravi Patel", "Age": 35, "City": "Ahmedabad" },
{ "Name": "Anjali Verma", "Age": 28, "City": "Pune" }
];
// Create an HTML table from JSON
let table = 'table>tr>';
for (const key in jsonData[0]) {
table += `th>${key}/th>`;
}
table += '/tr>';
jsonData.forEach(row => {
table += 'tr>';
for (const key in row) {
table += `td>${row[key]}/td>`;
}
table += '/tr>';
});
table += '/table>';
// Convert table to data URL and download
const dataUri = 'data:application/vnd.ms-excel,' + encodeURIComponent(table);
const link = document.createElement('a');
link.href = dataUri;
link.download = 'data.xls';
link.click();
}
</script>
</body>
</html>
Output
Exporting JSON to CSV format
CSV is an easier format that is compatible with Excel, this makes it an appropriate option for exporting JSON files. This method includes exporting JSON data, converting it to CSV format, and then initiating a download.
- Convert JSON to CSV: Map JSON data into comma-separated values.
- Download as CSV: Create a Blob and download the file.
Example Code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, initial-scale=1.0">
<title>Convert JSON to CSV</title>
</head>
<body>
<button onclick="convertJsonToCsv()">
Download CSV
</button>
<script>
function convertJsonToCsv() {
// JSON data
const jsonData = [
{ "Name": "Amit Kumar", "Age": 29, "City": "Mumbai" },
{ "Name": "Priya Sharma", "Age": 25, "City": "Delhi" },
{ "Name": "Ravi Patel", "Age": 35, "City": "Ahmedabad" },
{ "Name": "Anjali Verma", "Age": 28, "City": "Pune" }
];
// Convert JSON to CSV
const csvData = jsonData.map(row => Object.values(row).join(',')).join('
');
const csvHeader = Object.keys(jsonData[0]).join(',') + '
';
const csv = csvHeader + csvData;
// Create a Blob from CSV data and download
const blob = new Blob([csv], { type: 'text/csv' });
const url = URL.createObjectURL(blob);
const link = document.createElement('a');
link.href = url;
link.download = 'data.csv';
link.click();
URL.revokeObjectURL(url);
}
</script>
</body>
</html>
Output
2K+ Views
