javac#libreofficelibreoffice-calcuno

Programmatically check/uncheck values of a DataPilot row field with the uno library


I want to be able to programmatically filter a Calc DataPilot (pivot table) with C# or Java.

At the moment, I am able to do the below:

  1. go to the sheet that contains the DataPilot
  2. get a reference to the DataPilot
  3. Read its rowfields
  4. Get a reference to the rowfield of interest

Pending:

  1. By default, all values under the row field are checked. From that list, I want to be uncheck all of them and keep only specific.

Is this doable programmatically? If yes, what is the missing code to achieve this?

Current code

XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)document; 
XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 

//mSheetName is the name of the sheet that has the DataPilot I want to filter 
// get reference to the sheet that has my DataPilot 
XSpreadsheet sheet = (XSpreadsheet)xSpreadsheets.getByName(mSheetName).Value; 

//Get a reference to the DataPilot that I want to uncheck/check its row field values 
XDataPilotTablesSupplier xSupplier = (XDataPilotTablesSupplier)sheet; 
XDataPilotTables xSheetPilotTables = xSupplier.getDataPilotTables(); 

//mPivotTableName is the name of the DataPilot 
uno.Any xDPTableObj = xSheetPilotTables.getByName(mPivotTableName); 
XDataPilotTable xPilotTable = (XDataPilotTable)xDPTableObj.Value; 
XDataPilotDescriptor xDPDesc = (XDataPilotDescriptor)xPilotTable; 

// get available row fields
XNameAccess rowFields = (XNameAccess)xDPDesc.getRowFields(); 

//get the row field of interest 
//mFieldName is the name of the row field 
uno.Any xRowItemObj = rowFields.getByName(mFieldName); 

Example: if we assume the xRowItemObj has 2 checked values (e.g. 1 and 2) how do I keep value 1 checked only?

So far, any research in forums and libreOffice Documentation did not return a result. I am not even sure if it is doable


Solution

  • Get the list of items from the row field. Here is a Basic example that checks even-numbered boxes.

    oRowItem = oDPTable.getRowFields().getByIndex(0)
    oItems = oRowItem.getItems()
    For i = 0 To oItems.getCount() - 1
        oItem = oItems.getByIndex(i)
        If (i Mod 2 = 0) Then
            oItem.setPropertyValue("IsHidden", False)
        Else
            oItem.setPropertyValue("IsHidden", True)
        End If
    Next
    

    If you haven't yet, be sure to use an introspection tool such as MRI.