Standard/Advanced filter with Contains operator with java sdk problems

[renbee]

Using LibreOffice java sdk 7.4.7 on Mac OS with LibreOffice version 7.4.7.2. Connecting remotely and loading spreadsheet with existing data (works).

I want to programmatically (using java sdk) create and run Standard filter using operator Contains with value being regex to filter data based on it (this works perfectly with a manually created Standard filter). The issues I am facing:

1. TableFilterField.Operator field is FilterOperator enum which doesn’t include Contains operator. Therefore, I can’t use TableFilterField when specifying filterFields on XSheetFilterDescriptor used for filtering.
2. Found XSheetFilterDescriptor2 with TableFilterField2 where TableFilterField2 has Operator being long which is great since Contains operator can be set to it. So I tried below

XSheetFilterable xFilter = UnoRuntime.queryInterface(XSheetFilterable.class, cellRange);
XSheetFilterDescriptor2 xFilterDesc2 = UnoRuntime.queryInterface(XSheetFilterDescriptor2.class,
    // This returns SheetFilterDescriptor which also implements XSheetFilterDescriptor2
    xFilter.createFilterDescriptor(true));
TableFilterField2[] aFilterFields = new TableFilterField2[1];
aFilterFields[0] = new TableFilterField2();
aFilterFields[0].Field = 12; // 13th column
aFilterFields[0].IsNumeric = false; // Not-numeric means text
aFilterFields[0].Operator = FilterOperator2.CONTAINS;
aFilterFields[0].StringValue = "foo|bar";
xFilterDesc2.setFilterFields2(aFilterFields);
...

but XSheetFilterable.filter(XSheetFilterDescriptor xDescriptor) method accepts XSheetFilterDescriptor as its parameter NOT XSheetFilterDescriptor2 which I’ve constructed above, so I can’t use it for filtering, below won’t compile

xFilter.filter(xFilterDesc2);

Is there a way to make filtering work using XSheetFilterDescriptor2 ?
3. I also tried to do this filtering via Advanced Filter style below, which also doesn’t work because xFilterDesc is always null below

XSheetFilterable xFilter = UnoRuntime.queryInterface(XSheetFilterable.class, cellRange);
XCellRange xCritRange = xSpreadsheet.getCellRangeByName( "N1:N2" );
XCellRangeData xCritData = UnoRuntime.queryInterface(XCellRangeData.class, xCritRange);
Object[][] aCritValues =
    {
        { "Name"    },
        { "Contains foo|bar" } // trying to use Contains operator
    };
xCritData.setDataArray( aCritValues );
XSheetFilterableEx xCriteria = UnoRuntime.queryInterface(XSheetFilterableEx.class, xCritRange);
XSheetFilterDescriptor xFilterDesc = xCriteria.createFilterDescriptorByObject(xFilter);
if (xFilterDesc != null) {
    log.info("About to use xFilterDesc {}", xFilterDesc);
    ...

    xFilter.filter(xFilterDesc);
}
else {
    //I always get in here so can't use this method
    log.error("xFilterDesc was NULL");
}

Is there a way to make this work ?

[vib]

Maybe this will be helpful.

Chapter 50. Importing XML - LibreOffice Programming

The LOP Java Lib can be found here:

GitHub - Amourspirit/libreoffice_lop_java

[renbee]

Thanks for these links. While the issue at hand was solved by @ms777 " Java LibreOffice Programming is full of useful LO java kung-fu.

[ms777]

Hi,

I am not too experienced in Java, but maybe you should create the XSheetFilterDescriptor first, and as a second step query the XSheetFilterDescriptor2 interface?

Good luck,

ms777

XSheetFilterable xFilter = UnoRuntime.queryInterface(XSheetFilterable.class, cellRange);

XSheetFilterDescriptor xFD = xFilter.createFilterDescriptor(true);

XSheetFilterDescriptor2 xFilterDesc2 = UnoRuntime.queryInterface(XSheetFilterDescriptor2.class,  xFD);

TableFilterField2[] aFilterFields = new TableFilterField2[1];
aFilterFields[0] = new TableFilterField2();
aFilterFields[0].Field = 12; // 13th column
aFilterFields[0].IsNumeric = false; // Not-numeric means text
aFilterFields[0].Operator = FilterOperator2.CONTAINS;
aFilterFields[0].StringValue = "foo|bar";
xFilterDesc2.setFilterFields2(aFilterFields);

xFilter.filter(xFD)

[renbee]

Thank you very much! This did the trick. Perhaps documentation could be updated with this type of example to show others how to use TableFilterField2 (offering more operators including Contains).