Generate Excel spreadsheets from the server (Node.js+SpreadJS)

Node.js is a JavaScript runtime environment based on the Chrome V8 engine, commonly used to create web applications. It can handle multiple connections simultaneously and doesn't rely on threads like most other models.

For web developers, it happens from time to time that data is fetched from a database or web server and then output to an Excel file for further analysis. In the process of communicating with users from all walks of life at home and abroad, our technical team found that many users tried to run the SpreadJS pure front-end table control With any Excel dependencies installed, user input is collected and automatically exported to an Excel file.

In order to meet the needs of the majority of technology enthusiasts, and at the same time reduce the detours that everyone takes in future technology selection, this article will discuss the technical solution between SpreadJS and Node.js!

1. Install SpreadJS and Node.js

First, we need to install Node.js and Mock-Browser, BufferJS and FileReader. You can go to the following link to download and synchronize:

• Installing Node.js viaPackage Manager
• Mock-Browser
• BufferJS
• FileReader

We will use Visual Studio to create the application. With Visual Studio 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 the "app.js" file, the only file we will be changing.

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

npm install

Once installed, you may need to open your project's package.json file and add it to the "dependencies" section. The file content should look like this:

{
"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"
  }
}

In this example, we will use Node.js' filesystem module. We can load it into:

var fs = require('fs')

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

var mockBrowser =require('mock-browser').mocks.MockBrowser

Before loading the SpreadJS script, we need to initialize the simulated browser. Initialize variables we may need to use later in the application, especially the "window" variable:

global.window =mockBrowser.createWindow()
global.document = window.document
global.navigator = window.navigator
global.HTMLCollection =window.HTMLCollection
global.getComputedStyle =window.getComputedStyle

Initialize the FileReader library:

var fileReader = require('filereader');
global.FileReader = fileReader;

2. Use the SpreadJS npm package

Add the SpreadJS Sheets and ExcelIO packages from the SpreadJS installation file to the project.

You can add them to your project by right-clicking on the "npm" section of the Solution Explorer and selecting "Install New NPM Packages". You should be able to search for "GrapeCity" and install the following 2 packages:

@grapecity/spread-sheets
@grapectiy/spread-excelio

After adding the SpreadJS npm package to the project, the correct dependencies will be written to package.json:

1.    {
2.    "name": "spread-sheets-node-jsapp",
3.    "version": "0.0.0",
4.    "description": "SpreadSheetsNodeJSApp",
5.    "main": "app.js",
6.    "author": {
7.       "name": "admin"
8.    },
9.      "dependencies":{
10.       "@grapecity/spread-excelio": "^11.2.1",
11.       "@grapecity/spread-sheets": "^11.2.1",
12.       "FileReader": "^0.10.2",
13.       "bufferjs": "1.0.0",
14.       "mock-browser": "^0.92.14"
15.      }
16.    }

Now we need to include it in the app.js file:

var GC =require('@grapecity/spread-sheets')
var GCExcel =require('@grapecity/spread-excelio');

When using the npm package, you also need to set up a license key ( Click here to request a free license key ):

GC.Spread.Sheets.LicenseKey ="<YOUR KEY HERE>"

In this particular application, we will show the user which version of SpreadJS they are using. To do this, we can pull in the package.json file and then reference the dependencies to get the version number:

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

3. Load the Excel file into your Node.js application

Click here, download the ready made Excel template file which contains the data obtained from the user. Next, put the data into a file and export it. In this case, the file is in a state that the user can edit.

Initialize workbook and ExcelIO variables:

var wb = new GC.Spread.Sheets.Workbook();
var excelIO = new GCExcel.IO();

We wrap the code in a try/catch block while reading the file. Then, initialize the variable "readline", which lets you read the data the user enters into the console. Next, we store this into a JavaScript array to easily fill out the Excel file:

// Instantiate the spreadsheet and modifyit
console.log('\nManipulatingSpreadsheet\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);
}

4. Collect user input information

The image above shows the Excel file we are working with. We can create a separate function in the excelio.open call to prompt the user in the console for every item they need. We could also create a separate array, save the data after each input, and push it into the invoice.generalInfo array we created:

fillGeneralInformation();
function fillGeneralInformation() {
   console.log("-----------------------\nFill in InvoiceDetails\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 (ddMonth Year): ', (answer) => {
                generalInfoArray.push(answer);
                rl.question('Customer Name: ',(answer) => {
                   generalInfoArray.push(answer);
                    rl.question('CustomerCompany 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();
                                        });
                                    });
                               });
                            });
                        });
                    });
                });
            });
       });
   });
}

The function is called "fillCompanyDetails" and its purpose is to collect information about the company to fill in the second sheet of the workbook:

function fillCompanyDetails() {
   console.log("-----------------------\nFill in CompanyDetails\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 Line3: ', (answer) => {
                       companyDetailsArray.push(answer);
                        rl.question('AddressLine 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('RoutingNumber: ', (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("-----------------------\nFillin Invoice Items\n-----------------------")
                                                                           fillInvoiceItemsInformation();
                                                                        });
                                                                   });
                                                               });
                                                           });
                                                       });
                                                   });
                                               });
                                            });
                                        });
                                    });
                                });
                            });
                        });
                    });
                });
            });
       });
   });
}

Now that we have the user's basic information, we can focus on collecting a single item and call it another "fillInvoiceItemsInformation" function. Before each item executes, we ask the user if they want to add an item. If they keep typing "y", then we'll collect the item's information and 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 ItemInformation\n-----------------------");
                rl.question('Quantity: ',(answer) => {
                   invoiceItemArray.push(answer);
                    rl.question('Details: ',(answer) => {
                       invoiceItemArray.push(answer);
                        rl.question('UnitPrice: ', (answer) => {
                           invoiceItemArray.push(answer);
                           invoice.invoiceItems.push({
                               "quantity":invoiceItemArray[0],
                               "details": invoiceItemArray[1],
                               "unitPrice": invoiceItemArray[2]
                            });
                            console.log("ItemInformation Added");
                            rl.close();
                           fillInvoiceItemsInformation();
                        });
                    });
                });
                break;
            case "n":
               rl.close();
                return fillExcelFile();
                break;
            default:
                console.log("Incorrectoption, Please enter 'y' or 'n'.");
       }
   });
}

5. Fill in your Excel file

After gathering all the required user information, we can populate it into an Excel file:

function fillExcelFile() {
   console.log("-----------------------\nFilling in Excelfile\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);
}

To prevent users from adding more than the maximum number of rows in the sheet, we can automatically add more rows to the sheet. The line is added by default before setting the items in the form in the array:

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.quantity);
            sheet.getCell(rowIndex,2).value(invoice.invoiceItems.details);
            sheet.getCell(rowIndex,3).value(invoice.invoiceItems.unitPrice);
            rowIndex++;
       }
   }
}

6. Export document content from Node.js to Excel file

After filling in the information in the workbook, we can export the workbook to an Excel file. For this, we will use excelio to open the function. In this case, just enter the date into the filename:

function exportExcelFile() {
   excelIO.save(wb.toJSON(), (data) => {
       fs.appendFileSync('Invoice' + new Date().valueOf() + '.xlsx', newBuffer(data), function (err) {
            console.log(err);
       });
       console.log("Export success");
   }, (err) => {
       console.log(err);
   }, { useArrayBuffer: true });
}

The finished file will look like this:

The above is the entire content of the first article "Generating Excel Spreadsheets from the Server (Node.js+SpreadJS)". In order to meet the needs of batch binding data sources and exporting Excel, batch modifying a large number of Excel content and styles, batch printing on the server, and generating PDF documents, we provide a more mature official means: SpreadJS + GcExcel , this solution provides With better performance and stability than Node.js+SpreadJS, this is the main content of our next article "Generating Excel Spreadsheets from the Server (GcExcel + SpreadJS)", so stay tuned.