Cells find with custom formatting

Hi,


I’m having issues trying to use Cells.Find method with custom formatted cells. Please take a look at the attached sample for reproducing the error. Basically I’m formatting “0” valued cells to display “-” and the find doesn’t work any more. It was working on older versions of Aspose.Cells.

Regards,

Andrei

Hi,

Thanks for using Aspose.Cells.

Since you are looking for the value therefore you should change your line to this and it will work fine.

C#
foundCell = fileWorkbook.Worksheets[0].Cells.Find(" - ", foundCell, options);

In the previous versions of Aspose Cells this was working fine. You’re saying that if I have 20 ways of formatting a zero valued cell I should call find 20 times with different input string to accommodate all the possible outcomes of formatting? I’m sorry but that will not do as it breaks a lot of our code base and is now affecting our customers. Is there any Find overload that looks at the actual value instead of the formatted one?

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested this issue with some older versions and they are also not working fine. Could you please let me know the older version which is working fine. I will look into it and log this issue for a fix in our database.

Hi,


I’ve updated the sample to reflect the issue by adding another project which references Aspose Cells .NET 7.1.2.0 I’ve also added the input excel file we used to reproduce this along with a couple of code changes.

It seems i’m not able to update/upload a new zip file. Any idea why?

Please advise.

Regards,

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested lots of previous versions including 7.1.2.0 as well and your sample project is not working with any of them.

Could you please upload your working sample console application project with old Aspose.Cells dll on some file sharing site like dropbox.com and provide the download link here?

I am able to attach .zip file and did not find any issue. Could you please retry?

Hope this works


http://1drv.ms/23bQzOx

Hi,

Thanks for your useful sample projects and using Aspose.Cells.

We were able to observe this regression and logged it in our database for a fix. We will look into it and resolve this issue. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44182 - Cells find with custom formatting works in older version but not in newer version

Hi again,


This is to update you that we have resolved the problem logged earlier as CELLSNET-44182. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.

This is great and much appreciated but what does shortly mean in your system of reference?

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest version:

Aspose.Cells for .NET 8.7.0
your issue should be fixed with it.

The issue you filled is not listed in the release notes. An the issue is not fixed, please use the latest sample I’ve sent you a week ago to test.

Hi,

Thanks for your feedback and using Aspose.Cells.

You are right, I have tested the project with the latest version and found the issue is not fixed in 8.7.0 although the issue status is marked as resolved in database, so you will get the fix in 8.7.0.1 after few days. I have also logged the comment for it in the ticket CELLSNET-44182.

Hi,


We have evaluated your issue further.
For your information, when the “Look in” is “Formulas”, only then MS Excel could find “0” in your template file, so please change “options.LookInType = LookInType.Values;” to "options.LookInType = LookInType.Formulas;"

Let us know if you still have any issue.

Thank you.

Yes, I still have the same issue using the sample I’ve sent you. I find it odd you did not test on the sample I gave you. Can we please escalate this issue? It’s been a while since we filled it in and I can’t say there was any real progress. Facts are simple, this worked on previous versions, it doesn’t work now. Can’t your dev isolate it to a source file and then check what’s changed between these two versions in that source file?

Hi,


I have tested your scenario/ case using your sample code (mentioned below) with your template file, it works absolutely fine. Did you update your code segment a bit as suggested in my previous post. Also, please try our latest version/fix: e.g Aspose.Cells for .NET v8.7.0.1
e.g
Sample code:

var fileWorkbook = new Workbook(new MemoryStream(File.ReadAllBytes(“e:\test2\Excel_-PP.xlsx")));
var cellArea = new CellArea { StartColumn = 0, EndColumn = 4, StartRow = 0, EndRow = 0 };
var options = new FindOptions();
options.SetRange(cellArea);
options.SearchNext = true;
options.SeachOrderByRows = true;
options.LookInType = LookInType.Formulas;
options.LookAtType = LookAtType.EntireContent;
options.CaseSensitive = true;
Cell foundCell = null;
foundCell = fileWorkbook.Worksheets[0].Cells.Find(“0”, foundCell, options);
fileWorkbook.Worksheets[0].Cells.HideColumn(foundCell.Column);
fileWorkbook.Save("e:\test2\Excel - _PP_out.xlsx”);

I have also attached the output file for your reference. You may confirm it is working by opening into MS Excel, the first column is hidden that means your underlying cell is found and code is executed fine.

Let us know if you still have any issue.

Thank you.


I’m afraid you are not using the latest sample code I’ve sent. This is a code snippet from the last sample I’ve sent and there a couple of things different as well as the input file.


var fileWorkbook = new Workbook(new MemoryStream(File.ReadAllBytes(“TestFiles\excel65276552.xls”)));
fileWorkbook.CalculateFormula();
var cellArea = new CellArea { StartColumn = 27, EndColumn = 52, StartRow = 65, EndRow = 65 };
var options = new FindOptions();
options.SetRange(cellArea);
options.SearchNext = true;
options.SeachOrderByRows = true;
options.LookInType = LookInType.Formulas;
options.LookAtType = LookAtType.EntireContent;
options.CaseSensitive = true;
Cell foundCell = null;
object obj = 0;
foundCell = fileWorkbook.Worksheets[0].Cells.Find(obj, foundCell, options);
while (foundCell != null)
{
fileWorkbook.Worksheets[0].Cells.HideColumn(foundCell.Column);
foundCell = fileWorkbook.Worksheets[0].Cells.Find(obj, foundCell, options);
}

Hi,


Thanks for providing us further details.

You are right, I can still reproduce the issue using your other template file in your provided solution(s) with the following sample code:
e.g
Sample code:

var fileWorkbook = new Workbook(new MemoryStream(File.ReadAllBytes(“e:\test2\excel65276552.xls”)));
fileWorkbook.CalculateFormula();
var cellArea = new CellArea { StartColumn = 27, EndColumn = 52, StartRow = 65, EndRow = 65 };
var options = new FindOptions();
options.SetRange(cellArea);
options.SearchNext = true;
options.SeachOrderByRows = true;
options.LookInType = LookInType.Formulas;
options.LookAtType = LookAtType.EntireContent;
options.CaseSensitive = true;
Cell foundCell = null;
object obj = 0;
foundCell = fileWorkbook.Worksheets[0].Cells.Find(obj, foundCell, options);
while (foundCell != null)
{
fileWorkbook.Worksheets[0].Cells.HideColumn(foundCell.Column);
foundCell = fileWorkbook.Worksheets[0].Cells.Find(obj, foundCell, options);
}
fileWorkbook.Save(“e:\test2\out1.xls”);

In the output file the expected columns are not hidden which they should, so it is not finding those cells correctly. I have reopened your issue “CELLSNET-44182” again. Our concerned developer from product team will look into it soon.

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

Thank you.

Hi,

Thanks for using Aspose.Cells.

It seems that MS Excel could not find “0” if it is value of the formula and formatted as “-”.

If you find how to set find options in MS Excel to support your needs, please share us.

And we will add LookInType.OriginalValues to support your needs before that we need to know how to do it in MS Excel.

Hi,


I see you have a Value field for each cell. Why can’t you use that field for the search?

Regards,