JavaScript – Training By Himanshu

✅ What is JSON and Why Convert It to Excel?

JSON (JavaScript Object Notation) is a lightweight format to store and exchange data, commonly used in web APIs.
Excel (.xlsx) is a widely used spreadsheet format that allows users to view, analyze, and share data easily.

Converting JSON to Excel allows users to:

View JSON data in a readable tabular format.
Perform data analysis or reporting in Excel.
Download data from a web app for offline use.

🛠 Tool Required: SheetJS (`xlsx` Library)

SheetJS is the most popular JavaScript library for working with Excel files. It works in both browser and Node.js environments.

GitHub: https://github.com/SheetJS/sheetjs
CDN: https://cdnjs.com/libraries/xlsx

💻 1. Using SheetJS in Browser (Client-Side)

Step 1: Include the Library

Add this <script> tag in your HTML file:

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>

Step 2: Define Your JSON Data

This is the data you want to convert to Excel:

const jsonData = [
  { Name: "John", Age: 30, City: "New York" },
  { Name: "Anna", Age: 22, City: "London" },
  { Name: "Mike", Age: 32, City: "Chicago" }
];

Step 3: Create Button & Download Function

<button onclick="downloadExcel()">Download Excel</button>

<script>
function downloadExcel() {
  // Step 1: Convert JSON to Sheet
  const worksheet = XLSX.utils.json_to_sheet(jsonData);
  
  // Step 2: Create a new Workbook
  const workbook = XLSX.utils.book_new();
  
  // Step 3: Append Sheet to Workbook
  XLSX.utils.book_append_sheet(workbook, worksheet, "MyData");
  
  // Step 4: Export Workbook to Excel File
  XLSX.writeFile(workbook, "myData.xlsx");
}
</script>

👉 When the button is clicked, an Excel file named myData.xlsx will be downloaded.

📦 2. Using SheetJS in Node.js (Server-Side)

Step 1: Install the Library

npm install xlsx

Step 2: Write the JavaScript Code

const XLSX = require("xlsx");

// Sample JSON data
const jsonData = [
  { Name: "John", Age: 30, City: "New York" },
  { Name: "Anna", Age: 22, City: "London" },
  { Name: "Mike", Age: 32, City: "Chicago" }
];

// Step 1: Convert JSON to Sheet
const worksheet = XLSX.utils.json_to_sheet(jsonData);

// Step 2: Create a new Workbook
const workbook = XLSX.utils.book_new();

// Step 3: Append the Sheet
XLSX.utils.book_append_sheet(workbook, worksheet, "MyData");

// Step 4: Write Excel File to Disk
XLSX.writeFile(workbook, "output.xlsx");

console.log("Excel file created successfully!");

👉 Run this file using node filename.js. You’ll get output.xlsx in the project folder.

🎨 Optional: Customize Your Output

1. Set Custom Column Order

XLSX.utils.json_to_sheet(jsonData, {
  header: ["Name", "City", "Age"]  // custom column order
});

2. Add Column Titles (Headers)

// Already done by default from JSON keys

3. Format Dates or Numbers

You can format data before passing it to SheetJS.
For advanced styling (colors, fonts, borders), use xlsx-style fork or export CSV.

📁 Extra Tip: Convert to CSV Instead of Excel

If you want a .csv file instead:

const csv = XLSX.utils.sheet_to_csv(worksheet);
fs.writeFileSync("output.csv", csv);

✅ Summary

Task	SheetJS Function
Convert JSON to Sheet	`XLSX.utils.json_to_sheet()`
Create Workbook	`XLSX.utils.book_new()`
Add Sheet to Book	`XLSX.utils.book_append_sheet()`
Export Excel File	`XLSX.writeFile()`