There may be times when you need to generate an Excel file from your Node.js application. You may need get data from a database or a web service, then output 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 at the same time and doesn’t depend on threads like most other models.

In this tutorial, we'll use Spread.Sheets to gather information entered by the user and automatically export it to an Excel file -- all in your Node.js application. With the power of Spread.Sheets, performance is not affected whether using SpreadJS by itself or with Node.js.

The sample zip for this project can be found here.

Generate an Excel file from your Node.js application

Getting started with SpreadJS and Node.js

To start off, we'll need to install Node.js and the Mock-Browser, BufferJS, and FileReader, each of which can be found at these links:

Installing Node.js via Package Manager

Mock-Browser

BufferJS

FileReader

We'll be using Visual Studio to create the application. Once Visual Studio is open, create a new application using the JavaScript>Node.js>Blank Node.js Console Application template. This will automatically create the required files and open up the “app.js” file, which is the only file we'll be changing.

For the BufferJS library, you'll need to download that package and then install it manually in your project by navigating to the project folder (once it is created) and running the following command:

npm install

Once you have that installed, you may need to open up the package.json file of the project and add it to the "dependencies" section. The file contents should look like the following:

<pre><code class="language-javascript">
{
  "name": "spread-sheets-node-jsapp",
  "version": "0.0.0",
  "description": "SpreadSheetsNodeJSApp",
  "main": "app.js",
  "author": {
    "name": "admin"
  },
  "dependencies": {
    "FileReader": "^0.10.2",
    "bufferjs": "1.0.0",
    "mock-browser": "^0.92.14"
  }
}
</code></pre>

In this sample, we'll use the File System Module of Node.js. We can load that in:

<pre><code class="language-javascript">
var fs = require('fs')
</code></pre>

In order to use SpreadJS with Node.js, we can load the Mock-Browser that we installed:

<pre><code class="language-javascript">
var mockBrowser = require('mock-browser').mocks.MockBrowser
</code></pre>

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:

<pre><code class="language-javascript">
global.window = mockBrowser.createWindow()
global.document = window.document
global.navigator = window.navigator
global.HTMLCollection = window.HTMLCollection
global.getComputedStyle = window.getComputedStyle
</code></pre>

Initialize the FileReader library:

<pre><code class="language-javascript">
var fileReader = require('filereader');
global.FileReader = fileReader;
</code></pre>

Using the SpreadJS npm package

The SpreadJS Sheets and ExcelIO packages will need to be added to the project. You can add these to your project by right-clicking on the "npm" section of the Solution Explorer and select "Install New NPM Packages." You should be able to search for "GrapeCity" and install the following 2 packages:

@grapecity/spread-sheets

@grapectiy/spread-excelio

Once the SpreadJS npm packages have been added to the project, the package.json should be automatically uploaded with the correct dependencies:

<pre><code class="language-javascript">
{
  "name": "spread-sheets-node-jsapp",
  "version": "0.0.0",
  "description": "SpreadSheetsNodeJSApp",
  "main": "app.js",
  "author": {
    "name": "admin"
  },
  "dependencies": {
    "@grapecity/spread-excelio": "^11.2.1",
    "@grapecity/spread-sheets": "^11.2.1",
    "FileReader": "^0.10.2",
    "bufferjs": "1.0.0",
    "mock-browser": "^0.92.14"
  }
}
</code></pre>

Now we'll need to require that in the app.js file:

<pre><code class="language-javascript">
var GC = require('@grapecity/spread-sheets')
var GCExcel = require('@grapecity/spread-excelio');
</code></pre>

When using the npm package, the license key also needs to be set:

<pre><code class="language-javascript">
GC.Spread.Sheets.LicenseKey = "<YOUR KEY HERE>"
</code></pre>

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:

<pre><code class="language-javascript">
var packageJson = require('./package.json')
console.log('\n** Using Spreadjs Version "' + packageJson.dependencies["@grapecity/spread-sheets"] + '" **')
</code></pre>

Loading the Excel file

We'll load 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 and Excel IO variable:

<pre><code class="language-javascript">
var wb = new GC.Spread.Sheets.Workbook();
var excelIO = new GCExcel.IO();
</code></pre>

Let’s wrap our code in a try/catch block as we read in the file. Then we can initialize the variable “readline” – which is essentially a library that allows you to read data that the user inputs into the console. Next, we'll store into a JavaScript array that we can use to easily fill out the Excel file:

<pre><code class="language-javascript">
// Instantiate the spreadsheet and modify it
console.log('\nManipulating Spreadsheet\n---');
try {
    var file = fs.readFileSync('./content/billingInvoiceTemplate.xlsx');
    excelIO.open(file.buffer, (data) => {
        wb.fromJSON(data);
        const readline = require('readline');

        var invoice = {
            generalInfo: [],
            invoiceItems: [],
            companyDetails: []
        };
    });
} catch (e) {
    console.error("** Error manipulating spreadsheet **");
    console.error(e);
}
</code></pre>

Gathering user input

Generate an Excel Spreadsheet from your Node.js Application

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 excelio.open 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:

<pre><code class="language-javascript">
fillGeneralInformation();

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();
                                        });
                                    });
                                });
                            });
                        });
                    });
                });
            });
        });
    });
}
</code></pre>

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:

<pre><code class="language-javascript">
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();
                                                                        });
                                                                    });
                                                                });
                                                            });
                                                        });
                                                    });
                                                });
                                            });
                                        });
                                    });
                                });
                            });
                        });
                    });
                });
            });
        });
    });
}
</code></pre>

Generate an Excel Spreadsheet from your Node.js Application

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":

<pre><code class="language-javascript">
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();
                return fillExcelFile();
                break;
            default:
                console.log("Incorrect option, Please enter 'y' or 'n'.");
        }
    });
}
</code></pre>

Filling Out your Excel file

After all the required invoice information has been gathered, 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:

<pre><code class="language-javascript">
function fillExcelFile() {
    console.log("-----------------------\nFilling in Excel file\n-----------------------");

    fillBillingInfo();
    fillCompanySetup();
}

function fillBillingInfo() {
    var sheet = wb.getSheet(0);
    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 = wb.getSheet(1);
    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);
}
</code></pre>

The template we are using has a specific number of rows laid out for the items in the invoice. It is possible that the user will 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:

<pre><code class="language-javascript">
function fillInvoiceItems() {
    var sheet = wb.getSheet(0);
    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++;
        }
    }
}
</code></pre>

Exporting to Excel

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 excelio open function. In this case, just put the date in the filename:


<pre><code class="language-javascript">
function exportExcelFile() {
    excelIO.save(wb.toJSON(), (data) => {
        fs.appendFileSync('Invoice' + new Date().valueOf() + '.xlsx', new Buffer(data), function (err) {
            console.log(err);
        });
        console.log("Export success");
    }, (err) => {
        console.log(err);
    }, { useArrayBuffer: true });
}
</code></pre>

You can export your workbook to an Excel file with the above code snippet. Your completed file will look like this:

Generate an Excel Spreadsheet from your Node.js Application

Utilizing SpreadJS in conjunction with Node.js demonstrates another example of the versatility and extensibility of SpreadJS! For more articles like this, demos, videos, and tutorials, check out our blog page. If you have any questions, feedback, or demo requests, please leave a comment below!

Use SpreadJS in conjunction with Node.js

Download the latest version of SpreadJS

Download Now!