從服務端生成Excel電子表格(Node.js+SpreadJS)

葡萄城技術團隊發表於2022-01-26

Node.js是一個基於Chrome V8引擎的JavaScript執行環境,通常用於建立網路應用程式。它可以同時處理多個連線,並且不像其他大多數模型那樣依賴執行緒。

對於 Web 開發者來說,從資料庫或Web伺服器獲取資料,然後輸出到Excel檔案以進行進一步分析的場景時有發生。我們的技術團隊在跟國內外各行各業使用者交流的過程中,就曾發現有很多的使用者嘗試在Node.js的環境下執行SpreadJS 純前端表格控制元件,藉助該控制元件,可以在伺服器不預裝任何Excel依賴項的情況下,收集使用者輸入的資訊,並將其自動匯出到Excel檔案中。

為了滿足廣大技術愛好者的需要,同時減少大家在未來技術選型方面所走的彎路,本文將就SpreadJS 與 Node.js之間的技術性方案進行探討!

一、安裝 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"部分。檔案內容應如下所示:

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

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

現在我們需要在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('\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);
}


四、收集使用者輸入資訊

上圖顯示了我們正在使用的Excel檔案。我們可以在excelio.open呼叫中建立一個單獨的函式,以在控制檯中提示使用者需要的每一項內容。我們也可以建立一個單獨的陣列,將資料儲存到每個輸入後,然後將其推送到我們建立的invoice.generalInfo陣列中:

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


該函式被稱為" fillCompanyDetails",目的是收集有關公司的資訊以填充到工作簿的第二張表中:

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





現在我們已經有了使用者的基本資訊,我們可以集中精力收集單個專案,並另命名為" 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 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'.");
       }
   });
}

五、填入您的Excel 檔案

在收集所有必需的使用者資訊後,我們可以將其填入到Excel檔案中:

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);
}


為了防止使用者新增的數量超過工作表最大行數,我們可以在工作表中自動新增更多行。在設定陣列中表單中的專案之前,預設新增行:

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++;
       }
   }
}


六、將文件內容從 Node.js 匯出到 Excel 檔案

在工作簿中填寫完資訊後,我們可以將工作簿匯出到Excel檔案中。為此,我們將使用excelio開啟功能。在這種情況下,只需將日期輸入檔名即可:

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 });
}


完成的檔案將如下所示:

以上就是第一篇《從服務端生成Excel電子表格(Node.js+SpreadJS)》的全部內容。為了能夠解決批量繫結資料來源並匯出Excel、批量修改大量Excel內容及樣式、服務端批量列印以及生成PDF文件等需求,我們提供了更為成熟的官方手段:SpreadJS + GcExcel,該方案提供了比Node.js+SpreadJS更加優秀的效能和穩定性,這就是我們下一篇《從服務端生成Excel電子表格(GcExcel + SpreadJS)》的主要內容,敬請期待。

相關文章