How to programmatically apply a value filter to exclude blank entries in Aspose.Cells pivot table?

Aspose.Cells Product Family
1

I’m trying to create a value filter for my pivot table using Aspose.Cells. My goal is to programmatically filter out blank or empty values from certain columns in the pivot table. Currently, some columns in my pivot table contain blank or empty values. I’ve noticed that manually applying a value filter with the condition “not equal to zero” successfully removes these blank entries. However, I need to implement this filtering through code. I’ve reviewed the Aspose.Cells documentation, but haven’t found a clear solution for this specific need. I’ve successfully implemented a label filter using code (see below), but this doesn’t meet my requirements:

function addNotEmptyFilter(pivotTable: any, fieldIndex: number) {
    try {
        const pivotFilters = pivotTable.getPivotFilters();
        const filterIndex = pivotFilters.add(fieldIndex, aspose.cells.PivotFilterType.CAPTION_NOT_EQUAL);
        const filter = pivotFilters.get(filterIndex);

        filter.setValue1("blank");
        filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_CONTAINS, "blank");

        console.info(`Added "not blank" filter to field index: ${fieldIndex}`);
    } catch (error: any) {
        console.error(`Error adding "not blank" filter to field index ${fieldIndex}: ${error.message}`);
    }
}

What I need:

  1. A step-by-step guide to create a value filter for my pivot table that excludes blank or zero values.
  2. Explanation of when and how to use methods like setValue1() and setValue2() in pivot table filters. I’ve attached a sample file demonstrating the desired outcome: applying a value filter “not equal to zero” to remove all blank data from the pivot table. Any help or code examples would be greatly appreciated. Thanks in advance!

I have attached my sample files below:

samplefile.zip (175.0 KB)

2

@JayaPrakash031,

To filter out blank or empty values in a pivot table using Aspose.Cells, you can apply a value filter instead of a label filter. Since your manual filter approach using “not equal to zero” works, the equivalent in code is to use a custom value filter to exclude blank values (or zeroes). Here’s an approach to implement this using Aspose.Cells’ PivotFilter and AutoFilter functionalities:

  1. Instead of using PivotFilterType.CAPTION_NOT_EQUAL, switch to PivotFilterType.VALUE_NOT_EQUAL.
  2. Apply a filter that removes blank (or zero) values by targeting the appropriate data field rather than the label.
    e.g,
function addValueNotEmptyFilter(pivotTable: any, fieldIndex: number) {
    try {
        const pivotFilters = pivotTable.getPivotFilters();
        const filterIndex = pivotFilters.add(fieldIndex, aspose.cells.PivotFilterType.VALUE_NOT_EQUAL);
        const filter = pivotFilters.get(filterIndex);

        // Setting the value to filter out empty or blank entries
        filter.setValue1("0"); 
        //filter.setValue1("blank"); // Assuming blank values are treated as 0 in your case
        filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_EQUAL, 0);

        console.info(`Added "not empty" value filter to field index: ${fieldIndex}`);
    } catch (error: any) {
        console.error(`Error adding "not empty" value filter to field index ${fieldIndex}: ${error.message}`);
    }
}

In case, you still find any issue, kindly provide your complete sample (runnable) code and sample files to demonstrate the issue, we will check it soon.

3

@amjad.sahi , will try the above and let you know. but I still have some quires about the setValue1() function? what is the exact use of that method? I went through documentation, but I couldn’t find the exact use case of that method.

4

@JayaPrakash031,

The PivotFilter.setValue1() and PivotFilter.setValue2() methods are used to set comparison values when applying filters on a pivot table. These methods are typically used in scenarios where you’re filtering data based on numerical or textual conditions, and their usage depends on the type of filter you’re applying.

  1. setValue1() - This method sets the first comparison value, which is often used when you’re applying single-value filters such as “equal to,” “not equal to,” “greater than,” or “less than”, etc.

  2. setValue2() - This method is used in scenarios where a range filter is needed (e.g., “between” or “not between”). In such cases, setValue1() defines the lower boundary, and setValue2() defines the upper boundary.

Hope, this helps a bit.

5

@amjad.sahi thanks for the insight, I have just a small doubt here in this piece of code like for custom method the criteria which is 0, should it be String or can also be Numeric?

filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_EQUAL, 0)
6

@JayaPrakash031,

It should be:

filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_EQUAL, "0")
7

@amjad.sahi thank you. will let you know the result

8

@JayaPrakash031 ,

Please take your time and let us know your feedback.

9

Hey @amjad.sahi / @peyton.xu , the above is not working for me, like I’m not getting any errors, but no filter is applied is my pivot table.

here is full code

        if (customer.customerConfigurationBackupPivotTables) {
            customer.customerConfigurationBackupPivotTables.forEach((pivotTable: CustomerConfigurationBackupPivotTable) => {
                pivotTable.backupPivotTableFields.sort((a: BackupPivotTableFields, b: BackupPivotTableFields) => a.orderRowNumber - b.orderRowNumber);
            });
        }

        customer.customerConfigurationBackupPivotTables.forEach((pivotTableConfig: CustomerConfigurationBackupPivotTable) => {

            console.log(logTag, 'Working on Pivot Table:', pivotTableConfig.title);

            sampleWorkbook.getWorksheets().add(`${pivotTableConfig.title}`);

            const pivotSheet = sampleWorkbook.getWorksheets().get(`${pivotTableConfig.title}`);
            const pivotTables = pivotSheet.getPivotTables();

            const lastColumn = excelColumnAssigner(processedTransactionInvoiceDataKeys.length - 2);
            const sourceData = `=${'InvoiceData'}!A7:${lastColumn}${lastDataRow + 1}`;
            console.log("Source Data", sourceData);

            const pivotTableIndex: number = pivotTables.add(sourceData, 'A1', `${pivotTableConfig.title}`);
            const pivotTable = pivotTables.get(pivotTableIndex);

            pivotTableConfig.backupPivotTableFields.forEach((fieldConfig: BackupPivotTableFields) => {
                console.log(logTag, `  - Pivot Table Field: ${JSON.stringify(fieldConfig)}  - fieldConfig.isChecked: ${fieldConfig.isChecked}`);

                if (fieldConfig.isChecked) {

                    const fieldIndex = processedTransactionInvoiceDataKeys.findIndex(({ key }) => {
                        const matchesFieldName: boolean = key === fieldConfig.fieldName;
                        return matchesFieldName;
                    });

                    if (fieldIndex > 0) {
                        console.log(logTag, `  - fieldConfig.fieldName: ${fieldConfig.fieldName}  - fieldConfig.fieldType: ${fieldConfig.fieldType}  - Field Index: ${fieldIndex}`);
                    }

                    if (fieldIndex >= 0) {
                        try {
                            switch (fieldConfig.fieldType) {
                                case PivotFieldType.row:
                                    console.log(`Adding ROW field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.ROW}`);
                                    pivotTable.addFieldToArea(aspose.cells.PivotFieldType.ROW, fieldIndex);
                                    addNotEmptyFilter(pivotTable, fieldConfig.orderRowNumber - 1);
                                    break;
                                case PivotFieldType.column:
                                    console.log(`Adding COLUMN field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.COLUMN}`);
                                    pivotTable.addFieldToArea(aspose.cells.PivotFieldType.COLUMN, fieldIndex);
                                    addNotEmptyFilter(pivotTable, fieldConfig.orderRowNumber - 1);
                                    break;
                                case PivotFieldType.filter:
                                    console.log(`Adding PAGE field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.PAGE}`);
                                    pivotTable.addFieldToArea(aspose.cells.PivotFieldType.PAGE, fieldIndex);
                                    break;
                                case PivotFieldType.value:
                                    console.log(`Adding DATA field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.DATA}`);
                                    pivotTable.addFieldToArea(aspose.cells.PivotFieldType.DATA, fieldIndex);

                                    const dataFieldIndex = fieldConfig.orderRowNumber - 1;
                                    const pf = pivotTable.getDataFields().get(dataFieldIndex);

                                    if (pf) {
                                        if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'sum') {
                                            console.log(`Setting SUM function. ConsolidationFunction: ${aspose.cells.ConsolidationFunction.SUM}`);
                                            pf.setFunction(aspose.cells.ConsolidationFunction.SUM);
                                        } else if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'count') {
                                            console.log(`Setting COUNT function. ConsolidationFunction: ${aspose.cells.ConsolidationFunction.COUNT}`);
                                            pf.setFunction(aspose.cells.ConsolidationFunction.COUNT);
                                        }

                                        if (fieldConfig.customName) {
                                            try {
                                                pf.setDisplayName(fieldConfig.customName);
                                                console.log(`Set display name for data field: ${fieldConfig.customName}`);
                                            } catch (error: any) {
                                                console.log(`Setting display name for data field failed: ${error.message}`);
                                            }
                                        }
                                    } else {
                                        console.error(`Field "${fieldConfig.valueSummarizeMethod}" not found in pivot table data fields.`);
                                    }
                                    break;
                                default:
                                    console.error(`Unknown field type: ${fieldConfig.fieldType}`);
                            }
                        } catch (error: any) {
                            console.error(`Error in switch case for fieldType ${fieldConfig.fieldType}: ${error.message}`);
                            console.error(`fieldConfig: ${JSON.stringify(fieldConfig)}`);
                            console.error(`fieldIndex: ${fieldIndex}`);
                        }
                    } else {
                        console.error(`Field index out of bounds ${fieldIndex}`);
                    }

                }
            });

            pivotTable.setRowGrand(true);
            pivotTable.setColumnGrand(true);
            pivotTable.setShowDrill(false);

            console.log(logTag, "-->Start of setting row subtotals<--");

            console.log(logTag, pivotTable.getRowFields().getCount(), JSON.stringify(pivotTable.getRowFields()), "Row Field Data");

            if (pivotTable.getRowFields().getCount() > 0) {
                for (let i = 0; i < pivotTable.getRowFields().getCount(); i++) {
                    try {
                        const rowField = pivotTable.getRowFields().get(i);
                        const fieldName = rowField.getName() || "";

                        if (!rowField || typeof rowField.setSubtotals !== 'function') {
                            console.warn(`Invalid row field ${fieldName} at index ${i}. Skipping.`);
                            continue;
                        }

                        rowField.setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE, true);
                        console.log(`Successfully set subtotals for field: ${fieldName} and fieldIndex : ${i}`);
                    } catch (error) {
                        console.error(`Error setting subtotals for row field  ${pivotTable.getRowFields().get(i).getName()} and fieldIndex : ${i} `, error);
                    }
                }
            }

            console.log(logTag, "-->End of setting row subtotals<--");

            console.log(logTag, "-->Start of setting column subtotals<--");

            console.log(logTag, pivotTable.getColumnFields().getCount(), JSON.stringify(pivotTable.getColumnFields()), "Cloumn Field Data")


            if (pivotTable.getColumnFields().getCount() > 0) {
                for (let i = 0; i < pivotTable.getColumnFields().getCount(); i++) {
                    try {
                        const columnField = pivotTable.getColumnFields().get(i);
                        const fieldName = columnField.getName() || "";

                        if (!columnField || typeof columnField.setSubtotals !== 'function') {
                            console.warn(`Invalid column field ${fieldName} at index ${i}. Skipping.`);
                            continue;
                        }

                        columnField.setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE, true);
                        console.log(`Successfully set subtotals for field: ${fieldName} and fieldIndex : ${i}`);
                    } catch (error) {
                        console.error(`Error setting subtotals for column field ${i}: ${pivotTable.getColumnFields().get(i)?.getName() ?? ""} : `, error);
                    }
                }
            }

            console.log(logTag, "-->End of setting column subtotals<--");

            pivotTable.refreshData();
            pivotTable.calculateData();

            console.log(logTag, "Pivot table has been created");

        });

Here is sample data for customer.customerConfigurationBackupPivotTables

         "customerConfigurationBackupPivotTables": [
                {
                    "id": 520,
                    "title": "p",
                    "backupPivotTableFields": [
                        {
                            "id": 3624,
                            "fieldName": "timesheetEventDuration",
                            "customName": "Total Duration",
                            "isChecked": true,
                            "orderRowNumber": 1,
                            "fieldType": "value",
                            "valueSummarizeMethod": "Sum",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3625,
                            "fieldName": "eventDate",
                            "customName": "Absence Date",
                            "isChecked": true,
                            "orderRowNumber": 1,
                            "fieldType": "column",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3626,
                            "fieldName": "billedTotal",
                            "customName": "Total Amount",
                            "isChecked": true,
                            "orderRowNumber": 2,
                            "fieldType": "value",
                            "valueSummarizeMethod": "Sum",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3627,
                            "fieldName": "accountingCode",
                            "customName": "",
                            "isChecked": true,
                            "orderRowNumber": 2,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3628,
                            "fieldName": "accountingCodeDesc",
                            "customName": "",
                            "isChecked": true,
                            "orderRowNumber": 3,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3629,
                            "fieldName": "schoolName",
                            "customName": "",
                            "isChecked": true,
                            "orderRowNumber": 1,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3630,
                            "fieldName": "payCode",
                            "customName": "",
                            "isChecked": true,
                            "orderRowNumber": 10,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3631,
                            "fieldName": "payCodeDesc",
                            "customName": "",
                            "isChecked": true,
                            "orderRowNumber": 4,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3632,
                            "fieldName": "essEmplFullName",
                            "customName": "Substitute Name",
                            "isChecked": true,
                            "orderRowNumber": 5,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3633,
                            "fieldName": "employeeFullName",
                            "customName": "Employee Name",
                            "isChecked": true,
                            "orderRowNumber": 6,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3634,
                            "fieldName": "employeeExternalId04",
                            "customName": "Employee ID 04",
                            "isChecked": true,
                            "orderRowNumber": 7,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3635,
                            "fieldName": "confirmationNumber",
                            "customName": "Confirmation #",
                            "isChecked": true,
                            "orderRowNumber": 8,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3636,
                            "fieldName": "eventDurationType",
                            "customName": "",
                            "isChecked": true,
                            "orderRowNumber": 9,
                            "fieldType": "row",
                            "valueSummarizeMethod": "",
                            "customerConfigurationPivotTableId": 520
                        },
                        {
                            "id": 3637,
                            "fieldName": "batchId",
                            "customName": "Count Batch Number",
                            "isChecked": true,
                            "orderRowNumber": 3,
                            "fieldType": "value",
                            "valueSummarizeMethod": "Count",
                            "customerConfigurationPivotTableId": 520
                        },
                         {
                            "id": 3637,
                            "fieldName": "batchId",
                            "customName": "Count Batch Number",
                            "isChecked": true,
                            "orderRowNumber": 3,
                            "fieldType": "filter",
                            "valueSummarizeMethod": "Count",
                            "customerConfigurationPivotTableId": 520
                        }
                    ]
                }
            ]

So how my code works is it will be adding all the fileNames in customerConfigurationBackupPivotTables to pivot table one by one.
The Data for pivot table will be source from a excel sheet which I’ll be creating before this pivot table.

let me know if you guys need the full excel logic. It’s quite big that’s why I didn’t attach it here

10

@JayaPrakash031
Could you share your result file and expected file here ? We will check it soon.

11

@simon.zhao
here is expected and actual file
Expected And Actual Files.zip (103.1 KB)

The files and data in it are same, but in the expected one we’ll be applying values filter

12

@JayaPrakash031
Okay.we will check it soon.

13

@JayaPrakash031
Please set measured field index with calling PivotFilter.setMeasureFldIndex() if the pivot table contains more than one data pivot field.

static void  addValueNotEmptyFilter(PivotTable pivotTable, int fieldIndex) {
	  
	    	PivotFilterCollection pivotFilters = pivotTable.getPivotFilters();
	        int filterIndex = pivotFilters.add(fieldIndex,PivotFilterType.VALUE_NOT_EQUAL);
	        PivotFilter filter = pivotFilters.get(filterIndex);
	        filter.setMeasureFldIndex(0);

	        // Setting the value to filter out empty or blank entries
	       // filter.setValue1("0"); 
	        //filter.setValue1("blank"); // Assuming blank values are treated as 0 in your case
	        filter.getAutoFilter().custom(0, FilterOperatorType.NOT_EQUAL, 0);

	        
	   
	}

And we have logged an a ticket CELLSNET-57172 to improve API of Adding and updating pivot filter .

14

@simon.zhao
So, we don’t need the setValue1() function just custom filter is now?

15

@JayaPrakash031,

Yes, please try the suggested code segment by @simon.zhao and let us know your feedback.

16

@amjad.sahi
Whether the zero passed as argument should be integer or string? cause last time we tried with string. So just confirming.

Integer : 
filter.getAutoFilter().custom(0, FilterOperatorType.NOT_EQUAL, 0);

OR 

String : 
filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_EQUAL, "0")
17

@JayaPrakash031

For EQUAL and NOT_EQUAL, both of 0 and “0” should have the same effect. So you may use any one of them to get the expected result.

18

@JayaPrakash031,

If you are using Aspose.Cells for Node.js API, then it should be in string.

19

@amjad.sahi / @johnson.shi
okay thanks for the information.

20

@JayaPrakash031,

You are welcome. Should you have further queries or comments, please feel free to write us back.