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);
Well, it works… Will you fix the bug in any updates later on, or will this be the solution you will release?
Hi,
We have released a fix/solution in this latest version:
Aspose.Cells
for .NET v7.2.1.5
You can use this release for your production use. It is called minor release.
Both minor and major releases are equally same except major release is published with release notes and both are meant for production use.
Please see the sample release notes for Major release at the following link. I have also posted a screenshot for your reference.
Release Notes - Aspose.Cells for .NET 7.2.1
Screenshot:
That was not my question.
One of the things that made us decide to use Aspose was that it copied the functionality of Microsoft Excel, and it did it in a excellent way.
The solution you provided now is a workaround. I will be using a function in a way that Excel does not use it, and if you do this with this function will future bugs be fixed in the same way, or will you provide a fix that solves the core problem?
All the other times I’ve reported bugs you’ve always compared the function with how Excel executes the function, and if you begin to differ from that in this instance, what stops it from happening more times in the future?
Hi,
Actually, we have not provided you a workaround. We have provided you a solution.
Did you try this code?
C#
FindOptions opt = new FindOptions();
opt.LookAtType = LookAtType.Contains;
opt.LookInType = LookInType.ValuesExcludeFormulaCell;
Cell cell = cells.Find(“Test”, null, opt);
I just tested this:
FindOptions opt = new FindOptions();
opt.LookAtType = LookAtType.Contains;
opt.LookInType = LookInType.Values;
Cell cell = cells.Find(“Test”, null, opt);
and it worked, so the bug is fixed.
I just was confused by why
"ValuesExcludeFormulaCell"
was added, since that was not a part of my initial problem.
Hi,
That’s great to know that you have tested the code and your problem is now resolved. 
We have closed this ticket.
If you encounter any other problem or have some questions, please feel free to post, we will be glad to assist you.
Thanks for your time.
or maybe I’m just confused…
the “ValuesExcludeFormulaCell” works for me…
The major confusion on my part is that the “findstringcontains” has changed it’s behaviour, and I’m “scared” that the same will happen in other functions in the future.
Hi,
Actually, this is unlikely to happen in future because earlier we were developing and maintaining separate Aspose.Cells for .NET and Aspose.Cells for Java codes.
Because of that there were many features lacking and inconsistent in both .NET and Java versions.
So, we have to make them consistent and now Aspose.Cells for Java code is ported from Aspose.Cells for .NET code.
This was the reason that functionality changes occurred but now we have a consistent model, so this change is now highly unlikely to occur in future.
Ok, I will hold you to that… 
The issues you have found earlier (filed as CELLSNET-40704) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan