有时您可能需要在Node.js应用程序生成Excel文件。您可能需要从数据库或Web服务获取数据,然后输出到Excel文件以进行进一步的报告或分析。 SpreadJS可以在服务器端不依赖任何Excel的情况下实现这一点。

Node.js是一种流行的事件驱动的JavaScript运行时环境,通常用于创建Web应用程序。它可以同时处理多个连接,并且不像大多数其他模型那样依赖于线程。

在本教程中,我们将使用Spread JS收集用户输入的信息并自动将其导出到Excel文件 - 所有这些都在Node.js应用程序中。凭借Spread JS的强大功能,无论是单独使用Spread JS还是使用Node.js,性能都不会受到影响。

点击下载项目示例

Generate an Excel file from your Node.js application

SpreadJS和Node.js入门

首先,我们需要安装Node.js和Mock-Browser,BufferJS和FileReader,这些都可以在以下链接中找到:

我们将使用Visual Studio来创建应用程序。打开Visual Studio后,使用JavaScript> Node.js> Blank Node.js控制台应用程序模板创建一个新应用程序。这将自动创建所需的文件并打开“app.js”文件,本例中我们只需改动这一个文件。

对于BufferJS库,您需要下载该软件包,然后通过导航到项目文件夹(一旦创建)并运行以下命令,在项目中手动安装它:

npm install

安装完成后,可能需要打开项目的package.json文件并将其添加到“dependencies”部分。文件内容应如下所示:

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

在此示例中,我们将使用Node.js的文件系统模块。加载方式:

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

为了将SpreadJS与Node.js一起使用,我们可以加载我们安装的Mock-Browser:

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

在加载SpreadJS脚本之前,我们需要初始化模拟浏览器。初始化我们稍后可能需要在应用程序中使用到的变量,尤其是“window”:

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

初始化FileReader库:

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

使用SpreadJS npm包

现在,需要将SpreadJS包和ExcelIO包添加到项目中。您可以通过右键单击解决方案资源管理器的“npm”部分并选择“安装新的NPM包”将这些添加到项目中。您可以通过搜索“GrapeCity”并安装以下2个包:

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

将SpreadJS npm包添加到项目后,应该使用正确的依赖项自动上传package.json:

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

现在我们需要在app.js文件中添加依赖:

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

使用npm软件包时,还需要设置许可证密钥:

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

在这个特定的应用程序中,我们将向用户显示他们正在使用的SpreadJS版本。为此,我们可以请求package.json文件,然后引用依赖项以获取版本号:

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

加载Excel文件

我们将加载现有的Excel模板文件,从用户那里获取数据。接下来,将数据放入文件并导出。在这种情况下,该文件是用户可以编辑的发票。

首先初始化工作簿和Excel IO变量:

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

当我们在文件中读取时,让我们将代码包装在try / catch块中。然后我们可以初始化变量“readline” —— 它本质上是一个允许您读取用户输入控制台数据的库。接下来,我们将数据存储到一个JavaScript数组中,可以使用它轻松填写Excel文件:

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

Generate an Excel Spreadsheet from your Node.js Application

上图显示了我们正在使用的Excel文件。我们要收集的第一个信息是一般发票信息。我们可以在excelio.open调用中创建一个单独的函数,以便在控制台中为我们需要的每个项目来提示用户去填写。我们可以创建一个单独的数组来在每次输入之后保存数据,然后我们拥有该部分的所有输入。将它推送到我们创建的invoice.generalInfo数组:

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

在该函数中,我们称之为“fillCompanyDetails”,我们将收集有关公司的信息以填入工作簿的第二张表。该功能与之前的功能非常相似:

<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

现在我们已经获得了发票的基本信息,我们可以专注于收集各个发票项目,我们将在另一个名为“fillInvoiceItemsInformation”的函数中执行此操作。在每个项目之前,我们会询问用户是否要添加项目。如果他们继续输入“y”,那么我们将收集该项目的信息,然后再次询问,直到他们输入“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>

填写您的Excel文件

收集完所有必需的发票信息后,我们可以填写Excel文件。对于结算信息和公司设置,我们可以从JavaScript数组中手动​​设置单元格中的每个值:

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

我们使用的模板中,行数是以发票的项目数来设置的。用户在添加过程中可能会超过最大值。在这种情况下,我们可以简单地向工作表添加更多行:

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

导出到Excel

在工作簿中填写信息后,我们可以将工作簿导出到Excel文件。为此,我们将使用excelio open函数。用日期来为文件命名:

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

您可以使用上面的代码段将工作簿导出到Excel文件。您完成的文件如下所示:

Generate an Excel Spreadsheet from your Node.js Application

本例将SpreadJS与Node.js结合使用,演示了SpreadJS的多功能性和可扩展性!有关此类,演示,视频和教程的更多文章,请查看葡萄城官方博客页面。