@amjad.sahi will test it out and let you know the results
Sure, please feel free to take your time to test it for your specific scenario or case. Hopefully, it will work for the intended purpose for now.
@amjad.sahi , the changes above did not work for me, I’m not getting any value filter applied. I’m not sure what seems to the issue. I have been stuck with issue for a while now.
Is there any way to achieve this?
@JayaPrakash031
Please try the following sample code and refer to the attachment. out_java.zip (54.8 KB)
Workbook wb = new Workbook(filePath + "Actually Generated.xlsx");
PivotTable table = wb.getWorksheets().get("p").getPivotTables().get(0);
int index = table.getBaseFields().get("School Name").getBaseIndex();
addValueNotEmptyFilter(table, index);
wb.save(filePath + "out_java.xlsx");
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");
}
Hope helps a bit.
Hey @amjad.sahi / @John.He / @simon.zhao ,
Sorry for my misunderstanding, In my previous comment I mentioned that the code you have provided didn’t work but it actually kinda worked. The code that @simon.zhao sent seems to be applying the filter as expected, I can see the filter being applied.
but the issue is, even after a value filter being applied it doesn’t remove the blank.
When I dig into it, I found this weird behavior happening like though the filter is been applied from the code it does not remove the blank, but when I check the value filter applied and click on ok. now it blank are removed. not sure why it’s happening like that, do we have to refresh once after the filter is applied??
Right after applying values filter from code : (you can see the blanks are still there)
image.png (51.5 KB)
After I went inside the filter applied and clicked ok: (No blanks found and filter is being applied)
image.png (42.6 KB)
I have attached the file below, you can observe the same here
SampleFile (2).zip (15.6 KB)
My Current Code:
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);
filter.setMeasureFldIndex(0);
// Setting the value to filter out empty or blank entries
// filter.setValue1("0");
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}`);
}
}
Thanks for the template Excel file.
After initial testing, I was able to notice the issue using your sample code with template file. I noticed that “(blank)” data is not removed/filtered in the pivot table report. I even tried to add the following lines but to no avail.
filter.getAutoFilter().refresh(true);
pivotTable.refreshData();
pivotTable.calculateData();
We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46176
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@JayaPrakash031
If you want to exclude blank entries, it’s better that you can use Label filters:
public static void main(String[] args) throws Throwable {
Workbook wb = new Workbook(dir + "INV564135-2024-08-10-somealtname-ESSSouthCentral-ab (2).xlsx");
PivotTable table = wb.getWorksheets().get("p").getPivotTables().get(0);
PivotFilterCollection pivotFilters = table.getPivotFilters();
pivotFilters.clear();
PivotFieldCollection fields = table.getRowFields();
for(int i = 0; i < fields.getCount(); i++)
{
PivotField field = fields.get(i);
if(field != table.getDataField())
addCaptionNotEmptyFilter(table, field.getBaseIndex());
}
wb.save(dir + "dest.xlsx");
}
static void addCaptionNotEmptyFilter(PivotTable pivotTable, int fieldIndex)
{
PivotFilterCollection pivotFilters = pivotTable.getPivotFilters();
// pivotFilters.clear();
PivotFieldCollection dataFields = pivotTable.getDataFields();
int filterIndex = pivotFilters.add(fieldIndex, PivotFilterType.CAPTION_NOT_EQUAL);
PivotFilter filter = pivotFilters.get(filterIndex);
//filter.setMeasureFldIndex(dataFieldIndex);
// Setting the value to filter out empty or blank entries
filter.setValue1("");
//filter.setValue1("blank"); // Assuming blank values are treated as 0 in your case
filter.getAutoFilter().matchNonBlanks(0);
}
@JayaPrakash031
It’s hard to use value filter to exclude blank entries.
A pivot field only can has one value filter. So if there are some value fields, we can not add all filters for these value fields based on one field.
Blank entries still exist with the following codes because it only filters by the first data field, so “Total Duration” are filtered, but other value field still should display.
addValueNotEmptyFilter(table, 5);
static void addValueNotEmptyFilter(PivotTable pivotTable, int fieldIndex)
{
PivotFilterCollection pivotFilters = pivotTable.getPivotFilters();
// pivotFilters.clear();
PivotFieldCollection dataFields = pivotTable.getDataFields();
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("");
//filter.setValue1("blank"); // Assuming blank values are treated as 0 in your case
filter.getAutoFilter().custom(0, FilterOperatorType.NOT_EQUAL, 0);
}
It’s very strange that if you insert the new filter to first position, not append it, all blanks are filtered.
Please check the attached file:
INV.zip (16.2 KB)
A number value was entered into A12 in the worksheet InvoiceData .
The value filter can not work as excepted.
So please use caption filter.
@simon.zhao
It’s not just blank values, in some cases even the column name is present in the pivot table as value. you can observe in the file that I have attached; we have data in two sections one would be Weekly Billing, and another one would be under Prior Period Billing.
When selecting source data for pivot table. I’d be selecting them as whole which results in column Names in Prior Period Billing becoming values for pivot table as well but those are not the valid values.
When I apply the value !=0 filter, it not only removes the blanks but also all those columns names which are included as values in pivot table.
That’s the reason I’m trying to apply the value filter.
image.png (12.7 KB)
If there’s any solution to resolve the above-mentioned scenario, I’d be happy it hear it as well.
I’m attaching a sample file for you reference
SampleOutputFile.zip (53.8 KB)
@JayaPrakash031
If there are multiple values under a pivot field that need to be filtered, you can control them more finely by setting the properties of the pivot item. Please refer to the following example code and check the attachment. out_java.zip (56.8 KB)
Workbook wb = new Workbook(filePath + "SampleOutputFile.xlsx");
PivotTable pivot = wb.getWorksheets().get("p").getPivotTables().get(0);
PivotFieldCollection rowFields = pivot.getRowFields();
PivotField field = rowFields.get("School Name");
PivotItemCollection items = field.getPivotItems();
int count = items.getCount();
for (int i = 0; i < count; i++)
{
PivotItem item = items.get(i);
String value = item.getStringValue();
//null means (blank)
if (null == value || "School Name".equals(value))
{
item.setHidden(true);
}
}
pivot.setRefreshDataFlag(true);
pivot.refreshData();
pivot.calculateData();
pivot.setRefreshDataFlag(false);
wb.save(filePath + "out_java.xlsx");
Hope helps a bit.
Yeah I’ll try this out, and let you know the results.
Sure, please try the suggested code segment and hopefully it will work for the purpose.