Aspose.cells regex text search

Aspose.Cells Product Family
1

Hi, I am trying to switch from JXL to Aspose. Aspose did not have support for search text in sheets through regex. Recently this feature was added by your team. Now i am having one more issue. If there is a text like "abc " i.e. abc with ending spaces. Then with JXL if i search for "abc"+spaceregex (where spaceregex = "[\\s]*"). It finds the string even if there are any number of spaces at the end. But as per the latest version of aspose, it can search only with * used as regex. where * is any character sequence. It would be good if aspose too supports complete regex search. i.e. if i append spaceRegex="[\\s]*"; to the search string then it should search abc with any number of spaces at the end.

Thanks & Regards,

Rohan Naik

2

Hi,


Though using statment like

String searchRejex = “abc *”;

finds all such strings for you, but it has the limitation of including other strings as well that have non-space characters at the end. The idea of including spaceRegex seems to be partially implemented using the above statement, I am logging this as a new feature in our database for our development team to look at it and advice further.
3

Hi, The search string in my case cannot be "abc *". What i would search for is "abc"+regex. No white spaces in search string. The space regex should take care of the white spaces if found any in the sheet. So my search string would be "abc[\\s]*" and it should search for string abc in sheet with any number of ending spaces.

Thanks & Regards,

Rohan Naik

4

Hi,

Thanks for your input.

We have logged your comments against the issue id: CELLSJAVA-40191

Development team will look into your requirements and once we have some update relating to, we will update you asap.

5

Hi,

Please download and try the latest fix: Aspose.Cells for Java v7.2.1.4

Please try this fix with following code for your purpose:

Java


FindOptions opt = new FindOptions();

opt.setRegexKey(true); //take the searched key of find() method as standard regex

opt.setLookAtType(LookAtType.ENTIRE_CONTENT);

cells.find(“abc[\s]*$”, null, opt); //to search content that ends with abc and any number of spaces.


6

Hi. I tested the code with this latest jar. It is giving me CellsException: Fails to find the next cell.

Following is the sample code and attached is the test file.

public static void main(String[] args) {

String filepathA = "C://Users//ronaik//Desktop//FileA.xls";

String test7 = "that is the open of(*) :";

String test8 = "* close *";

String spaceRegex="[\\s]*$";

try{

LoadOptions loadOptions1 = new LoadOptions(FileFormatType.EXCEL_97_TO_2003);

Workbook workbook1 = new Workbook(filepathA);

CellArea cellArea = new CellArea();

cellArea.StartRow = 0;

cellArea.EndRow = 50;

cellArea.StartColumn = 0;

cellArea.EndColumn = 255;

FindOptions opt = new FindOptions();

opt.setRange(cellArea);

opt.setRegexKey(true);

opt.setLookAtType(LookAtType.ENTIRE_CONTENT);

test7 = test7+spaceRegex;

Cell c = workbook1.getWorksheets().get(0).getCells().find(test7,null, opt);

Cell c1 = workbook1.getWorksheets().get(0).getCells().find(test8,null, opt);

System.out.println("test");

}catch(Exception e){

e.printStackTrace();

}

}

7

Hi,

Thanks for your input.

I was able to replicate the exception using your code with the latest version:
Aspose.Cells
for Java v7.2.1.4

I have logged your code and comment in our database. Development team will look into this issue and fix the problems. I have also reopened this issue.

The comment has been logged against the issue id: CELLSJAVA-40191

Below is a screenshot for a reference.

Screenshot:

8

Hi,

For following code:

FindOptions opt = new FindOptions();
opt.setRegexKey(true); //take the searched key of find() method as standard regex

When you set the RegexKey property as true, we will take your input searched key as standard regex. Please note the wildcard '’ in windows environment and ms excel is not a standard interpretation for regex. To use it as wildcard for search, RegexKey must be false. To use standard regex, the wildcard should be ‘.’. For your case, the code should be:

String test7 = "that is the open of\(.\) :";
String test8 = “.* close .*”;

9

The issues you have found earlier (filed as CELLSJAVA-40191) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
10

Hi. Well there seems to be one problem with regex search functionality.

When i set the setRegexKey to true, then the string to be searched is searched in cells as .*sampleString.* even though i do not append the .* characters. You can test the below code for attached sample file. Run it with setRegexKey set to true. Run it once with .* characters appended and once without appending. In both cases with setRegexKey true, it will search the string in the file.

public static void main(String[] args) {

String filepathA = "Z://Desktop//FileA.xls";

String test7 = "close";

String test8 = ".* close .*";

String spaceRegex="[\\s]*$";

try{

LoadOptions loadOptions1 = new LoadOptions(FileFormatType.EXCEL_97_TO_2003);

Workbook workbook1 = new Workbook(filepathA);

CellArea cellArea = new CellArea();

cellArea.StartRow = 0;

cellArea.EndRow = 50;

cellArea.StartColumn = 0;

cellArea.EndColumn = 255;

FindOptions opt = new FindOptions();

opt.setRange(cellArea);

opt.setRegexKey(true);

opt.setLookAtType(LookAtType.ENTIRE_CONTENT);

//test7 = test7+spaceRegex;

Cell c = workbook1.getWorksheets().get(0).getCells().find(test7,null, opt);

System.out.println("test");

}catch(Exception e){

e.printStackTrace();

}

}

Thanks & Regards,

Rohan

11

Hi,

Thanks for your sample code and test file.

I was able to notice the problem using your sample code with the latest version. Please verify the below findings if they are correct.

Input: String test7 = “close”;
Expected Output: Should be nothing

Input: String test8 = “.close.”;
Expected Output: Should be “at close of”

12

Thats right. But with regexKey set to true. Because my search string some times happens to be regex too.

13

Hi,

Thanks for your input.

Yes, the expected output will be valid only when you set the regexKey.

We have logged your comments in our database. We will look into it and fix the issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40259.

14

Hi,

When RegexKey is true, the searched key will be taken as standard regex and we check whether there is at least one match in one cell’s value for the regex. That is, regex expression “close” is same with “.close.” for finding match in cell’s value. To match entire content from start to end, please use the regex expression "^close$” instead.

For example, for following cells
:
cells.get(“A1”).setValue(“a close of”);
cells.get(“A2”).setValue(“close of”);
cells.get(“A3”).setValue(“a close”);
cells.get(“A4”).setValue(“close”);

with find regex “close” and “.close.”, four cells all will be found.
with find regex “^close”, “A2” and “A4” will be found.
with find regex “close$”, “A3” and “A4” will be found.
with find regex “^close$”, only “A4” can be found.

For your case, maybe you want to use “^close$” to get what you expected.

Thank you.