5 Ways to Create Excel Sheets Using JavaScript

Creating Excel sheets programmatically using JavaScript offers a versatile way to manage, analyze, and share data dynamically without the need for heavy back-end setups. Whether for web applications, data analytics, or seamless data integration, JavaScript excels in offering real-time interaction and manipulation of spreadsheets directly within the browser or server side. Here are five efficient methods to achieve this:

1. Using SheetJS js-xlsx

SheetJS or js-xlsx is a widely used library for reading and writing spreadsheet files. Here's how to utilize it:

• Install SheetJS via npm or by downloading the library from GitHub.
• Create a workbook, add worksheets, and insert data into the cells.
• Write the workbook to an Excel file (.xlsx).

const XLSX = require('xlsx');
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([['A1', 'B1'], ['A2', 'B2']]);
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
XLSX.writeFile(wb, 'MyExcelSheet.xlsx');

💡 Note: This method allows exporting of data to .xlsx format, which is compatible with recent versions of Microsoft Excel, Google Sheets, and other spreadsheet software.

2. Utilizing ExcelJS

ExcelJS is another powerful library for handling Excel files with robust features for cell styling, formulae, and more:

• Install ExcelJS via npm.
• Create a workbook, add worksheets, and format cells as needed.
• Save the workbook to a file or stream.

const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('MySheet');
worksheet.columns = [
  { header: 'ID', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 }
];
worksheet.addRow({id: 1, name: 'Alice'});
workbook.xlsx.writeFile('MyExcelSheet.xlsx');

🛠️ Note: ExcelJS is particularly useful when you need to apply complex formatting or include formulas in your Excel sheets.

3. Using JSON-Excel-Converter

For simpler tasks or when dealing with JSON data, JSON-Excel-Converter can be employed:

• Install via npm or use directly in the browser.
• Transform JSON data into an Excel-compatible format.
• Create a Blob and trigger the download.

let data = [{ “name”: “Alice”, “age”: 25 }, { “name”: “Bob”, “age”: 30 }];
let ws = XLSX.utils.json_to_sheet(data);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, ws, “People”);
const excelBuffer = XLSX.write(workbook, { bookType: ‘xlsx’, type: ‘array’ });
saveAsExcelFile(excelBuffer, ‘people.xlsx’);

4. Server-Side Generation with Node Excel

For web applications requiring server-side Excel generation, node-excel-export is an excellent choice:

• Install node-excel-export.
• Define your headers and data.
• Generate and send the Excel file as a response.

const excel = require(‘exceljs’);

function generateExcel(data) {
  const workbook = new excel.Workbook();
  const worksheet = workbook.addWorksheet(‘MySheet’);
  // Add headers
  worksheet.addRow([‘Name’, ‘Age’, ‘Address’]);
  // Add data
  data.forEach(row => worksheet.addRow(row));

return workbook.xlsx.writeFile(‘output.xlsx’);
}

5. Browser-Based Export with Table-to-Excel

If your application has data in HTML tables, Table-to-Excel can convert this data into an Excel file directly in the browser:

• Include the library in your project.
• Call the export function on your table element.

const TableToExcel = require(‘table-to-excel’);
TableToExcel.download(document.querySelector(‘#your-table-id’), ‘mySpreadsheet.xlsx’);

Summarizing the key points:

• SheetJS for reading, writing, and converting various formats of spreadsheets.
• ExcelJS for more complex Excel tasks including styling and formulas.
• JSON-Excel-Converter for easy JSON to Excel conversion.
• Node Excel for server-side generation to handle large datasets.
• Table-to-Excel for directly converting HTML tables to Excel files.

Can I use JavaScript to create complex Excel documents?

+

Yes, libraries like ExcelJS allow for the creation of complex documents with styles, formulas, and even charts.

Which method is best for simple data exports?

+

JSON-Excel-Converter or SheetJS are great for straightforward JSON to Excel conversion, with the latter being more versatile.

Is server-side or client-side Excel generation better?

+

It depends on your application’s needs. Client-side methods are faster for the user but can be limited by browser capabilities. Server-side is better for large datasets or when more control over the process is needed.