Worksheet.Cells.Find Cannot find date if formatted differently

Aspose.Cells Product Family
1

Hi,

I'm using sheet.Cells.Find("01/08/2011", Nothing, dateOptions) to search for dates in an excel file and it works fine when the date in the file are formatted the same way.

However if the date in excel is formatted a different way eg. 01-Aug-2011, then it cannot find the date even though the underlying cell value is 01/08/2011.

Why is this?

I'm using Aspose Cells 7.0.3.

Thanks,

Steve.

2

Hi,

Thanks for your posting and considering Aspose.Cells.

Please provide us your code sample and the sample input xls/xlsx file. Please also test your issue with the latest version:
Aspose.Cells for .NET v7.0.3.2
and let us know your feedback.

3

Hi,

I upgraded to 7.0.3.2 and it does not solve the problem. My software has had this issue for many versions but I've just worked around it by ensuring the files are formatted correctly. It's only now becoming a problem.

I've attached two excel files, the one that works ok ("OK.xls") and one that does not ("Fails.xls"). Changing to .xlsx does not make any difference.

I won't include all of my code because it doesn't really serve any purpose, but a simplified version is:

Dim dateCell As Cell
Dim dateOptions As New FindOptions()
Dim dateArea As New CellArea()

dateArea.StartColumn = startColumn
dateArea.StartRow = HeaderRow
dateArea.EndColumn = maxColumn
dateArea.EndRow = HeaderRow

dateOptions.SetRange(dateArea)
dateCell = sheet.Cells.Find("01/01/2010", Nothing, dateOptions)

Thanks,
Steve.

4

Hi,

I tried to find the date as mentioned in your post using Ms-Excel and I found, it also does not find the cell with this date.

Please check. Aspose.Cells work the same way as Ms-Excel.

Please see the screenshot.

5

Hi,

I can find it ok in excel, however I notice the format of dates in your screenshot is different due to your computer's regional settings. In Australia we use DD/MM/YYYY however if you look in your formula bar you will see yours is MM-DD-YYYY. If you search for 01-28-2010 I'm certain your search in excel would be successful in the 'Fails.xls' file even though the excel formatting is d-mmm-yyyy.

I have attached the screenshot of my successful excel search.

In my last post I also uploaded the wrong "OK.xls" file - my apologies. Please find the correct files attached.

Thanks,
Steve.

6

Hi,

Thanks for your screenshot and the feedback.

We have logged this issue in our database. We will help you with the code example asap if possible or update you.

This issue has been logged as CELLSNET-40139.

7

Hi,

Please use:

Cell cell = cells.Find(new DateTime(2010, 1, 1), null, dateOptions);