The autofilter applied to cells with formula is not working

Aspose.Cells Product Family
1

Hi,

We have this problem in the production of a sheet excel office 2010.

We apply an autofilter on columns can contain a value or formula, in this case

the autofilter dont work.

NB With offices throughout 2003-2007 worked properly.

Aspose.cell version : 7.2.2.0

O.S.: Windows 7

Office 2010

Enclosed is a sample file of the product and its source codeEnclosed is a sample file of the product and its source code

Can’t help me ?

thanks

LUCA VALLERUGO

ENGINEERING.IT

luca.vallerugo@eng.it

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please provide your complete runnable code. When I extracted files form your

Attachment: TestAutoFilter.zip

I found, there is just one Program.cs and that file has only this code. Please provide us the full code, so that we could check this issue.

Program.cs
================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceProcess;
using System.Text;

namespace WinSincro
{
static class Program
{
///


/// The main entry point for the application.
///
static void Main()
{
ServiceBase[] ServicesToRun;
ServicesToRun = new ServiceBase[]
{
new SincroDevice()
};
ServiceBase.Run(ServicesToRun);
}
}
}

Screenshot:

3

Sorry,I was wrong.Now the attachment is giusNow the attachment is right.

LUCA

4

Hi,

Thanks for providing the sample code. Please also provide the expected output xlsx file.

I am attaching the output file which I got by running your code using the latest version:
Aspose.Cells
for .NET v7.2.2.7.
Please download it and fix it manually using Ms-Excel and post here.

Please also elaborate your problem with screenshots, it will help us look into this problem more precisely.

C#
string filePath = @“F:\Shak-Data-RW\Downloads\TestAutoFilter.xlsx”;

Workbook wb = new Workbook();
Worksheet sh = wb.Worksheets[0];
int iloop = 30;

sh.Cells[0, 4].PutValue(“Liv1”);
sh.Cells[0, 5].PutValue(“Liv2”);
sh.Cells[0, 6].PutValue(“Liv3”);
sh.Cells[0, 7].PutValue(“Liv4”);

for (int r = 1; r < iloop; r++)
{

if (r > 10 && r < 20)
{
if (r < 15)
{
sh.Cells[r, 0].PutValue(“ROW_FORMULATED_TRUE”);
sh.Cells[r, 1].PutValue(“s”);
sh.Cells[r, 2].PutValue(“s”);
sh.Cells[r, 3].PutValue(“s”);
sh.Cells[r, 4].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 5].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 6].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 7].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
}
else
{
sh.Cells[r, 0].PutValue(“ROW_FORMULATED_FALSE”);
sh.Cells[r, 1].PutValue(“”);
sh.Cells[r, 2].PutValue(“s”);
sh.Cells[r, 3].PutValue(“”);
sh.Cells[r, 4].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 5].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 6].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 7].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
}
}
else
{
if (r < 10)
{
sh.Cells[r, 0].PutValue(“ROW_X”);
sh.Cells[r, 1].PutValue(“”);
sh.Cells[r, 2].PutValue(“”);
sh.Cells[r, 3].PutValue(“”);
sh.Cells[r, 4].PutValue(“X”);
sh.Cells[r, 5].PutValue(“X”);
sh.Cells[r, 6].PutValue(“X”);
sh.Cells[r, 7].PutValue(“X”);
}
else
{
sh.Cells[r, 0].PutValue(“ROW_NULL”);
sh.Cells[r, 1].PutValue(“”);
sh.Cells[r, 2].PutValue(“”);
sh.Cells[r, 3].PutValue(“”);
sh.Cells[r, 4].PutValue(“”);
sh.Cells[r, 5].PutValue(“”);
sh.Cells[r, 6].PutValue(“”);
sh.Cells[r, 7].PutValue(“”);
}
}
}

sh.AutoFilter.Range = CellsHelper.ColumnIndexToName(4) + “1:” + CellsHelper.ColumnIndexToName(7) + (sh.Cells.MaxDataRow);
sh.AutoFilter.Custom(1, FilterOperatorType.Equal, “X”);
sh.AutoFilter.Refresh();
wb.Save(“OutTestAutoFilter.xlsx”, SaveFormat.Xlsx);

5

Errore Description

6

The cells with formulas should appear since openingv

7

Hi,

Thanks for your description. I was able to notice the problem.

We have logged this issue in our database. We will look into it and once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-40842.

Below is a screenshot that highlights the problem.

Screenshot:

8

Hi,

We have looked into your issue and the issue was occurring because you were missing Workbook.CalculateFormula() in your code.

Please call Workbook.CalculateFormula() before autofilter. It will resolve your problem.

Please also download and use the latest version:
Aspose.Cells
for .NET v7.2.2.7


For your help, please see the following
code:

C#


wb.CalculateFormula();

sh.AutoFilter.Range = CellsHelper.ColumnIndexToName(4) + “1:” + CellsHelper.ColumnIndexToName(7) + (sh.Cells.MaxDataRow);

sh.AutoFilter.Custom(1, FilterOperatorType.Equal, “X”);

sh.AutoFilter.Refresh();


Your complete code will look like the following, I have highlighted the changes in red.

Complete Code
string filePath = @“F:\Shak-Data-RW\Downloads\TestAutoFilter.xlsx”;

Workbook wb = new Workbook();
Worksheet sh = wb.Worksheets[0];
int iloop = 30;

sh.Cells[0, 4].PutValue(“Liv1”);
sh.Cells[0, 5].PutValue(“Liv2”);
sh.Cells[0, 6].PutValue(“Liv3”);
sh.Cells[0, 7].PutValue(“Liv4”);

for (int r = 1; r < iloop; r++)
{

if (r > 10 && r < 20)
{
if (r < 15)
{
sh.Cells[r, 0].PutValue(“ROW_FORMULATED_TRUE”);
sh.Cells[r, 1].PutValue(“s”);
sh.Cells[r, 2].PutValue(“s”);
sh.Cells[r, 3].PutValue(“s”);
sh.Cells[r, 4].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 5].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 6].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 7].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
}
else
{
sh.Cells[r, 0].PutValue(“ROW_FORMULATED_FALSE”);
sh.Cells[r, 1].PutValue(“”);
sh.Cells[r, 2].PutValue(“s”);
sh.Cells[r, 3].PutValue(“”);
sh.Cells[r, 4].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 5].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 6].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
sh.Cells[r, 7].Formula = “=IF(COUNTIF(B” + r + “:” + “D” + r + “,"s")=3,"","X")”;
}
}
else
{
if (r < 10)
{
sh.Cells[r, 0].PutValue(“ROW_X”);
sh.Cells[r, 1].PutValue(“”);
sh.Cells[r, 2].PutValue(“”);
sh.Cells[r, 3].PutValue(“”);
sh.Cells[r, 4].PutValue(“X”);
sh.Cells[r, 5].PutValue(“X”);
sh.Cells[r, 6].PutValue(“X”);
sh.Cells[r, 7].PutValue(“X”);
}
else
{
sh.Cells[r, 0].PutValue(“ROW_NULL”);
sh.Cells[r, 1].PutValue(“”);
sh.Cells[r, 2].PutValue(“”);
sh.Cells[r, 3].PutValue(“”);
sh.Cells[r, 4].PutValue(“”);
sh.Cells[r, 5].PutValue(“”);
sh.Cells[r, 6].PutValue(“”);
sh.Cells[r, 7].PutValue(“”);
}
}
}

wb.CalculateFormula();

sh.AutoFilter.Range = CellsHelper.ColumnIndexToName(4) + “1:” + CellsHelper.ColumnIndexToName(7) + (sh.Cells.MaxDataRow);
sh.AutoFilter.Custom(1, FilterOperatorType.Equal, “X”);
sh.AutoFilter.Refresh();
wb.Save(“TestAutoFilter.xlsx”, SaveFormat.Xlsx);

9

Thanks,

I have solved by moving commands calculateformula() and Autofilter.Refresh()

outside of the routine


Filter(sh);

FilterDetails(sh);

wb.CalculateFormula();

sh.AutoFilter.Refresh();

10

Hi

Thanks for your feedback.

It’s good to know your issue is now resolved.

Please feel free to post, if you face any other issue. We will be happy to help you.

Have a good day.