How to access AutoFilter Cells

Hello,


I am using the following code to AutoFilter my spreadsheet:

AutoFilter autoFilter = worksheet.AutoFilter;

autoFilter.Range = “E2:E” + worksheet.Cells.MaxDataRow;
autoFilter.AddFilter(0, “”);


How can I access the Cells within the AutoFiltered range to use the SetSharedFormula to the filtered range?

Thank you!

–Eric

Hi,


Thanks for your query.

See the following sample code for your needs for your reference. The code shows on how you may access the filtered cells in the auto-filtered range, it will print all the filtered cells’ names. Please refer to the code segment, so you may write your own code for your requirements accordingly:
e.g
Sample code:

//Create a new workbook
Workbook workbook = new Workbook();
//Get the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];

//Get the cells collection in the sheet
Cells cells = sheet.Cells;

//Put some values into cells
cells[“A1”].PutValue(“Fruit”);
cells[“B1”].PutValue(“Total”);
cells[“A2”].PutValue(“Apple”);
cells[“B2”].PutValue(1000);
cells[“A3”].PutValue(“Orange”);
cells[“B3”].PutValue(2500);
cells[“A4”].PutValue(“Bananas”);
cells[“B4”].PutValue(2500);
cells[“A5”].PutValue(“Orange”);
cells[“B5”].PutValue(1000);
cells[“A6”].PutValue(“Grape”);
cells[“B6”].PutValue(2000);
cells[“D1”].PutValue(“Count:”);

//Set a formula to E1 cell
cells[“E1”].Formula = “=SUBTOTAL(2,B1:B6)”;
workbook.CalculateFormula();
Console.WriteLine(cells[“E1”].StringValue);//5 - Ok
//Represents the range to which the specified AutoFilter applies
sheet.AutoFilter.Range = “A1:B6”;
//Add your desired filters
sheet.AutoFilter.AddFilter(0, “Orange”);
sheet.AutoFilter.Refresh();
workbook.CalculateFormula();
//Get the changed value for count.
Console.WriteLine(cells[“E1”].StringValue);//2 - Ok

//The filtered rows are visible and non filtered cells are invisible,
//So you may easily get to know the filtered cells.
//Scan the autofilter range: A2:B6 to get the filtered cell names
for (int i = 1; i <= cells.MaxDataRow; i++)
{
Row row = cells.Rows[i];
if (!row.IsHidden)
{
for (int j = 0; j < 2; j++)
{

Console.WriteLine(cells[i, j].Name);

}
}

}
workbook.Save(“e:\test2\out1myfilters1.xlsx”);

Hope, this helps a bit.

Thank you.

Thank you!


Is there a more efficient way to do this when I have a very large number of rows?

Perhaps I should be more clear on what I am trying to accomplish.


I have a worksheet with thousands of rows. One of the columns can have blank values.

I would like to somehow filter just that range of blank cells in that column and apply a formula to those cells.

Hi,


Could you perform your task in MS Excel manually to save an Excel file and provide us here. Also, give us details/ steps involved on how could you accomplish the task in MS Excel manually, so we could understand you better and may help you on how to do it via Aspose.Cells APIs.

Thank you.

I have attached a sample Excel file that was built from my previous process, which used Excel Interop. I have removed any sensitive data, and have removed a lot of the rows to make it easier for you to look at.


What I am trying to accomplish is a way to select only rows 2, 3, and 4 where column E is blank. And for just those 3 cells, I want to apply the VLOOKUP formula.

Here is the C# code that works using Excel Interop, I’m having trouble re-writing it to use Aspose.Cells:

//Apply VLOOKUP for blank Common Names

Excel.Range range = worksheet.UsedRange;
range.AutoFilter(5, “=”, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);

//Iterate the rows in the filtered range
foreach (Excel.Range row in filteredRange.Rows)
{
if (row.Cells[5].Value == “”)
row.Cells[5].FormulaR1C1 = “=VLOOKUP(RC[15],‘Common Name For IT’!C[-3]:C[-1],3,FALSE)”;
}

//Clear AutoFilter on the Common Name field
range.AutoFilter(5);



Hi,


Thanks for the template file and sample code snippet (using Excel interop. APIs).

Well, I think you may try to use Find and Search options provided by Aspose.Cells APIs to scan the specified column’ cells only and replace your desired formula to those (found) cells. This is an efficient way to accomplish your task a bit. See the sample code segment for your reference:
e.g
Sample code:

string filePath = @“e:\test2\sample.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;
Aspose.Cells.Cell prevcell = null;
Aspose.Cells.Cell foundCell = null;
string stringToFind = “=”; //You can put formula strings too or change it as per your needs
FindOptions opts = new FindOptions();
//Create a Cells Area based on the E column only
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 4;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = 4;

//Set cells area for find options
opts.SetRange(ca);
opts.LookAtType = LookAtType.StartWith; //use as per your needs
opts.LookInType = LookInType.Formulas; //since you want to search in formulas only

do
{
foundCell = worksheet.Cells.Find(stringToFind, prevcell, opts);
if (foundCell == null)
break;
Console.WriteLine(foundCell.Name);
if (foundCell.StringValue == “”)
{
foundCell.R1C1Formula = “=VLOOKUP(RC[15],‘Common Name For IT’!C[-3]:C[-1],3,FALSE)”;

}
prevcell = foundCell;

} while (foundCell != null);

workbook.Save(“e:\test2\out1.xlsx”, SaveFormat.Xlsx);


Hope, this helps a bit.

Thank you.

Excellent, thank you very much, this method worked for me!

Hi,


Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.