It recently came to my knowledge that the functionality in the function FindStringContains has changed. (we upgraded from older version of Aspose.cells (4.3) to fix Excel 2010 issues).
If you have a sheet with the following columns:
A B
1 =B1 Test
xlCell = xlWB.Worksheets(i).Cells.FindStringContains(“Test”, Nothing)
would in previous versions return “B1”, in later version (7.1) it returns “A1”, and I do not see that as correct behaviour.
Please reply ASAP!
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
Please download and use the latest version:
Aspose.Cells
for .NET v7.2.1.4
with the following code.
It will show you both cells A1 and B1
I have attached the source and screenshot for your reference.
VB.NET
Dim filePath
As String = “F:\source.xlsx”
Dim xlWB
As Workbook =
New Workbook(filePath)
'Will show A1
Dim xlCell
As Cell = xlWB.Worksheets(0).Cells.FindStringContains(“Test”,
Nothing)
'Will show B1
Dim xlCell2
As Cell = xlWB.Worksheets(0).Cells.FindStringContains(“Test”, xlCell)
C#
string filePath = @"F:\Shak-Data-RW\Downloads\source.xlsx";
Workbook xlWB = new Workbook(filePath);
//Will show A1
Cell xlCell = xlWB.Worksheets[0].Cells.FindStringContains("Test", null);
//Will show B1
Cell xlCell2 = xlWB.Worksheets[0].Cells.FindStringContains("Test", xlCell);
Screenshot:
Yes, but that does not help us. We have an application we’ve been working on since 2007 using tags in documents that are replaced by using aspose. Implementing a change like the one you suggest would take up many hours of tests since it is currently being used by 300+ customers and 1000+ different templates.
Can you verify that the funcionality has been changed between version 4 and 7?
Hi,
Please see, you are using quite an old version and it is quite possible that functionality has now changed.
But it looks to me a correct behavior.
Could you please create a sample application, source files and your expected output?
We will look into it and log in our database and if there are problems, we will fix it asap.
Also, I strongly recommend you to upgrade your code according to the latest version because we provide fixes based on latest versions only.
Your example shows the problem exactly.
When you’re looking for the cell containing a specific value, you do not want it to return a cell that’s referencing to a cell containing the value.
Hi,
If you just want to find inside formulas, then you will use the following code.
Please use FindOptions.LookInType to specify that you want to search formulas only.
Let me know if it fits your needs.
VB.NET
Dim filePath As String = “F:\Shak-Data-RW\Downloads\source.xlsx”
Dim xlWB As Workbook = New Workbook(filePath)
Dim fopts As FindOptions = New FindOptions
fopts.LookInType = LookInType.Formulas
Dim xlCell As Cell = xlWB.Worksheets(0).Cells.FindString(“Test”, Nothing, fopts)
Hi,
Using latest versions e.g v7.x.x, you may use FindString() method and specify FindOptions accordingly. See the updated code (provided by Shakeel Faiz with his file), it works fine for your needs.
Sample code:
Dim filePath As String = “e:\test2\source.xlsx”
Dim xlWB As New Workbook(filePath)
Dim opt As New FindOptions()
opt.LookAtType = LookAtType.Contains
opt.LookInType = LookInType.Values
'Will show B1
Dim xlCell As Aspose.Cells.Cell = xlWB.Worksheets(0).Cells.FindString(“Test”, Nothing, opt)
Thank you.
Ah, FindString with FindOptions does the trick. It means I wont have to replace FindStringContains with loops filtering out formulas (and that would require tests!)!
Still dangerous when a core functionality like FindStringContains is changed in such a dangerous way!
Hi,
Thanks for your feedback.
It’s good to know that FindString with FindOptions.LookInType fits your needs.
As I suggested earlier, you should upgrade your code with the latest version asap because all our fixes will now be based on latest versions only.
Please download and try the latest offline demos, they will help you in upgrading your code.
These demos can work with Visual Studio 2005, 2008 or 2010. Please read the readme.txt file before running the demos.
![]()
We use very basic functionality in the aspose-component, so upgrading is very easy (4.3 -> 7 I only needed to change the references). The problem as I said is when the core functionality changes.
We’ve created approx. 800 000 excel-documents with aspose, and this is the first real problem we’ve had when upgrading.
Hi,
Thanks for your further information.
It is good to know that you were able to upgrade your code so quickly.
Also you can continue using your older application as long as there is no problem with it.
Have a nice day.
URGENT!
The workaround DOES NOT WORK!
Dim opt As New FindOptions()
opt.LookAtType = LookAtType.Contains
opt.LookInType = LookInType.Values
If I have a cell with value “<!tag1!><!tag2!>“
And I run the code:
xlWB.Worksheets(0).Cells.FindString(”<!tag1!>”, Nothing, opt)
the cell with value “<!tag1!><!tag2!>” WILL NOT BE FOUND!
This is now putting a halt to our environment, fix is needed ASAP!
Hi
You are right. It seems like this functionality is broken so it should be fixed.
I have logged this issue in our database. Development team will soon look into it and fix the problem.
This issue has been logged as CELLSNET-40704.
VB.NET
Dim filePath As String = “F:\Shak-Data-RW\Downloads\source.xlsx”
Dim xlWB As Workbook = New Workbook(filePath)
Dim fopts As FindOptions = New FindOptions
fopts.LookAtType = LookAtType.Contains
fopts.LookInType = LookInType.Values
Dim xlCell As Cell = xlWB.Worksheets(0).Cells.FindString("<!tag1!>", Nothing, fopts)
Hi,
This seems to me an issue with FindString method with FindOptions specified, we have already logged a ticket for the issue (as Shakeel Faiz has done already).
As a workaround, you may try to use FindStringContains method instead for it (when you use tags to find), it works fine as I tested.
Aspose.Cells.Cell xlCell = xlWB.Worksheets(0).Cells.FindStringContains("<!tag1!>", Nothing);
Thank you.
Yes, but FindStringContains is also broken, since it will return a cell with a formula referencing the value being searched…
So at the moment I cannot use your component!
Hi,
I think, you are having problem distinguishing the cell with formula and cell with actual value.
You can check Cell.Formula property, if Cell.Formula property is empty and the Cell.StringValue contains your value, then it means it is the cell you are searching for. It is the cell which does not have any formula but only have your searched value.
And if Cell.Formula property is not empty, then you can skip this cell and search next cell.
Yes, but that means doing this distinction in about 40 places in the code, and testing it against 50+ different scenarios, and that will take me about 4 work-days, and we have customers complaining about the loss of functionality already, so that is not an option.
A fix in the component is the only way to cope with the problem.
Hi,
If the workarounds do no fit your needs, please spare us a little time (2-3 working days), hopefully, we can fix your issue soon. Once we figure your issue out, we will let you know here immediately.
Thanks for your considerations.
FredrikMolnar:
Yes, but that means doing this distinction in about 40 places in the code, and testing it against 50+ different scenarios, and that will take me about 4 work-days, and we have customers complaining about the loss of functionality already, so that is not an option.
A fix in the component is the only way to cope with the problem.
Hi,
Please wait for a fix for 2~3 working days.
Also, I have a written my own find string method which uses the built-in find string method but returns only those cells which does not have formula.
Please see if it could be any help for you.
I have attached the source workbook used by this code and the output that it shows.
VB.NET
Sub Test()
Dim filePath As String = "F:\Shak-Data-RW\Downloads\source.xlsx"
Dim xlWB As Workbook = New Workbook(filePath)
Dim ws As Worksheet = xlWB.Worksheets(0)
Dim xlCell As Cell = MyFindString(ws, "Test", Nothing)
While Not xlCell Is Nothing
'Will print the cell name
Debug.WriteLine(xlCell.Name)
xlCell = MyFindString(ws, "Test", xlCell)
End While
End Sub
'This find string will skip the cell if the cell has formulas
Function MyFindString(ByVal ws As Worksheet, ByVal inputString As String, ByVal previousCell As Cell) As Cell
Dim xlCell As Cell = ws.Cells.FindStringContains(inputString, previousCell)
If xlCell Is Nothing Then
Return Nothing
End If
If xlCell.Formula <> "" Then
Return MyFindString(ws, inputString, xlCell)
End If
Return xlCell
End Function
Output:B1
D1
F1
H1
I1
J1
K1
L1
M1
D2
D3
D4
D5
D6
D7
Screenshot:
Hi,
We have fixed this issue.
Please download and try the latest fix: Aspose.Cells for .NET v7.2.1.5
We have added one LookInType for your requirement, please use the new option with Cells.Find() method.
See the following code example.
C#
FindOptions opt =
new FindOptions();
opt.LookAtType = LookAtType.Contains;
opt.LookInType = LookInType.ValuesExcludeFormulaCell;
Cell cell = cells.Find(“Test”,
null, opt);