前端（vue）入門到精通課程：進(jìn)入學(xué)習(xí)

Node是一個(gè)基于Chrome V8引擎的JavaScript運(yùn)行環(huán)境，通常用于創(chuàng)建網(wǎng)絡(luò)應(yīng)用程序。它可以同時(shí)處理多個(gè)連接，并且不像其他大多數(shù)模型那樣依賴線程。

對(duì)于 Web 開發(fā)者來(lái)說(shuō)，從數(shù)據(jù)庫(kù)或Web服務(wù)器獲取數(shù)據(jù)，然后輸出到Excel文件以進(jìn)行進(jìn)一步分析的場(chǎng)景時(shí)有發(fā)生。我們的技術(shù)團(tuán)隊(duì)在跟國(guó)內(nèi)外各行各業(yè)用戶交流的過程中，就曾發(fā)現(xiàn)有很多的用戶嘗試在Node.js的環(huán)境下運(yùn)行SpreadJS 純前端表格控件，借助該控件，可以在服務(wù)器不預(yù)裝任何Excel依賴項(xiàng)的情況下，收集用戶輸入的信息，并將其自動(dòng)導(dǎo)出到Excel文件中。

為了滿足廣大技術(shù)愛好者的需要，同時(shí)減少大家在未來(lái)技術(shù)選型方面所走的彎路，本文將就SpreadJS 與 Node.js之間的技術(shù)性方案進(jìn)行探討！

一、安裝 SpreadJS 和 Node .js

首先，我們需要安裝Node.js以及Mock-Browser，BufferJS和FileReader，大家可以前往以下鏈接進(jìn)行下載，同步操作：

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

我們將使用Visual Studio創(chuàng)建應(yīng)用程序。打開Visual Studio后，使用JavaScript> Node.js>Blank Node.js控制臺(tái)應(yīng)用程序模板創(chuàng)建一個(gè)新應(yīng)用程序。這將自動(dòng)創(chuàng)建所需的文件并打開" app.js"文件，也是我們將要更改的唯一文件。

對(duì)于BufferJS庫(kù)，您需要下載該軟件包，然后通過導(dǎo)航到項(xiàng)目文件夾（一旦創(chuàng)建）并運(yùn)行以下命令，將其手動(dòng)安裝到項(xiàng)目中：

npm install

安裝完成后，您可能需要打開項(xiàng)目的package.json文件并將其添加到" dependencies"部分。文件內(nèi)容應(yīng)如下所示：

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

在此示例中，我們將使用Node.js的文件系統(tǒng)模塊。我們可以將其加載到：

var fs = require('fs')

為了將SpreadJS與Node.js結(jié)合使用，我們還需要加載已安裝的Mock-Browser：

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

在加載SpreadJS腳本之前，我們需要初始化模擬瀏覽器。初始化我們稍后在應(yīng)用程序中可能需要使用的變量，尤其是" window"變量：

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

初始化FileReader庫(kù)：

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

二、使用SpreadJS npm 包

將SpreadJS安裝文件中的SpreadJS Sheets和ExcelIO包添加到項(xiàng)目中。

您可以通過右鍵單擊解決方案資源管理器的" npm"部分并將它們添加到您的項(xiàng)目中，然后選擇"安裝新的NPM軟件包"。您應(yīng)該能夠搜索" GrapeCity"并安裝以下2個(gè)軟件包：

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

將SpreadJS npm軟件包添加到項(xiàng)目后，正確的依賴關(guān)系將被寫入package.json：

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

現(xiàn)在我們需要在app.js文件中引入它：

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

使用npm軟件包時(shí)，還需要設(shè)置許可證密鑰（點(diǎn)擊此處，免費(fèi)申請(qǐng)?jiān)S可證密鑰）：

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

在這個(gè)特定的應(yīng)用程序中，我們將向用戶顯示他們正在使用哪個(gè)版本的SpreadJS。為此，我們可以引入package.json文件，然后引用依賴項(xiàng)以獲取版本號(hào)：

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

三、將 Excel 文件加載到您的 Node.js 應(yīng)用程序中

點(diǎn)擊此處，下載現(xiàn)成的Excel模板文件，該文件包含了從用戶那里獲取數(shù)據(jù)。接下來(lái)，將數(shù)據(jù)放入文件中并導(dǎo)出。在這種情況下，文件是用戶可以編輯的狀態(tài)。

初始化工作簿和ExcelIO變量：

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

我們?cè)谧x取文件時(shí)將代碼包裝在try / catch塊中。然后，初始化變量" readline"，讓您讀取用戶輸入到控制臺(tái)的數(shù)據(jù)。接下來(lái)，我們將其存儲(chǔ)到一個(gè)JavaScript數(shù)組中，以便輕松填寫Excel文件：

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

四、收集用戶輸入信息

上圖顯示了我們正在使用的Excel文件。我們可以在excelio.open調(diào)用中創(chuàng)建一個(gè)單獨(dú)的函數(shù)，以在控制臺(tái)中提示用戶需要的每一項(xiàng)內(nèi)容。我們也可以創(chuàng)建一個(gè)單獨(dú)的數(shù)組，將數(shù)據(jù)保存到每個(gè)輸入后，然后將其推送到我們創(chuàng)建的invoice.generalInfo數(shù)組中：

fillGeneralInformation(); function fillGeneralInformation() {    console.log("-----------------------nFill in InvoiceDetailsn-----------------------")    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();                                         });                                     });                                });                             });                         });                     });                 });             });        });    }); }

該函數(shù)被稱為" fillCompanyDetails"，目的是收集有關(guān)公司的信息以填充到工作簿的第二張表中：

function fillCompanyDetails() {    console.log("-----------------------nFill in CompanyDetailsn-----------------------")    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 Itemsn-----------------------")                                                                            fillInvoiceItemsInformation();                                                                         });                                                                    });                                                                });                                                            });                                                        });                                                    });                                                });                                             });                                         });                                     });                                 });                             });                         });                     });                 });             });        });    }); }

現(xiàn)在我們已經(jīng)有了用戶的基本信息，我們可以集中精力收集單個(gè)項(xiàng)目，并另命名為" fillInvoiceItemsInformation"函數(shù)。在每個(gè)項(xiàng)目執(zhí)行之前，我們會(huì)詢問用戶是否要添加一個(gè)項(xiàng)目。如果他們繼續(xù)輸入" y"，那么我們將收集該項(xiàng)目的信息，然后再次詢問直到他們鍵入" 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 ItemInformationn-----------------------");                 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'.");        }    }); }

五、填入您的Excel 文件

在收集所有必需的用戶信息后，我們可以將其填入到Excel文件中：

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

為了防止用戶添加的數(shù)量超過工作表最大行數(shù)，我們可以在工作表中自動(dòng)添加