I have been trying to add value filter in pivot table but it’s not working as expected , I’m not sure what’s the issue cause I’m a newbie to aspose. I have attached my code below.
help me out here please
image.png (23.7 KB)
@JayaPrakash031
Please try to call the following code to refresh and calculate the pivot table after completing all pivot table settings.
The sample code as follows:
pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.setRefreshDataFlag(false);
You can also use the following method to refresh and calculate all the pivot tables of the entire workbook.
workbook.getWorksheets().refreshPivotTables();
If you still have questions, would you like to provide sample files and runnable test code? We will check them soon.
Hi @John.He , thanks your reply. I have attached my complete code for pivot table along with the sample data below. I don’t have any test scripts right now.
var aspose: any = aspose || {};
aspose.cells = require(‘aspose.cells’);
export interface CustomerConfigurationBackupPivotTable {
id: number;
title: string;
backupPivotTableFields: BackupPivotTableFields[];
}
export const PivotFieldType = {
row: ‘row’,
column: ‘column’,
filter: ‘filter’,
value: ‘value’
};
export interface BackupPivotTableFields {
fieldName: string;
customName: string;
isChecked: boolean;
orderRowNumber: number;
fieldType: string;
valueSummarizeMethod: string;
}
export interface Customer {
customerConfigurationBackupPivotTables: CustomerConfigurationBackupPivotTable[]
}
const sampleCustomerPivotableData: Customer = {
customerConfigurationBackupPivotTables: [{
id: 1,
title: “pivot table”,
backupPivotTableFields: [
{
“fieldName”: “schoolName”,
“fieldType”: “row”,
“customName”: “”,
“valueSummarizeMethod”: “”,
“orderRowNumber”: 2,
“isChecked”: true
},
{
“fieldName”: “eventDate”,
“fieldType”: “column”,
“customName”: “Absence Date”,
“valueSummarizeMethod”: “”,
“orderRowNumber”: 1,
“isChecked”: true
},
{
“fieldName”: “employeeExternalId04”,
“fieldType”: “value”,
“customName”: “Sum of Employee External ID 04”,
“valueSummarizeMethod”: “Sum”,
“orderRowNumber”: 1,
“isChecked”: true
},
{
“fieldName”: “essEmplFullName”,
“fieldType”: “value”,
“customName”: “Sum of Substitute Full Name”,
“valueSummarizeMethod”: “Sum”,
“orderRowNumber”: 2,
“isChecked”: true
},
{
“fieldName”: “payCode”,
“fieldType”: “column”,
“customName”: “”,
“valueSummarizeMethod”: “”,
“orderRowNumber”: 2,
“isChecked”: true
},
{
“fieldName”: “payCodeDesc”,
“fieldType”: “filter”,
“customName”: “”,
“valueSummarizeMethod”: “”,
“orderRowNumber”: 1,
“isChecked”: true
},
{
“fieldName”: “eventDurationType”,
“fieldType”: “row”,
“customName”: “”,
“valueSummarizeMethod”: “”,
“orderRowNumber”: 1,
“isChecked”: true
},
{
“fieldName”: “eventReason”,
“fieldType”: “row”,
“customName”: “”,
“valueSummarizeMethod”: “”,
“orderRowNumber”: 3,
“isChecked”: true
}
]
}]
}
const generatePivotTable = (customer: Customer) => {
const logTag = “[generatePivotTable]”;
const sampleWorkbook = new aspose.cells.Workbook();
const worksheet = sampleWorkbook.getWorksheets().get(0);
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");
});
}
const addNotEmptyFilter = (pivotTable: any, fieldIndex: number) => {
let filterApplied = false;
const logTag = ‘[addNotEmptyFilter]’
try {
const pivotFilters = pivotTable.getPivotFilters();
const filterIndex = pivotFilters.add(fieldIndex, aspose.cells.PivotFilterType.VALUE_NOT_EQUAL);
const filter = pivotFilters.get(filterIndex);
console.log(‘Available methods for pivot filter (first approach):’, Object.getOwnPropertyNames(Object.getPrototypeOf(filter.getAutoFilter())));
filter.setValue1(“0”);
console.log(logTag, Added "not zero" filter to field index: ${fieldIndex});
filterApplied = true;
} catch (error) {
console.error(logTag, Error with first approach of adding "not zero" filter to field index ${fieldIndex}:, error);
}
// If the first approach fails, try the second approach
if (!filterApplied) {
try {
const pivotFilters = pivotTable.getPivotFilters();
const filterIndex = pivotFilters.add(fieldIndex, aspose.cells.PivotFilterType.VALUE_NOT_EQUAL);
const filter = pivotFilters.get(filterIndex);
console.log(‘Available methods for pivot filter (second approach):’, Object.getOwnPropertyNames(Object.getPrototypeOf(filter.getAutoFilter())));
filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_EQUAL, “0”);
console.log(Added "not zero" filter to field index: ${fieldIndex} using second approach);
} catch (error) {
console.error(Error with second approach of adding "not zero" filter to field index ${fieldIndex}:, error);
}
}
}
export const excelColumnAssigner = (index: number): string => {
let letter = ‘’;
let tempIndex = index;
while (tempIndex >= 0) {
letter = String.fromCharCode(‘A’.charCodeAt(0) + tempIndex % 26) + letter;
tempIndex = Math.floor(tempIndex / 26) - 1;
}
return letter;
};
generatePivotTable(sampleCustomerPivotableData);
To give a better context of what your looking at, I’m trying to use aspose.cells from js via java. I’m trying to populate the value in customerConfigurationBackupPivotTables
in to the pivot table of excel.
everything in the above code works except the pivot table emptyFilter part.
I tried applying caption filter it worked for me but when I try to apply a value filter, that where I’ m facing the issue
My requirement is to apply a value filter !=0 , that way it filter out all the blank row and column in pivot table . I have attached functionality in excel I’m trying to achieve.
thanks in advance
image.png (11.8 KB)
image.png (28.2 KB)
I have attached sample excel zip file for reference
sample exel file.zip (1.0 MB)
Thanks for the screenshots and code snippets.
Could you please also zip and attach Excel file(s) containing the pivot tables. We will look into your issue soon.
I have updated my above comment kindly look into it and let me know if you need anything else
@JayaPrakash031
Thanks for your feedback and further details. We will evaluate your issue further and get back to you soon.
@JayaPrakash031
Please try:
filter.getAutoFilter().custom(0, aspose.cells.FilterOperatorType.NOT_EQUAL, "*blank*");
I’m glad to hear that the suggested line of code resolved your issue. Please don’t hesitate to reach out to us again if you have any other questions or comments.