Worksheet autofilter

johnson.shi · January 16, 2008, 3:25am

Hi,

Please try this fix.

HGOAS4 · January 16, 2008, 4:16am

Did you see the problem ?!

Can you give me more feedback ?

I always experience new troubles when applying new fixes ! Very annoying !!

HGOAS4 · January 16, 2008, 4:30am

Still same error !!

java.lang.ArrayIndexOutOfBoundsException
at java.lang.Throwable.(Throwable.java:180)
at java.lang.Exception.(Exception.java:29)
at java.lang.RuntimeException.(RuntimeException.java:32)
at java.lang.IndexOutOfBoundsException.(IndexOutOfBoundsException.java:27)
at java.lang.ArrayIndexOutOfBoundsException.(ArrayIndexOutOfBoundsException.java:26)
at com.aspose.cells.bj.e(Unknown Source)
at com.aspose.cells.bj.a(Unknown Source)
at com.aspose.cells.ce.d(Unknown Source)
at com.aspose.cells.ce.a(Unknown Source)
at com.aspose.cells.v.b(Unknown Source)
at com.aspose.cells.v.a(Unknown Source)
at com.aspose.cells.Cell.c(Unknown Source)
at com.aspose.cells.Worksheet.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)

amjad.sahi · January 16, 2008, 4:38am

Hi,

We will figure it out soon.

Thanks for being patient!

HGOAS4 · January 16, 2008, 7:29am

Can this help you ?

johnson.shi · January 16, 2008, 7:09pm

Hi,

I had tested the fix with your template file before post it to you, it works fine. Following is my sample code:

Workbook wb = new Workbook();
wb.open("FCC037_XUCOM.xls");
wb.calculateFormula();
wb.calculateFormula(); //is it the case you said "Works fine on first call, but on second call error occurs"?
wb.save("tmp.xls");

If the same issue occurred with another template, would you please give us the template file to reproduce it? Thanks for your patience.

The previous version before this fix did have one bug when calculate formulas, we made many changes on the inner data structure and logic from version 1.9.2 for removing 65k rows limitation, unfortunately that introduces this bug. Sorry for the inconvenience it caused.

HGOAS4 · January 17, 2008, 4:44am

Ok, I'll have a new try.

The error occured on second call, meaning :

Workbook wb = new Workbook();
wb.open("FCC037_XUCOM.xls");
wb.calculateFormula();
wb.save("tmp.xls");

Workbook wb = new Workbook();
wb.open("FCC037_XUCOM.xls");
wb.calculateFormula();
wb.save("tmp.xls"); --> Error

Thanks

HGOAS4 · January 17, 2008, 8:34am

Ok, problem solved.

Thanks !

HGOAS4 · January 21, 2008, 2:15am

What happened to autoFilter.matchnonBlanks !!!!!!!!!????????

I'm using this new function in my programs, but it's gone in this fix !!!!!

Again some jobs crashed because of aspose.cells. How can I explain all these things to my boss !!??

Can you please fix this soon !

Laurence · January 21, 2008, 2:49am

I am sorry for the problem.

For autoFilter.matchnonBlanks issue, it is a code merge problem. We will provide a fix soon.

For other crashed job, could you give us more detail information? Thank you very much.

HGOAS4 · January 21, 2008, 3:04am

Current crashes are all due to matchnonblanks issue.

Warren · January 21, 2008, 3:07am

Hi,

Sorry for our mistake.

Please try this fix.

Could you give us more detail about crashed jobs(template file and sample codes)? We will check it soon.

HGOAS4 · September 10, 2012, 9:37am

Hello,

How do I specify this “criteria” ?

Can I put “X or Y” ?

Thanks a lot !

shakeel.faiz · September 10, 2012, 11:28am

Hi,

Thanks for using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.3.1.2 and try the following code.

I have attached the output file generated by this code and a screenshot for your reference.

Please also refer to this article for your further help. (The code is obsolete, we will soon update it).

Data Filtering and Validation

In case, you still need help, please create your sample xls/xlsx file manually using Ms-Excel and attach it here. We will look into your issue and help you asap.

C#

//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(“Pear”);

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)”;

//Represents the range to which the specified AutoFilter applies

sheet.AutoFilter.Range = “A1:B6”;

workbook.Save(“output2.xlsx”);

Screenshot:

HGOAS4 · September 11, 2012, 12:53am

Hello,

Thanks for the reply.

I already know how to set a filterrange, and how to filter on one criteria, but how can I select multiple selections from the selection-list of one column ?

For instance, how can I set filter to “Orange” + “Grape” ?

Thanks !

amjad.sahi · September 11, 2012, 2:55am

Hi,

See the lines in bold, I have added to Shakeel 's example for your reference:

//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(“Pear”);

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)”;

//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.AddFilter(0, “Grape”);

sheet.AutoFilter.Refresh();

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

HGOAS4 · September 11, 2012, 3:06am

Thanks.

How does the system know if its a AND/OR relation ?

HGOAS4 · September 11, 2012, 3:30am

Hello,

Can you tell me the possible values for the second parameter of the “CustomFilter” function ?

first parameter = column number

second parameter = ??? --> type of comparison ??

Third parameter = filter string

Thanks a lot !

amjad.sahi · September 11, 2012, 3:34am

Hi,

I think you may use AutoFilter.Custom() method. See the API Reference page (check the isAnd parameter for reference):

public void Custom ( Int32 fieldIndex, FilterOperatorType operatorType1, Object criteria1, Boolean isAnd, FilterOperatorType operatorType2, Objectcriteria2 )

HGOAS4 · September 11, 2012, 3:34am

I’m trying to achieve something like

ActiveSheet.Range("$A$4:$AP$143").AutoFilter Field:=1, Criteria1:="=Y", _

Operator:=xlOr, Criteria2:="=YX"

This is a macro recorded in Excel