Custom Row filter in Aspose.Grid!

Hi,

I'm the licensed user of aspose.dll. The Aspose.Grid is taking time to apply custom row filter. It is taking around 10 seconds to search in 50000 records. I'm attaching a dummy desktop application VS 2008, so that you can check. Please make it fast as soon as possible.

Also pls tell how to do like search in custom row filter in aspose. For example, In sql server, if we have to do like search we use:

1) '%Fund%' to perfom contains search

2) 'Fund%' to perform begins search

Is there any facility in aspose filter to do so, if not then pls also implement this as soon as possible.

Hi,

Thanks for providing us the project.

If you are using ComboxBoxe in multi-threading, so the performance is acceptable. Anyways, we have enhanced the formula calculation engine in the attached version, so please give it it a try if it enhances the performance. Also, kindly let us know about it.

1) '%Fund%' to perfom contains search

2) 'Fund%' to perform begins search

We have already implemented it, please check the thread for reference: <A href="</A></P> <P> </P> <P>Thank you.</P>

Hi,

As told in the link for begins and contains search, I have implemented this code in the same dummy application. But it is not working accordingly. Also the "Ignore Case" property is not working. And from that link I think that "IsStartWithCriteria" is only for "begins search". And if it is not true then the search is only for exact value. But I also need the property for "contains search".

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

grdDataEntry.Worksheets(0).Columns(1).IsStartWithCriteria = True

grdDataEntry.Worksheets(0).Columns(1).IgnoreCase = True

grdDataEntry.Worksheets(0).RowFilter.Criteria = "CELL1=""" + RichTextBox1.Text.Trim + """"

grdDataEntry.Worksheets(0).RowFilter.HeaderRow = 1

grdDataEntry.Worksheets(0).RowFilter.StartRow = 2

grdDataEntry.Worksheets(0).RowFilter.EndRow = grdDataEntry.Worksheets(0).RowsCount

grdDataEntry.Worksheets(0).RowFilter.FilterRows()

End Sub

Pls help me in this..

Hi,

After an initial test, we found the issue implementing your code, we will figure it out and get back to you soon.

Thank you.

Hi,

Also I have to implement search on two columns with the help of row filter property. For ex. I have two text box and they represent two different columns in the grid. When I will enter value in these two text boxes then I should only get those values that matches the criteria. Please implement it with And, Or both

I'm trying with this code but of no use:

lstrCriteria = "CELL" & m_intInvestorNameColIndex & "=""" & lstrInvestorName & """ And "" CELL" & m_intFundNameColIndex & "=""" & lstrFundName & """"

Pls help me in this as both (above & this) the implementaions are very urgent.

Hi,

Please try the attached version. In this version we have implemented your requirements regarding custom row filter. Please try the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'grdDataEntry.Worksheets(0).RowFilter.Criteria = "CELL1=""" + RichTextBox1.Text.Trim + """"

'grdDataEntry.Worksheets(0).RowFilter.Criteria = "STARTWITH(CELL1, """ + RichTextBox1.Text.Trim + """ ,false)"

'grdDataEntry.Worksheets(0).RowFilter.Criteria = "COMPAREIGNORECASE(CELL1, """ + RichTextBox1.Text.Trim + """ ,True)"

'grdDataEntry.Worksheets(0).RowFilter.Criteria = "CONTAIN(CELL1, """ + RichTextBox1.Text.Trim + """ ,true)"

'Dim part1 As String = "CELL1=""" + RichTextBox1.Text.Trim + """"

Dim part1 As String = "CONTAIN(CELL1, """ + RichTextBox1.Text.Trim + """ ,true)"

Dim part2 As String = "CELL3=""" + RichTextBox2.Text.Trim + """"

'grdDataEntry.Worksheets(0).RowFilter.Criteria = "AND(" + part1 + "," + part2 + ")"

grdDataEntry.Worksheets(0).RowFilter.Criteria = "OR(" + part1 + "," + part2 + ")"

grdDataEntry.Worksheets(0).RowFilter.HeaderRow = 1

grdDataEntry.Worksheets(0).RowFilter.StartRow = 2

grdDataEntry.Worksheets(0).RowFilter.EndRow = grdDataEntry.Worksheets(0).RowsCount

grdDataEntry.Worksheets(0).RowFilter.FilterRows()

End Sub

Thank you.

Hi,

I have checked your new dll. But the new implementaions is working only in the case of "Contains Search" not with the "Begins Search". Also the true or false property in the string part1 is not affecting the search. It is returning the same values.

I'm implementing this code in the same dummy application:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'grdDataEntry.Worksheets(0).Columns(1).IsStartWithCriteria = True

Dim part1 As String = "CONTAIN(CELL1, """ + RichTextBox1.Text.Trim + """ ,false)"

grdDataEntry.Worksheets(0).RowFilter.Criteria = part1

grdDataEntry.Worksheets(0).RowFilter.HeaderRow = 1

grdDataEntry.Worksheets(0).RowFilter.StartRow = 2

grdDataEntry.Worksheets(0).RowFilter.EndRow = grdDataEntry.Worksheets(0).RowsCount

grdDataEntry.Worksheets(0).RowFilter.FilterRows()

End Sub

Have you worked on your older property of:

grdDataEntry.Worksheets(0).Columns(1).IsStartWithCriteria = True

Pls tell me if it is working now or not? If yes, pls provide some sample code

Also pls implement this "Begins with Search" and "Conatins Search" properly as I needed them urgently and correctfully

Hi,

Well, the IsStartWithCriteria property works for Auto Row Filter feature and not for Custom Row Filter.

Please try the commented lines in previous reply i.e…,:

’grdDataEntry.Worksheets(0).RowFilter.Criteria = “CELL1=”"" + RichTextBox1.Text.Trim + “”""

’grdDataEntry.Worksheets(0).RowFilter.Criteria = “STARTWITH(CELL1, “”” + RichTextBox1.Text.Trim + “”" ,false)"

’grdDataEntry.Worksheets(0).RowFilter.Criteria = “COMPAREIGNORECASE(CELL1, “”” + RichTextBox1.Text.Trim + “”" ,True)"

’grdDataEntry.Worksheets(0).RowFilter.Criteria = “CONTAIN(CELL1, “”” + RichTextBox1.Text.Trim + “”" ,true)"

'Dim part1 As String = “CELL1=”"" + RichTextBox1.Text.Trim + “”""

Dim part1 As String = “CONTAIN(CELL1, “”” + RichTextBox1.Text.Trim + “”" ,true)"

Dim part2 As String = “CELL3=”"" + RichTextBox2.Text.Trim + “”""

'grdDataEntry.Worksheets(0).RowFilter.Criteria = “AND(” + part1 + “,” + part2 + “)”

grdDataEntry.Worksheets(0).RowFilter.Criteria = “OR(” + part1 + “,” + part2 + “)”

Notes:

The “=” operator matches exactly. The STARTWITH function matches at the beginning part. The COMPAREIGNORECASE function matches without case. The CONTAIN function matches at any part.

The 3 parameters of these functions indicate whether ignoring case-sensitive. If set to true, it ignores case-sensitivity; else retains/applies.

The AND, OR implement the logical calculations.

Hopefully, you understand now.

Thank you.

Hi,

If I have three or more strings, then can I filter the row criteria as:

grdDataEntry.Worksheets(0).RowFilter.Criteria = “OR(” + part1 + “,” + part2 + “,” +part3+ “,” +part4+")"

same with AND.

Hi,

Yes, you can, following is my test code:

Dim part1 As String = "CONTAIN(CELL1, """ + TextBox1.Text.Trim + """ ,true)"

Dim part2 As String = "CELL3=""" + TextBox2.Text.Trim + """"

Dim part3 As String = "CELL5=""" + TextBox3.Text.Trim + """"

Dim part4 As String = "CELL7=""" + TextBox4.Text.Trim + """"

grdDataEntry.Worksheets(0).RowFilter.Criteria = "OR(" + part1 + "," + part2 + "," + part3 + "," + part4 + ")"

grdDataEntry.Worksheets(0).RowFilter.HeaderRow = 1

grdDataEntry.Worksheets(0).RowFilter.StartRow = 2

grdDataEntry.Worksheets(0).RowFilter.EndRow = grdDataEntry.Worksheets(0).RowsCount

grdDataEntry.Worksheets(0).RowFilter.FilterRows()

Thank you.

Hi,

I have just checked that after doing the search when I'm using this code:

grdDataEntry.Worksheets(0).RowFilter.ClearFilter()

on a click of button is adding one extra blank row at the bottom of the grid. Can you pls check it in the dummy application. And pls modify this soon as we can't afford one extra blank row at the bottom. This will create a huge problem for us.

Pls correct as soon as possible

Hi,

Yes, we noticed the issue as you have mentioned, we will try to figure it out soon.

Thank you.

Hi,

Also can you please make the searching of the grid more fast. It is taking time when the condition of search criteria is more complex. As shown in the notepad file named "search criteria" this is the final search criteria. Also the code i'm using to implement this is attached with file name as "code".

We have to implement this search criteria to have the required output.

Pls update as soon as possible

Hi,

Please modify the line as following:

grdDataEntry.Worksheets(0).RowFilter.EndRow = grdDataEntry.Worksheets(0).RowsCount – 1.

This will resolve the issue of the blank row at the bottom of the GridDesktop.

We have checked your “code” and “search criteria”. I think this is not an efficient solution via custom row filter if the criteria is too complex, it will take long time to calculate the criteria for each row.

Here are 2 possible solutions instead of custom row filter.

  1. Create a new DataTable object and use DataTable.ImportRow() method to import the selected DataRow array objects. And then use Worksheet.ImportDataTable() method to import data again.

  2. Implement your row filter via setting GridRow.Hidden property. You can get the selected rows index via invoking DataTable.Rows.IndexOf() method. And then use the index to get the GridRow object and set its Hidden property.

By the way, the custom row filter criteria supports “CELL2 >10013”, “CELL2 <= 15000”, CELL2 <>10013, CELL2 <> A1 etc. But these range operators don’t suit your need.

Hopefully, this can help you.

Thank you.