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 code works except the pivot table addNotEmptyFilter 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 image of the functionality in excel I’m trying to achieve.
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);