getCells.findString to search regex

naikrohan · April 20, 2012, 9:57am

Hi, I am using findString method to search strings in a sheet. Now i want to search the regex. Is there any way to do it? e.g. i want to search a string abc-123-xyz. But my requirement is i just want to check if abc-*-xyz is present or not. The numbers 123 can be any numbers. Can i achieve this?

Thanks & Regards,

Rohan Naik

amjad.sahi · April 20, 2012, 12:35pm

Hi,

I think you may try to use the following relevant methods/overloads of the Cells class to find the string in chain of strings, e.g

findStringContains, findStringStartsWith, findStringEndsWith, etc.

Details about the methods:

findStringStartsWith

public Cell findStringStartsWith(java.lang.String inputString, Cell previousCell)

Finds the cell starting with the input string.

Returns null(Nothing) if no cell is found.

Parameters:

inputString - The string to search for.

previousCell - Previous cell with the same string. This parameter can be set to null if seaching from the start.

Returns:

Cell object.

findStringEndsWith

public Cell findStringEndsWith(java.lang.String inputString, Cell previousCell)

Finds the cell ending with the input string.

Returns null(Nothing) if no cell is found.

Parameters:

inputString - The string to search for.

previousCell - Previous cell with the same string. This parameter can be set to null if seaching from the start.

Returns:

Cell object.

findStringContains

public Cell findStringContains(java.lang.String inputString, Cell previousCell)

Finds the cell containing with the input string.

Returns null(Nothing) if no cell is found.

Parameters:

inputString - The string to search for.

previousCell - Previous cell with the same string. This parameter can be set to null if seaching from the start.

Returns:

Cell object.

findStringContains

public Cell findStringContains(java.lang.String inputString, Cell previousCell, boolean isCaseSensitive, CellArea area)

Finds the cell containing with the input string.

Returns null(Nothing) if no cell is found.

Parameters:

inputString - The string to search for.

previousCell - Previous cell with the same string. This parameter can be set to null if seaching from the start.

isCaseSensitive - Indicates if the searched string is case sensitive.

area - Searched area.

Returns:

Cell object.

findStringContains

public Cell findStringContains(java.lang.String inputString, Cell previousCell, boolean isCaseSensitive)

Finds the cell containing with the input string.

Returns null(Nothing) if no cell is found.

Parameters:

inputString - The string to search for.

previousCell - Previous cell with the same string. This parameter can be set to null if seaching from the start.

isCaseSensitive - Indicates if the searched string is case sensitive.

Returns:

Cell object.

Thank you.

naikrohan · April 23, 2012, 7:32am

Hi. Thanks. I can do it by using these methods. But that requires lot of coding to get to my requirements. It would have been great if aspose.cells supported regex search. Also while searching i want to use findStringEndsWith method to search in a particular range of cells. But this method does not seem to take CellArea parameter. Please suggest some workaround.

Thanks & Regards,

Rohan Naik

amjad.sahi · April 23, 2012, 8:49am

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.

naikrohan · April 23, 2012, 9:23am

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

amjad.sahi · April 23, 2012, 12:23pm

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.

naikrohan · April 26, 2012, 2:03am

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?

amjad.sahi · April 26, 2012, 3:28am

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.

shakeel.faiz · April 28, 2012, 2:24am

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);

naikrohan · May 3, 2012, 1:41am

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

amjad.sahi · May 3, 2012, 3:00am

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.

naikrohan · May 3, 2012, 3:21am

Ok. Thanks.

Regards,

Rohan Naik

shakeel.faiz · May 4, 2012, 6:06am

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);

naikrohan · May 4, 2012, 6:29am

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

amjad.sahi · May 4, 2012, 7:19am

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.

naikrohan · May 4, 2012, 8:23am

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);

amjad.sahi · May 4, 2012, 9:01am

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.

shakeel.faiz · May 6, 2012, 9:37pm

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.

naikrohan · May 7, 2012, 1:41am

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

amjad.sahi · May 7, 2012, 2:04am

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.