getCells.findString to search regex

Hi,


Could you give details how could you perform Regex / your desired functionality in MS Excel (manually). If MS Excel provides this functionality, we will definitely log your feature request into our issue tracking system (internal) and support it as after all we follow MS Excel standards.

Thank you.

Hi, Actually MS Excel does not support searching with regex. But some APIs like JXL do. So i was thinking it shud have been there in Aspose.cells too. Its very handy when u have such type of requirements. Like i want to search a string abc .* xyz .* 123. then it shud search such string. ".*" can be any character sequence. And about that findStringEndsWith requirement, i want to search string like .*789. So i can use findStringEndsWith. But i want to search within a particular cellArea only. And the problem is unlike other findString methods, findStringEndsWith method doesnot take in that parameter cellArea.

Thanks & Regards,

Rohan Naik

Hi,


Thanks for providing further details.

I have logged a ticket for your feature request with an id: CELLSJAVA-40178. Once we have any update about it, we will let you know here.

Thank you.

Hi. Thanks. On my first post i am able to see this id i.e. CELLSJAVA-40178. But it also shows resolved status. So is the ticket resolved?

Hi,


Yes, we have supported this feature (regex e.g: search the strings in the range like: abc-*-xyz ) now. Our next fix will include this feature. We will provide the fix soon (within 2-3 working days hopefully).

Thank you.

Hi,

Please download and try this fix: Aspose.Cells for Java v7.2.0.4

We have supported regex in the string parameter to be searched, code like:

Java


Cells.findString(“abc*def”, null);


Hi, We have purchased the License copy of Aspose.cells. At that time we got 7.1.2 version of aspose-cells jar. So how do we go about this latest version now? Please guide. And also the findString method which takes cellArea as in parameter doesn't seem to have given this feature. Please provide it in that method too and all other findString methods. That would be great. Thanks.

Regards,

Rohan R. Naik

Hi,


Well, when you purchase a license, you are authorized to use this license file with any new ( and upcoming) versions / hot fixes of the component for the next whole year. You can open your license file into notedpad (without editing - If you do edit the license, it will not work either) and check the subscription expiry date for your knowledge. Moreover, you license will never expire if you use your license with any Aspose.Cells version/hot fix that should be released before your subscription expiry date.

In short, you may use the fix v7.2.0.4 without any problem.

For including regex feature in other overloads (e.g having CellArea), we have noted it down and we will consider it providing this feature in other overloads of find methods as well for your needs. Once we support it, we will let you know here.

Thank you.

Ok. Thanks.

Regards,

Rohan Naik

Hi,



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


We have supported find objects in range by new API: FindOptions.setRange(CellArea).

Please see the following code.

Java


FindOptions opt = new FindOptions();

opt.setRange(CellArea.createCellArea(“A1”, “A9”));

cells.find(findObj, prevCell, opt);


Hi, I am sorry to say but the regex search feature doesn't seem to work with this. Please just have a look and update if i am wrong. Thanks.

Regards,

Rohan Naik

Hi,


"I am sorry to say but the regex search feature doesn’t seem to work with this"
Kindly create a sample Java code and attach your template file to reproduce the issue on our end. Also try our new fix v7.2.0.5.

Thank you.

Hi. Please find the below code where i am using the find method to search string using regex. Also find the attached sample file. Thanks.

String filepathA = "C://FileA.xls";

String test = "*Close of*";

try{

LoadOptions loadOptions1 = new LoadOptions(FileFormatType.EXCEL_97_TO_2003);

Workbook workbook1 = new Workbook(filepathA,loadOptions1);

FindOptions opt = new FindOptions();

opt.setRange(CellArea.createCellArea("A8", "F8"));

Cell c5 = workbook1.getWorksheets().get(0).getCells().find(test, null, opt);

Hi,


Thanks for the file and sample code.

Please use findString() method instead, so you may change your code a bit as following:

String filepathA = “C://FileA.xls”;

String test = “Close of”;

try{

LoadOptions loadOptions1 = new LoadOptions(FileFormatType.EXCEL_97_TO_2003);

Workbook workbook1 = new Workbook(filepathA,loadOptions1);

FindOptions opt = new FindOptions();

opt.setRange(CellArea.createCellArea(“A8”, “F8”));

Cell c5 = workbook1.getWorksheets().get(0).getCells().findString(test, null, opt);


I have tested it with your file and it works fine.


Thank you.

Hi,

For searching string value in a specified range, especially for regex, please use corresponding Cells.findString(…) methods that with CellArea parameter instead of Cells.find() method. Currently Cells.find() method does not support regex and Cells.findString(String inputString, Cell previousCell,FindOptions findOptions) does not support specified range in FindOptions.

Currently Cells.find() method does not support regex and Cells.findString(String inputString, Cell previousCell,FindOptions findOptions) does not support specified range in FindOptions

Hi, In version v7.2.0.5 of Aspose.cells for java, FindOptions does provide FindOptions.createRange() method to specify the range. So i can use this right?

Thanks & Regards,

Rohan Naik

Hi,

We think you are talking about FindOptions.setRange() method. Yes, you can use it to specify a search range for Cells.find(Object, Cell, FindOptions) method. However, the specified range does not take effect for method findString(String, Cell, FindOptions) currently (So you cannot search a regex in a specified range with the current fix). We will try to make this method support for the specified range soon in later fixes.

Thank you.

Hi,

Yes indeed. Even if you specify range in FindOptions, it searches outside that range too. in short the whole sheet is searched even with provided range. Do let me know here when that findString method works fine. Thanks!

Regards,

Rohan Naik

Hi,

Thanks for your posting.

I have logged your comment in our database against the issue id: CELLSJAVA-40178

We will let you know once we will have some update for you.

Hi,


We have fixed this issue. Please download and try this latest fix of Aspose.Cells for Java V 7.2.0.7.

Thanks