Generate Excel spreadsheets from the server (GcExcel + SpreadJS)

To generate Excel spreadsheets on the server, in addition to using Node.js + SpreadJS, Grape City officially recommends using SpreadJS + GcExcel. This solution can not only meet the requirements of batch binding data sources and exporting Excel, batch modifying a large number of Excel content and styles, batch printing on the server side, and generating PDF documents, etc., but also provides component performance far exceeding industry standards.

In order to verify the processing performance of SpreadJS + GcExcel, this article will compare the data of GcExcel for Java and Node.js running SpreadJS. Because the components of SpreadJS and GcExcel are very rich in functions, this article only selects the two most common function points for comparison, namely setting regional data and exporting Excel documents.

1. Several major premises of this test

Since Node.js executes JavaScript based on the V8 engine, its js is also a non-blocking single-threaded operation based on the event mechanism, and the I/O of its files are executed asynchronously, and the reason why Node.js chooses single-threaded The way is because the coding is simple, the development difficulty is low, and the mental consumption of "code farmers" is relatively small; and its file I/O is executed asynchronously, so it does not need to create and recycle threads like Java (Node. The I/O operation of js is also a thread at the bottom layer, which will not be discussed in depth here), and the overhead in this regard is small.
However, single thread has no advantage over multi-threading in performing complex operations, and cannot use multi-threading to effectively allocate multi-core CPUs for optimization. Therefore, running SpreadJS in Node.js can only be single-threaded JS, which will also Affects the data processing performance of SpreadJS.

Therefore, in order to obtain more accurate test results, the test cases designed in this article are executed in a single thread in both environments (Java and Node.js), and batch I/O more matching Node.js is selected. Actions are used as test cases.

Second, the test code and results of Node.js and SpreadJS:

Software version	CPU	RAM
Node.js 16.10.0	Intel(R) Core(TM) i7-9750HQ CPU @ 2.80 GHz	32G

Test code: As shown below, use a Performance class to perform 1000 operations of setting data and exporting Excel documents.

const fs = require('fs');

// Initialize the mock browser variables
const mockBrowser = require('mock-browser').mocks.MockBrowser;
global.window = mockBrowser.createWindow();
global.document = window.document;
global.navigator = window.navigator;
global.HTMLCollection = window.HTMLCollection;
global.getComputedStyle = window.getComputedStyle;

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

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

GC.Spread.Sheets.LicenseKey = GCExcel.LicenseKey = "Your License";

const dataSource = require('./data');

function runPerformance(times) {

  const timer = `test in ${times} times`;
  console.time(timer);

  for(let t=0; t<times; t++) {
    // const hostDiv = document.createElement('div');
    // hostDiv.id = 'ss';
    // document.body.appendChild(hostDiv);
    const wb = new GC.Spread.Sheets.Workbook()//global.document.getElementById('ss'));
    const sheet = wb.getSheet(0);
    for(let i=0; i<dataSource.length; i++) {
      sheet.setValue(i, 0, dataSource[i]["Film"]);
      sheet.setValue(i, 1, dataSource[i]["Genre"]);
      sheet.setValue(i, 2, dataSource[i]["Lead Studio"]);
      sheet.setValue(i, 3, dataSource[i]["Audience Score %"]);
      sheet.setValue(i, 4, dataSource[i]["Profitability"]);
      sheet.setValue(i, 5, dataSource[i]["Rating"]);
      sheet.setValue(i, 6, dataSource[i]["Worldwide Gross"]);
      sheet.setValue(i, 7, dataSource[i]["Year"]);
    }
    exportExcelFile(wb, times, t);
  }
  
}

function exportExcelFile(wb, times, t) {
    const excelIO = new GCExcel.IO();
    excelIO.save(wb.toJSON(), (data) => {
        fs.appendFile('results/Invoice' + new Date().valueOf() + '_' + t + '.xlsx', new Buffer(data), function (err) {
          if (err) {
            console.log(err);
          }else {
            if(t === times-1) {
              console.log('Export success');
              console.timeEnd(`test in ${times} times`);
            }
          }
        });
    }, (err) => {
        console.log(err);
    }, { useArrayBuffer: true });
}

runPerformance(1000)

For the complete test project, please refer to: https://gitee.com/GrapeCity/Node.js-SpreadJS-two.git

How the test project runs:

• npm install
• node ./app.js

running result: average cost per time 18.1 ms

3. GcExcel test code and results

Software version	CPU	RAM
GcExcel V5.0	Intel(R) Core(TM) i7-9750HQ CPU @ 2.80 GHz	32G

The test code looks like this:

public class Performance {

        public static void main(String[] args) {
                System.out.println(System.getProperty("user.dir") + "/sources/jsonData");
                String jsonStr = readTxtFileIntoStringArrList(System.getProperty("user.dir") + "/sources/jsonData");
                JSONArray jsonArr = JSON.parseArray(jsonStr);
                //JSONObject jsonObj = (JSONObject) jsonArr.get(0);
                //System.out.println(jsonObj.get("Film"));
                run(1000, jsonArr);
        }

        public static void run(int times, JSONArray dataArr) {
                String path = System.getProperty("user.dir") + "/results/";
                System.out.println(path + "result.xlsx");
                long start = new Date().getTime();
                for (int i = 0; i < times; i++) {
                        Workbook workbook = new Workbook();
                        IWorksheet worksheet = workbook.getWorksheets().get(0);
                        for (int j = 0; j < dataArr.size(); j++) {
                                JSONObject jsonObj = (JSONObject) dataArr.get(j);
                                worksheet.getRange(j, 0, 1, 8).get(0).setValue(jsonObj.get("Film"));
                                worksheet.getRange(j, 0, 1, 8).get(1).setValue(jsonObj.get("Genre"));
                                worksheet.getRange(j, 0, 1, 8).get(2).setValue(jsonObj.get("Lead Studio"));
                                worksheet.getRange(j, 0, 1, 8).get(3).setValue(jsonObj.get("Audience Score %"));
                                worksheet.getRange(j, 0, 1, 8).get(4).setValue(jsonObj.get("Profitability"));
                                worksheet.getRange(j, 0, 1, 8).get(5).setValue(jsonObj.get("Rating"));
                                worksheet.getRange(j, 0, 1, 8).get(6).setValue(jsonObj.get("Worldwide Gross"));
                                worksheet.getRange(j, 0, 1, 8).get(7).setValue(jsonObj.get("Year"));
                        }
                        workbook.save(path + "result" + i + ".xlsx");
                }
                System.out.println("运行"+times+"次花费时常（ms）: " + (new Date().getTime() - start));

        }

        public static String readTxtFileIntoStringArrList(String filePath) {
                StringBuilder list = new StringBuilder();
                try {
                        String encoding = "GBK";
                        File file = new File(filePath);
                        if (file.isFile() && file.exists()) {
                                InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);// 考虑到编码格式
                                BufferedReader bufferedReader = new BufferedReader(read);
                                String lineTxt = null;

                                while ((lineTxt = bufferedReader.readLine()) != null) {
                                        list.append(lineTxt);
                                }
                                bufferedReader.close();
                                read.close();
                        } else {
                                System.out.println("找不到指定的文件");
                        }
                } catch (Exception e) {
                        System.out.println("读取文件内容出错");
                        e.printStackTrace();
                }
                return list.toString();
        }

}

For the complete test project zip, please refer to the attachment: https://gitee.com/GrapeCity/Node.js-SpreadJS-two.git

Test script running mode: directly run as Application after importing Eclipse

The running results of are as follows: Average cost per time 8.4 ms

Fourth, summary analysis:

1. Analysis of test results: Node.js takes an average of 18.1 ms each time, and GcExcel takes an average of 8.4 ms each time. Both perform 1,000 operations of setting data and exporting Excel documents at the same time, and the performance difference is 2 times.

2. Comparative analysis of processing performance:

Even for single-threaded batch I/O operations, the performance of SpreadJS in Node.js is still not as good as that of SpreadJS in GcExcel for Java. On the one hand, because GcExcel has excellent performance, it uses many excellent and mature applications on the Java platform. The solution has achieved the most first-class performance among similar products. On the other hand, GcExcel has more comprehensive functional support for Excel and SpreadJS. At present, GcExcel has become the preferred solution for server-side processing of Excel documents in the industry.

3. Analysis of technical selection:

In addition to performance and coding difficulty, there is one thing that cannot be ignored in terms of technology selection, that is, the platform. If the project itself adopts the Java Web or .Net Web architecture, it is obviously more suitable for GcExcel which provides dual platform support ( GcExcel for java and GcExcel for .NET ).

The above is the whole content of this article. Combined with the test results of this article, SpreadJS + GcExcel can provide better performance and stability for batch processing, modification, export of Excel, as well as server-side batch printing and PDF document generation requirements. performance, you can rest assured that it will be the first choice for future projects.