How to Import and Export Excel XLSX Using Node.js
What You Will Need
• SpreadJS
• Visual Studio 2022
• NPM
Controls Referenced
• SpreadJS
Tutorial Concept
Node.js Excel Import/Export — Add the functionality to import and export Excel XLSX documents in your Node.js application.
There may be times when you need to generate an Excel file from your Node.js application. You may need to get data from a database or a web service, then output it to an Excel file for further reporting or analysis. SpreadJS makes this possible without any Excel requirements on the server.
Node.js is a popular event-driven JavaScript runtime that is typically used in creating network applications. It can handle multiple connections simultaneously and doesn’t depend on threads like most other models.
In this tutorial, learn how to use SpreadJS to gather information entered by the user and automatically export it to an Excel file — all in your Node.js application.
- Get Started with SpreadJS and Node.js
- Use the SpreadJS npm Package
- Read an Excel File In Your Node.js Application
- Gather User Input
- Fill Out Your Excel File
- Export Node.js Out to Excel
With the power of SpreadJS, performance is not affected whether using SpreadJS by itself or with Node.js.
Download the sample for this project to follow along.
Get Started with SpreadJS Spreadsheets and Node.js
To begin, we’ll need to install Node.js and the Mock-Browser, Canvas, and FileReader.
While you can use most IDEs to create this application, we’ll use Visual Studio 2019 in this blog. Once Visual Studio is open, create a new application using Create a new project, and then search for “Blank Node.js Console Application”. Please give it a name and specify a location to create the project.
This will automatically create the required files and open up the “app.js” file, which is the only file we’ll be changing.
To install the packages in the project, right-click the “npm” header in the Solution Explorer, click Install New npm Packages and search and install each package for “Mock-Browser,” “Canvas,” and “FileReader.”
Once you have that installed, the dependencies should update in the package.json file:
{
"name": "spread-jsnode-jsio",
"version": "0.0.0",
"description": "SpreadJSNodeJSIO",
"main": "app.js",
"author": {
"name": ""
},
"devDependencies": {
"eslint": "^8.21.0"
},
"eslintConfig": {},
"dependencies": {
"canvas": "^2.11.2",
"filereader": "^0.10.3",
"mock-browser": "^0.92.14"
}
}In this sample, we’ll use the File System Module of Node.js. We can load that in:
var fs = require('fs');To use SpreadJS with Node.js, we can load the Mock-Browser that we installed:
var mockBrowser = require('mock-browser').mocks.MockBrowser;We also need to load the Canvas:
var canvas = require('canvas');And, finally, the FileReader library:
var fileReader = require('filereader');Before loading the SpreadJS script, we’ll need to initialize the mock-browser. Initialize the variables that we may need to use later in the application, particularly the “window” variable:
global.window = mockBrowser.createWindow();
global.document = window.document;
global.navigator = window.navigator;
global.HTMLCollection = window.HTMLCollection;
global.getComputedStyle = window.getComputedStyle;
global.customElements = null;
global.FileReader = fileReader;
global.canvas = canvas;
global.HTMLElement = window.HTMLElement;
global.HTMLDivElement = window.HTMLDivElement;Use the SpreadJS npm Package
The SpreadJS and ExcelIO packages will need to be added to the project. You can add these to your project by right-clicking the “npm” section of the Solution Explorer and selecting Install New npm Packages. You should be able to search for “MESCIUS” and install the following two packages:
@mescius/spread-sheets
@mescius/spread-sheets-io
Once the SpreadJS npm packages have been added to the project, the package.json should be automatically uploaded with the correct dependencies:
{
"name": "spread-jsnode-jsio",
"version": "0.0.0",
"description": "SpreadJSNodeJSIO",
"main": "app.js",
"author": {
"name": ""
},
"devDependencies": {
"eslint": "^8.21.0"
},
"eslintConfig": {},
"dependencies": {
"@mescius/spread-sheets": "^17.1.5",
"@mescius/spread-sheets-io": "^17.1.5",
"canvas": "^2.11.2",
"filereader": "^0.10.3",
"mock-browser": "^0.92.14"
}
}Now we’ll need to require that in the app.js file:
var MC = require('@mescius/spread-sheets');
var IO = require('@mescius/spread-sheets-io');When using the npm package, the license key also needs to be set for both:
MC.Spread.Sheets.LicenseKey = "<YOUR KEY HERE>";In this particular application, we’ll show the user which version of SpreadJS they are using. To do this, we can require the package.json file and then reference the dependency to get the version number:
var packageJson = require('./package.json');
console.log('\n** Using SpreadJS Version "' + packageJson.dependencies["@mescius/spread-sheets"] + '" **');Read the Excel File Into Your Node.js Application
We’ll read in an existing Excel template file, getting data from the user. Next, place the data into the file and export it. In this case, the file is an invoice that the user can edit.
Start by initializing the workbook variable, variables for the sheet index and invoice, and “readline,” which is essentially a library that allows you to read data that the user inputs into the console:
var spread = new GC.Spread.Sheets.Workbook();
var billingInvoiceSheetIndex = 0;
var companySetupSheetIndex = 1;
const readline = require('readline');
var invoice = {
generalInfo: [],
invoiceItems: [],
companyDetails: []
};Let’s wrap our code in a try/catch block as we read in the file. We will use the FileSystem library to read in the file and then the SpreadJS Workbook.import method to read that file into SpreadJS:
// Instantiate the spreadsheet and modify it
console.log('\nManipulating Spreadsheet\n---');
try {
var file = fs.readFileSync('./content/billingInvoiceTemplate.xlsx');
spread.import({ name: 'billingInvoiceTemplate.xlsx', buffer: file }, function () {
// Code to fill in the file here
}, function (e) { console.log(e) }, { fileType: MC.Spread.Sheets.FileType.excel });
} catch (e) {
console.error("** Error manipulating spreadsheet **");
console.error(e);
}Gather User Input
The above image shows the Excel file we are using. The first information we want to gather is the general invoice information. We can make a separate function within the workbook.import call to prompt the user in the console for each item that we’ll need.
We can create a separate array to save the data to after each input, then when we have all of the input for that section. Push it to the invoice.generalInfo array that we created:
function fillGeneralInformation() {
console.log("-----------------------\nFill in Invoice Details\n-----------------------")
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
var generalInfoArray = [];
rl.question('Invoice Number: ', (answer) => {
generalInfoArray.push(answer);
rl.question('Invoice Date (dd Month Year): ', (answer) => {
generalInfoArray.push(answer);
rl.question('Payment Due Date (dd Month Year): ', (answer) => {
generalInfoArray.push(answer);
rl.question('Customer Name: ', (answer) => {
generalInfoArray.push(answer);
rl.question('Customer Company Name: ', (answer) => {
generalInfoArray.push(answer);
rl.question('Customer Street Address: ', (answer) => {
generalInfoArray.push(answer);
rl.question('Customer City, State, Zip (<City>, <State Abbr> <Zip>): ', (answer) => {
generalInfoArray.push(answer);
rl.question('Invoice Company Name: ', (answer) => {
generalInfoArray.push(answer);
rl.question('Invoice Street Address: ', (answer) => {
generalInfoArray.push(answer);
rl.question('Invoice City, State, Zip (<City>, <State Abbr> <Zip>): ', (answer) => {
generalInfoArray.push(answer);
rl.close();
invoice.generalInfo.push({
"invoiceNumber": generalInfoArray[0],
"invoiceDate": generalInfoArray[1],
"paymentDueDate": generalInfoArray[2],
"customerName": generalInfoArray[3],
"customerCompanyName": generalInfoArray[4],
"customerStreetAddress": generalInfoArray[5],
"customerCityStateZip": generalInfoArray[6],
"invoiceCompanyName": generalInfoArray[7],
"invoiceStreetAddress": generalInfoArray[8],
"invoiceCityStateZip": generalInfoArray[9],
});
console.log("General Invoice Information Stored");
fillCompanyDetails();
});
});
});
});
});
});
});
});
});
});
}Within that function, we call fillCompanyDetails. We’ll gather information about the company to fill into the second sheet of the workbook. The function will be very similar to the previous function:
function fillCompanyDetails() {
console.log("-----------------------\nFill in Company Details\n-----------------------");
const rl = readline.createInterface({ input: process.stdin, output: process.stdout });
var companyDetailsArray = []
rl.question('Your Name: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Company Name: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Address Line 1: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Address Line 2: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Address Line 3: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Address Line 4: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Address Line 5: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Phone: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Facsimile: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Website: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Email: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Currency Abbreviation: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Beneficiary: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Bank: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Bank Address: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Account Number: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Routing Number: ', (answer) => {
companyDetailsArray.push(answer);
rl.question('Make Checks Payable To: ', (answer) => {
companyDetailsArray.push(answer); rl.close();
invoice.companyDetails.push({ "yourName": companyDetailsArray[0], "companyName": companyDetailsArray[1], "addressLine1": companyDetailsArray[2], "addressLine2": companyDetailsArray[3], "addressLine3": companyDetailsArray[4], "addressLine4": companyDetailsArray[5], "addressLine5": companyDetailsArray[6], "phone": companyDetailsArray[7], "facsimile": companyDetailsArray[8], "website": companyDetailsArray[9], "email": companyDetailsArray[10], "currencyAbbreviation": companyDetailsArray[11], "beneficiary": companyDetailsArray[12], "bank": companyDetailsArray[13], "bankAddress": companyDetailsArray[14], "accountNumber": companyDetailsArray[15], "routingNumber": companyDetailsArray[16], "payableTo": companyDetailsArray[17] });
console.log("Invoice Company Information Stored");
console.log("-----------------------\nFill in Invoice Items\n-----------------------");
fillInvoiceItemsInformation();
});
});
});
});
});
});
});
});
});
});
});
});
});
});
});
});
});
});
}Now that we have the basic information for the invoice, we can focus on gathering the individual invoice items, which we’ll do in another function called fillInvoiceItemsInformation. Before each item, we’ll ask the user if they would like to add an item. If they keep entering “y”, then we’ll gather that item’s information, then ask again until they type “n”:
function fillInvoiceItemsInformation() {
const rl = readline.createInterface({
input: process.stdin, output: process.stdout
});
var invoiceItemArray = [];
rl.question('Add item?(y/n): ', (answer) => {
switch (answer) {
case "y": console.log("-----------------------\nEnter Item Information\n-----------------------");
rl.question('Quantity: ', (answer) => {
invoiceItemArray.push(answer);
rl.question('Details: ', (answer) => {
invoiceItemArray.push(answer);
rl.question('Unit Price: ', (answer) => {
invoiceItemArray.push(answer);
invoice.invoiceItems.push({
"quantity": invoiceItemArray[0], "details": invoiceItemArray[1], "unitPrice": invoiceItemArray[2]
});
console.log("Item Information Added");
rl.close();
fillInvoiceItemsInformation();
});
});
});
break;
case "n": rl.close();
fillExcelFile();
break;
default: console.log("Incorrect option, Please enter 'y' or 'n'.");
}
});
}Add Data to the Excel Spreadsheet
After gathering all the required invoice information, we can fill out the Excel file. For the billing information and company setup, we can manually set each value in the cell from the JavaScript array:
function fillExcelFile() {
console.log("-----------------------\nFilling in Excel file\n-----------------------");
fillBillingInfo();
fillCompanySetup();
}
function fillBillingInfo() {
var sheet = spread.getSheet(billingInvoiceSheetIndex);
sheet.getCell(0, 2).value(invoice.generalInfo[0].invoiceNumber);
sheet.getCell(1, 1).value(invoice.generalInfo[0].invoiceDate);
sheet.getCell(2, 2).value(invoice.generalInfo[0].paymentDueDate);
sheet.getCell(3, 1).value(invoice.generalInfo[0].customerName);
sheet.getCell(4, 1).value(invoice.generalInfo[0].customerCompanyName);
sheet.getCell(5, 1).value(invoice.generalInfo[0].customerStreetAddress);
sheet.getCell(6, 1).value(invoice.generalInfo[0].customerCityStateZip);
sheet.getCell(3, 3).value(invoice.generalInfo[0].invoiceCompanyName);
sheet.getCell(4, 3).value(invoice.generalInfo[0].invoiceStreetAddress);
sheet.getCell(5, 3).value(invoice.generalInfo[0].invoiceCityStateZip);
}
function fillCompanySetup() {
var sheet = spread.getSheet(companySetupSheetIndex);
sheet.getCell(2, 2).value(invoice.companyDetails[0].yourName);
sheet.getCell(3, 2).value(invoice.companyDetails[0].companyName);
sheet.getCell(4, 2).value(invoice.companyDetails[0].addressLine1);
sheet.getCell(5, 2).value(invoice.companyDetails[0].addressLine2);
sheet.getCell(6, 2).value(invoice.companyDetails[0].addressLine3);
sheet.getCell(7, 2).value(invoice.companyDetails[0].addressLine4);
sheet.getCell(8, 2).value(invoice.companyDetails[0].addressLine5);
sheet.getCell(9, 2).value(invoice.companyDetails[0].phone);
sheet.getCell(10, 2).value(invoice.companyDetails[0].facsimile);
sheet.getCell(11, 2).value(invoice.companyDetails[0].website);
sheet.getCell(12, 2).value(invoice.companyDetails[0].email);
sheet.getCell(13, 2).value(invoice.companyDetails[0].currencyAbbreviation);
sheet.getCell(14, 2).value(invoice.companyDetails[0].beneficiary);
sheet.getCell(15, 2).value(invoice.companyDetails[0].bank);
sheet.getCell(16, 2).value(invoice.companyDetails[0].bankAddress);
sheet.getCell(17, 2).value(invoice.companyDetails[0].accountNumber);
sheet.getCell(18, 2).value(invoice.companyDetails[0].routingNumber);
sheet.getCell(19, 2).value(invoice.companyDetails[0].payableTo);
}The template we are using has a specific number of rows laid out for the items in the invoice. The user may add more than the max. In this case, we can simply add more rows to the sheet. We’ll add the rows before setting the items in the sheet from the array:
function fillInvoiceItems() {
var sheet = spread.getSheet(billingInvoiceSheetIndex);
var rowsToAdd = 0;
if (invoice.invoiceItems.length > 15) {
rowsToAdd = invoice.invoiceItems.length - 15;
sheet.addRows(22, rowsToAdd);
}
var rowIndex = 8;
if (invoice.invoiceItems.length >= 1) {
for (var i = 0; i < invoice.invoiceItems.length; i++) {
sheet.getCell(rowIndex, 1).value(invoice.invoiceItems[i].quantity);
sheet.getCell(rowIndex, 2).value(invoice.invoiceItems[i].details);
sheet.getCell(rowIndex, 3).value(invoice.invoiceItems[i].unitPrice);
rowIndex++;
}
}
}Export Node.js Out to an Excel XLSX Spreadsheet
After the information has been filled out in the workbook, we can export the workbook to an Excel file. To do this, we’ll use the workbook.export function. In this case, just put the date in the filename:
function exportExcelFile() {
spread.export((blob) => {
saveBlobToFile(blob, 'Invoice' + new Date().valueOf() + '.xlsx');
async function saveBlobToFile(blob, filePath) {
const arrayBuffer = await blob.arrayBuffer();
const buffer = Buffer.from(arrayBuffer);
fs.writeFile(filePath, buffer, (err) => {
if (err) {
console.error('Error saving blob:', err);
} else {
console.log('File saved successfully to:', filePath);
}
});
}
console.log("Export success");
}, (e) => {
console.log(e);
}, { fileType: MC.Spread.Sheets.FileType.excel });
}You can export your workbook to an Excel file with the above code snippet. Your completed file will look like this:
Utilizing SpreadJS in conjunction with Node.js demonstrates another example of the versatility and extensibility of SpreadJS! Check out our blog page for more articles like this, demos, videos, and tutorials. Be sure to also check out our demos and documentation.
Learn more about this JavaScript Spreadsheet Component:
If you have any questions, feedback, or demo requests, please comment below!
Originally published at https://www.developer.mescius.com on September 20, 2024.
