Filter is available in one of the cell in my excel sheet. I need to remove the filter to select / display all the items in the sheet. The filter is not in the column level but in the cell.
I have tried different code to remove the filter but no luck.
Including different code that I have tried.
Frist try:
Workbook workbook = new Workbook(@“C:\Projects\Resources\excel.xlsx”);
workbook.Worksheets[0].AutoFilter.Range = null;
workbook.Save(@“C:\Projects\excel.xlsx”);
Second try:
Workbook workbook = new Workbook(@“C:\Projects\Resources\excel.xlsx”);
workbook.Worksheets[0].RemoveAutoFilter();
workbook.Worksheets[0].AutoFilter.Refresh();
workbook.Save(@“C:\Projects\excel.xlsx”);
Third try:
Workbook workbook = new Workbook(@“C:\Projects\Resources\excel.xlsx”);
workbook.Worksheets[0].AutoFilter.RemoveFilter(2);
workbook.Save(@“C:\Projects\excel.xlsx”);
Forth try:
Workbook workbook = new Workbook(@“C:\Projects\Resources\excel.xlsx”);
workbook.Worksheets[0].RemoveAutoFilter();
workbook.Save(@“C:\Projects\excel.xlsx”);
None of the code removing the filter.
Excel with filter.JPG (137.1 KB)
Excel without filter.JPG (218.6 KB)
Attached the picture.
@faysalmahmood,
It seems your template file contains pivot table having filtered data. Please zip your template file and attach it here, we will check it soon.
FilteredExcel.zip (22.2 KB)
Thanks for the reply. I have attached the template file for your reference.
@faysalmahmood,
Thank you for the sample file. We will analyze it and share our feedback soon.
@faysalmahmood,
We have analyzed your requirement and logged it in our database for providing a sample code to generate the desired output. You will be notified here once any update is ready for sharing.
This issue is logged as:
CELLSNET-47860 - Show all data in Pivot Table
@faysalmahmood,
Please use the following code to test it:
Workbook wb = new Workbook(filePath + “FilteredExcel.xlsx”);
PivotTable pivot = wb.Worksheets[“Plan1”].PivotTables[0];
PivotField columnField = pivot.ColumnFields[0];
columnField.IsMultipleItemSelectionAllowed = true;
PivotItemCollection items = columnField.PivotItems;
int count = items.Count;
for (int i = 0; i < count; i++)
{
PivotItem item = items[i];
item.IsHidden = false;
}
wb.Save(filePath + “out.xlsx”);
Please try it and let us know your feedback.
Hi John,
Thank you very much for giving me a sample code. Looks like this code is selecting all the unselected items and saving the file. But when I am opening the newly saved file, Selected data still hidden. So this code is not fulfilling my purpose. Expecting some improvement.
I have a big file where I have multiple pivot tables and I have to select all the items and save in a new file or just refresh the original file so that I can select all the hidden data.
I have attached the new sample file for your understanding. In my newly attached file, in “Column C and rows 205, 240, 275, 310” you will see I have lot’s of hidden items and I have to select all the items in those dropdownlist.
The sample code I have tried to achieve my goal also added below.
var license = new License();
try
{
license.SetLicense("Aspose.Total.lic");
}
catch (FileNotFoundException)
{
// This should not keep the code from running.
}
var workbook = new Workbook(@"C:\Projects\FilteredExcel.xlsx");
foreach(var tbl in workbook.Worksheets["Plan1"].PivotTables)
{
foreach (PivotField fld in tbl.ColumnFields)
{
fld.IsMultipleItemSelectionAllowed = true;
PivotItemCollection items1 = fld.PivotItems;
for(int i=0; i<items1.Count; i++)
{
PivotItem item = items1[i];
item.IsHidden = false;
}
}
}
workbook.Save(@"C:\Projects\ExcelWithoutFilter.xlsx");
FilteredExcel.zip (388.0 KB)
@faysalmahmood,
I checked your file(s) a bit. It seems the pivot table(s) in the file has external data source or external file. That’s why Aspose.Cells cannot refresh data of the pivot table (it just selects all the items for filters). I am afraid, Aspose.Cells does not support to refresh pivot tables whose data source is external.
Thank you for looking into this issue. I am sure there is no external files available but might have external data sources. Would you be able to tell me where are those data sources have added in the excel file. I am not an excel expert and even couldn’t figure out details about this file.
Do you know any other solution to refresh the file/data after selecting all the items in C#?
@faysalmahmood,
Using your file, I checked data source for one of the pivot table in MS Excel and it gives me “‘G:\CDE - Coordenação de Banco de Dados e Estatísticas\05 - PUBLICAÇÕES SPD\ANP - Dados Mensais\Dados Mensais (2020)\Importações e Exportações[Import & Export m3 (dados de origem).xlsx]Import Petro’!$A$24:$Q$45”. I checked it in MS Excel manually by clicking on a cell in pivot table and then click “Analyze|Change Data Source” menu item/command under “PivotTable Tools”.