Cells.Find Contains doesn't work with Tilde characters

Aspose.Cells Product Family
1

Prior to upgrading to 21.12, searching for “~~Value” in a cell using LookAtType.Contains option would find the cell. After the upgrade, the search fails. Even when we escape the tildes using additional tildes. The escape of the tildes works when using LookAtType.StartWith, but NOT with LookAtType.Contains.

Here is a test method that demonstrates the failure, note that the searches with “Contains” option fail to find the cells:

[Test]
	public void FindExtensionTest()
	{
		Workbook wb = new Workbook();
		Worksheet ws = wb.Worksheets.Add("Tilde Test 1");
		ws.Cells[0, 1].PutValue("fooey");
		ws.Cells[1, 1].PutValue("~blah");
		ws.Cells[4, 4].PutValue("~TildeTest");
		ws.Cells[5, 5].PutValue("~~TildeTest");
		ws.Cells[5, 6].PutValue("Don't find me.");
		ws.Cells[6, 1].PutValue("?Hello");
		ws.Cells[7, 2].PutValue("??Goodbye");
		ws.Cells[8, 3].PutValue(string.Empty);
		ws.Cells[9, 4].PutValue("~~ContainsTest");

		FindOptions findOptions = new FindOptions();

		// Demonstrate that single tilde finds no data.
		findOptions.LookAtType = LookAtType.StartWith;
		Cell cell = ws.Cells.Find("~", null, findOptions);
		Assert.AreEqual(null, cell?.Value);

		// Demonstrate that nullifying the tilde with a tilde will search for a tilde.
		findOptions.LookAtType = LookAtType.StartWith;
		cell = ws.Cells.Find("~~", null, findOptions);
		Assert.AreEqual("~blah", cell.Value);

		// Demonstrate that the same search fails when doing Contains option.
		findOptions.LookAtType = LookAtType.Contains;
		cell = ws.Cells.Find("~~", null, findOptions);
		Assert.IsNull(cell);

		// Do a double tilde search without nullifying the tildes
		// and verify that the wrong value was found.
		findOptions.LookAtType = LookAtType.StartWith;
		cell = ws.Cells.Find("~~TildeTest", null, findOptions);
		Assert.AreNotEqual("~~TildeTest", cell.Value);

		// Do double tilde search with proper nullification
		// and verify that the correct cell was found.
		findOptions.LookAtType = LookAtType.StartWith;
		cell = ws.Cells.Find("~~~~TildeTest", null, findOptions);
		Assert.AreEqual("~~TildeTest", cell.Value);

		// Demonstrate that the same search fails when doing Contains option.
		findOptions.LookAtType = LookAtType.Contains;
		cell = ws.Cells.Find("~~~~TildeTest", null, findOptions);
		Assert.IsNull(cell);
	}
2

@jcapson,

Please notice, I am able to reproduce the issue as you mentioned. I found Cells.Find Contains does not work properly with Tilde characters. I have logged a ticket with an id “CELLSNET-50861” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

3

@Amjad_Sahi, thank you for the reply and for logging a ticket.

4

@jcapson,

You are welcome.

5

@jcapson,

We have fixed one issue of searching string with LookAtType.Contains. In the next (fixed) version, you will use below code:

            findOptions.LookAtType = LookAtType.StartWith;
            cell = ws.Cells.Find("~", null, findOptions);
            Console.WriteLine("1:" + cell);

            findOptions.LookAtType = LookAtType.StartWith;
            cell = ws.Cells.Find("~~", null, findOptions);
            Console.WriteLine("2:" + cell);

            findOptions.LookAtType = LookAtType.Contains;
            cell = ws.Cells.Find("~~", null, findOptions);
            Console.WriteLine("3:" + cell);

            findOptions.LookAtType = LookAtType.StartWith;
            cell = ws.Cells.Find("~~TildeTest", null, findOptions);
            Console.WriteLine("4:" + cell);

            findOptions.LookAtType = LookAtType.Contains;
            cell = ws.Cells.Find("~~TildeTest", null, findOptions);
            Console.WriteLine("4:" + cell);

            findOptions.LookAtType = LookAtType.StartWith;
            cell = ws.Cells.Find("~~~~TildeTest", null, findOptions);
            Console.WriteLine("5:" + cell);

            findOptions.LookAtType = LookAtType.Contains;
            cell = ws.Cells.Find("~~~~TildeTest", null, findOptions);
            Console.WriteLine("6:" + cell);

it will give the following output:

1:
2:Aspose.Cells.Cell [ B2; ValueType : IsString; Value : ~blah ]
3:Aspose.Cells.Cell [ B2; ValueType : IsString; Value : ~blah ]
4:Aspose.Cells.Cell [ E5; ValueType : IsString; Value : ~TildeTest ]
4:Aspose.Cells.Cell [ E5; ValueType : IsString; Value : ~TildeTest ]
5:Aspose.Cells.Cell [ F6; ValueType : IsString; Value : ~~TildeTest ]
6:Aspose.Cells.Cell [ F6; ValueType : IsString; Value : ~~TildeTest ]

Could you please check whether it is the expected result?

6

@Amjad_Sahi, your console writes look correct.

7

@jcapson,

Thanks for your confirmation.

We will keep you posted on the fixed version (once available).

8

The issues you have found earlier (filed as CELLSNET-50861) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi