Custom import - getCellByPosition bottleneck

[jeremys]

I am currently creating a custom importer/exporter for my own file type with java using XFilter.

I now have a serious bottleneck when importing. Here is my code for inserting my content into a spreadsheet:

Code: Select all

    XSpreadsheetDocument document = UnoRuntime.queryInterface(
        XSpreadsheetDocument.class,
        targetDocument);
    XMultiServiceFactory serviceFactory = UnoRuntime.queryInterface(
        com.sun.star.lang.XMultiServiceFactory.class,
        document);

    for (int i = 0; i < numberOfSpreadsheets; i++) {
        Object spreadsheetObject = serviceFactory.createInstance("com.sun.star.sheet.Spreadsheet");
        XSpreadsheet spreadsheet = UnoRuntime.queryInterface(
            XSpreadsheet.class,
            spreadsheetObject);
        document.getSheets().insertByName(spreadsheetNames.get(i), spreadsheet);
        
        // Not sure about this part
        spreadsheet = UnoRuntime.queryInterface(
            XSpreadsheet.class, 
            document.getSheets().getByName(spreadsheetNames.get(i));

        for (int rowIndex = 0; rowIndex < numberOfRows; rowIndex++) {
            for (int columnIndex = 0; columnIndex < numberOfCellsInRow; columnIndex++) {
                // This call to getCellByPosition seems to be the bottleneck
                XCell cell = spreadsheet.getCellByPosition(columnIndex, rowIndex);

                XText text = UnoRuntime.queryInterface(XText.class, cell);
                text.setString(cellValue);
            }
        }
    }

Is there any way I can omit all these calls to getCellByPosition?
Is there some sort of bulk API available?

[RoryOF]

Perhaps try getCellRangeByPosition and do it on a line by line basis.

[jeremys]

Perhaps try getCellRangeByPosition and do it on a line by line basis.

This is not any faster.

I also tried setDataArray, which is faster, but does not allow to format the cells. And the formatting afterwards with getCellByPosition still takes a lot of time.

[jeremys]

I now implemented XExportFilter and XDocumentHandler for the export and it is also very slow.

[Zizi64]

I also tried setDataArray, which is faster, but does not allow to format the cells. And the formatting afterwards with getCellByPosition still takes a lot of time.

Use the Styles instead of the direct formatting properties. You can preformat the target template file, or you can create/apply cell styles by your program code.

[Villeroy]

Of course you are not able to apply any kind of number format after setting the cells STRING.
A spreadsheet full of strings is not a spreadsheet anymore. It would be easier to write text files (csv) instead.

[Lupp]

(Yes, I accept this evidence that C code is even uglier than Basic code.
I will try to talk about this in simple, human readable terms.)

Whatever you want to do with the cell range
myRg = spreadsheet.getCellRangeByPosition(0, EndColumn, 0, EndRow)
will be done slow if you create the cell objects one by one and then play with them. (In current LibO V6 it's even much slower.)

If there is remedy depends essentially on your answer to the following question:

Do you need to be able to assign content (.Value, .String, or .Formula) and attributes (many; partly best bundled in cell styles) differently for each cell, or are the assignments the same for all the cells in the range (or for subsets of cells described by a reasonably low number of rectangular subranges)?

In the first case you are lost.
In the second case there are ways to do it in a blink of an eye for a few thousand cells. (Least fast for setting a formula I assume.)