Node.js provides a powerful platform for creating various types of files, including Excel (xlsx) files, which are commonly used for reporting and data analysis. By leveraging certain libraries, we can automate the process of generating well-structured Excel documents. This tutorial will guide you through the process step by step.
Getting Started
Before diving into code examples, let’s set up our Node.js environment and install the necessary library for handling Excel files.
const { writeFile } = require('fs').promises;
const ExcelJS = require('exceljs');
async function generateBasicExcel(){
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 30 },
{ header: 'Email', key: 'email', width: 30 }
];
worksheet.addRow({id: 1, name: 'John Doe', email: '[email protected]'});
await workbook.xlsx.writeFile('basic.xlsx');
console.log(`basic.xlsx file generated successfully!`);
}
generateBasicExcel();
Adding Styles and Formats
Customizing the workbook with styles and formats enhances the readability and appearance of the data within the Excel file.
// ... [Code shortened for brevity]
const headerFont = { name: 'Calibri', family: 4, size: 12, bold: true };
worksheet.getRow(1).font = headerFont;
// Apply styles to columns
worksheet.getColumn(2).numFmt = '@'; // Text format
worksheet.getColumn(3).numFmt = '0.00'; // Two decimal places
// ...Working with Multiple Sheets and Complex Data
Dealing with datasets often requires handling multiple sheets and complex data arrangements.
// ... [Code shortened for brevity]
function addNewSheet(workbook, sheetName){
const newSheet = workbook.addWorksheet(sheetName);
// Configure the sheet as needed
// ...
}
// Use the function as follows
addNewSheet(workbook, 'Second Sheet');
// ...Data Visualization with Charts
To give a visual touch to the data, Excel charts can be programmatically added to the sheets.
// ... [Code shortened for brevity]
const chart = worksheet.addChart('pie', {
title: {
text: 'Sample Pie Chart'
},
series: [
{
name: 'Pie Series 1',
labels: ['Category 1', 'Category 2', 'Category 3'],
dataFormula: 'Sheet1!$B$1:$B$3'
}
],
legend: {
position: 'right'
}
});
// Set the chart position
worksheet.addDrawing(chart);
// ...Conclusion
By following the steps above and experimenting with different features offered by Node.js libraries, you can generate sophisticated Excel files tailored to your specific needs. The possibilities for automation, formatting, and data presentation are extensive with Node.js and Excel files.
