There are many articles on the Internet on the topic «How to use OLE Automation to generate documents».   Data compilation and retrieval is proceeded using iDispatch, the interface of an external process COM server, which can be Word, Exel, etc.  This approach has several serious limitations:

  • Marshaling is a process of transferring data to an external process COM server. This procedure has a serious influence on the speed of code and data acquisition.
  • IDispatch is not the most user-friendly interface for using.
  • The problem of integration into its own application (positioning, management of actions allowed to users, etc.) if necessary to show users the results of generated data.

If these problems were fixed for programmers during work with external process MSO servers, almost all developed systems that require document generation, would use them too.  Users got used to working with these applications, they know how the data is presented in the document from their personal experience, for example complex tables, that take several sheets and etc. 

The Solution

SComponents are intended to solve these issues. We have worked as much as possible on data presentation, so users could feel comfortable while working with their components. Moreover, the components are realized using Java and .Net.  An important point is that their Java solution works on all platforms on which Java runs. The integration of Com-server into JavaFX application is performed using the Jacob version 1.19 library. I selected the Scell.J component for test, the demo version is available on the site. I will not give the MS Excel and SCell.J integration code inside the JavaFX application, I will give you only the code of the tests themselves.

IDispatch interface using code: 

protected void startTest1() {
        Long time = System.currentTimeMillis();
        Dispatch sheet = Dispatch.get(xb, "ActiveSheet").toDispatch();
        Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] {
            "A1"
        }, new int[1]).toDispatch();
        Dispatch.put(cell, "Value", "1,0");
        cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] {
            "B1"
        }, new int[1]).toDispatch();
        Dispatch.put(cell, "Value", "2,0");
        for (int i = 2; i < 32000; i++) {
            cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] {
                "B" + String.valueOf(i)
            }, new int[1]).toDispatch();
            Dispatch.put(cell, "Formula", "=A1+B" + String.valueOf(i - 1));
        } 
//System.out.println("Test time elapsed="+(System.currentTimeMillis()-time)); resTime1.setText("Test send time: " + (System.currentTimeMillis()-time) + " mc"); } 

protected void startTest2() {
    Long time = System.currentTimeMillis();
    Dispatch sheet = Dispatch.get(xb, "ActiveSheet").toDispatch();
    Dispatch cell;
    for (int i = 2; i < 32000; i++) {
        cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] {
            "B" + String.valueOf(i)
        }, new int[1]).toDispatch();
        System.out.println("Read value from " + "B" + String.valueOf(i) + " = " + Dspatch.get(cell, "Value").toString());
    }
    resTime2.setText("Test receive time: " + (System.currentTimeMillis() - time) + " mc");
}

Code of using Scel.J component: 

protected void startTest1() {
    Long time = System.currentTimeMillis();
    ((Workbook) spreadsheetView.getBook().getWorkbook()).setProgrammerMode(true);
    spreadsheetView.getBook().getActiveSheet().range("A1").setValue(1.0);
    spreadsheetView.getBook().getActiveSheet().range("B1").setValue(2.0);
    for (int i = 2; i < 32000; i++) {
        spreadsheetView.getBook().getActiveSheet().range("B" + String.valueOf(i)).setFormula("A1+B" + String.valueOf(i - 1));
    }((Workbook) spreadsheetView.getBook().getWorkbook()).setProgrammerMode(false);
    spreadsheetView.getBook().getActiveSheet().complete();
    resTime1.setText("Test time: " + (System.currentTimeMillis() - time) + " mc");
}

protected void startTest2() {
    Long time = System.currentTimeMillis();
    ((Workbook) spreadsheetView.getBook().getWorkbook()).setProgrammerMode(true);
    for (int i = 2; i < 32000; i++) {
        String s = spreadsheetView.getBook().getActiveSheet().range("B" + String.valueOf(i)).getStringValue();
        System.out.println("Read value from " + "B" + String.valueOf(i) + " = " + s);
    }((Workbook) spreadsheetView.getBook().getWorkbook()).setProgrammerMode(false);
    spreadsheetView.getBook().getActiveSheet().complete();
    resTime2.setText("Test time: " + (System.currentTimeMillis() - time) + " mc");
}

The Test

The test was performed on a computer with Intel Core i-5 8th generation and 16 GB RAM DDR 4, JDK 8.151. I ran the applications directly from the studio, because it did not make sense to create a special, clean environment, since the difference in processing speed is huge. I also noticed that the restart of this test using IDicpatch, over the data entered, is performed so long that it is not acceptable for any developer, the time varies between 4-5 minutes. In Scell.J, the test rerun time is comparable to the time of the first test. 

Data processing mechanism Data transferring test  Data receiving test  Subiculal call 
External process COM server (IDispatch)  38873 mc- 44397 mc  24662 mc – 25748mc 4-5 minutes 
Scell.J  component 6157mc – 7226 mc 401 mc– 434 mc 4652мс – 4761мс 

Next, I provide a visual confirmation of my tests. Screenshots show the slowest data transfer option for both types of tests.


I selected Spreadsheets for tests, because it was convenient to make a simple and clear test for them, but the results for the word processor will be the same. Approximately the same results will be received using any externally process COM-server with a similar .Net component.

Conclusion

Of course, there are other ways to generate and read documents. I’m talking about libraries that support the Open XML standard.  They are implemented into different programming languages, but the ones know are not convenient to use. In addition, they do not allow users to generate and display, make changes or print the document created by the developer.

P.S. Please do not judge strictly. The article reflects my personal opinion and my view on existing solutions on the market for generating and processing Open XML documents, which many developers are forced to resort to.