站長資訊網
前端（vue）入門到精通課程：進入學習
Node是一個基于Chrome V8引擎的JavaScript運行環境，通常用于創建網絡應用程序。它可以同時處理多個連接，并且不像其他大多數模型那樣依賴線程。
對于 Web 開發者來說，從數據庫或Web服務器獲取數據，然后輸出到Excel文件以進行進一步分析的場景時有發生。我們的技術團隊在跟國內外各行各業用戶交流的過程中，就曾發現有很多的用戶嘗試在Node.js的環境下運行SpreadJS 純前端表格控件，借助該控件，可以在服務器不預裝任何Excel依賴項的情況下，收集用戶輸入的信息，并將其自動導出到Excel文件中。
為了滿足廣大技術愛好者的需要，同時減少大家在未來技術選型方面所走的彎路，本文將就SpreadJS 與 Node.js之間的技術性方案進行探討！
一、安裝 SpreadJS 和 Node .js
首先，我們需要安裝Node.js以及Mock-Browser，BufferJS和FileReader，大家可以前往以下鏈接進行下載，同步操作：
- Installing Node.js viaPackage Manager
- Mock-Browser
- BufferJS
- FileReader
我們將使用Visual Studio創建應用程序。打開Visual Studio后，使用JavaScript> Node.js>Blank Node.js控制臺應用程序模板創建一個新應用程序。這將自動創建所需的文件并打開" app.js"文件，也是我們將要更改的唯一文件。
對于BufferJS庫，您需要下載該軟件包，然后通過導航到項目文件夾（一旦創建）并運行以下命令，將其手動安裝到項目中：
npm install
安裝完成后，您可能需要打開項目的package.json文件并將其添加到" dependencies"部分。文件內容應如下所示：
{ "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的文件系統模塊。我們可以將其加載到：
var fs = require('fs')
為了將SpreadJS與Node.js結合使用，我們還需要加載已安裝的Mock-Browser：
var mockBrowser =require('mock-browser').mocks.MockBrowser
在加載SpreadJS腳本之前，我們需要初始化模擬瀏覽器。初始化我們稍后在應用程序中可能需要使用的變量，尤其是" window"變量：
global.window =mockBrowser.createWindow() global.document = window.document global.navigator = window.navigator global.HTMLCollection =window.HTMLCollection global.getComputedStyle =window.getComputedStyle
初始化FileReader庫：
var fileReader = require('filereader'); global.FileReader = fileReader;
二、使用SpreadJS npm 包
將SpreadJS安裝文件中的SpreadJS Sheets和ExcelIO包添加到項目中。
您可以通過右鍵單擊解決方案資源管理器的" npm"部分并將它們添加到您的項目中，然后選擇"安裝新的NPM軟件包"。您應該能夠搜索" GrapeCity"并安裝以下2個軟件包：
@grapecity/spread-sheets @grapectiy/spread-excelio
將SpreadJS npm軟件包添加到項目后，正確的依賴關系將被寫入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" } }
現在我們需要在app.js文件中引入它：
var GC =require('@grapecity/spread-sheets') var GCExcel =require('@grapecity/spread-excelio');
使用npm軟件包時，還需要設置許可證密鑰（點擊此處，免費申請許可證密鑰）：
GC.Spread.Sheets.LicenseKey ="<YOUR KEY HERE>"
在這個特定的應用程序中，我們將向用戶顯示他們正在使用哪個版本的SpreadJS。為此，我們可以引入package.json文件，然后引用依賴項以獲取版本號：
var packageJson =require('./package.json') console.log('n** Using Spreadjs Version"' + packageJson.dependencies["@grapecity/spread-sheets"] +'" **')
三、將 Excel 文件加載到您的 Node.js 應用程序中
點擊此處，下載現成的Excel模板文件，該文件包含了從用戶那里獲取數據。接下來，將數據放入文件中并導出。在這種情況下，文件是用戶可以編輯的狀態。
初始化工作簿和ExcelIO變量：
var wb = new GC.Spread.Sheets.Workbook(); var excelIO = new GCExcel.IO();
我們在讀取文件時將代碼包裝在try / catch塊中。然后，初始化變量" readline"，讓您讀取用戶輸入到控制臺的數據。接下來，我們將其存儲到一個JavaScript數組中，以便輕松填寫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調用中創建一個單獨的函數，以在控制臺中提示用戶需要的每一項內容。我們也可以創建一個單獨的數組，將數據保存到每個輸入后，然后將其推送到我們創建的invoice.generalInfo數組中：
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(); }); }); }); }); }); }); }); }); }); }); }
該函數被稱為" fillCompanyDetails"，目的是收集有關公司的信息以填充到工作簿的第二張表中：
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(); }); }); }); }); }); }); }); }); }); }); }); }); }); }); }); }); }); }); }
現在我們已經有了用戶的基本信息，我們可以集中精力收集單個項目，并另命名為" fillInvoiceItemsInformation"函數。在每個項目執行之前，我們會詢問用戶是否要添加一個項目。如果他們繼續輸入" y"，那么我們將收集該項目的信息，然后再次詢問直到他們鍵入" 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); }
為了防止用戶添加的數量超過工作表最大行數，我們可以在工作表中自動添加
