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:
Pending:
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
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.